PL/SQL Stored Procedure Showing Oracle Error 27476 (Job Doesn't Exist) [message #680058] |
Mon, 20 April 2020 12:13 |
|
bmccollum
Messages: 15 Registered: April 2020
|
Junior Member |
|
|
I have a stored procedure, shown below:
CREATE OR REPLACE PACKAGE BODY ILSCOMMON.pkgjobraiseeventsassign AS
PROCEDURE spraiseeventssuccessandfailure
IS
BEGIN
FOR r_record IN (select *
from dba_scheduler_jobs
where owner not in ('SYS', 'ORACLE_OCM')
and raise_events is null) LOOP
dbms_output.put_line(r_record.owner || ': $' || r_record.job_name);
sys.dbms_scheduler.set_attribute(name => '"' || r_record.owner || '"' || '.' ||
'"' || r_record.job_name || '"',
attribute => 'raise_events',
value => dbms_scheduler.job_succeeded +
dbms_scheduler.job_failed);
END LOOP;
exception
when others then
raise;
end;
END pkgjobraiseeventsassign;
I'm basically wanting to cycle thru the dba_scheduler_jobs table, determine what values meet the above criteria and have null in the "raise_events" column, and update the values in those rows to "dbms_scheduler.job_succeeded + dbms_scheduler.job_failed".
I've had some databases where I've executed the above stored procedure and it's identified and updated the appropriate records just fine.
Occasionally though with a few databases that this stored procedure is executed against, it's returning an Oracle Error of 27476, informing me that job such-and-such doesn't exist. However, when I run the following query (below), the job absolutely DOES exist.:
select *
from dba_scheduler_jobs
where owner not in ('SYS', 'ORACLE_OCM')
and raise_events is null
The odd twist is that if I run something such as the following (below) for a specific job that the stored procedure tells me doesn't exist (which DOES actually exist), the updating of the "raise_events" attribute takes place just fine without error.:
sys.dbms_scheduler.set_attribute( name => '"ODS"."TBM044_INSERTS"', attribute => 'raise_events', value => dbms_scheduler.job_succeeded + dbms_scheduler.job_failed);
Anyone have any thought on this at all? I have no idea why the dynamic cycling-thru of jobs in the stored procedure tells me a few jobs aren't there, when in reality they are, but the directly-executed statements such as the one I posted before this paragraph run without issue.
Thank you!
|
|
|
|
|