Create VIEW problem
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: Create VIEW problem

  1. #1
    Join Date
    Jun 2001
    Location
    Dublin, Ireland
    Posts
    66

    Question Create VIEW problem

    Any explanation for the following would be much appreciated.

    I have two users A and B, both with their own schemas.

    if I execute this sql (as UserA)

    create table test_table
    as select account_no,
    period
    from userB.accounts
    where period = last_day(to_date('31-OCT-2002')-170)

    No problem, Table created.

    But if I execute this sql

    create view test_view
    as select account_no,
    period
    from userB.accounts
    where period = last_day(to_date('31-OCT-2002')-170)

    Then I get an error

    from userB.accounts
    *
    ERROR at line 6:
    ORA-00942: table or view does not exist

    I can overcome this by granting SELECT ANY TABLE to UserA, but I realy don't want to give this privilege to this user.
    Why will oracle let me create a table in userA schema based on a select from a table in userB schema, but wont let me create a view based on the same select statement?




    Share on Google+

  2. #2
    Join Date
    Aug 2001
    Location
    Waterloo, On
    Posts
    547
    Preplexing!!

    Raminder Singh

    Oracle Certified DBA: Oracle 8i, 9i


    Mail me at raminderahluwalia@rediffmail.com.
    Share on Google+

  3. #3
    Join Date
    Jun 2001
    Location
    Dublin, Ireland
    Posts
    66
    btw Oracle version is 8.0.6

    I have tried the above statement while granting CREATE VIEW and CREATE ANY VIEW to userA but still get the same error. Only granting SELECT ANY TABLE seems to work.
    Share on Google+

  4. #4
    Join Date
    Jun 2001
    Location
    Dublin, Ireland
    Posts
    66
    Sorry guys,
    It seems to be working now with just CREATE VIEW grant. I dont know how it didnt work earlier.
    Share on Google+

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width