SQL & PL/SQL

y number:= 5;
x varchar2(4):= 'y';
z number;

I want to be able to somehow store the value 5 in z by doing some operation on x , ie, without directly using variable y.

(In my application, I get a large # of pararmeters passed to me as scalar variables, and I have to check their values. I don't want several hundred if-statements with the parameter names hard-coded)

in Unix, I believe you can say z=eval(x) - in other words, you get the VALUE of the variable whose name is stored as a character string in x (not the string itself).

I've tried to get this via dynamic sql, but run into problems:

y number:= 5;
x varchar2(4):= 'y';
z number;
sql_str varchar(200);

sql_str := 'begin z :=' || vbl_name || ';end;' ;
execute immediate sql_str ;

This generates a plsql cmd
begin z := y; end;
but you get the error PLS-00201: Identifier 'Z'
must be defined

I thought this would be considered a block nested within the enclosing block, and would therefore know about the variables, but it doesn't seem to be true.

So I tried

sql_str := 'begin :var :=' || vbl_name || ';end;' ;
execute immediate sql_str using z;

This is fine for returning a value into z, but I can't figure out how to build
begin :var := y; end;
such that the value of y is known in the dynamic
block.

Help! This seems like such a reasonable thing to accomplish.

...Ellen