Retrieving parameter names and values dynamically in PLSQL procedures
592207Sep 4 2007 — edited Dec 6 2007I'm currently coding some simple routines to log procedure calls and errors within a procedure, but am looking for a better way to obtain the name of the current procedure, the name of it's arguments and the values that have been passed for those arguments.
I can, of course, use constants, however assigning these can lead to an ugly obtrusive block of code if there is a lot of parameters. I really don't want to be using the call stack for the name or querying oracle views for parameter names (and even then this doesn't give me the values). Does anyone know of a way a procedure can dynamically retrieve this information - is this information available at all?
An example:
procedure testproc( param1 In VARCHAR2, param2 IN VARCHAR2)
IS
proc_params <some type>;
proc_name <some type>;
BEGIN
proc_params := <fetch param names and values from oracle>;
proc_name := <fetch name from oracle>;
log_procedure_call(proc_name,proc_params);
-- code goes here
...
EXCEPTION
WHEN xxxxx THEN
log_error(proc_name, proc_params, ....);
RAISE;
END;
Thanks,
Megan