Toad World® Forums

How to use Dynamic SQL Execute statement to assign a value to a variable


#1

I am trying to dynamically assign values to a variable depending on a parameter that is provided.

For example

If v_Addon is 01 then execute v_Assign := v_Value01;

If v_Addon is 02 then execute v_Assign := v_Value02;

What I have tried is:

Declare
v_Execute Varchar2(1000) := Null;
v_Assign Varchar2(50) := Null;
v_Addon Varchar2(2) := ‘01’;
v_Value01 Number(10) := 10;

v_Value02 Number(10) := 20;
Begin

/* Create dynamic statement to execute.
*/
v_Execute := ‘Begin v_Assign := v_Value’ ||
v_Addon || ‘; End;’;

/* Execute Query and assign a count to v_Result.
*/
Execute Immediate v_Execute; – Into v_Result;

End;

It errors saying v_Assign must be declared. I understand the execute statement runs as if I called it in a procedure or function, but I want the assignment to occur as if I had the hard coded statement in the program. So for example, I could have a loop that processed a row with columns pay01, pay02, pay03, etc. amounts by creating a single row for each pay amount in a cash receipt table, for example

Declare

v_Cash_Receipt Cash_Receipt%RowType;

v_Execute varchar2(100);

For cnt in 1 to 9

v_Execute := ‘v_Cash_Receipt.Pay_Amount := v_Pay_Record.Pay_’ || CNT;

Execute v_Execute;

v_Cash_Receipt.Account_Number := v_Pay_Record.Account_Number;

Insert into Cash_Receipt v_Cash_Receipt;

End Loop;