dbms_output and the scheduler

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.

Advertisements

2 thoughts on “dbms_output and the scheduler

  1. 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” ?

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s