How to pass column name as a variable

[richardn]

How can I pass a column name as a parameter to a SQL statement ?
I have a table with columns named Log1,Log2,...,Log10.
I want to run a loop and for each cicle to update the alue in LogX column.
This is my code :
<<
DECLARE
logX myTable.Log1%TYPE;
BEGIN
for i IN 1..11 LOOP
logX := CONCAT('Log',TO_CHAR(i,'99'));
UPDATE myTable set &logX='010100000000000';
end loop;
END;
>>
"logX" is the varaible that contais the current column name.
Thanks,
Richard

[rigatoni]

You can build a sql statement in a character string
and then use the execute immediate command to run it.

Here's an example. I'm using a bind variable to hold
the column I want to update. It could also be passed
as a parameter to a stored procedure. The approach would
be basically the same.


Make a sample table and load it.

sys@AMSAA> create table testit
as select * from tab;

table created.

Here's the test data.

sys@AMSAA> select tname from testit;

TNAME
------------------------------
BONUS
DEMO
DEPT
EMP
PLAN_TABLE
SALGRADE
T1
TESTIT

8 rows selected.


Now we'll define a bind variable for the field we want to update.

sys@AMSAA> variable myfield varchar2(30);
sys@AMSAA> exec :myfield := 'tname';

PL/SQL procedure successfully completed.

Now let's define a new value for that field

sys@AMSAA> variable myval varchar2(30);
sys@AMSAA> exec :myval := 'green';

PL/SQL procedure successfully completed.

sys@AMSAA> select :myfield from dual;

:MYFIELD
--------------------------------
tname


sys@AMSAA> select :myval from dual;

:MYVAL
--------------------------------
green


You would typically not want to use :myval the way I have.
You'd want to use it as a bind variable in the execute immediate command so that your SQL is reusable. I left that out for clarity. I'm also going to update just the first row in this example.

sys@AMSAA> declare
2 l_sql varchar2(2000);
3 begin
4 l_sql := 'update testit set ' ||
5 :myfield ||' = ''' || :myval || ''' ' ||
6 'where rownum = 1';
7 execute immediate (l_sql);
8 end;
9
10 /

PL/SQL procedure successfully completed.

sys@AMSAA> select tname from testit;

TNAME
------------------------------
green
DEMO
DEPT
EMP
PLAN_TABLE
SALGRADE
T1
TESTIT

8 rows selected.

[richardn]

Thanks