Results 1 to 5 of 5
  1. #1
    Join Date
    Apr 2004
    Posts
    15

    Unanswered: CREATE VIEW gives error but the SELECT statement in the same query works fine

    Hi,
    I have a query:

    CREATE OR REPLACE VIEW abcview
    (Col1, Col2) AS
    SELECT a.C1, b.C2
    FROM a, b
    WHERE a.ID = b.ID;

    This query gives me error that Table or View Does not Exists on
    table 'b'. But if I just run the SELECT statement it gives me
    correct results. Has anyone came across this type of problem?

    Bhargav

  2. #2
    Join Date
    Jun 2004
    Posts
    7
    My guess would be that tables 'a' and 'b' are in different schemas to the schema that the view is being created in.

    The select statement may be accessing the tables through a synonym, which may case a problem in the view. Try specifying the full name <schema 1.a> and <schema 2>.b in the from clause.

    Alternatively, the user executing the select may be granted select priviedges via a role. This probably won't work for the view (it doesn't for PL/SQL) which would need the user to be directly granted select on the two tables.

  3. #3
    Join Date
    Apr 2004
    Posts
    15
    I tried specifying full names (with schemaname) in the from clause, still
    getting the same error. By the way, two tables in the from clause are
    from two different schema, and the view I am creating is for one of those
    two schema. Any suggestion?

    Thanks
    Bhargav

  4. #4
    Join Date
    Jul 2003
    Posts
    2,296
    your syntax is wrong.
    PHP Code:
    CREATE OR REPLACE VIEW abcview AS
    SELECT a.C1b.C2
      FROM a
    b
        WHERE a
    .ID b.ID
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  5. #5
    Join Date
    Jun 2004
    Posts
    7
    The syntax looks OK to me. I suspect that its a privileges issue.

    If table A is in schema SA and table B in in schema SB and you are creating the view in SA - log on as the owner of SB (or system) and type

    grant select on B to SA; (Assuming that the owner of SA is the user SA)

    Then log back as the owner of SA and try and create the view again.

    PS I was wrong about the synonyms, they should work fine.

Posting Permissions

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