How can i use variables dynamically by changing their names using code

Question

Want a way to refer different variables by changing their names dynamically in a plsql block.

Tried with simple loops which will change effective names of the variables. Writing a cascaded plsql block using statement but due to size of existing code it is of no use.

set serveroutput on;
declare
foo1 number:=111;
foo2 number:=222;
begin
execute immediate 'dbms_output.put_line(foo'||'1)';
for i in 0..2
loop
dbms_output.put_line(foo||i);
end loop;
end;
/

expected output to be 111 output is error as below Error report - ORA-00900: invalid SQL statement ORA-06512: at line 5 00900. 00000 - "invalid SQL statement"

Answer 1

Your variables are local. The DBMS_OUTPUT package doesn't know them. So the resulting string dbms_output.put_line(foo1) can not be executed with execute immediate.

The typical way to solve this is to use an array of values rather than separate variables:

declare
  type t_array is varray(2) of integer;
  v_array t_array := t_array(111, 222);
begin
  for i in 1..2 loop
    dbms_output.put_line(v_array(i));
  end loop;
end;

The same with a dynamic array:

declare
  type t_array is table of integer;
  v_array t_array := t_array();
begin
  v_array.extend(1);
  v_array(1) := 111;
  v_array.extend(1);
  v_array(2) := 222;
  for i in 1 .. v_array.count loop
    dbms_output.put_line(v_array(i));
  end loop;
end;

Answer 2

Let's take it one step at a time. First, static code that works:

SQL> declare
  2  foo1 number:=111;
  3  foo2 number:=222;
  4  begin
  5  dbms_output.put_line(foo1);
  6  end;
  7  /

111

Now, if you want to EXECUTE IMMEDIATE some text, you have to put all the code in the text:

SQL> declare
  2    l_code_block varchar2(4000) := '
  3  declare
  4  foo1 number:=111;
  5  foo2 number:=222;
  6  begin
  7  dbms_output.put_line(foo2);
  8  end;
  9  ';
 10  begin
 11  execute immediate l_code_block;
 12  end;
 13  /

222

Next step: if we want to change a value dynamically, we can and should use a "bind variable".

SQL> declare
  2    l_code_block varchar2(4000) := '
  3  declare
  4  foo1 number:=111;
  5  foo2 number:=222;
  6  begin
  7  dbms_output.put_line(:n);
  8  end;
  9  ';
 10  begin
 11  execute immediate l_code_block using 1;
 12  end;
 13  /

1

But if we try to use a bind variable to change the code, it doesn't work.

SQL> declare
  2    l_code_block varchar2(4000) := '
  3  declare
  4  foo1 number:=111;
  5  foo2 number:=222;
  6  begin
  7  dbms_output.put_line(foo:n);
  8  end;
  9  ';
 10  begin
 11  execute immediate l_code_block using 1;
 12  end;
 13  /

...
Error report -
ORA-06550: line 6, column 25:
PLS-00103: Encountered the symbol "" when expecting one of the following
...

So if we want to change the code dynamically, we have to do a REPLACE on the text.

SQL> declare
  2    l_code_block varchar2(4000) := '
  3  declare
  4  foo1 number:=111;
  5  foo2 number:=222;
  6  begin
  7  dbms_output.put_line(foo#N#);
  8  end;
  9  ';
 10  begin
 11  execute immediate replace(l_code_block,'#N#',1);
 12  end;
 13  /

111

Finally, here is your loop:

SQL> declare
  2    l_code_block varchar2(4000) := '
  3  declare
  4  foo1 number:=111;
  5  foo2 number:=222;
  6  begin
  7  dbms_output.put_line(foo#N#);
  8  end;
  9  ';
 10  begin
 11    for i in 1..2 loop
 12      execute immediate replace(l_code_block,'#N#',i);
 13    end loop;
 14  end;
 15  /

111
222

Please understand, I am trying to answer your question as asked. This "dynamic" approach should be avoided whenever possible, and it is almost always possible. We would have to back up to the business requirement in order to recommend the most appropriate technique.

Best regards, Stew Ashton