Hello all!
I've created a cursor and using a for loop I generate dml statements to add comments into a text field.
I've noticed something strange. Not all of the generated sql statement seem to run, although they are all individually correct.
I define a cursor
I use a for rec in cursor loop
within this loop i generate the sql statements and use execute immediate-
I have even added a commit statement within the loop.
My cursor-loop generates the statements similar to:
Update tablex set comments = commenst || ' new_comment' where primary_key = 1
Update tablex set comments = comments || ' new_comment' where primary_key = 2
Update tablex set comments = comment || ' another_new_comment' where primary_key = 1
If the update is on the same row (i.e. it has the same primary_key). The 2nd update is not made.
ie. In my example, the first and second statements take effect. The third does not.
If I run the generated sql myself, one statement after the other, there's no problem.
If I rerun the skript the duplicate is then also dealt with.
Why is this? And does anyone know a workaround?
Example:
I have had to rewrite this. Table names were all in foreign language.
The real cursor returns all necessary rows.
The real generated statements are all valid. If I rerun the code for the remaining duplicates they are subsequency correctly executed (This time around there are no duplicates).
Table 1 contains rooms, table 2 contains properties. I want to transfer comments from rooms to properties.
Table1: room_id, prop_id, comments
table2: prop_id, comments
CODE:
DECLARE
CURSOR cur_transferablecomments is
Select
eg.room_id,
eg.prop_id,
el.comments,
eg.comments as new_comments
from table1 eg, table2 EL
where eg.prop_id=el.prop_id
and el.comments not like '%' ||eg.comments||'%'
;
v_statement varchar2(800):='dummy';
BEGIN
for rec in cur_transferablecomments LOOP
v_statement := 'UPDATE table2 SET comments= '''|| replace(rec.comments,'''','''''') || ' ' || replace(rec.new_comments,'''','''''') || ''' WHERE prop_id= '||rec.prop_id;
execute IMMEDIATE v_statement ;
commit;
END LOOP;
END;
I also tried something without execute immediate like this:
(please assume the update statement is valid)
DECLARE
CURSOR cur_transferablecomments is
Select
eg.room_id,
eg.prop_id,
el.comments,
eg.comments as new_comments
from table1 eg, table2 EL
where eg.prop_id=el.prop_id
and el.comments not like '%' ||eg.comments||'%'
;
BEGIN
for rec in cur_transferablecomments LOOP
UPDATE table2
SET comments= '''|| replace(rec.comments,'''','''''') || ' ' || replace(rec.new_comments,'''','''''') || '''
WHERE prop_id= '||rec.prop_id ;
END LOOP;
END;