DBMS_SCHEDULER Is Suppressing NO_DATA_FOUND Exceptions for Jobs that Execute Stored Procedures
(Doc ID 1331778.1)
Last updated on FEBRUARY 23, 2019
Applies to:
Oracle Database - Enterprise Edition - Version 11.2.0.2 and laterOracle Database Cloud Schema Service - Version N/A and later
Oracle Database Exadata Express Cloud Service - Version N/A and later
Oracle Database Exadata Cloud Machine - Version N/A and later
Oracle Cloud Infrastructure - Database Service - Version N/A and later
Information in this document applies to any platform.
Symptoms
NO_DATA_FOUND exceptions for jobs that execute Stored Procedures being suppressed by DBMS_SCHEDULER.
E.g.:
SQL> CREATE OR REPLACE PROCEDURE p AS
BEGIN
RAISE NO_DATA_FOUND;
END;
/
Procedure created.
SQL> BEGIN
DBMS_SCHEDULER.CREATE_JOB (
job_name => 'TEST',
job_type => 'STORED_PROCEDURE',
job_action => 'p',
enabled => true
);
EXCEPTION WHEN OTHERS THEN
RAISE;
END;
/
PL/SQL procedure successfully completed.
SQL> SELECT log_date, job_name, error#, status
FROM user_scheduler_job_run_details
WHERE job_name = 'TEST';
LOG_DATE JOB_NAME ERROR# STATUS
------------------------------------ ---------- -------- ---------
10-JUN-11 10.18.45.698000 PM +00:00 TEST 0 SUCCEEDED
SQL> CREATE OR REPLACE PROCEDURE p AS
BEGIN
RAISE NO_DATA_FOUND;
END;
/
Procedure created.
SQL> BEGIN
DBMS_SCHEDULER.CREATE_JOB (
job_name => 'TEST',
job_type => 'STORED_PROCEDURE',
job_action => 'p',
enabled => true
);
EXCEPTION WHEN OTHERS THEN
RAISE;
END;
/
PL/SQL procedure successfully completed.
SQL> SELECT log_date, job_name, error#, status
FROM user_scheduler_job_run_details
WHERE job_name = 'TEST';
LOG_DATE JOB_NAME ERROR# STATUS
------------------------------------ ---------- -------- ---------
10-JUN-11 10.18.45.698000 PM +00:00 TEST 0 SUCCEEDED
If the JOB_TYPE is PLSQL_BLOCK, the job will show as failed:
E.g.
SQL> BEGIN
DBMS_SCHEDULER.CREATE_JOB (
job_name => 'TEST2',
job_type => 'PLSQL_BLOCK',
job_action => 'RAISE NO_DATA_FOUND;',
enabled => true
);
EXCEPTION WHEN OTHERS THEN
RAISE;
end;
/
PL/SQL procedure successfully completed.
SQL> SELECT log_date, job_name, error#, status
FROM user_scheduler_job_run_details
WHERE job_name = 'TEST2';
LOG_DATE JOB_NAME ERROR# STATUS
------------------------------------ ---------- -------- ---------
10-JUN-11 10.08.51.517000 PM +00:00 TEST2 1403 FAILED
SQL> BEGIN
DBMS_SCHEDULER.CREATE_JOB (
job_name => 'TEST2',
job_type => 'PLSQL_BLOCK',
job_action => 'RAISE NO_DATA_FOUND;',
enabled => true
);
EXCEPTION WHEN OTHERS THEN
RAISE;
end;
/
PL/SQL procedure successfully completed.
SQL> SELECT log_date, job_name, error#, status
FROM user_scheduler_job_run_details
WHERE job_name = 'TEST2';
LOG_DATE JOB_NAME ERROR# STATUS
------------------------------------ ---------- -------- ---------
10-JUN-11 10.08.51.517000 PM +00:00 TEST2 1403 FAILED
Cause
To view full details, sign in with your My Oracle Support account. |
|
Don't have a My Oracle Support account? Click to get started! |
In this Document
| Symptoms |
| Cause |
| Solution |
| References |