Invalid rowid error

Question

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?

Answer 1

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.