PLSQL- dynamically declaring variable

Question

How can I assign a variable dynammically in PL/SQL?

I tried something like this, which doesn't work:

declare
   v_variable number := execute immediate 'select max(value) from mytable';
begin
   dbms_output.put(v_variable);
end;

How could this be done?

score 2 · Accepted Answer · answered May 30 '14 at 11:43

Just assign it in the body of the code, not in the declaration. Also, you're not using any dynamic SQL, so no need for EXECUTE IMMEDIATE..

declare
   v_variable number;
begin

   select max(value) 
   into v_variable
   from mytable;

   dbms_output.put(v_variable);
end;

score 1 · Answer 2 · edited May 23 '17 at 10:32

You can only assign variable values to variables in the execution (after begin) part of the code. For instance, the following (taken from https://stackoverflow.com/a/9957933/150533) swaps values from two variables:

declare
  no1 number(3):=31;
  no2 number(3):=34;
  temp number;
begin
  dbms_output.put_line('Before swap');
  dbms_output.put_line('No1 : '||no1||'  No2 : '||no2 );
  --  no1:=(no1+no2)-(no2:=no1);  generate error
  temp := no1;
  no1 := no2;
  no2 : temp;
  dbms_output.put_line('After swap');
  dbms_output.put_line('No1 : '||no1||'  No2 : '||no2 );
end;

PLSQL- dynamically declaring variable

2 Answers2