Apr 292013
 
create sequence ora 
start  with -1002 minvalue -1002;

begin
  for commits in (
    select loops 
    from   queries
    where  we = 'specify'
    for    update
  ) loop
    dbms_output.put_line(
      'Causes an ' || sqlerrm(ora.nextval));
    commit;
  end loop;
end;
/

Error report:
ORA-01002: fetch out of sequence
Issuing a commit after opening a cursor with the "for update" releases the row locks
and invalidates the cursor, meaning you can no longer fetch data out of it!

Click here for a complete script to see this in action.

 Leave a Reply

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

(required)

(required)