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

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

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>