7 Replies Latest reply: Dec 31, 2012 1:28 PM by krissco RSS

    Querying user_jobs within a dbms_job

      Oracle 9iR2.

      I use dbms_job to schedule recurring reports to be delivered. The basic architecture:
      Each report has its own query and set of parameters. The parameters are saved in a table.
      The reports are scheduled via dbms_job (each "WHAT" is my_reporting_package.execute_report(JOB);).
      When the job executes, it queries my parameter table, executes the query, transforms the results into HTML,XML,CSV (whatever the user selected).

      My issue is that I would like to monitor failing and broken jobs using the same software. I define a report which is something like:
      select * 
      from user_jobs
      where failures > 0;
      I keep getting a deadlock, or something similar. I think the problem is trying to query user_jobs within the context of a dbms_job.

      Is there a safe way to query user_jobs within the context of a dbms_job?