Re: PL/SQL: dynamically determining parameter names and values

Jeremy schrieb:
> Oracle 9iR2
>
> I would like to be able to generically write out to a table the
> parameter names of a stored procedure and the value that each parameter
> had. For example:
>
> procedure p
> (p1 in varchar2,
> p2 in varchar2,
> p3 in varchar2)
> is
> begin
> --
> -- write to logfile
> --
> insert into logtab(pname, pvalue)
> values ('p1',p1);
>
> insert into logtab(pname, pvalue)
> values ('p2',p2);
>
> insert into logtab(pname, pvalue)
> values ('p3',p3);
> end;
>
> Ignoring data type conversions etc. (plus the functional requirements
> here....) for the time being, what I would like to be able to do is have
> a block of code that would be able to loop through the parameter names
> and give me the name and value pairs such that if I could use it in any
> procedure.
>
> I don't think it's possible but would be delighted to to be proved
> wrong.
>
> cheers
>

Hi!

PL/SQL is rather poor on reflection but it is not totally impossible. This is the direction I would take if I really had to do that:

• Define a schema-level trigger that executes 'after create'.
• If ora_dict_obj_type is some PL/SQL then load the code from user_source into some pl/sql table.
• Query user_arguments for every included top-level procedure/function and insert tracing code into the table at the appropriate places (you will have to write a simple parser or insert place-holders where the log info should go).
• Re-execute the code in the pl/sql table (make sure that the trigger doesn't start a cycle).

Lucas Jellema of AMIS wrote about AOP a few months ago, this is more generic: http://technology.amis.nl/blog/?p=1083

Hth,
Chris Received on Mon Nov 13 2006 - 06:00:51 CST