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;