Hi all:
How does DBMS_SCHEDULER handle exceptions coming from calling a STORED_PROCEDURE?
This question is raised as I would like to set up email notifications during a JOB_FAILED event from the scheduler.
If this is my test procedure:
CREATE OR REPLACE PROCEDURE SYS.FAILTEST AS
excpt EXCEPTION;
BEGIN
RAISE excpt;
EXCEPTION
WHEN excpt THEN
DBMS_OUTPUT.PUT_LINE('exception!');
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('others!');
END;
/
My test job:
BEGIN
DBMS_SCHEDULER.CREATE_JOB (
job_name => 'JOB_FAILTEST',
job_type => 'PLSQL_BLOCK',
job_action => 'BEGIN SYS.FAILTEST; END;',
start_date => SYSTIMESTAMP,
repeat_interval => 'FREQ=MONTHLY;INTERVAL=1;bymonthday=1;byhour=1;byminute=0;bysecond=0',
end_date => NULL,
enabled => TRUE);
END;
/
However, when the above job is run, the status is returned as SUCCESS (from DBA_SCHEDULER_JOB_RUN_DETAILS), even though there was an EXCEPTION thrown:
BEGIN DBMS_SCHEDULER.RUN_JOB(JOB_NAME => 'JOB_FAILTEST'); END;
/
Out:
exception!
PL/SQL procedure successfully completed.
How can I propagate the except from the stored procedure to trigger a scheduler JOB_FAILED event?