Oracle 'identifier myschema.mytable must be declared'

Question

Hi,

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!

Answer 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

Answer 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.

Answer 3

This is a good explanation of the issue...

http://stackoverflow.com/questions/968857/dbajobsrunning-table-or-view-does-not-exist-when-trying-to-access-from-procedu/969140#969140

Answer 4

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