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

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

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

  • what DBMS are you using?
    – Hannah Vernon
    Mar 26, 2013 at 18:48
  • i'm just checking the output of the cursor.
    – Ravi
    Mar 26, 2013 at 18:52
  • @jWeavers: please remember to always include a tag for the database system you're using (unless it's a database-agnostic question).
    – Mat
    Mar 26, 2013 at 19:57

1 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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.