EXECUTE IMMEDIATE Update query with variable column

Question

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;

Construct the SQL query and print it out. 95% of the time, the error is obvious. — Gordon Linoff
– Gordon Linoff, Commented Jun 13, 2017 at 11:54

Wernfried Domscheit · Accepted Answer · 2017-06-13 12:08:38Z

4

Try this one:

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

answered Jun 13, 2017 at 12:08

Wernfried Domscheit

60.4k10 gold badges93 silver badges132 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Matthew McPeak Over a year ago

This is better than the accepted answer, in terms of performance and scalability.

Piotr Rogowski · Accepted Answer · 2017-06-13 12:00:14Z

2

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;

answered Jun 13, 2017 at 12:00

Piotr Rogowski

3,90021 silver badges24 bronze badges

2 Comments

Xplosio Over a year ago

Thank you! Sometimes it is so simple you can't see the problem.

Wernfried Domscheit Over a year ago

Note, this will fail for values like don't quote it like this

Collectives™ on Stack Overflow

EXECUTE IMMEDIATE Update query with variable column

2 Answers 2

1 Comment

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related