Re: reading "argv[]" for pl/sql

On Jan 12, 12:11 pm, ErikYkema <erik.yk..._at_gmail.com> wrote:
> Hello,
> I want to implement a general logging framework for our development
> team at our site, currently studying several samples and initiatives
> around log4j, java.util.logging and log4plsql.
>
> What I like to log in every invocation of a stored procedure is the
> name and value of arguments that the procedure has been called with.
> I can lookup the parameter names in e.g. [all|dba]_parameters.
> Of course I can ask to explicitly write something like
> (log_pck being some imagined utility):
>
>   log_pck.log_par(..., p_parameter_name => r_param.argument_name,
> p_parameter_value => p_param1);
> where p_param1 is the name of a parameter of the invoked procedure
>
> However I am a programmer and looking for ways to reduce the number of
> lines I write and the number of errors I make and also have seen e.g.
> C's and ksh's arrays of parameters: argv[0..argc-1], or $#. How
> convenently it would be to write in every module at line 1:
>
>   log_pck.log_pars(p_parameterlist => p_argv);
>
> where p_argv is a system supplied local table of ANY or LONG where all
> parameter values are listed.
> Within log_pck.log_pars, I would use who_called_me to see who wants to
> log, and then loop through the table_of and write the values.
>
> However with getting the values dynamically I am completely stuck.
> Throwing dynamic SQL in hasn't helped since the local variable space
> of the dynamice context has no knowledge over my parameters.
>
> I believe that execution time logging/instrumentation is immensly
> important and often overlooked or under-implemented, and also that
> having such a system supplied list as an enhancement in a future DBMS
> release would be a great addition.
> The issue of a list of values or a list of references (i.e. pointers)
> in order to prevent data volume issues is of course to be addressed in
> some clever way.
>
> Any clues, hints are appreciated. Or perhaps it's just out there and I
> didn't see it?
>
> Regards,
> Erik Ykema

Erik,
I have developed a logging/call stack feature that displays arguments in PL/SQL and java stored procs too. I could not figure out how to get at the argv[] equiv, so I took the tedious path of explicitly putting args in logging procedure calls. It does not sound so great, however, I use an IDE that provides template usage that makes it convenient (Allroundautomation's PL/SQL Developer). With those templates, I provide procedure/function name and argument names + data types. Within the template, I can then create procedures that not only list the arguments, but makes certain calls. These templates allow for any number of arguments and correctly places them inside parens.

Template: prompts for procedure name, argument names, types and data types, generates a code block where my cursor is placed (I have simplified this only showing a single argument, but my real template allows for more):

PROCEDURE [Procedure Name]( [Arg 1 Name ] [Arg 1 Type] [Arg 1 DataType])
IS

   lc_proc CONSTANT VARCHAR2(61) := gc_package || '.[Procedure Name]';

BEGIN    logger.bproc( lc_proc, util.parms2str('[Arg 1 Name]',[Arg 1 Name]) );

   logger.eproc( lc_proc );

EXCEPTION
   WHEN OTHERS THEN

       logger.fproc( lc_proc, SQLCODE, SQLERRM );
       RAISE;

END [Procedure Name]

Example procedure Name="run_queued", argument(single for example) = "p_group_id", "IN", "PLS_INTEGER" would look like this:

PROCEDURE run_queued( p_group_id IN PLS_INTEGER ) IS

   lc_proc CONSTANT VARCHAR2(61) := gc_package || '.run_queued';

BEGIN    logger.bproc( lc_proc, util.parms2str('p_group_id',p_group_id) );

   logger.eproc( lc_proc );

EXCEPTION
   WHEN OTHERS THEN

       logger.fproc( lc_proc, SQLCODE, SQLERRM );
       RAISE;

END run_queued;

broc = logger's "begin a procecedure"
eproc = logger's "end a procedure", takes an optional arg for a message, such as a return value
fproc = logger's "end a procedure with a failure"

util.parms2str procedure takes up to 8 name value pairs of a varchar2. Anything that cannot be implied as char must be explicitly converted ( i.e. dates), this allows me to place dates in whatever position is warrented, without creating separate overloads that have every possible combination of char parms and date parms.

After my creation of the code in the appropriate location within a package, I just start coding (between the bproc and eproc calls). I know this is not exactly what you are looking for, but this shows what can be done, with minimal extra coding.

The resulting logging display looks something like this:

07:11:15.123 Starting JOBS_PKG.run_reports 07:11:15.123 ..Starting REPORTS_PKG.run_queued with group_id=1234 ...
07:11:15.201 ..Ending REPORTS_PKG.run_queued 07:11:15.201 Ending JOBS_PKG.run_reports

I use these calls throughout all my code and allows for easy debugging and problem solving in addition to pinpointing specific code that takes longer than expected, etc. The key is that for most of the calls, I dont have to type any code, the template places the code for me.

Vince Received on Tue Jan 15 2008 - 09:25:43 CST