Re: create dynamic variables

From: Vladimir M. Zakharychev <vladimir.zakharychev_at_gmail.com>
Date: 9 Aug 2006 08:57:59 -0700
Message-ID: <1155139079.601960.193950@m79g2000cwm.googlegroups.com>

mgmonzon wrote:
> Hi Everybody,
>
> Is posible to create dynamic variables in a PL/SQL procedure?
>
> This is my problem, I have a table called TABLE_VARIABLE with this
> values :
>
> # VAR_NAME VAR_VALUE
> ------- --------------- ------------------------
> 1 SALARY 1000
> 2 DISCOUNT 300
> 3 NET (SALARY - DISCOUNT)
>
>
> I want create a procedure where the variables names should be the same
> name defined in the table TABLE_VARIABLE.
> The user is able to create ne records and new variables names an the
> names should be processed like a pl/sql variables in order to store
> the result information in other table.
>
> Sample :
>
> Create or replace read_variables as
> Begin
> for reg in (select VAR_NAME, VAR_VALUE from TABLE_VARIABLE ) loop
> <create variable defined in the field VAR_NAME in TABLE_VARIABLE> ;
> <Assign value using the field VAR_VALUE in TABLE_VARIABLE> ;
> End Loop ;
> End ;
>
> Is possible do that?
>
>
> Mgmonzon

Probably not exactly what you are looking for, but you could create an anonymous PL/SQL block from these variables definitions and then EXECUTE IMMEDIATE it. You can bind a nested table or an array to this block to get the results back, like this:

declare

   type array_t is table of number index by binary_integer;    outarray array_t;
begin
execute immediate
'declare

   salary number := 1000;
   discount number := 300;
   net number := (salary - discount);
begin

   :outarray(1) := salary;
   :outarray(2) := discount;
   :outarray(3) := net;

end;' using out outarray;
end;

and then process the output as needed. A little problem here is that the table doesn't tell you data types of the variables. PL/SQL is not VB or JavaScript, all variables are typed... If you add data types to the picture, writing the anonymous block builder is pretty trivial.

Hth,

    Vladimir M. Zakharychev
    N-Networks, makers of Dynamic PSP(tm)     http://www.dynamicpsp.com Received on Wed Aug 09 2006 - 10:57:59 CDT