Skip to Main Content

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, Vic.

Asked: October 13, 2021 - 8:27 am UTC

Last updated: October 19, 2021 - 12:37 pm UTC

Version: 12.1

Viewed 100+ times

You Asked

Hi,

There is no test case needed, it's more of a question.

We have a stored proc which on success of the execution of the stored proc there is either a 1 or 0 returned. Can the 1 or 0 be captured by the oracle scheduler?

Thanks
Vic

and Chris said...

Jobs capture dbms_output data in user_scheduler_job_run_details.output. So you can do something like this:

create or replace procedure p ( p out int ) as
begin
  p := 1;
end p;
/

var job_name varchar2(10);
exec :job_name := 'OUT_PROC';

begin
    dbms_scheduler.create_job (
      job_name => :job_name,
      job_type => 'PLSQL_BLOCK',
      job_action => 'declare
  val int;
begin 
  p ( val );
  dbms_output.put_line ( val ); 
end;',
      number_of_arguments => 0,
      enabled => false,
      auto_drop => false
    );

    dbms_scheduler.set_attribute( 
       name => :job_name, 
       attribute => 'store_output', value => TRUE
    );
    dbms_scheduler.enable (
      name => :job_name
    );
END;
/

create or replace procedure p ( p out int ) as
begin
  p := 0;
end p;
/

exec dbms_scheduler.run_job ( job_name => :job_name, use_current_session => false );

select job_name, status, output 
from   user_scheduler_job_run_details
where  job_name = :job_name
order  by log_date desc;

JOB_NAME    STATUS       OUTPUT   
OUT_PROC    SUCCEEDED    0         
OUT_PROC    SUCCEEDED    1

exec dbms_scheduler.drop_job ( :job_name );


That said, if you want to capture information about a job I strongly recommend logging it to your own table.

Rating

  (1 rating)

Comments

Fantastic

Vic Cordiner, October 19, 2021 - 10:51 am UTC

Fantastic thank you very much

Vic
Chris Saxon
October 19, 2021 - 12:37 pm UTC

You're welcome

More to Explore

Administration

Need more information on Administration? Check out the Administrators guide for the Oracle Database