dba_jobs_running: table or view does not exist when trying to access from procedure

Question

Hi Folks, Simply querying running jobs using something like

select * from dba_jobs_running;

works fine when executed in my sqldevelopers SQL console.

However, it does not work, when having exactly the same statement within a procedure. Compilation fails with

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

Any ideas? Is there something like a scope to be considered?

Any suggestions are highly appreciated, thanks in advance :)

Answer 1

Is procedure owned by another user? If so have a look at: Definer and Invoker Rights for stored routines in PL/SQL manual.

Rob

Answer 2

You probably need to do a direct GRANT of DBA_JOBS_RUNNING to the user that owns the procedure. Doing a GRANT via a role won't work.... the grant needs to be explicit.

EDIT:

Doing a SELECT from within a procedure requires subtly different permissions to doing a SELECT from outside a procedure (e.g. in SQL-Developer). The user that owns a procedure must have been explicitly granted rights to the table or view... if running a query from outside a view this is not the case (you can be granted the permission through a role for example)

You need to connect as SYS and go:

GRANT SELECT ON SYS.DBA_JOBS_RUNNING TO <user-that-owns-proc>;

Answer 3

Procedures are executed without roles. One way to see if you can run a command in a procedure is to execute:

SQL> set role none;

Role set

You will have the same set of rights as your procedures:

SQL> SELECT * FROM dba_jobs_running;

SELECT * FROM dba_jobs_running

ORA-00942: table or view does not exist

You have to grant select on the view directly to the user:

SQL> -- with dba account
SQL> grant select on dba_jobs_running to a;

Grant succeeded

You will then be able to compile the procedure:

SQL> -- with application schema
SQL> CREATE OR REPLACE PROCEDURE test_dba AS
  2  BEGIN
  3     FOR cc IN (SELECT * FROM dba_jobs_running) LOOP
  4        NULL;
  5     END LOOP;
  6  END test_dba;
  7  /

Procedure created