Invalid rowid error

cursorslockingoracleplsql

I'm trying to see, how UPDATE Lock helps to minimize error while dml (delete/update) operations.

declare
 cursor update_lock is select empno from emp where deptno=&no for update of sal;
 num number;
begin
 --for i in update_lock loop
 --end loop;
 open update_lock;
 loop
   fetch update_lock into num;
   exit when update_lock%notfound;
   dbms_output.put_line(num);
 end loop;
 update emp set sal=sal+10 where current of update_lock;
 close update_lock;
end;

I'm using very simple code to check, how does it works. But, it showing Invalid ROWID. Can anyone help me?

Best Answer

Your loop is going through all the rows in emp for deptno=&no, without updating them. When you reach the end of the result set (one past the last row), exit when udpate_lock%notfound triggers and you jump out of the loop, to the update statement.

At that point, the cursor is invalid. You've gone beyond the last row. So the update [...] where current of will fail - there is no current of for update_lock, the cursor isn't positioned on a row.

Related Question