5

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 :)

OMG Ponies
  • 325,700
  • 82
  • 523
  • 502
PeterP
  • 4,502
  • 7
  • 22
  • 21

3 Answers3

9

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>;
cagcowboy
  • 30,012
  • 11
  • 69
  • 93
  • Same here... I am using just one user for all those things, thus, I think there should not be any permission constraints... or should there? – PeterP Jun 09 '09 at 08:51
  • 1
    thanks, i think you were right... cannot verify however, since i am not in a position to grant these rights.... however, i found out that user_scheduler_running_jobs also gives me the information I need :) – PeterP Jun 09 '09 at 12:28
5

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
Vincent Malgrat
  • 66,725
  • 9
  • 119
  • 171
  • Thanks for your good explanations, sounds very reasonable. Unfortunately I do not have permission to grant select in this database... However, I found that user_scheduler_running_jobs also gives me the information I need :) Thanks a lot. – PeterP Jun 09 '09 at 12:29
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

Rob van Laarhoven
  • 8,737
  • 2
  • 31
  • 49
  • No, the procedure is created, owned and executed by the very same user I use to connect with SQLdeveloper... Thus, I think the permission should be the same in console and procedure. Or is there a basic misunderstanding in my perception of oracle permissions? – PeterP Jun 09 '09 at 08:49
  • Please read this, the other answers are probably pointing you in the right direction (grants to roles are not used in stored procedures): http://www.builderau.com.au/program/oracle/soa/Understanding-roles-in-Oracle-stored-procedures/0,339028441,339288998,00.htm – Rob van Laarhoven Jun 09 '09 at 11:36