DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: Stored Proc reference table outside schema

  1. #1
    Join Date
    Jan 2001
    Posts
    3

    Exclamation

    I am very new to Oracle and PL/SQL. I think I have a simple mistake here but don't know what I'm doing wrong.
    I'm trying to create a simple stored procedure. I am storing it in my schema(lew) but want to query tables in a schema called Oracle. This SQL statement works in SQL editor. But when I compile it I get the message PLS-00201: identifier 'ORACLE.PRODUCT' must be declared.

    CREATE OR REPLACE PROCEDURE LEWTESTPROC IS
    v_Code Oracle.Product.Product_Code%TYPE;
    v_Description Oracle.Product.Product_Description%TYPE;

    BEGIN
    SELECT Oracle.Product_Description, Oracle.Product_Code
    INTO v_Description, v_Code
    FROM Oracle.Product
    WHERE Oracle.Product_Code = 'T';

    EXCEPTION
    WHEN NO_DATA_FOUND THEN
    Null;
    WHEN OTHERS THEN
    Null;
    END LEWTESTPROC;

    THANKS FOR YOUR HELP

  2. #2
    Join Date
    Jan 2001
    Posts
    28
    ok, the error is in the statement Oracle.product
    when u use a xyz. in front of a table name, then xyz has to be the user name. If you use it before a field, then it has to be an alias name you have given to a table. for eg.

    select a, b
    from user1.table1
    where ....

    select a.fld1, b.fld1
    from table1 a, table2 b
    where .....

    I hope that clarifies the problem. Just remove the "oracle." from the procedure and it should work fine.
    - Mayur.

  3. #3
    Join Date
    Jan 2001
    Posts
    3
    My new code, still doesn't work. Same error message except now it points to the FROM clause. Other suggestions?

    CREATE OR REPLACE PROCEDURE LEWTESTPROC IS

    v_Code Varchar2(25);
    v_Description VarChar2(25);

    BEGIN
    SELECT P.Product_Description, P.Product_Code
    INTO v_Description, v_Code
    FROM Oracle.Product P
    WHERE P.Product_Code = 'T';

    EXCEPTION
    WHEN NO_DATA_FOUND THEN
    Null;
    WHEN OTHERS THEN
    Null;
    END LEWTESTPROC;

  4. #4
    Join Date
    Jan 2001
    Posts
    28
    Remove the Oracle. in the FROM clause and it should work fine. Remember Oracle is not a user unless you have created a user id like that.
    - Mayur.

  5. #5
    Join Date
    Jan 2001
    Posts
    3
    Actually we did name a schema as Oracle. I recreated all the tables that were in our Oracle schema into my own schema and then removed all references to Oracle from the stored procedure and it compiled fine. I think there must be some kind of permission problem with a stored procedure running outside the Oracle schema. THANK for your help

  6. #6
    Join Date
    Jan 2001
    Posts
    515

    You need rights to that table

    What you needed to do was have the oracle owner grant you rights to select on his table.

    grant select on product to lew;
    You procedure would then 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
  •  


Click Here to Expand Forum to Full Width