0

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?

  • what DBMS are you using? – Max Vernon Mar 26 '13 at 18:48
  • i'm just checking the output of the cursor. – Ravi 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
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, privacy policy and cookie policy

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