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

From: Vince <vinnyop_at_yahoo.com>
Date: 15 Dec 2006 08:16:00 -0800
Message-ID: <1166199360.276828.258400@t46g2000cwa.googlegroups.com>

Jeremy wrote:
> 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
>
> --
> jeremy
>
> ============================================================
> ENVIRONMENT:
> Oracle 9iR2 / Oracle HTTP Server / mod_plsql / Solaris 8
> ============================================================

You could change your log table to be constructed more like:

procedure varchar2,
start_date,
end_date,
p1Name,
p1,
p2Name,
p2,
p3Name,
p3,
...

Create a procedure that can be called regardless of the number of parameters.

procedure logger( p_proc_name in varchar2,

p_p1Name in varchar2,
p_p1 in varchar2,
p_p2Name in varchar2 default null,
p_p2 in varchar2 default null,
p_p3Name in varchar2 default null,
p_p3 in varchar2 default null, ...)

is
begin
  insert into logtab values (p_proc_name, sysdate, null, p_p1Name,....);
end;

Now call it:

procedure p( p1 in varchar2, p2 in varchar2) is
begin
  logger('p', 'p1',p1,'p2', p2);
 ...

Some IDE/GUI tools have templates that can be customized in such a way that it can place this type of code in newly created procedures automatically. Suppose it would be alot more work to go back and add them to existing, though. Received on Fri Dec 15 2006 - 10:16:00 CST