PDA

View Full Version : PL/SQL: eval OR Reference to specific column/field NUMBER

waldemar
12-13-01, 10:43
Originally I was looking for an EVAL-like function in PL/SQL: I have a variable that contains the name of a column. Now I would like to access these column values, something like

myvalue := myrecord_rec.MYVARIABLE;
^^^^^^^^^^ EVAL() ???

How is this done in PL/SQL?

But.....

...maybe eval just doesnt exist in PL/SQL...

is there a way to refer to a certain column number?? Instead of using names I could simply choose the "value of colum(numer 2)" or something similar....??

Thanks in advance
waldemar
12-13-01, 13:34
Alright, I guess

EXECUTE IMMEDIATE

does the trick... However even the smallest test doesnt work.... HEEEELP!!!!!!:

EXECUTE IMMEDIATE 'BEGIN HTP.P("test"); END;';
feliziaa
09-29-06, 10:24
Hi all,
I have the same problem: got a record and a variable containing the column name. What I need is a procedure get_value returning the value of the column in the record. Example:

cursor cur is SELECT * FROM mytable;
rec mytable%ROWTYPE;
col varchar2;
val varchar2;

BEGIN

col := 'ID';
...

open cur;
loop

fetch cur into rec;
exit when cur%NOTFOUND;

get_value(rec, col, val); -- that should return rec.ID into val !!!
...

end loop;
close cur;

END

Can anyone help?
Thank you!


Originally I was looking for an EVAL-like function in PL/SQL: I have a variable that contains the name of a column. Now I would like to access these column values, something like

myvalue := myrecord_rec.MYVARIABLE;
^^^^^^^^^^ EVAL() ???

How is this done in PL/SQL?

But.....

...maybe eval just doesnt exist in PL/SQL...

is there a way to refer to a certain column number?? Instead of using names I could simply choose the "value of colum(numer 2)" or something similar....??

Thanks in advance
LKBrwn_DBA
09-29-06, 11:45
Alright, I guess

EXECUTE IMMEDIATE

does the trick... However even the smallest test doesnt work.... HEEEELP!!!!!!:

EXECUTE IMMEDIATE 'BEGIN HTP.P("test"); END;';
If "test" is the variable containing the name of the column, then try something like this:
EXECUTE IMMEDIATE 'BEGIN HTP.P("'||test||'"); END;';
;)
joebednarz
09-29-06, 15:40
LK, I think your statement should be:


EXECUTE IMMEDIATE 'BEGIN HTP.P('''||test||'''); END;';


feliziaa,

Are you trying to write a cursor that will return data from different columns? If so, I'd recommend something like this:



PROCEDURE my_proc (col_to_select VARCHAR2)
IS
TYPE curvar_type is REF CURSOR;
curvar curvar_type;

cursor_value VARCHAR2(10);

BEGIN
OPEN curvar FOR 'SELECT ' || col_to_select || ' FROM my_table';

WHILE TRUE LOOP
FETCH curvar INTO cursor_value;
EXIT WHEN curvar%NOTFOUND;

... do whatever with the value you retrieve ...

END LOOP;
CLOSE curvar;

END;