No data found -> success in dbms_scheduler

Posted in: Technical Track

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.

Interested in working with Julien? Schedule a tech call.

About the Author

Julien Lamarche is a DBA with The Pythian Group since 2006. He graduated from the University of Ottawa with Magna Cum Laude (B.A.Sc. Software Engineering, 2004). In the past, he has added multilingual capabilities to the weblets sourceforge project as well as collaborated with OpenConcept.ca on their CMS back-end.Outside of Pythian life, Julien works with the Green Party of Canada Ottawa-Vanier Greens.In 2005, Julien went accross Canada with 12 other cyclists for the environmental education NGO The Otesha Project.Julien's Pythian blog posts can be viewed at http://www.pythian.com/blogs/author/lamarche/

4 Comments. Leave new

Julien, initial conclusions of this support engineer reminded me the highly logical speeches of Captain Jack Sparrow. Was his name Johnny Depp by any chance?

Reply
William Robertson
February 2, 2007 8:24 am

That “special case” argument is a bit weaselly. Yes, a PL/SQL function will have NO_DATA_FOUND exceptions implicitly trapped when called as part of a query, but it’s a bit of a leap to say that DBMS_SCHEDULER should do the same thing when executing procedures. They just made that up.

Reply
Alex Gorbachev
February 2, 2007 9:22 am

Julien, initial conclusions of this support engineer reminded me the
highly logical speeches of Captain Jack
Sparrow
. Was his name Johnny Depp by any chance?

Reply

>> initial conclusions of this support engineer reminded me the
>> highly logical speeches of Captain Jack Sparrow.

Actually it’s more like Captain Barbossa:

‘First, your return to shore was not part of our negotiations nor our agreement, so I must do nothin’. And secondly, you must be a pirate for the Pirate’s Code to apply, and you’re not. And thirdly, the Code is more what you’d call “guidelines” than actual rules.’

Cheers, APC

Reply

Leave a Reply

Your email address will not be published. Required fields are marked *