Oracle Tips and Tricks — David Fitzjarrell

May 30, 2008

Feelin’ bad, Feelin’ blue, Gots me a ORA-01002

Filed under: General — dfitzjarrell @ 18:45

Most application programmers do their utmost to make their code as efficient as possible. Of course sometimes that means doing things that, at first blush, seem eminently appropriate but end up as, well, disasters by producing errors which aren’t quite understood. A good case in point is the ORA-01002 error. Oracle states this is a ‘fetch out of sequence’, but what, exactly, does that mean? Let’s look at a PL/SQL example and find out.

I’ve written before about committing inside of a loop but the ORA-01555 isn’t the only error that can cause. Depending upon how the cursor is written it could throw an ORA-01002 in your face:

SQL> --
SQL> -- "The Road to Hell is paved with
SQL> -- good intentions"
SQL> --
SQL> -- The pavement starts here...
SQL> --
SQL> -- We've opened a cursor for update
SQL> -- then do the dastardly deed of
SQL> -- committing inside the loop
SQL> --
SQL> -- When a commit is issued during a
SQL> -- select ... for update transaction
SQL> -- the cursor is closed
SQL> --
SQL> -- So, we can't fetch any more
SQL> -- records
SQL> --
SQL> -- But, we try anyway
SQL> --
SQL>
SQL> declare
  2          --
  3          -- This is perfectly fine
  4          -- provided we don't commit
  5          -- somewhere in the loop
  6          --
  7          cursor get_emp_info is
  8          select empno, ename, sal, comm
  9          from emptest
 10          for update;
 11
 12          --
 13          -- This is a bad omen indicating
 14          -- we're keeping track of how many
 15          -- records we've updated
 16          --
 17          -- I see an interim commit looming
 18          -- on the horizon
 19          --
 20          ctr number:=0;
 21
 22  begin
 23          --
 24          -- So far, so good
 25          --
 26          -- We fetch the data from the cursor
 27          -- and go off on our merry way
 28          --
 29          for emprec in get_emp_info loop
 30
 31                  --
 32                  -- And we update the data ...
 33                  --
 34                  update emptest set sal = sal + (emprec.sal*.1), comm = emprec.sal*.2
 35                  where empno = emprec.empno;
 36
 37                  --
 38                  -- And we keep count ...
 39                  --
 40                  ctr := ctr+1;
 41
 42                  if ctr > 4 then
 43                          --
 44                          -- And we kiss it goodbye
 45                          --
 46                          -- This causes the ORA-01002
 47                          --
 48                          commit;
 49                  end if;
 50
 51          end loop;
 52
 53          --
 54          -- We'll never get here
 55          -- because of what we did
 56          -- back there
 57          --
 58          commit;
 59
 60  end;
 61  /
declare
*
ERROR at line 1:
ORA-01002: fetch out of sequence
ORA-06512: at line 29


SQL>

Note that the cursor is written as a select … for update, a special sort of select which, when met with either a commit or a rollback will close. Unceremoniously. And you won’t know it’s happened until you see that ugly ORA-01002 staring you in the face:

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

What this means is that you’ve tried to fetch records from a now-invalid cursor, one where no data is available. Of course the goal was to conserve on rollback/undo space by releasing the currently occupied blocks to make room for the next ‘block’ of data to be modified. Unfortunately the cursor was closed with the commit (or rollback) and there was no ‘next block’ of records to modify. Oracle tried dutifully to honor your request, but fought against itself in doing so, raising the ‘fetch out of sequence’ error. And the problem is resolved by not committing inside the loop. Oracle does a fine job all by itself of managing rollback/undo segments so additional efforts by the application programmers are not necessary.

This same error can raise its ugly head in Java, Pro*C/C++ and OCI programs for similar reasons, and also for attempting to fetch beyond the last row in the result set (which, by the way, is something you can do in PL/SQL because it won’t raise an exception; it will simply keep fetching the same last row over and over and over and over and …) Sometimes good intentions create more problems than they attempt to solve.

Commitment is good. Just not inside of a FOR loop.

Advertisements

4 Comments »

  1. great post, keep it up

    Comment by Anonymous — December 14, 2008 @ 07:49 | Reply

  2. is this true for every Oracle version or it happen in specific versions, say 11g ?

    Comment by Anonymous — October 5, 2011 @ 14:26 | Reply

  3. It happens in every Oracle version.

    Comment by d_d_f — October 6, 2011 @ 17:16 | Reply

  4. […] one can receive this error for fetching across commits let’s look at that for a moment. As the ORA-01555 error indicates to you that the UNDO […]

    Pingback by The Dreaded ORA-01555 « Oracle Tips and Tricks — July 23, 2012 @ 10:07 | Reply


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Create a free website or blog at WordPress.com.

%d bloggers like this: