ORA-01002: fetch out of sequence

The Oracle documentation says this happens only (or at least mainly) in 3-GL programs that use OCI to interact with Oracle and only if they attempt to "fetch" either before opening a cursor or after closing it.

I have seen this error at times in PL\SQL blocks and SQR programs.  Sometimes it is true, that the program attempted a fetch before opening a cursor or after closing it.  Other times though, the code looked correct.

Another situation that seems to cause this sometimes (but certianly not always) is with a commit inside of a cursor loop.  PL\SQL cursors that fetch records via a database link seem to get this error more often.

Hi,

one bad example can be:

declare
cursor c is select dummy from dual for update;
str dual.dummy%type;
begin
 open c;
 loop
  exit when c%notfound;
  fetch c into str;
  commit;
 end loop;
 close c;
end;

another bad one which causes the same problem is:

declare
begin
 for c in (select dummy from dual for update) loop
  commit;
 end loop;
end;

NOTE:
avoid them by good programming, NO COMMIT inside fetching a cursor which contains FOR UPDATE clause.


best regards,
Marius Nicoras

well,
thanks for your responses,
sorry for delay, i had to sleep ;-)

now to the problem,
which does not fit to your comments

simplified sample

....
  type t_cursor is ref cursor;
  v_cursor t_cursor;
....

  v_statement := 'SELECT  rowid'||
                 ' FROM '||pi_tabelle||
                 ' WHERE fieldname = :pi_parameter';
  open v_cursor for v_statement using in pi_parameter;
  loop
    fetch v_cursor into v_rowid;
    exit when v_cursor%notfound;
    call_a_customizeable_workprocess(v_rowid);
  end loop;
     
in the
  call_a_customizeable_workprocess(v_rowid);
are primary dynamic-sql dmls and dynamic package-procedure calls with commits and rollbacks

the cursor self runs over tenthousends-records,
the dmls affects over 10 tables with inserts and updates,
so that i cannot not
avoid a commit/rollback within the loop because

-the rollback-segement becomes to small
-there may some asynchronous calls, which needs commited data

as stated, there is no for update clause
and all is pure pl/sql-coded, no database-link

the fitting part is this line of markgeers comment
>situation that seems to cause this sometimes (but
>certianly not always) is with a commit inside of a cursor
>loop

the question is why does this happen (sometimes),
because it is not documented (i found nothing)
and how to avoid this

meikl ;-)

are you getting this error allways or just sometimes?
i found this in docs (well, it's from 8i, but maybe it helps): "This [ORA-01002] can occur if an attempt was made to FETCH from an active set after all records have been fetched."
so, looks like that instead of returning NO_DATA_FOUND sometimes oracle returns ORA-01002.maybe you have a loop somewhere in call_a_customizeable_workprocess that doesn't have a exit when cursor%notfound? or maybe you handle NO_DATA_FOUND with exeption when NO_DATA_FOUND?

to 333

using oreacle 8.1.7

>maybe you have a loop somewhere in
>call_a_customizeable_workprocess that doesn't have a exit
>when cursor%notfound?

no

>maybe you handle NO_DATA_FOUND with exeption when
>NO_DATA_FOUND

no


>Cursors are supposed to become invalid at the end of a
>transaction (ANSI standard) - and the rollback/commit
>ends the transaction.

sounds logical, but some thousends records are
commited/rollbacked until this error comes up,
which should not work, if this would true

meikl ;-)

>of the bind variable has been changed
the bindvariables remains static until the cursor is closed

have you read the doc on metalink?
here's a little bit more from it:
"Oracle has alway been overgenerous by failing to
invalidate cursors - so code can be written which
commits, or rolls back, in the middle of a cursor
loop. However, such code may fail in a completely
random way - you say that you haven't observed a
failure if you replace the ROLLBACK with a COMMIT -
if you search the archive you will find that this
is just luck on your part, other users have complained
of exactly this problem in the past when using a
commit in a cursor loop.

You should always code on the assume that every
cursor becomes invalid on rollback/commit - or
write your code to trap the inevitable, but
unpredictable, 1002 error."

so you may get this error after few fetches, after thousand fetches or you may not get this error at all (if you're lucky) :)
anyway, i think that autonomous transactions may help here

hmm,
in this case,
there must be a
lot of lucky coders,
because i saw a lot of code
from others, which do such

for example, i know a routine
which loops about millions of records
of a table and transports some data
to another table (updates).
each 1000 record is done a commit.

there is no problem and
no autonomous transaction

about metalink, i've no account there

meikl ;-)

me too never had such error (lucky me :), but as stated above, you may don't get this error (as most people do) or you may get it.
once i wrote a program that selects from few tables and inserts into other few tables. there wasn't millions of records, only about 100000. i used bulk inserts/collects and sometimes i get "end of file on communication channel" :)
of course, noone else had such error ....

it just proves, that any case is unique ;)

if you're intrested, i can post article from metalink here.

hmm,

sounds like,
if i want to by an airplane,
the constructor explains me,
sometimes the plane breaks in air,
didn't know why,
maybe because its heavier than air,
but mostly you can fly and land properly.

i would not fly with this plane.

>if you're intrested,
>i can post article from metalink here.

would be nice, if it is a long article,
then you could send it to me via eMail
to info@meikl.de

meikl ;-)

i've sent article to you.
it's ANSI standard that after transaction ends, all cursors for this transaction should be invalidated. oracle tries to overcome such limitation, but sometimes it doesn't work :)
i saw such error first time, and all the time i was sure that you may freely commit/rollback transaction in cursor loop (simple loop, not FOR UPDATE one) without any problems. well... now i don't know what to think :o

There's interesting (and hopefully helpful) information in some of these comments.  I guess I've usually been fortunate too with commits inside a cursor loop.  Often that is the only (or at least the easiest) way to get a job done that involves processing lots of rows.

Yes, autonomous transactions may be able to help you, but be careful with them.  Make sure you do not use them with database links (distributed transactions).  Unfortunately Oracle does not support that combination, and you may get strange errors or unpredictably failures if you try it.

333,

thanks for the article

the artice indicates,
that there is a "bug" with
oracle version 8.1.7, when a
rollback in a loop is done.

commits seems not to be the problem.

i will check this on monday,
and if i can confirm this,
then i can code a workaround,
and you gets the points, 333

i've raised the points up to 150

markgeer, mnicoras,
i will post, after my check at next monday,
50 pts questions for you both
for your in general comments.

in conclusion,
i'm to 99% sure that a rollback in
a cursor-loop
causes this error
on an oracle 8.1.7 server

in the meantime
have a nice weekend

meikl ;-)

well, article says that commit may cause this error also.  so 1% for commit :)

yep,
1% for commit,
because commit seems to be only a problem,
if a for update-clause is given

meikl ;-)

Hi,

sorry to bother but the following example is your problem ...

declare
begin
for c in (select rowid,dummy from dual for update) loop
 commit;
end loop;
end;

looks like the problem is that somehow you will update the record set you have selected ...

I'm right?

it's not a bug as I see, it's just a case when it happens in your plsql code to update the result set ...

best regards,
Marius Nicoras

ps: I had to sleep also ... :-)

just for clarify, Marius,

there is definitivly NO for update-clause
by the problem described above

sure would a cursor with a for update-clause
with a commit in the loop
cause the ora-01002 exception,
in all oracle-server versions,
i guess

meikl ;-)

:-( couldn't reproduce this problem with following sample

declare
  type t_cursor is ref cursor;
  --cursor v_cursor is
  --select sendungs_id from sendungen;
  v_count number := 0;
  dummy atable.id%TYPE;
  v_cursor t_cursor;
begin
  dbms_output.put_line('First Loop->Inserts');
  open v_cursor for 'select id from atable';
  loop
    fetch v_cursor into dummy;
    exit when v_cursor%NOTFOUND;
    insert into mk_text (ID) values (dummy);
    v_count := v_count + 1;
    if (mod(v_count,1000) = 0) then
      dbms_output.put_line('Before Rollback');
      rollback;
    else
      commit;
    end if;
  end loop;
  close v_cursor;

  dbms_output.put_line('Second Loop->Updates');
  v_count := 0;
  open v_cursor for 'select id from mk_text order by flag desc';
  loop
    fetch v_cursor into dummy;
    exit when v_cursor%NOTFOUND;
    execute immediate 'update mk_text set flag = ''X'' where id = :dummy' using in dummy;
    v_count := v_count + 1;
    if (mod(v_count,1000) = 0) then
      dbms_output.put_line('Before Rollback');
      rollback;
    else
      commit;
    end if;
  end loop;
  close v_cursor;

exception
  when others then
    if v_cursor%ISOPEN then
      close v_cursor;
    end if;
    dbms_output.put_line(substr(sqlerrm,1,250));
END;
 

this code runs about 35000 records,
each record is commited, each 1000th
record is rollbacked,
in both loops

runs fine, which is just not this what i had expected :-(

searching further, take this just as an info

meikl ;-)

you should try something like:

...
begin
-- transaction starts   insert into atable (id) values (23456);   open c for 'select id from atable';
   loop
      fetch c into dummy;
      exit when c%NOTFOUND;
      if dummy=100 then
-- transaction ends;
         rollback;      end if;
   end loop;
-- transaction ends;
   commit;
end;
so, as you see, your cursor should fetch new inserted value, but after rollback this value will gone (dunno where :) and you get fetch out of sequence.
i didn't tested this example, will try it for myself later.

hehe,
yes, this i would understand, 333,
but such a configuration isn't given
by my problem->threetimes checked

meikl ;-)

well,

autonomous transaction solves this problem,
even if i don't know what this error causes,
nor that it is not so simple to reproduce.

thanks for your help 333

markgeer, mnicoras,
watch out for your points

meikl ;-)

When I ran into this problem, the solution was to turn off autocommit and then do an explicit commit after each update:
         conn.setAutoCommit(false);
...
         conn.commit();

I just want to add that gshifrin's solution, setting autocommit off, worked for me.

grtz Gstarr