CODEWITHSUNDEEP
sqloracle-databasedynamic-sql
Xplosio
Xplosio

Reputation: 13

EXECUTE IMMEDIATE Update query with variable column

I am using the first time the command EXECUTE IMMEDIATE and I like to ask if you can help me.

I have a variable column, because of that I search on the internet and found the EXECUTION IMMEDIATE command but it gives me the following Error:

ORA-00904: "ACTUATION": invalid identifier
ORA-06512: at line 9

I am really confused, because ACTUATION is the "old_val" column and not the "column_name" column maybe you can help me. Here is the code:

declare
cursor such is 
select column_name, old_val, CTN.ID from abc.firsttable
ctn, def.secondtable#CC ctncc
where CTN.bup#rev = CTNCC.bup#rev
and ID in (127605689)
and CTN.bup#changefrom > to_date ('08/06/2017', 'dd/MM/YYYY');
begin
for srec in such loop 
EXECUTE IMMEDIATE
'update firsttable ctn2
set ctn.' || srec.column_name || ' = '|| srec.old_val ||' 
where CTN2.ID = '|| srec.id;
end loop; 
end;

Upvotes: 0

Views: 3163

Answers (2)

Wernfried Domscheit
Wernfried Domscheit

Reputation: 59513

Try this one:

EXECUTE IMMEDIATE
   'update firsttable ctn2
    set ctn.' || srec.column_name || ' = :OldVal 
    where CTN2.ID = :id' 
using srec.old_val, srec.id;

Upvotes: 4

Piotr Rogowski
Piotr Rogowski

Reputation: 3890

i think so, you must add more ' between params of string:

show this, in your code i added 3 ' between || srec.old_val ||

EXECUTE IMMEDIATE
'update firsttable ctn2
set ctn.' || srec.column_name || ' = '''|| srec.old_val ||'''
where CTN2.ID = '|| srec.id;
end loop; 
end;

Upvotes: 2

Related Questions