What's the point?

A very good habit to develop is to add application-level tracing to your code. This tracing capability should be something you can turn on and off from outside your application code. Use it to keep track of application-specific information that is being used or processed by your code.

Options for tracing include:

• The DBMS_APPLICATION_INFO package
   
• The log4plsql open source utility
   
• The qd_runtime package of the Quest Codegen Utility

Whichever tracing option you choose, information that very often needs to be traced/verified are the values that were passed into a program through the parameter list (IN and IN OUT arguments). Programmers usually write such tracing code themselves, which can be a major pain in the neck when you have lots of arguments.

Below you will find a program that extracts the arguments of a program from the ALL_ARGUMENTS data dictionary view and generates a starting point for your argument tracing. It assumes that your tracing function takes two string inputs:

• the context: defaulted to the name of the program you provided
   
• the trace message: a single string that contains concatenations of all the arguments in the form ARGUMENT_NAME = ARGUMENT_VALUE.

You may well need to change either this program or the output from the program to match your actual tracing interface or to remove any arguments whose type does not allow implicit conversions to strings. At least it will have generated a nice starting point for you, saving a bunch of time.

Note: many thanks to Jornica for his help in enhancing and testing this utility!

Show me the code!

Sorry, rather than show you all the code here (very clumsy), I offer the source code and any supporting files in this zip file.

You can also download my entire "demo zip", containing all the scripts and reusable code that are part of my regular trainings. The zip for this Quseful is inside that zip as well.

How do I use it?

Call gen_trace_call and pass it the name of the program for which you need argument tracing. It then queries the correct argument information from ALL_ARGUMENTS and generates code by displaying it on the screen with DBMS_OUTPUT.

This program has five arguments:

• pkg_or_prog_in - The name of the package that contains the subprogram you want to trace, or the name of the schema-level function or procedure you want to trace.
   
• pkg_subprog_in - If tracing a program in a package, provide the name of that function or procedure here. If tracing a schema-level program, pass NULL.
   
• nest_tracing_in - Pass TRUE (the default) if you want to nest your tracing call within a conditional statement that is used to first see whether or not tracing is enabled. This is useful for reducing the runtime overhead of tracing when disabled.
   
• tracing_enabled_func_in - The name of the function (or chunk of code) that you want to run to see if tracing is enabled. The default is 'qd_runtime.trace_enabled', which is the function used by the Quest CodeGen Utility to check to see if tracing is enabled.
   
• trace_func_in - The name of the function that you want to call to do the tracing. The default is 'qd_runtime.trace', which is the trace function offered by the Quest CodeGen Utility.

Examples

Here are some examples, generating code for programs that are available in my "demo zip" file. Please note that I have formatted all code using Toad's auto-formatter. It will not be quite as pretty "out of the box". I have also turned on serveroutput before running these scripts.

1. A schema-level function (betwnstr.sf):

DECLARE
/* AFTER ENTERING - IN and IN OUT argument tracing */
PROCEDURE trace_in_arguments
IS
FUNCTION bool_to_char (bool_in IN BOOLEAN)
   RETURN VARCHAR2
IS
BEGIN
   IF bool_in
   THEN
      RETURN 'TRUE';
   ELSIF NOT bool_in
   THEN
      RETURN 'FALSE';
   ELSE
      RETURN 'NULL';
   END IF;
END bool_to_char;
BEGIN
IF qd_runtime.trace_enabled
THEN
   qd_runtime.TRACE ('BETWNSTR'
                   ,    'STRING_IN='
                     || string_in
                     || ' - START_IN='
                     || start_in
                     || ' - END_IN='
                     || end_in
                     || ' - INCLUSIVE_IN='
                     || bool_to_char (inclusive_in)
                    );
END IF;
END trace_in_arguments;

/* BEFORE LEAVING - OUT and IN OUT argument tracing */
PROCEDURE trace_out_arguments
IS
BEGIN
IF qd_runtime.trace_enabled
THEN
   qd_runtime.TRACE ('BETWNSTR', 'RETURN_VALUE=' || return_value);
END IF;
END trace_out_arguments;
BEGIN
NULL;
END;

DECLARE
/* AFTER ENTERING - IN and IN OUT argument tracing */
PROCEDURE trace_in_arguments
IS
FUNCTION bool_to_char (bool_in IN BOOLEAN)
    RETURN VARCHAR2
IS
BEGIN
    IF bool_in
    THEN
       RETURN 'TRUE';
    ELSIF NOT bool_in
    THEN
       RETURN 'FALSE';
    ELSE
       RETURN 'NULL';
    END IF;
END bool_to_char;
BEGIN
IF qd_runtime.trace_enabled
THEN
    qd_runtime.TRACE ('DYN_PLACEHOLDER.ALL_IN_STRING'
                    ,    'STRING_IN='
                      || string_in
                      || ' - DYN_PLSQL_IN='
                      || bool_to_char (dyn_plsql_in)
                     );
END IF;
END trace_in_arguments;

/* BEFORE LEAVING - OUT and IN OUT argument tracing */
PROCEDURE trace_out_arguments
IS
BEGIN
IF qd_runtime.trace_enabled
THEN
    qd_runtime.TRACE ('DYN_PLACEHOLDER.ALL_IN_STRING'
                    , 'RETURN_VALUE=' || return_value
                     );
END IF;
END trace_out_arguments;
BEGIN
NULL;
END;

DECLARE
/* AFTER ENTERING - IN and IN OUT argument tracing */
PROCEDURE trace_in_arguments
IS
FUNCTION bool_to_char (bool_in IN BOOLEAN)
    RETURN VARCHAR2
IS
BEGIN
    IF bool_in
    THEN
       RETURN 'TRUE';
    ELSIF NOT bool_in
    THEN
       RETURN 'FALSE';
    ELSE
       RETURN 'NULL';
    END IF;
END bool_to_char;
BEGIN
IF mypkg.tracing_on ()
THEN
mupkg.show_action ('DYN_PLACEHOLDER.ALL_IN_STRING'
                     ,    'STRING_IN='
                       || string_in
                       || ' - DYN_PLSQL_IN='
                       || bool_to_char (dyn_plsql_in)
                      );
END IF;
END trace_in_arguments;

/* BEFORE LEAVING - OUT and IN OUT argument tracing */
PROCEDURE trace_out_arguments
IS
BEGIN
IF mypkg.tracing_on ()
THEN
    mupkg.show_action ('DYN_PLACEHOLDER.ALL_IN_STRING'
                     , 'RETURN_VALUE=' || return_value
                      );
END IF;
END trace_out_arguments;
BEGIN
NULL;
END;