Friday, September 05, 2008

ORA-00942: table or view does not exist -> within a stored procedure

Today I read "ORA-00942: table or view does not exist" error within a stored procedure -> metalink (Note:391068.1) + (Note:168168.1)

That talked about when we grant select table(A) on user1 to some role(r_A) and then grant that role(r_A) to user2. user2 can query that table(A) by SQL statement [select * from user1.A],

But found ORA-00942 when use that table(A) on stored procedure.

If user2 need to use that table(A) on stored procedure, user1 must grant directly to user2.

Roles are not meant to be used by application developers, because the privileges to access schema objects within stored programmatic constructs must be granted directly.


SQL> connect / as sysdba


SQL> grant create role to scott;

Grant succeeded.

SQL> connect scott/tiger


SQL> create table foo (id number);

Table created.

SQL> insert into foo values(1);

1 row created.

SQL> commit;

Commit complete.

SQL> create role rl_foo;

SQL> grant all on foo to rl_foo;

Role created.

SQL> grant rl_foo to testuser;

Grant succeeded.

SQL> connect testuser/testuser
SQL> select * from;


SQL> create procedure test is
2 my_values;
3 cursor c1 is
4 select id from;
5 begin
6 open c1;
7 fetch c1 into my_values;
8 close c1;
9 end;
10 /

Warning: Procedure created with compilation errors.

SQL> show error

-------- -----------------------------------------------------------------
2/13 PL/SQL: Item ignored
2/13 PLS-00201: identifier 'SCOTT.FOO' must be declared
4/5 PL/SQL: SQL Statement ignored
4/26 PL/SQL: ORA-00942: table or view does not exist
7/3 PL/SQL: SQL Statement ignored
7/17 PLS-00320: the declaration of the type of this expression is
incomplete or malformed

try to test with create view=>

SQL> create view test2 as select * from;

create view test2 as select * from
ERROR at line 1:
ORA-01031: insufficient privileges

So, logon scott and then:

SQL> grant all on foo to testuser;

Grant succeeded.

and then recreated again on testuser

SQL> create or replace procedure test is
cursor c1 is
select id from;
open c1;
fetch c1 into my_values;
close c1;

Procedure created.

try to test with view again:

SQL> create view test2 as select * from;

View created.


No comments: