DBMS_SCHEDULER Is Suppressing NO_DATA_FOUND Exceptions for Jobs that Execute Stored Procedures (Doc ID 1331778.1)

Last updated on JULY 04, 2017

Applies to:

Oracle Database - Enterprise Edition - Version 11.2.0.2 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


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

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