Systems Engineering and RDBMS

Archive for January 11th, 2008

Fetch out of Sequence Error

Posted by decipherinfosys on January 11, 2008

This is another of those mistakes done by beginning PL/SQL programmers. You can see our previous post on fetching across commits here. In this post, we will cover ” the ORA-01002: Fetch out of Sequence” error.  Suppose the SQL code looks like this:

for x in ( select * from tbl where <some condition> for update)
/* some appl. logic over here */
update tbl set <some column values> where pk_col =;
end loop;

ERROR at line 1:
ORA-01002: fetch out of sequence
ORA-06512: at line 2

That is if you have a “FOR UPDATE” cursor and you are trying to do a fetch across commit on this cursor.  You will not be able to do so and will get the error that is the title of this post.  When the commit is done, the locks are released and the cursor is gone and there is nothing to fetch from.   The fix is the same as was mentioned in the previous post as well.

Posted in Oracle | Leave a Comment »