CODEWITHSUNDEEP
oracle-databaseplsqlplsqldeveloper
Shravan Yadav
Shravan Yadav

Reputation: 1317

Get value from variable created on run time from another variable in Oracle PLSQL

I have this thing in unix for example below

filename1_temp=3
var1=filename1
var2=${var1}_temp
var3=\$${var2}

so basically var3 contains the value stored in variable filename1_temp which is 3. I need this implementation in oracle PL/SQL. For example

filename1_temp:=3
var1:="filename1";
var2:=var1||"_temp";
var3:=???

How will I do it in PL/SQL??

Please suggest.

Upvotes: 1

Views: 890

Answers (2)

user272735
user272735

Reputation: 10648

You'd like to evaluate a string as a PL/SQL code. Essentially the following code should do what you're asking but unfortunately it doesn't work:

declare
  filename1_temp constant varchar2(32767) := 'magic';
  var1 constant varchar2(32767) := 'filename1';
  var2 constant varchar2(32767) := var1 || '_temp';
  var3 varchar2(32767);
  function eval(p_var in varchar2) return varchar2 is
    v_ret varchar2(32767);
  begin
    execute immediate 'begin :ret := ' || p_var || '; end;'
      using out v_ret;
    return v_ret;
  end;
begin
  dbms_output.put_line('var3 = ' || var3);
  var3 := eval(var2); -- expecting magic
  dbms_output.put_line('var3 = ' || var3);
end;
/

It fails with:

ORA-06550: line 1, column 15:
PLS-00201: identifier 'FILENAME1_TEMP' must be declared

because execute immediate runs in SQL context and it doesn't know the details of the anonymous PL/SQL block. So you have to make the variable accessible from SQL too. Here is an example where I have used public package variable:

create or replace package so49 is
  filename1_temp varchar2(32767);
end;
/
show errors

declare
  var1 constant varchar2(32767) := 'so49.filename1';
  var2 constant varchar2(32767) := var1 || '_temp';
  var3 varchar2(32767);
  function eval(p_var in varchar2) return varchar2 is
    v_ret varchar2(32767);
  begin
    execute immediate 'begin :ret := ' || p_var || '; end;'
      using out v_ret;
    return v_ret;
  end;
begin
  so49.filename1_temp := 'magic';
  dbms_output.put_line('var3 = ' || var3);
  var3 := eval(var2); -- expecting magic
  dbms_output.put_line('var3 = ' || var3);
end;
/

The magic happens:

SQL> @so49

var3 =
var3 = magic

PL/SQL procedure successfully completed.

SQL>

Hope this helps.

Upvotes: 1

vishnu sable
vishnu sable

Reputation: 358

I am not sure what you are looking for but do you want this kind of stuff..

declare
  filename1_temp number(4);
  var1           number(4);
  var2           varchar2(100);
  var3           varchar2(100);
begin
  filename1_temp := 3 ;
  var1 := filename1_temp;
  var2           := to_char(var1) || '_temp';
  var3           := var2;
  dbms_output.put_line(var3);
end;

Upvotes: 0

Related Questions