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

On Jan 13, 11:39 pm, Mark D Powell <Mark.Pow..._at_eds.com> wrote:
> On Jan 12, 3: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
>
> I do not think such a function is such a great idea since you are
> going to be adding extra overhead and tracking information for every
> call but look into the following supplied Oracle packages and
> procedures to see what ideas you can come up with:
>
> dbms_utility.format_call_stack
> dbms_utility.format_error_stack
> dbms_application_info
>
> I do not think the actual parameter values are available via the call
> stack but it definitely lets you find out who called the code in
> question.
>
> anonymous transactions - Write a logging routine that runs as an
> anonymous transaction and call it when needed such as on error to log
> useful information. It can also be called by debugging code, perhaps
> even triggered by a debug parameter, so that the code could always be
> present in the procedure/package. See PL/SQL manaul for example use
> of anonymous transactions.
>
> Visithttp://asktom.oracle.comand try a seach on who_called_me. If
> that does not find an example of using the format_call_stack then try
> searching on "format_call_stack". I am sure I have seen the routine
> used by some of Tom's code.
>
> HTH -- Mark D Powell --

Hi Mark,Thanks for the reply.
I am not worried about the overhead, the sample is mostly for making clear what I look for as an option, not to actually do this in every call. (mostly OLTP batch, ETL/OWB mappings.) I may conclude that you do not think argv[] is such a good idea? I am sorry to hear that.
The refs to Tom are fine but besides the point. Regards,
Erik Received on Mon Jan 14 2008 - 05:07:25 CST