PL/SQL Documentation output

In a scheduler task output can be written to different destinations. All these output destinations can be viewed on the task properties window (output panel) and with the database view SO_VW_TASK_DESTINATIONS.

Screenshot PL/SQL scheduler window W25 Task properties (output panel)

Default all the output is written to one task output destination. Writing data can be done using the standard oracle package DBMS_OUTPUT or with the output procedures defined in the SO_RUN package.
Both methods can be used together in the same PL/SQL program.

The output procedures in the SO_RUN package are preferred because their output is directly send to the scheduler server and immediate visible.

If only the DBMS_OUTPUT package is used the output is at least visible when the scheduler task is ready. However each time a procedure or function from the SO_RUN package is used the output generated with the DBMS_OUTPUT package is flushed to the scheduler server.

Back to top 

Output destinations

Creating extra output destinations for a scheduler task is done with the function SO_RUN.CREATE_DESTINATION in the PL/SQL program that is executed. There is no limit on the number of destinations.
The function returns an id that is used when output must be written to the new destination.

function create_destination(
    p_destination_name in varchar2)
return integer;
  
Parameter Description
p_destination_name The name of the task output destination.
p_destination_id A unique task output destination identifier.
A value null or zero means the default task output destination.
Error Description
OSC-10065
SCHEDULER FUNCTIONALITY CAN ONLY BE USED WITHIN A RUNNING TASK
OSC-10069
DESTINATION NAME IS NULL
OSC-10070
DESTINATION NAME "%1" TOO LONG

If a destination with the same name already exists the id of the existing destination is returned.
The function SO_RUN.GET_DESTINATION_ID can be used to get an existing destination id. If the destination does not exist an exception is raised.

function get_destination_id(
    p_destination_name in varchar2)
return integer;
  
Parameter Description
p_destination_name The name of the task output destination.
p_destination_id A unique task output destination identifier.
A value null or zero means the default task output destination.
Error Description
OSC-10071
DESTINATION "%1" DOES NOT EXIST
Back to top 

Output

The output for each destination can be seen on the task output window and with the database view SO_VW_TASK_OUTPUT. Output is written to any destination with the function SO_RUN.OUTPUT.

procedure output(
    p_output_text in varchar2 default null,
    p_destination_id in integer default null);
  
Parameter Description
p_output_text The task output.
p_destination_id A unique task output destination identifier.
A value null or zero means the default task output destination.
Error Description
OSC-10072
INVALID DESTINATION IDENTIFIER "%1"

When the destination id is NULL the output is written to the default destination that is created when the scheduler task is activated.
If the data must be written to another destination use the destination id returned by the SO_RUN.CREATE_DESTINATION or SO_RUN.GET_DESTINATION_ID function.

The next procedure displays text in a box.

procedure output_box(
    p_output_text in varchar2 default null,
    p_destination_id in integer default null);
  
Parameter Description
p_output_text The task output.
p_destination_id A unique task output destination identifier.
A value null or zero means the default task output destination.
Error Description
OSC-10072
INVALID DESTINATION IDENTIFIER "%1"

The next example will write output to the default destination.

so_run.output_box('some text displayed in a box');
 ********************************
 *                              *
 * some text displayed in a box *
 *                              *
 ********************************
  
Back to top 

Dbms_output

Output generated with the DBMS_OUTPUT package is written to the default destination.
It is however possible to redirect the DBMS_OUTPUT output to another destination or even disable it.

The procedure SO_RUN.SET_DBMS_OUTPUT redirects output generated with the DBMS_OUTPUT package to another destination.

procedure set_dbms_output(
    p_destination_id in integer default null);
  
Parameter Description
p_destination_id A unique task output destination identifier.
A value null or zero means the default task output destination.
Error Description
OSC-10072
INVALID DESTINATION IDENTIFIER "%1"

With procedure SO_RUN.DISABLE_DBMS_OUTPUT the output generated with the DBMS_OUTPUT package is discarded.

procedure disable_dbms_output;
  

With the procedure SO_RUN.ENABLE_DBMS_OUTPUT the output generated with the DBMS_OUTPUT package is written again to a destination.

procedure enable_dbms_output;
  

If output must be flushed to a destination before the scheduler task is ready use the SO_RUN.FLUSH_DBMS_OUTPUT procedure.

Each time a procedure or function from the SO_RUN package is called the output generated with the DBMS_OUTPUT package is automatically flushed to a task output destination.
procedure flush_dbms_output;
  
Back to top 

Output program example

This output example is implemented in the so_demo package and is included in the scheduler output demo and can be started by submitting script demo script SO DEMO MULTI OUTPUT.

  procedure multi_output
  is
    l_count integer := 3;
    l_id_extra_output integer;
    l_file varchar2(100);
  begin

    while (l_count > 0)
    loop
      -- (1) so_run.output to the standard destination
      so_run.output('so_run.output start loop.');

      -- (2) create a new output destination
      l_file := 'destination '||to_char(l_count);
      l_id_extra_output := so_run.create_output(p_name => l_file);

      -- (3) so_run.output to the new destination
      so_run.output(p_destination_id => l_id_extra_output
                   ,p_text => 'so_run.output '||l_count||' '||l_file);

      -- (4) so_run.output to the standard destination
      so_run.output('so_run.output');

      -- (5) redirect dbms_output to the new destination
      so_run.set_dbms_output(p_destination_id => l_id_extra_output);
      dbms_output.put_line('dbms_output '||l_count||' '||l_file);

      -- (6) so_run.output still goes to the standard destination
      so_run.output('so_run.output');

      -- (7) dbms_output and so_run.output goes to the new destination
      dbms_output.put_line('dbms_output '||l_count||' '||l_file);
      so_run.output(p_destination_id => l_id_extra_output
                   ,p_text => 'so_run.output '||l_count||' '||l_file);

      -- (8) redirect dbms_output back to the standard output destination
      so_run.set_dbms_output;
      dbms_output.put_line('dbms_output.put_line');
      so_run.output('so_run.output end loop.');

      l_count := l_count - 1;

    end loop;
  end multi_output;
  
  1. Default the output goes to the default output destination. This destination is always created when a scheduler task is activated.
  2. A new task output destination is created, the id returned is needed when data must be written to this new destination.
  3. Data is written to the new output destination.
  4. Because no destination id is passed the data is written to the default output.
  5. After this statement all data written with the DBMS_OUTPUT package is written to the output destination identified by the destination id.
  6. Because no destination id is passed the data is written to the default output destination.
  7. Data written with the DBMS_OUTPUT package is written to the new destination.
  8. Data written with DBMS_OUTPUT package is redirected back to the default destination.

The output destinations can be viewed on the task properties window (output panel) and can be retrieved with the database view SO_VW_TASK_DESTINATIONS.

column name format a15
select destination_id    id,
       destination_name  name,
       destination_lines lines,
       destination_bytes bytes
from   so_vw_task_destinations
where  task_id=11668;

        ID NAME                 LINES      BYTES
========== =============== ========== ==========
         0 output                  27        959
      1232 destination 3            4        116
      1233 destination 2            4        116
      1234 destination 1            4        116
  

The destination output itself can be viewed on the task output window and with the database view SO_VW_TASK_OUTPUT.

column output_text format a30
select output_line line,
       output_task,
       output_text 
from   so_vw_task_output
where  task_id=11668
and    destination_id=1232;
    
   LINE OUTPUT_TASK         OUTPUT_TEXT
======= =================== ==============================
      1 2007-03-21 12:22:01 so_run.output 3 destination 3
      2 2007-03-21 12:22:01 dbms_output 3 destination 3
      3 2007-03-21 12:22:01 dbms_output 3 destination 3
      4 2007-03-21 12:22:01 so_run.output 3 destination 3
  
Back to top