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

On Jan 15, 4:25 pm, Vince <vinn..._at_yahoo.com> wrote:
> 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

Hi Vince,
Thanks for the pragmatic suggestions, I will experiment with them. And file a feature request with Oracle, if I can find the address for that.
Regards,
Erik Received on Wed Jan 16 2008 - 05:18:04 CST