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:
- 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 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?").
