Dba_jobs_running: table or view does not exist when trying to access a procedure

Question

Just requesting current assignments using something like

select * from dba_jobs_running; 

works great when sqldevelopers executes in my SQL console.

However, this will not work if the procedure has exactly the same operator. Compilation failed with

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

Any ideas? Is there something like an area to consider?

Any suggestions are welcome, thanks in advance :)

Answer 1

You probably need to do a direct GRANT of DBA_JOBS_RUNNING to the user who owns this procedure. Doing GRANT with a role will not work ... the grant must be explicit.

EDIT:

Performing SELECT using a procedure requires subtle different permissions to execute SELECT outside the procedure (for example, in SQL-Developer). The user who owns the procedure must explicitly grant rights to the table or view ... if the query is executed from outside the view, this is not so (you may be granted 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 2

Procedures are performed without roles. One way to check if a command can be executed 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 must provide the choice in the view directly to the user:

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

Then you can 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 

Answer 3

Is the procedure owned by another user? If so, check out: Definer and Invoker Rights for stored procedures in the PL / SQL manual.

Rob