How to use DBA_JOBS in procedure

Question

I want to make sendmsg when deadjob. So I need DBA_JOBS select in procedure How can i use DBA_JOBS in procedure I trying for this code :

SELECT COUNT(*)
  FROM DBA_JOBS
 WHERE JOB in('539','639','679','719','919','1359');

But this error block my new procedure

ORA-00942 table or view does not exist

Answer 1

You need to explicitly give read permission to this view.

oracle@esmd:~> sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Tue Dec 4 08:15:47 2018

Copyright (c) 1982, 2011, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Release 11.2.0.3.0 - 64bit Production

SQL> grant select on dba_jobs to scott;

Grant succeeded.

SQL>

Answer 2

You do not need to be granted for dba_jobs view that's needed for the usage through the whole database. Use user_jobs view instead :

SELECT COUNT(*) as count
  FROM USER_JOBS
 WHERE JOB in('539','639','679','719','919','1359');