Re: reading "argv[]" for pl/sql
From: news.verizon.net <kennedyii_at_verizon.net>
Date: Wed, 16 Jan 2008 14:50:07 GMT
Message-ID: <zQojj.33674$Zo3.2216@trnddc02>
>> 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
Just file an SR on Metalink.
Jim Received on Wed Jan 16 2008 - 08:50:07 CST
Date: Wed, 16 Jan 2008 14:50:07 GMT
Message-ID: <zQojj.33674$Zo3.2216@trnddc02>
"ErikYkema" <erik.ykema_at_gmail.com> wrote in message news:a253fa1c-8936-444f-85a6-40b04cadd631_at_f10g2000hsf.googlegroups.com...
> 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
Just file an SR on Metalink.
Jim Received on Wed Jan 16 2008 - 08:50:07 CST