Last updated on JULY 04, 2017
Applies to:
Oracle Database - Enterprise Edition - Version 11.2.0.2 and laterInformation 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
Sign In with your My Oracle Support account |
|
Don't have a My Oracle Support account? Click to get started |
My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms