Solved

ORA-01002: fetch out of sequence

Posted on 2002-06-20
24
4,121 Views
Last Modified: 2007-11-27
what causes this error?
how to avoid this error?

first in general,
later i may describe a detailed problem

meikl ;-)
0
Comment
Question by:kretzschmar
  • 11
  • 7
  • 2
  • +3
24 Comments
 
LVL 34

Expert Comment

by:Mark Geerlings
ID: 7096228
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.
0
 
LVL 3

Expert Comment

by:mnicoras
ID: 7096544
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
0
 
LVL 27

Author Comment

by:kretzschmar
ID: 7097889
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 ;-)
0
 
LVL 2

Expert Comment

by:333
ID: 7097953
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?
0
 
LVL 2

Accepted Solution

by:
333 earned 150 total points
ID: 7098010
searching metalink i found one doc that may be helpfull (search metalink for  Doc ID:96085.996)
a little excerpt from doc:
"Unfortunately for you, it is not a bug, it is a design error. Cursors are supposed to become invalid at the end of a transaction (ANSI standard) - and the rollback/commit ends the transaction. "

so, you may try to wrap every loop that has commit/rollback as autonomous transaction.

also, somewhere i saw (can't find it), that ORA-1002 may be returned if the value of the bind variable has been changed while cursor wasn't closed and trying to fetch from it without CLOSE/OPEN. this was true for precompilers, but who knows, maybe it's true in stored procs?
0
 
LVL 27

Author Comment

by:kretzschmar
ID: 7098048
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 ;-)
0
 
LVL 27

Author Comment

by:kretzschmar
ID: 7098059
>of the bind variable has been changed
the bindvariables remains static until the cursor is closed
0
 
LVL 2

Expert Comment

by:333
ID: 7098094
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
0
 
LVL 27

Author Comment

by:kretzschmar
ID: 7098159
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 ;-)
0
 
LVL 2

Expert Comment

by:333
ID: 7098189
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.
0
 
LVL 27

Author Comment

by:kretzschmar
ID: 7098408
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 ;-)

0
 
LVL 2

Expert Comment

by:333
ID: 7098452
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
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 34

Expert Comment

by:Mark Geerlings
ID: 7098604
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.
0
 
LVL 27

Author Comment

by:kretzschmar
ID: 7098716
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 ;-)
0
 
LVL 2

Expert Comment

by:333
ID: 7098759
well, article says that commit may cause this error also.  so 1% for commit :)
0
 
LVL 27

Author Comment

by:kretzschmar
ID: 7098793
yep,
1% for commit,
because commit seems to be only a problem,
if a for update-clause is given

meikl ;-)
0
 
LVL 3

Expert Comment

by:mnicoras
ID: 7098854
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 ... :-)
0
 
LVL 27

Author Comment

by:kretzschmar
ID: 7099178
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 ;-)
0
 
LVL 27

Author Comment

by:kretzschmar
ID: 7103290
:-( 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 ;-)
0
 
LVL 2

Expert Comment

by:333
ID: 7103377
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.
0
 
LVL 27

Author Comment

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

meikl ;-)
0
 
LVL 27

Author Comment

by:kretzschmar
ID: 7104369
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 ;-)
0
 

Expert Comment

by:gshifrin
ID: 8287666
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();
0
 

Expert Comment

by:Gstarr
ID: 8378786
I just want to add that gshifrin's solution, setting autocommit off, worked for me.

grtz Gstarr
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

760 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now