reading "argv[]" for pl/sql

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 Received on Sat Jan 12 2008 - 14:11:01 CST