Home » SQL & PL/SQL » SQL & PL/SQL » PL/SQL Stored Procedure Showing Oracle Error 27476 (Job Doesn't Exist) (Oracle 12c)
PL/SQL Stored Procedure Showing Oracle Error 27476 (Job Doesn't Exist) [message #680058] Mon, 20 April 2020 12:13 Go to next message
bmccollum
Messages: 8
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!
Re: PL/SQL Stored Procedure Showing Oracle Error 27476 (Job Doesn't Exist) [message #680059 is a reply to message #680058] Mon, 20 April 2020 12:45 Go to previous messageGo to next message
Michel Cadot
Messages: 67363
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Did you solve your problem in your previous topic?
If so, then post the root of it and its solution.

Please read the OraFAQ Forum Guide.

Re: PL/SQL Stored Procedure Showing Oracle Error 27476 (Job Doesn't Exist) [message #680063 is a reply to message #680059] Mon, 20 April 2020 14:43 Go to previous message
bmccollum
Messages: 8
Registered: April 2020
Junior Member
Have not received any suggestions or replies yet on this issue.
Previous Topic: RAISE_APPLICATION_ERROR not invoked by Logon trigger
Next Topic: Extracting specific portion of a string using REGEX
Goto Forum:
  


Current Time: Fri Sep 18 23:25:24 CDT 2020