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

Question

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

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>;