-
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
-
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.
-
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;
-
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.
-
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
-
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|