CODEWITHSUNDEEP
oracle-databaseexceptionscheduler
Gella
Gella

Reputation: 35

Oracle no_data_found exception is not propagated into the scheduler

We have discovered, to our dismay, that no_data_found exceptions are not propagated into the Oracle scheduler.

If you have a procedure that does select x into my_var and the select fails with no_data_found, the procedure will fail, but the scheduler program running this procedure will still succeed, as will the chain and the job.

I was wondering if no_data_found is the only exception affected in this way, or if there are others?

In addition, the only solution we came up with so far is to catch no_data_found within the procedure itself and raise a user-defined exception instead. It does work, but it means that if we want to fail-proof our code we would have to go into every single procedure and introduce this exception handling block.

Can you think of a solution that would not involve code change? Maybe a setting at the database level?

Please see the code below

--------------procedure

create procedure test as
 l_var number;
 begin
   select 0 
     into l_var 
     from dual 
    where 1 = 0; 
 end;
 /

--------------program

BEGIN
  dbms_scheduler.create_program (
    program_name   => 'PRG_TEST',
    program_type   => 'STORED_PROCEDURE',
    program_action => 'TEST',
    enabled        => TRUE,
  );
END;
/

-----------------job

BEGIN
 dbms_scheduler.create_job (
   job_name             => 'JOB_TEST',
   program_name         => 'PRG_TEST',
   enabled              =>  TRUE,
  );
END;
/

We are wrapping the procedure into a program which in turn is wrapped into a job. Because we enabled the job it will run right away, and succeed.

However, if we just execute the procedure like this:

begin
   test;
end;

It will (correctly) fail - with no_data_found exception. The question is: is there a way to make the job fail without having to change the code of the procedure?

Upvotes: 1

Views: 552

Answers (1)

Luke Woodward
Luke Woodward

Reputation: 64949

This appears to be a known issue with DBMS_SCHEDULER: see for example:

To get around this, you can change the type of the job to PLSQL_BLOCK. Here's what happens when I create a job, check its status, give it a second or so to run in the background and check again:

SQL> create or replace procedure test as
  2  begin
  3    raise no_data_found;
  4  end;
  5  /

Procedure created.

SQL> BEGIN
  2   dbms_scheduler.create_job (
  3     job_name             => 'JOB_TEST',
  4     job_type             => 'PLSQL_BLOCK',
  5     job_action           => 'TEST',
  6     enabled              =>  TRUE,
  7     auto_drop            =>  FALSE
  8    );
  9  END;
 10  /

PL/SQL procedure successfully completed.

SQL> SELECT job_name, state, failure_count FROM user_scheduler_jobs WHERE job_name = 'JOB_TEST';

JOB_NAME                       STATE           FAILURE_COUNT
------------------------------ --------------- -------------
JOB_TEST                       SCHEDULED                   0

SQL> EXEC dbms_lock.sleep(1);

PL/SQL procedure successfully completed.

SQL> SELECT job_name, state, failure_count FROM user_scheduler_jobs WHERE job_name = 'JOB_TEST';

JOB_NAME                       STATE           FAILURE_COUNT
------------------------------ --------------- -------------
JOB_TEST                       FAILED                      1

Upvotes: 2

Related Questions