CODEWITHSUNDEEP
oracle-databasestored-proceduresplsqlexceptionjobs
xslguy
xslguy

Reputation: 91

Pl Sql Procedure handle exception while scheduled using oracle scheduler

I have written a procedure which will be scheduled using Oracle scheduler, and am trying to handle an exception using the following exception block:

EXCEPTION
   WHEN OTHERS THEN
      raise_application_error(-20001,'An error was encountered - '
      || SQLCODE || ' -ERROR- ' || SQLERRM);
END;

I also want to make sure if I use above support team or DBA come to know in case of any exception and they can take appropriate action as well as all the transactions should be rolled back.

Upvotes: 0

Views: 2756

Answers (1)

Frank Schmitt
Frank Schmitt

Reputation: 30765

If the only reason for the exception block is to notify the support team - don't do it, since it's completely superfluous.

Oracle logs the outcome of every job run in the views XX_SCHEDULER_JOB_RUNS / XX_SCHEDULER_JOB_RUN_DETAILS, where XX is one of DBA/ALL/USER (User contains all runs for the currently logged-on user, All contains all runs the currently logged-on user is allowed to see, and DBA (which requires special privileges) contains all runs in the database.

So all your support team has to do is monitor XX_SCHEDULER_JOB_RUN_DETAILS and check for any entries with status FAILURE.

Example

Create a job that always fails, and run it once:

begin
  dbms_scheduler.create_job(
      job_name => 'JOB_RAISE_DEMO'
     ,job_type => 'PLSQL_BLOCK'
     ,job_action => 'begin raise_application_error(-20001, ''custom error''); end; '

     ,start_date      => to_timestamp_tz('2015-11-20 13:00:00 Europe/Berlin',
                                         'yyyy-mm-dd hh24:mi:ss tzr')
     ,repeat_interval => null
     ,enabled => TRUE
     ,auto_drop => false);
end;

begin
  dbms_scheduler.run_job('JOB_RAISE_DEMO', use_current_session => false);
end;

Then, check the job status:

select log_date, job_name, status, error# 
from user_scheduler_job_run_details 
where job_name = 'JOB_RAISE_DEMO';

This returns:

LOG_DATE                        JOB_NAME        STATUS  ERROR#
20.11.15 12:35:53,516000 +01:00 JOB_RAISE_DEMO  FAILED  20001

Upvotes: 3

Related Questions