dbms_output and the scheduler

June 27, 2016

1 minute

One of the nifty things in 12c is the ability to pick up DBMS_OUTPUT output from your scheduler jobs. So if you haven’t built an extensive instrumentation or logging facility, you’ll still have some details you can pick up from the scheduler dictionary views. Let’s look at an example


SQL> create or replace
  2  procedure do_stuff is
  3  begin
  4    dbms_output.put_line('Commenced');
  5    dbms_lock.sleep(30);
  6    dbms_output.put_line('Working');
  7    dbms_lock.sleep(30);
  8    dbms_output.put_line('Done');
  9  end;
 10  /

Procedure created.

SQL>
SQL> begin
  2      dbms_scheduler.create_job (
  3         job_name           =>  'OUTPUT_DEMO',
  4         job_type           =>  'PLSQL_BLOCK',
  5         job_action         =>  'do_stuff;',
  6         start_date         =>  sysdate,
  7         enabled            =>  true,
  8         comments           =>  'Output demo');
  9  end;
 10  /

PL/SQL procedure successfully completed.

After my job completes, I can now query the dictionary


SQL> select OUTPUT
  2  from DBA_SCHEDULER_JOB_RUN_DETAILS
  3  where job_name = 'OUTPUT_DEMO';

OUTPUT
-----------------------------------------------------
Commenced
Working
Done

The column is a VARCHAR2(4000), so what happens if you exceed that ? Let’s change our PL/SQL procedure


SQL> create or replace
  2  procedure do_stuff is
  3  begin
  4    for i in 1 .. 1000 loop
  5      dbms_output.put_line(i||' Commenced');
  6    end loop;
  7  end;
  8  /

Procedure created.

SQL>
SQL> begin
  2      dbms_scheduler.create_job (
  3         job_name           =>  'OUTPUT_DEMO',
  4         job_type           =>  'PLSQL_BLOCK',
  5         job_action         =>  'do_stuff;',
  6         start_date         =>  sysdate,
  7         enabled            =>  true,
  8         comments           =>  'Output demo');
  9  end;
 10  /

PL/SQL procedure successfully completed.

SQL>
SQL> select output
  2  from DBA_SCHEDULER_JOB_RUN_DETAILS
  3  where job_name = 'OUTPUT_DEMO';

OUTPUT
----------------------------------------------------------------------------------------------------------------------------------
Commenced
Working
Done

1 Commenced
2 Commenced
3 Commenced
4 Commenced
5 Commenced
6 Commenced
...
144 Commenced
145 Commenced
146 Commenced
147 Commenced
148 Commenced
149 Commenced
150 Commenced
151 Comm


2 rows selected.

You can see that the output is truncated. However, all of the output IS retained in another column BINARY_OUTPUT which is a blob column. So whilst a basic query seems cryptic


SQL> select BINARY_OUTPUT
  2  from DBA_SCHEDULER_JOB_RUN_DETAILS
  3  where job_name = 'OUTPUT_DEMO';

BINARY_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------
436F6D6D656E6365640A576F726B696E670A446F6E65
3120436F6D6D656E6365640A3220436F6D6D656E6365640A3320436F6D6D656E6365640A3420436F6D6D656E6365640A3520436F6D6D656E6365640A3620436F6D
6D656E6365640A3720436F6D6D656E

you can cast it back to a string to get what you need.


SQL> select utl_raw.cast_to_varchar2(BINARY_OUTPUT)
  2  from DBA_SCHEDULER_JOB_RUN_DETAILS
  3  where job_name = 'OUTPUT_DEMO';

UTL_RAW.CAST_TO_VARCHAR2(BINARY_OUTPUT)
----------------------------------------------------------------------------------------------------------------------------------
Commenced
Working
Done

1 Commenced
2 Commenced
3 Commenced
4 Commenced
5 Commenced
...
990 Commenced
991 Commenced
992 Commenced
993 Commenced
994 Commenced
995 Commenced
996 Commenced
997 Commenced
998 Commenced
999 Commenced
1000 Commenced


2 rows selected.

7 responses to “dbms_output and the scheduler”

Morten Braten

July 6, 2016 at 9:50 pm

There seems to be a typo in the article: “all of the output IS retained in another column ADDITIONAL_OUTPUT which is a blob column.”, while the queries go on to show that this column is in fact called “BINARY_OUTPUT” ?

Reply
connormcdonald

July 7, 2016 at 10:03 am

Thanks Morten. Corrected.

Reply
arcsdegeo

November 26, 2017 at 10:31 pm

Nice Article….thanks

Reply
rodwest

June 6, 2018 at 8:16 pm

I tried this using a job attached to a file watcher, ie. the job queue_spec pointing to a dbms_scheduler file watcher. Sad to report that the dbms_output isn’t logged in the output column which is a shame because it would a nice way to log the processing carried out by the file watcher. Works great with a regular job. Can you think of anything I am missing here?

Reply
1. efuentes
  
  February 12, 2021 at 8:33 pm
  
  You should check that the attribute “STORE_OUTPUT” on your scheduled job is set to TRUE. If not, simply execute the following:
  
  BEGIN
  SYS.DBMS_SCHEDULER.SET_ATTRIBUTE
  ( name => ”
  ,attribute => ‘STORE_OUTPUT’
  ,value => TRUE);
  END;
  
  Reply
  1. Connor McDonald
    
    February 12, 2021 at 8:48 pm
    
    Thanks – I was unaware of that attribute
    
    Reply
Pete

January 3, 2019 at 1:01 am

Very nice, just what I was searching for.

Reply