Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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?

share|improve this question
what DBMS are you using? – Max Vernon Mar 26 '13 at 18:48
i'm just checking the output of the cursor. – jWeaver Mar 26 '13 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 '13 at 19:57
up vote 1 down vote accepted

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.

share|improve this answer

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

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