Tutorial :Oracle 'identifier myschema.mytable must be declared'



Question:

Trying to run some sql in a pl/sql procedure.

Select field from schema.view;

I get a compile error

Error(22,18): PLS-00201: identifier 'schema.view' must be declared

From the error, it seems that my user does not have access to the table. I can run the same statement in a query window.

Is there a permission I need to grant?

Thanks!


Solution:1

You need to grant the user running the procedure explicit permission on the table/view. Granting it through a role will not work.

Regards K


Solution:2

Yes. Oracle doesn't consider permissions that you have from role membership when you are executing procedures, so it is likely that the user who owns the procedure was granted access to schema.view via a role. You need to explicitly grant permissions for that object to the owner of the procedure.


Solution:3

The PLS-00201 exception seems a bit unusual to me, for the "privileges granted through a role not available in a stored procedure" problem. As Steve Broberg and Khb have already pointed out, granting privileges directly to a user will resolve exception

ORA-00942: table or view does not exist.  

(That's the exception I normally see when compiling a stored procedure, when the statement works outside the stored procedure, and i find that privileges are granted through roles.)

What's kind of peculiar is that the exception you are seeing is a PLS-00201 (That has me puzzled.)


Another workaround to the ORA-942 "no privileges through roles" issue is to define the procedure with invoker rights and use dynamic SQL:

create procedure foo authid current_user  is    ln_cnt number;  begin    execute immediate 'select cnt(1) from schema.view' into ln_cnt;  end;  /  

I don't think this is the best approach (it has its own issues) but it is a workaround.

http://download.oracle.com/docs/cd/B28359_01/appdev.111/b28370/dynamic.htm


Note:If u also have question or solution just comment us below or mail us on toontricks1994@gmail.com
Previous
Next Post »