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

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.

Visit http://asktom.oracle.com and 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 -- Received on Sun Jan 13 2008 - 16:39:39 CST