Saturday, April 21, 2007

Quseful #1: Trace argument passing

About Qusefuls

A Quseful is a Quick and Useful (as opposed to Quick and Dirty) tip on now to write programs in the Oracle PL/SQL language more effectively. I will publish Qusefuls on my blog (feuerthoughts.blogspot.com) on a regular (hopefully at least weekly) basis. Each Quseful contains a description of the tip ("What's the point?"), some PL/SQL code you can install in your environment to implement the tip ("Show me the code!"), an example of how to use the code to help you get your job done ("How do I use it?").

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:

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:

  1. the context: defaulted to the name of the program you provided
  2. 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 four 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;

You will find an example of betwnstr that includes this tracing logic in it in the betwnstr_with_tracing.sf file.

2. A function inside a package (dyn_placeholder.pks/pkb):

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;

3. Same function, but using overrides for the tracing programs:

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;

Gotchas

Keep the following in mind:

  • You have serveroutput turned on to see the output from this program.
  • If your parameter list contains complex datatypes, like records and collections, you will definitely need to modify the output before it will work.
  • It will only generate trace information for programs defrined in the current schema. You can add a schema argument to the program and change the user_arguments reference to all_arguments to generate code for programs in other schemas.
  • On Oracle9i, you will still be facing a limit of 255 characters in a call to DBMS_OUTPUT.PUT_LINE (rises to 32K in 10g and above). You can avoid this issue by substituting the call to DBMS_OUTPUT.PUT_LINE with a program that works around this issue, a number of which are available in my "demo zip", including the pl.sp procedure and the p.pks/pkb package.

Do you like it? Do you use it?

I'd love to hear what you think of this utility and, in particular, if you found it useful. So please don't hesitate to post a comment on this blog or send me a note at steven@stevenfeuerstein.com.

About Qusefuls

A Quseful is a Quick and Useful (as opposed to Quick and Dirty) tip on now to write programs in the Oracle PL/SQL language more effectively. I will publish Qusefuls on my blog (feuerthoughts.blogspot.com) on a regular (hopefully at least weekly) basis. Each Quseful contains a description of the tip ("What's the point?"), some PL/SQL code you can install in your environment to implement the tip ("Show me the code!"), an example of how to use the code to help you get your job done ("How do I use it?").

5 comments:

Unknown said...

What do I think??? Well if the King of PL/SQL says "Good Pratice", "Should Do", "Great Idea", "Here is a Quseful", I say give me more. Steven, your thoughts and idea just make me look at PL/SQL in a different way everytime I read something of yours. Thanks for the Quseful and look forward to future ones. Now I am off to play with what you have provided to see how I can use it in my environement.

JochenVdV said...

Maybe you should add another "gotcha": it works fine in 10g, but as you know, 9i still has a limit of 255 characters for dbms_output.put_line. Nevertheless, as I was already using your DO wrapper package (slightly customized to loop over strings longer than 255 chars), the fix was quite easy :-)

Steven Feuerstein said...

Thanks Jochenvdv, I will update the posting to reflect this.

Erik Ykema said...

Hi Steven, Nice approach but from a limited world. In e.g. Kornshell I got the variable $@ that is an array that holds all my input parameters an d I can read it at will.
I am longing for a context or array provided by the pl/sql engine that (may be implemented using pointers in the background, using the anytype), that I can pass to a package that will do whatever I like with it. Send it e.g. to log4plsql.
Please also see the Aspect Oriented Programming blog entry by Amis' Lucas Jellema (http://technology.amis.nl/blog/?p=1083). It is a little clumsy, he uses a post-ddl trigger to dynamically insert code similar to your pl/sql sample in the code using a custom mnemonic comment, but is more like what I long for. I strongly believe that an extension to the pl/sql engine making this more dynamic, is worthwhile and valuable.
Thanks for the good work,
Erik Ykema

Steven Feuerstein said...

Well, Eric, it is true. I do live in a very limited world!

As for your desires for PL/SQL, please do send an email to Bryn.Llewellyn@oracle.com outlining your idea, the rationale (why it should be prioritized), etc.

SF