Thanks for the question, JD.

Asked: June 21, 2016 - 12:15 pm UTC

Last updated: July 12, 2022 - 1:26 pm UTC

Version: Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit

Viewed 10K+ times! This question is

You Asked

I have successfully created and scheduled my first job through the Scheduler that calls a stored procedure. One thing I noticed is that when the job ran was that if it failed, the ora-xxxxx error was in the addtional_info column of the log details (which I think is very useful). When the job runs successfully, that column is blank. So I got to thinking... there is information that I would like to put in the run log details when it's successful because the job does a couple of different things based on the time of day. I have searched and I haven't found a single piece of information related to this column other than it's there.

If I RAISE_APPLICATION_ERROR(-20001, 'The job did X and Y) at the end of the procedure, this is put in the additional_info column. Great!!! Except that I shouldn't have to raise an error to report success.

If I change the procedure to a function, and execute it manually; at the end it writes out to the log that "The job did X and Y". Great!!! Except apparently a scheduled job can't run a function.

So my question is... what am I missing? How do I pass useful information upon success to the job and have it as part of the Run Log Details?

Thanks for any help or insight you can provide!
JD

and Chris said...

You can write to it using dbms_scheduler.end_detached_job_run. But you should only use this for ending "detached" jobs. It's an 11g feature too:

https://oracle-base.com/articles/11g/scheduler-enhancements-11gr1#detatched_jobs

So I believe the answer is no.

12c brings an "output" column. This shows the result of calls to dbms_output:

BEGIN
    DBMS_SCHEDULER.CREATE_JOB (
            job_name => 'TEST_J',
            job_type => 'PLSQL_BLOCK',
            job_action => 'begin
  dbms_output.put_line(''Output 1'');
  dbms_output.put_line(''Output 2'');
end;',
            number_of_arguments => 0,
            enabled => true,
            auto_drop => true,
            comments => '');
end;
/

select output from user_scheduler_job_run_details
where  job_name = 'TEST_J';

OUTPUT                                                                                                                                                         
--------------------------------------------------
Output 1
Output 2

I agree raising an exception for success is kludgy. So until you upgrade you're best off storing results in a logging table.

Rating

(6 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

A reader, June 21, 2016 - 4:10 pm UTC

Jess, January 15, 2019 - 4:51 pm UTC

Hi Chris,

Creating a new job (in 12c), the 'output' column doesn't seem to be populating... I thought (from Connor's article) that put_lines inside the procedure run from the job should be picked up as well...

The job is defined like so:

  DBMS_SCHEDULER.CREATE_JOB (
    job_name => 'TEST_JOB',
    job_type => 'PLSQL_BLOCK',
    job_action => 'begin my_owner.my_package.my_proc(); end;',
    number_of_arguments => 0,
    enabled => TRUE,
    comments => 'my comments');

The "my_proc" procedure that's being called has "dbms_output.put_line('XYZ');" in it.
If I set serveroutput on, I can see it being written there when the job runs.

But then looking at dba_scheduler_job_run_details, the job succeeds, but 'output' column is null.
I can't see what's going wrong here... Any ideas?

Thank you.

January 15, 2019 - 5:18 pm UTC

It looks fine for me (on 12.1.0.2):

create or replace procedure p as
begin
  dbms_output.put_line ( 'Some output' );
end p;
/

begin
  DBMS_SCHEDULER.CREATE_JOB (
    job_name => 'OUTPUT_JOB',
    job_type => 'PLSQL_BLOCK',
    job_action => 'begin p; end;',
    number_of_arguments => 0,
    enabled => TRUE,
    comments => 'my comments');
end;
/

select log_date, output 
from   user_scheduler_job_run_details
where  job_name = 'OUTPUT_JOB';

LOG_DATE                   OUTPUT        
15-JAN-2019 09.17.41 -08   Some output

Could you share your complete example?

Jess, January 15, 2019 - 6:44 pm UTC

Hi Chris,

Thanks for a super fast reply.

I tried to put together a simplified version of what's going on, and now I'm even further confused by result...

create or replace procedure myschema.test11 as
begin
    dbms_output.put_line('inside test11 stand-alone proc');
end;
/


create or replace package myschema.test_pkg as
    procedure test12;
end test_pkg;
/

create or replace package body myschema.test_pkg as
    procedure test12 is
        begin
            dbms_output.put_line('inside test12 from package');
        end;
end test_pkg;
/


BEGIN
  DBMS_SCHEDULER.CREATE_JOB (
    job_name => 'TEST_XYZ_11',
    job_type => 'PLSQL_BLOCK',
    job_action => 'begin myschema.test11(); end;',
    number_of_arguments => 0,
    enabled => TRUE,
    auto_drop => FALSE,
    job_class => 'DEFAULT_JOB_CLASS',
    comments => 'job 11 proc');
 DBMS_SCHEDULER.SET_ATTRIBUTE ('MYSCHEMA.TEST_XYZ_11', 'logging_level', DBMS_SCHEDULER.LOGGING_FULL);
END;
/    

BEGIN
  DBMS_SCHEDULER.CREATE_JOB (
    job_name => 'TEST_XYZ_12',
    job_type => 'PLSQL_BLOCK',
    job_action => 'begin myschema.test_pkg.test12(); end;',
    number_of_arguments => 0,
    enabled => TRUE,
    auto_drop => FALSE,
    job_class => 'DEFAULT_JOB_CLASS',
    comments => 'job 12 pkg');
 DBMS_SCHEDULER.SET_ATTRIBUTE ('MYSCHEMA.TEST_XYZ_12', 'logging_level', DBMS_SCHEDULER.LOGGING_FULL);
END;
/    

exec dbms_scheduler.run_job('TEST_XYZ_11');
exec dbms_scheduler.run_job('TEST_XYZ_12');


select log_id, job_name, status, actual_start_date, output 
from dba_scheduler_job_run_details 
where owner = 'MYSCHEMA' and job_name like 'TEST_XYZ%' order by log_id;;

LOG_ID  JOB_NAME        STATUS          ACTUAL_START_DATE                               OUTPUT
1438950 TEST_XYZ_11 SUCCEEDED 15-JAN-2019 13.35.05.979640000 EUROPE/LONDON inside test11 stand-alone proc
1438954 TEST_XYZ_11 SUCCEEDED 15-JAN-2019 13.35.55.242249000 EUROPE/LONDON inside test11 stand-alone proc
1438958 TEST_XYZ_12 SUCCEEDED 15-JAN-2019 13.36.11.305654000 EUROPE/LONDON inside test12 from package
1438960 TEST_XYZ_11 SUCCEEDED 15-JAN-2019 13.36.33.836768000 EUROPE/LONDON <null>
1438962 TEST_XYZ_12 SUCCEEDED 15-JAN-2019 13.36.36.851525000 EUROPE/LONDON <null>

I ran each job exactly 1 time... I don't exactly understand where multiple executions are coming from to be honest. But that aside, you can clearly see that 2 of them have no output. So confusing! What do you make of it?

Thank you!

January 18, 2019 - 11:30 am UTC

When you create an enabled job, the database runs it immediately. So you do have two runs!

The output issue is more interesting...

By default run_job uses the current session. So dbms_output goes to your client. Not the scheduler views.

To change this and update the view, you need to set use_current_session to false:

SQL> create or replace procedure test11 as
  2  begin
  3      dbms_output.put_line('inside test11 stand-alone proc');
  4  end;
  5  /

Procedure TEST11 compiled

SQL>
SQL> BEGIN
  2    DBMS_SCHEDULER.CREATE_JOB (
  3      job_name => 'TEST_XYZ_11',
  4      job_type => 'PLSQL_BLOCK',
  5      job_action => 'begin test11(); end;',
  6      number_of_arguments => 0,
  7      enabled => TRUE,
  8      auto_drop => FALSE,
  9      job_class => 'DEFAULT_JOB_CLASS',
 10      comments => 'job 11 proc');
 11   DBMS_SCHEDULER.SET_ATTRIBUTE ('TEST_XYZ_11', 'logging_level', DBMS_SCHEDULER.LOGGING_FULL);
 12  END;
 13  /

PL/SQL procedure successfully completed.

SQL>
SQL> select log_id, job_name, status, actual_start_date, output
  2  from   user_scheduler_job_run_details
  3  where  job_name like 'TEST_XYZ%' order by log_id;
  LOG_ID JOB_NAME      STATUS      ACTUAL_START_DATE                            OUTPUT
  357576 TEST_XYZ_11   SUCCEEDED   18-JAN-19 11.27.17.389590000 EUROPE/LONDON   inside test11 stand-alone proc


SQL>
SQL> exec dbms_scheduler.run_job('TEST_XYZ_11');
inside test11 stand-alone proc


PL/SQL procedure successfully completed.

SQL>
SQL> select log_id, job_name, status, actual_start_date, output
  2  from   user_scheduler_job_run_details
  3  where  job_name like 'TEST_XYZ%' order by log_id;
  LOG_ID JOB_NAME      STATUS      ACTUAL_START_DATE                            OUTPUT
  357576 TEST_XYZ_11   SUCCEEDED   18-JAN-19 11.27.17.389590000 EUROPE/LONDON   inside test11 stand-alone proc
  357580 TEST_XYZ_11   SUCCEEDED   18-JAN-19 11.27.18.655208000 EUROPE/LONDON


SQL>
SQL> exec dbms_scheduler.run_job('TEST_XYZ_11', false);

PL/SQL procedure successfully completed.

SQL>
SQL> select log_id, job_name, status, actual_start_date, output
  2  from   user_scheduler_job_run_details
  3  where  job_name like 'TEST_XYZ%' order by log_id;
  LOG_ID JOB_NAME      STATUS      ACTUAL_START_DATE                            OUTPUT
  357576 TEST_XYZ_11   SUCCEEDED   18-JAN-19 11.27.17.389590000 EUROPE/LONDON   inside test11 stand-alone proc
  357580 TEST_XYZ_11   SUCCEEDED   18-JAN-19 11.27.18.655208000 EUROPE/LONDON
  357582 TEST_XYZ_11   SUCCEEDED   18-JAN-19 11.27.19.635841000 EUROPE/LONDON   inside test11 stand-alone proc

Jess, January 18, 2019 - 3:10 pm UTC

Hi Chris,

Oh man, must admin, didn't know run_job even had a second param! Super well spotted, thank you. Updated our docs accordingly, so others also know how to run it properly.

Thanks ever so much!

January 21, 2019 - 1:04 am UTC

glad we could help

Does this apply to job running as PROGRAM?

Flavio, June 18, 2019 - 10:13 am UTC

I can't get populated the OUTPUT column as described on Oracle 12.2.0.1.

I have procedure that creates a JOB initially DISABLED.
This job doesn't execute a PLSQL block but calls a PROGRAM.
I set the program arguments with the required values and then I enable the JOB.
The PROGRAM in turn calls a procedure in a package in the same schema as the job.
It runs perfectly but the dbms_output originated by procedure inside the package is not logged even it runs as as a background job in a separate session as a matter of fact.

Any ideas?

Of course I can log everything in a custom table, but I'd have preferred to be able to log something in that column because I could easily query the view for the information written there and also leverage the periodic log cleanup without having to do that myself.

Thank you
Flavio

June 18, 2019 - 10:29 am UTC

You're gonna have to provide a test case, because it all looks fine to me:

create or replace procedure p as
begin
dbms_output.put_line ( 'This is output' );
end p;
/

begin
dbms_scheduler.create_program (
program_name => 'plsql_block_prog',
program_type => 'PLSQL_BLOCK',
program_action => 'begin p; end;',
enabled => true,
comments => 'Run procedure p');
end;
/

begin
dbms_scheduler.create_job (
job_name => 'proj_job',
program_name => 'plsql_block_prog',
enabled => true,
auto_drop => false,
comments => 'Job defined by existing program and inline schedule.'
);

dbms_scheduler.set_attribute ('proj_job', 'logging_level', dbms_scheduler.logging_full);
end;
/

set serveroutput on
exec dbms_scheduler.run_job('proj_job', true);

This is output

exec dbms_scheduler.run_job('proj_job', false);

select log_id, output
from user_scheduler_job_run_details
where job_name = 'PROJ_JOB';

LOG_ID OUTPUT
735568 This is output
735570 <null>
735572 This is output

Want to see dbms_scheduler put_line output from a plsql_block?

Vin, July 12, 2022 - 12:42 pm UTC

Check you enabled "store_output" value. In 19G.

SYS.DBMS_SCHEDULER.SET_ATTRIBUTE
    ( name      => 'OWNER.SCHED_JOB_NAME'
     ,attribute => 'STORE_OUTPUT'
     ,value     => TRUE);

July 12, 2022 - 1:26 pm UTC

Good point - thanks for sharing

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library

Breadcrumb

Question and Answer

You Asked

and Chris said...

Rating

Comments

Does this apply to job running as PROGRAM?

Want to see dbms_scheduler put_line output from a plsql_block?

More to Explore

PL/SQL demos

PL/SQL docs

Update Additional_Info Column of a Job's Run Log Details

Breadcrumb

Question and Answer

You Asked

and Chris said...

Rating

Comments

Does this apply to job running as PROGRAM?

Want to see dbms_scheduler put_line output from a plsql_block?

More to Explore

PL/SQL demos

PL/SQL docs