NAME

dbug - Perform debugging in Oracle PL/SQL

SYNOPSIS

DESCRIPTION

The dbug package is used for debugging. The destination of the debugging is flexible. Initially three methods can be activated: plsdbug, dbms_output and log4plsql. The user of this package may provide his own method and activate that (see the NOTES for section Plug and play).

The plsdbug method implements the functionality of the dbug library written in the programming language C. This dbug library can be used to perform regression testing and profiling.

The communication to the plsdbug application server must use a pipe: 'DBUG_' concatenated with the Oracle username. This enables private debugging sessions. The plsdbug application server must use this pipe too.

Debugging is by default not active and must be activated by the client. Depending on the method, some extra initialisation may be needed too (for example with plsdbug).

These are the functions/procedures:

done

Cleanup all the mess. Run the done function for each activated package. So call DBUG_DBMS_OUTPUT.DONE when DBMS_OUTPUT is activated.

activate

The activate method is used to activate or deactivate debugging by the client. Parameters are the method of debugging and enabling/disabling that method. Initially the available methods are 'PLSDBUG', 'DBMS_OUTPUT' and 'LOG4PLSQL'. A method indicates which implementation package to use for debugging. An implementation package is the method name prefixed with dbug_. More than one method may be enabled, hence output to different destinations is possible.

active

Is debugging active for a method?

set_level

Set the current threshold level which determines which dbug operations (dbug.enter, dbug.print, dbug.leave, dbug.on_error, dbug.leave_on_error or their buffered variants) will be executed or not. The default threshold level is DEBUG.

This method may only be used when no dbug work is in progress. Dbug work is in progress if dbug.enter operations are waiting to be matched by their dbug.leave.

The exception PROGRAM_ERROR is raised when dbug work is in progress. The exception VALUE_ERROR is raised when the level is not between C_LEVEL_ALL and C_LEVEL_OFF.

When the level of a dbug operation is at least the current threshold level, the dbug operation is executed.

The dbug operations dbug.enter, dbug.leave use a fixed break point 'trace' with level DEBUG.

The level for dbug.print (and dbug.on_error which calls dbug.print with break point 'error') is determined by its break point.

For historical reasons 'debug', 'trace', 'input', 'output' have all the DEBUG level, 'info' has the INFO level, 'warning' has the WARNING level, 'error' the ERROR level and 'fatal' the FATAL level. Other break points not mentioned here, have the DEBUG level by default.

You may override the default break point levels by calling set_breakpoint_level.

get_level

Returns the current log level.

set_breakpoint_level

Assign levels to break points. When dbug encounters a break point not set in this table, that break point will get a default level of DEBUG. See set_level for the default break point levels.

The exception PROGRAM_ERROR is raised when dbug work is in progress. The exception VALUE_ERROR is raised when the level for a break point is not between C_LEVEL_DEBUG and C_LEVEL_FATAL.

get_breakpoint_level

Returns the current break point levels.

enter

Enter a function called p_module. To be used at the start of a function.

leave

Leave a function. To be used at the end of a function.

on_error

Show errors. To be used in an exception block. Must be the first dbug operation in such a block. Errors shown include sqlerrm, dbms_utility.format_error_backtrace (if package dbms_utility is available) and Oracle Headstart errors (if package cg$errors is available). The availability of the last two packages is verified using dynamic SQL. Please note that the Oracle Headstart errors are not removed by displaying them (the error stack is reconstructed).

leave_on_error

Leave a function. To be used in an exception block. Calls on_error and leave. This must be last dbug operation in an exception block.

cast_to_varchar2

Casts a boolean to varchar2. It returns 'TRUE' for TRUE, 'FALSE' for FALSE and 'UNKNOWN' for NULL.

print

Print a line. Parameters are a break point and a string or a printf format string and up till 5 string arguments. If the string arguments are NULL, the string <NULL> is used. A date argument (p_arg1) uses to_char(p_arg1, 'YYYYMMDDHH24MISS') to convert to a varchar2.

set_ignore_buffer_overflow

Set the flag for ignoring a dbms_output buffer overflow.

get_ignore_buffer_overflow

Get the flag for ignoring a dbms_output buffer overflow.

NOTES

Plug and play

Each method must be implemented by an implementation package named after the method name with a prefix of dbug_. So for dbms_output there is a package dbug_dbms_output which uses dbms_output to do the logging.

The dbug package uses dynamic SQL internally which calls the method specific procedures. Each implementation package should at least provide the following procedures:

done
  procedure done;
enter
  procedure enter(
    p_module in dbug.module_name_t
  );
leave
  procedure leave;
print
  procedure print(
    p_break_point in varchar2,
    p_fmt in varchar2,
    p_arg1 in varchar2
  );

  procedure print(
    p_break_point in varchar2,
    p_fmt in varchar2,
    p_arg1 in varchar2,
    p_arg2 in varchar2
  );

  procedure print(
    p_break_point in varchar2,
    p_fmt in varchar2,
    p_arg1 in varchar2,
    p_arg2 in varchar2,
    p_arg3 in varchar2
  );

  procedure print(
    p_break_point in varchar2,
    p_fmt in varchar2,
    p_arg1 in varchar2,
    p_arg2 in varchar2,
    p_arg3 in varchar2,
    p_arg4 in varchar2
  );

  procedure print(
    p_break_point in varchar2,
    p_fmt in varchar2,
    p_arg1 in varchar2,
    p_arg2 in varchar2,
    p_arg3 in varchar2,
    p_arg4 in varchar2,
    p_arg5 in varchar2
  );

Missing dbug.leave calls

For every dbug.enter call at the start of a method the program has to execute dbug.leave too. But, since exceptions and program logic errors (method may return before calling dbug.leave) may occur, the dbug package tries to adjust for those missing dbug.leave calls.

So, given this anonymous block:

   1  declare
   2
   3  procedure f1(p_count pls_integer := 5)
   4  is
   5  begin
   6    dbug.enter('f1');
   7    if p_count > 0
   8    then
   9      f1(p_count-1);
  10    end if;
  11    -- Oops, forgot to dbug.leave;
  12  end;
  13
  14  procedure f2
  15  is
  16  begin
  17    dbug.enter('f2');
  18    f1;
  19    dbug.leave;
  20  end;
  21
  22  procedure f3
  23  is
  24  begin
  25    dbug.enter('f3');
  26    f2;
  27    dbug.leave;
  28  end;
  29
  30  begin
  31    dbug.activate('dbms_output');
  32    dbug.enter('main');
  33    f3;
  34    dbug.leave;
  35* end;

the stack trace will be (without any adjustments):

  >main
  |   >f3
  |       >f2
  |           >f1
  |               >f1
  |                   >f1
  |                       >f1
  |                           >f1
  |                               >f1
  |                               <
  |                           <
  |                       <

However, the task is to show a normal trace like this:

  >main
  |   >f3
  |       >f2
  |           >f1
  |               >f1
  |                   >f1
  |                       >f1
  |                           >f1
  |                               >f1
  |                               <
  |                           <
  |                       <
  |                   <
  |               <
  |           <
  |       <
  |   <
  <

Analysis

The dbms_utility.format_call_stack provides us information about the PL/SQL call stack. In our example when dbug.enter is called in f1 while being called from f2, this is the call stack (XXX is any line number):

  ----- PL/SQL Call Stack -----
    object      line  object
    handle    number  name
  69E09330       XXX  package body EPCAPP.DBUG
  69E09330       XXX  package body EPCAPP.DBUG
  6953B000         6  anonymous block
  6953B000        18  anonymous block
  6953B000        26  anonymous block
  ...

When line 11 would have contained dbug.leave, this would be the call stack when dbug.leave is called in f1 while being called from f2:

  ----- PL/SQL Call Stack -----
    object      line  object
    handle    number  name
  69E09330       XXX  package body EPCAPP.DBUG
  69E09330       XXX  package body EPCAPP.DBUG
  6953B000        11  anonymous block
  6953B000        18  anonymous block
  6953B000        26  anonymous block
  ...

However, since the first dbug.leave called is in line 19, this is the call stack when dbug.leave is called for the first time:

  ----- PL/SQL Call Stack -----
    object      line  object
    handle    number  name
  69E09330       XXX  package body EPCAPP.DBUG
  69E09330       XXX  package body EPCAPP.DBUG
  6953B000        19  anonymous block
  6953B000        26  anonymous block
  ...

So, given these stack traces, the idea is to store (stackwise) at each dbug.enter call the second line after the last EPCAPP.DBUG line. Thus when dbug.enter is called from f1 and f2, that line will be:

  6953B000        18  anonymous block

When dbug.leave is called, the second line after the last EPCAPP.DBUG line is compared against the stored line. When dbug.leave has not been forgotton, these lines are the same. But when one or more dbug.leave calls have been forgotton (due to an exception or program logic error), we have to check previous lines stored when dbug.enter was called.

In our example the line when dbug.leave is called first is:

  6953B000        26  anonymous block

and the stack maintained by dbug.enter is

  6953B000         9  anonymous block
  6953B000         9  anonymous block
  6953B000         9  anonymous block
  6953B000         9  anonymous block
  6953B000         9  anonymous block
  6953B000        18  anonymous block
  6953B000        26  anonymous block

So now we know that 6 dbug.leave calls have been missed.

Restarting a PL/SQL block with dbug.leave calls missing due to an exception

When this anonymous block is invoked twice in SQL*Plus:

   1  begin
   2    dbug.activate('dbms_output');
   3    dbug.enter('main');
   4    raise value_error;
   5    dbug.leave;
   6  exception
   7   when others then null;
   8* end;

the stack trace will be (without any adjustments):

  >main
  |   >main

The task is to show a normal trace like this:

  >main
  <
  >main

Analysis

This problem can be solved by storing the call stack and module name the first time dbug.enter has been called. Now when a subsequent dbug.enter call is made with the same module name and call stack, we know that the second dbug.enter call should be the first on the stack. So we adjust for the missing dbug.leave calls and reset the stack.

EXAMPLES

Using the plsdbug method

  declare
    function
    factorial(p_value in pls_integer)
    return pls_integer
    is
      l_value pls_integer := p_value;
    begin
      dbug.enter( 'factorial' );
      dbug.print( 'find', 'find %s factorial', l_value );
      if (l_value > 1) 
      then
        l_value := l_value * factorial( l_value-1 );
      end if;
      dbug.print( 'result', 'result is %s', l_value );
      dbug.leave;
      return (l_value);
    exception
      when others
      then
        dbug.leave;
        return -1;
    end;
  begin
    dbug.activate('PLSDBUG', true);
    dbug_plsdbug.init( 'd;t;g' ); -- debugging, tracing and profiling
    dbms_output.put_line( factorial( 5 ) );
    dbug.done;
  end;

Changing log level

The following SQL*Plus script will not print anything, because only error break point are printed:

  set serveroutput on

  begin
    dbug.set_level(c_level_error);
    dbug.activate('DBMS_OUTPUT');
    dbug.enter('main');
    dbug.leave;
  end;

The following SQL*Plus script will print the error line:

  set serveroutput on

  begin
    dbug.set_level(c_level_error);
    dbug.activate('DBMS_OUTPUT');
    dbug.enter('main');
    dbug.print('error', 'Only this line will be printed');
    dbug.leave;
  end;

AUTHOR

Gert-Jan Paulissen

BUGS

SEE ALSO

DBUG

See https://github.com/TransferWare/plsdbug.

LOG4PLSQL

See http://sourceforge.net/projects/log4plsql.