PL/SQL Documentation trace

This documentation contains info on how to generate trace information with the executed script procedure.
The PL/SQL trace procedures can be used for debugging code by writing extra information to any task output destination.
How the trace is written can be defined in the scheduler interface package.
All trace procedures are located in the SO_RUN package.

Back to top 

Enable and disable trace

For a scheduler task the trace is enabled on the script submit window (trace panel).

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

If set the scheduler trace is enabled.

Oracle

If set an oracle trace file is generated containing all the sql statements used by the script program.

The task trace can also be dynamically enabled and disabled in the script procedure itself by calling the SO_RUN.SET_TRACE procedure.
When this procedure is used the trace task setting is overruled.

procedure set_trace(
    p_trace_task in boolean default true);
  
Parameter Description
p_trace_task If set the scheduler trace is enabled.
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 trace is enabled the scheduler interface trace enable procedure is called, if it is disabled the scheduler interface trace disable procedure is called.
These interface procedures are only called when a disabled trace is enabled or an enabled trace is disabled. If for example the trace is more than once enabled the scheduler interface trace enable procedure is called only once.

Back to top 

Trace destination

Standard the output generated with trace is written to the task output default destination.
With the SO_RUN.SET_TRACE_DESTINATION the trace output is redirected to another task output destination.

procedure set_trace_destination(
    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"
Back to top 

Trace

The next overloaded SO_RUN.TRACE procedures write the trace information to a task output destination only if the task trace is enabled.

procedure trace;

procedure trace(
    p_trace_text in varchar2);

procedure trace(
    p_trace_text in varchar2,
    p_value_number in number);

procedure trace(
    p_trace_text in varchar2,
    p_value_varchar2 in varchar2);

procedure trace(
    p_trace_text in varchar2,
    p_value_date in date);

procedure trace(
    p_trace_text in varchar2,
    p_value_boolean in boolean);

procedure trace_box(
    p_trace_text in varchar2);
  
Parameter Description
p_trace_text Trace information.
p_value_number The numeric value.
p_value_varchar2 The varchar2 value.
p_value_date The date value.
p_value_boolean The boolean value.
Back to top 

Trace example

The next trace example is implemented in the SO_DEMO package and is included in the scheduler trace demo.
The example is started by submitting the demo script SO DEMO TRACE.

The trace output in this example is based on the output generated by the default installation interface package.
procedure trace
is
  l_bool boolean;
  l_date date;
  l_text varchar2(100);
  l_number number := 1.1;
begin

  -- boolean trace
  l_bool := true;
  so_run.trace('Bool true',l_bool);
  l_bool := false;
  so_run.trace('Bool false',l_bool);
  l_bool := null;
  so_run.trace('Bool null',l_bool);

  -- date trace
  l_date := sysdate;
  so_run.trace('Date sysdate',l_date);
  l_date := trunc(l_date);
  so_run.trace('Date trunc(sysdate)',l_date);
  l_date := null;
  so_run.trace('Date null',l_date);

  -- varchar2 trace
  l_text := 'Some text';
  so_run.trace('Varchar2 text',l_text);
  l_text := null;
  so_run.trace('Varchar2 null',l_text);
  l_text := '';
  so_run.trace('Varchar2 empty string',l_text);

  -- number trace
  l_number := 0;
  so_run.trace('Number zero',l_number);
  l_number := -100.888;
  so_run.trace('Number negative',l_number);
  l_number := 876537;
  so_run.trace('Number positive',l_number);
  l_number := null;
  so_run.trace('Number null',l_number);

  -- only text
  so_run.trace('Some trace text');

  -- empty lines
  so_run.trace;
  so_run.trace;

  -- box trace
  so_run.trace_box('Some trace text in a box');

end trace;
  

Executing the above code with the task trace enabled generates the next output.

TRACE Bool true                                    : TRUE
TRACE Bool false                                   : FALSE
TRACE Bool null                                    : NULL
TRACE Date sysdate                                 : 2007-04-18 11:21:00
TRACE Date trunc(sysdate)                          : 2007-04-18 00:00:00
TRACE Date null                                    : NULL
TRACE Varchar2 text                                : [Some text]
TRACE Varchar2 null                                : NULL
TRACE Varchar2 empty string                        : NULL
TRACE Number zero                                  : 0
TRACE Number negative                              : -100.888
TRACE Number positive                              : 876537
TRACE Number null                                  : NULL
TRACE Some trace text.
TRACE
TRACE
****************************
*                          *
* Some trace text in a box *
*                          *
****************************
  
Back to top 

Interface

For each trace procedure a scheduler interface trace procedure exists with the same paramaters. All the procedures also have one extra parameter; the destination id of the task output destination where the trace output must be written to.
The PL/SQL code in these procedures can be freely edited to change the way how the trace is displayed.

Also when trace is enabled the scheduler interface trace enable procedure is called and when the trace is disabled the scheduler interface trace disable procedure is called.

Back to top