-
How to pass column name as a variable
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
-
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.
-
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|