We have a script for update of a specific column. In this script we are using a FOR UPDATEcursor. In the first version of the script we did not use the OFpart of the FOR UPDATEclause. As we found here and here this should not affect the script as all rows of all joined tables should be locked and therefore can be updated.
But when we were running the script although the log messages were printed no update was made on the column (column_a).
When we were changing the script with the cursor having an FOR UPDATE OF t1.column_athe same log messages appear but the updates are made correctly!
Can anyone explain why the script does not work without the OFclause?
The Oracle database version is 'Oracle Database 11g Enterprise Edition Release 11.2.0.3.0' also tested with 'Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit'.
Here's a simple version of the executed script:
BEGIN
-- anonymous procedure
DECLARE PROCEDURE update_column_a IS
c_to_find CONSTANT NUMBER := -42;
c_constant_value CONSTANT VARCHAR2 := 'value';
CURSOR c_my_cursor IS
SELECT t1.*
FROM table_1 t1, table_2 t2, table_3 t3
WHERE t1.t2_id = t2.id
AND t2.t3_id = t3.id
AND t3.column_b = c_to_find
-- FOR UPDATE with OF clause works
-- FOR UPDATE OF t1.column_a;
-- FOR UPDATE without OF clause does not
FOR UPDATE;
BEGIN
FOR cursor_rec IN c_my_cursor LOOP
IF cursor_rec.column_a IS NULL OR cursor_rec.column_a = '' THEN
dbms_output.put_line('Updating column...');
UPDATE t1 SET column_a = c_constant_value WHERE CURRENT OF c_my_cursor;
ELSE
dbms_output.put_line('Column already set...');
END IF;
END LOOP;
END update_column_a;
-- anonymous execution
BEGIN
update_column_a;
END;
END;
/
According to the Oracle 11G PL/SQL documentation here:
When SELECT FOR UPDATE queries multiple tables, it locks only rows whose columns appear in the FOR UPDATE clause.
So it might appear that in your example, no rows are locked and current of might not work.
However, when I try this:
declare
cursor c is
select ename, dname
from emp join dept on dept.deptno = emp.deptno
for update;
begin
for r in c loop
null;
end loop;
end;
I find that the rows of EMP and DEPT are locked (an update to either from another session hangs).
If I change the code to try to update one of the tables, it works fine for EMP:
declare
cursor c is
select ename, dname
from emp join dept on dept.deptno = emp.deptno
for update;
begin
for r in c loop
update emp
set ename = upper(ename)
where current of c;
end loop;
end;
But if I try to update DEPT instead I get the exception:
ORA-01410: invalid ROWID
This doesn't surprise me, because I have a foreign key from EMP to DEPT, and EMP will be "key-preserved" by the cursor's query, but DEPT will not be (i.e the same DEPT row can appear more than once in the results).
This suggests to me that the documentation is wrong, or at least misleading. However, I cannot see how your code could just not update the row, without raising an error as mine did.