Roles , PL/SQL, and ORA-00942: table or view does not exist

articles: 

Many developers find that their code fails with ORA-942, but when they test the failing statement from the SQL> prompt, it works. Why, and what can you do to fix it?

Consider this example:

orclz>
orclz> drop user jw cascade;

User dropped.

orclz> grant dba to jw identified by jw;

Grant succeeded.

orclz> conn jw/jw
Connected.
orclz> delete from scott.emp;

14 rows deleted.

orclz> rollback;

Rollback complete.

orclz>

The DELETE works. Now try it in a procedure:
orclz>
orclz> create procedure p1 as begin
  2  delete from scott.emp;
  3  end;
  4  /

Warning: Procedure created with compilation errors.

orclz> sho err
Errors for PROCEDURE P1:

LINE/COL ERROR
-------- -----------------------------------------------------------------
2/1      PL/SQL: SQL Statement ignored
2/19     PL/SQL: ORA-00942: table or view does not exist
orclz>

The code won't even compile, never mind run, because the procedure cannot see the table. Why not? Because the necessary privilege was granted through a role. To prove this:
orclz>
orclz> set role none;

Role set.

orclz> delete from scott.emp;
delete from scott.emp
                  *
ERROR at line 1:
ORA-00942: table or view does not exist


orclz> set role dba;

Role set.

orclz> delete from scott.emp;

14 rows deleted.

orclz> rollback;

Rollback complete.

orclz>

Why can't PL/SQL use roles? Because PL/SQL is a statically compiled language, whereas roles are dynamic: they can be enabled or disabled at any time. It is impossible for the compiler to know at compilation time what roles might or might not be enabled at runtime, so it has to rely only on direct grants of privileges. A privilege is always going to be available. If it were revoked, the PL/SQL would be invalidated and have to be recompiled. So the solution is to grant the necessary privilege, and then the code will work:
orclz>
orclz> grant delete on scott.emp to jw;

Grant succeeded.

orclz> conn jw/jw
Connected.
orclz> create or replace procedure p1 as begin
  2  delete from scott.emp;
  3  end;
  4  /

Procedure created.

orclz> select count(*) from scott.emp;

  COUNT(*)
----------
        14

orclz> exec p1

PL/SQL procedure successfully completed.

orclz> select count(*) from scott.emp;

  COUNT(*)
----------
         0

orclz>

I've described the most common PL/SQL security usage, which uses the definer's rights security model. There are more sophisticated ways to manage this, particularly in release 12. Next time....

--
John Watson
Oracle Certified Master DBA
http://skillbuilders.com