CODEWITHSUNDEEP
oracle-databaseplsqldynamic-sql
Ravi
Ravi

Reputation: 31

Dynamic parameter name on PL/SQL block

I was trying to generate a multiplication algorithm for multiple parameters using dynamic PL/SQL. I agree there are multiple approaches out there for doing this but this multiplication problem is a PoC for something else that I need to do.

So here is the code 

declare
  var1    number := 1;
  var2    number := 2;
  output  number := 1;
  varname varchar2(10);
begin
  for counter in 1..2
  loop
    execute immediate q'[select var]'||counter||' * :val1 from dual' into output using output;
  end loop;

  dbms_output.put_line(output);
end;

So what I was trying to do is generate the parameter name at run time and then evaluate that parameter name.

Is this feasible? Deep down I don't think so... but kind of hoping to avoid the huge pain coming my way if this does not work. :). BTW I am on Oracle 11GR2.

Here is what happened when I ran the code.

Error starting at line : 1 in command -
declare
  var1    number := 1;
  var2    number := 2;
  output  number := 1;
  varname varchar2(10);
begin
  for counter in 1..2
  loop
    execute immediate q'[select var]'||counter||' * :val1 from dual' into output using output;
  end loop;

  dbms_output.put_line(output);
end;
Error report -
ORA-00904: "VAR1": invalid identifier
ORA-06512: at line 9
00904. 00000 -  "%s: invalid identifier"
*Cause:    
*Action:
Elapsed: 00:00:00.082

Thanks and Regards.

Upvotes: 3

Views: 677

Answers (2)

An Van Luu
An Van Luu

Reputation: 1

Is this what you want to achieve?

DECLARE
   var1      NUMBER := 1;
   var2      NUMBER := 2;
   output    NUMBER := 1;
   varname   VARCHAR2 (10);
BEGIN
   FOR counter IN 1 .. 2 LOOP
      EXECUTE IMMEDIATE 'select ''var''||:val1*:counter from dual'
         INTO varname
         USING var1, counter;

      DBMS_OUTPUT.put_line (varname);
   END LOOP;
END;

Result:

var1 
var2

Upvotes: 0

Wernfried Domscheit
Wernfried Domscheit

Reputation: 59456

You can do it like this:

declare
  var1    number := 1;
  var2    number := 2;
  output  number := 1;
  varname varchar2(10);
begin

  for counter in 1..2 loop
      EXECUTE IMMEDIATE 'BEGIN :ret := var'||counter ||'; END;' USING OUT output;
       dbms_output.put_line(output);
  end loop;

end;

Upvotes: 1

Related Questions