One of our clients found a bug with
dbms_scheduler: it logs a job as successful when a ORA-01403 is thrown.
SQL> create or replace procedure x_no_data_found is v_dummy varchar2(1); begin select dummy into v_dummy from dual where dummy != dummy; end; / Procedure created. SQL> exec x_no_data_found; BEGIN x_no_data_found; END; * ERROR at line 1: ORA-01403: no data found ORA-06512: at "JULIEN.X_NO_DATA_FOUND", line 4 ORA-06512: at line 1 SQL> begin 2 dbms_scheduler.create_job( 3 job_name => 'test_no_data_found', 4 job_type => 'plsql_block', 5 job_action => 'x_no_data_found;', 6 repeat_interval => 'freq=minutely', 7 enabled => true, 8 comments => ''); 9 end; 10 / PL/SQL procedure successfully completed. SELECT LOG_DATE, STATUS FROM DBA_SCHEDULER_JOB_RUN_DETAILS WHERE OWNER = 'JULIEN'; LOG_DATE STATUS 22-JAN-07 126.96.36.1998139 PM -05:00 SUCCEEDED 22-JAN-07 188.8.131.529485 PM -05:00 SUCCEEDED
Our current work-around is to throw an application error.
procedure x_no_data_found is v_dummy varchar2(1); begin select dummy into v_dummy from dual where dummy != dummy; exception when no_data_found then raise_application_error(-20001, 'No rows found at ....'); end; SELECT LOG_DATE, STATUS, ADDITIONAL_INFO FROM DBA_SCHEDULER_JOB_RUN_DETAILS WHERE OWNER = 'JULIEN' ORDER BY LOG_DATE DESC; LOG_DATE STATUS ADDITIONAL_INFO 22-JAN-07 184.108.40.206891 PM -05:00 FAILED ORA-20001: ORA-20001: No rows found at .... ORA-06512: at "JULIEN.X_NO_DATA_FOUND", line 10 ORA-06512: at line 1 22-JAN-07 220.127.116.11787 PM -05:00 SUCCEEDED 22-JAN-07 18.104.22.168921 PM -05:00 SUCCEEDED
According to the SR I opened with Oracle, it is a normal behavior since 22.214.171.124. Bug 5768299 says:
This is not a bug. Though no_data_found is an exception in plsql terms,it maps to ora-1403. When you select from a table/view where the criteria returns no rows you do not receive an error because SQL handles ora-1403 as a special case. The same is true when it occurs as a result of a function call. This has worked the same way for a very long time, eg the above testcase returns a single row with a null column in 126.96.36.199 right through to 11g; this is not a change in behaviour.
…but dbms_scheduler only exists since 10, and
dbms_jobs throws an error correctly:
SQL> variable v_job number; SQL> begin dbms_job.submit( job => :v_job, what => 'x_no_data_found;'); end; / 2 3 4 5 6 PL/SQL procedure successfully completed. SQL> exec dbms_job.run(:v_job); BEGIN dbms_job.run(:v_job); END; * ERROR at line 1: ORA-12011: execution of 1 jobs failed ORA-06512: at "SYS.DBMS_IJOB", line 406 ORA-06512: at "SYS.DBMS_JOB", line 272 ORA-06512: at line 1
We’re waiting to see what else Oracle has to say.
Update: Oracle was pointing us to unpublished bug #4552696 . They said a patch is available for 11 and offered to backport the patch.