DBMS_JOB and ALL_* data dictionary views (Oracle 9i)

Hi,

I seem to be having a problem with a procedure I am running through the DBMS_JOBs.
The procedure I have written is designed to examine the state of the triggers is a given database schemas. If any of the triggers have errors then it raises an email and rechedules another check of the triggers in x number of minutes.

The procedure is designed to be ran through the SYSTEM user, so I use the ALL_TRIGGERS and ALL_OBJECTS data dictionary views. For example, these are two of the cursors I use...

    CURSOR cur_uncompiled(pOwner all_objects.owner%TYPE) IS

      SELECT object_name
        FROM all_objects
       WHERE UPPER(status) = 'INVALID'
         AND UPPER(owner) = UPPER(pOwner)
         AND UPPER(object_type) = 'TRIGGER';

    CURSOR cur_disabled(pOwner all_triggers.owner%TYPE) IS
      SELECT trigger_name
        FROM all_triggers
       WHERE UPPER(owner) = UPPER(pOwner)
         AND UPPER(status) = 'DISABLED';

I have been testing the procedure on a schema I know has broken triggers. I initially start the procedure by running it through SQLplus logged in as System. Everything runs okay.. I recieve the email and another run of the procedure is scheduled in the DBMS_JOB queue with LOG_USER, PRIV_USER and SCHEMA_USER set to SYSTEM. After x mins this runs, but I recieve no email and the procedure is not rescheduled into the queue!

Through investigation I have discovered that the problem is that the SELECTS from ALL_TRIGGERS and ALL_OBJECTS are returning no results when ran through DBMS_JOBS - even through they are scheduled to be run as the same user.

Is this a bug in Oracle, or am I missing something? Has anyone got any alternative solutions? Are DBA_TRIGGERS and DBA_OBJECTS more reliable? Unfortunately, I cannot login as the SCHEMA and use USER_TRIGGERS and USER_OBJECTS.

Any suggestions welcome...

Thanks

Iain Received on Fri Feb 23 2007 - 03:38:13 CST