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 12.17.49.768139 PM -05:00	SUCCEEDED
22-JAN-07 12.18.49.849485 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 12.36.49.139891 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 12.35.49.104787 PM -05:00	SUCCEEDED
22-JAN-07 12.34.49.107921 PM -05:00	SUCCEEDED

According to the SR I opened with Oracle, it is a normal behavior since 8.1.7.4. 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 8.1.7.4 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.

You can follow any responses to this post through the RSS 2.0 feed. You can leave a response, or trackback from your own site.