Home » Developer & Programmer » Precompilers, OCI & OCCI » ORA-01002: fetch out of sequence (Oracle 10.1)
ORA-01002: fetch out of sequence [message #398597] Thu, 16 April 2009 20:58 Go to next message
oracle123
Messages: 56
Registered: March 2009
Member
Hi all,

I am back with one more issue. I am fetching records through a cursor from database using Pro*C and inserting some records as shown below:

        EXEC SQL DECLARE cur_num CURSOR FOR
                 SELECT STUDENT_NUMBER
                   FROM STUDENTS
                  WHERE STDID = :temp_num
               ORDER BY STUDENT_NUMBER;

        EXEC SQL OPEN cur_num;

        EXEC SQL WHENEVER NOT FOUND DO break;

        EXEC SQL WHENEVER SQLERROR DO sqlerror();

        for(;;)
        {
           EXEC SQL FETCH cur_num INTO :std_num ;

	   /* Some calculations here */


           EXEC SQL INSERT INTO RESULTS(STUDENT_NUMBER,
                                        SUBJECT,
                                        MARKS)
                                 VALUES(:std_num,
                                        :sub,
                                        :marks);

           EXEC SQL COMMIT;        

        }

        EXEC SQL CLOSE cur_num;



This code works fine sometimes but ends up with this error sometimes:
ORA-01002: fetch out of sequence

and also do I need to do null termination once i fetch data from the database like this:

Quote:
EXEC SQL FETCH cur_num INTO :std_num ;

std_num.arr[std_num.len] = '\0';


std_num is declared as varchar datatype.

Thanks,
Scott.

Re: ORA-01002: fetch out of sequence [message #398625 is a reply to message #398597] Thu, 16 April 2009 23:51 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
ORA-01002: fetch out of sequence
 *Cause: This error means that a fetch has been attempted from a cursor
         which is no longer valid.  Note that a PL/SQL cursor loop
         implicitly does fetches, and thus may also cause this error.
         There are a number of possible causes for this error, including:
         1) Fetching from a cursor after the last row has been retrieved
            and the ORA-1403 error returned.
         2) If the cursor has been opened with the FOR UPDATE clause,
            fetching after a COMMIT has been issued will return the error.
         3) Rebinding any placeholders in the SQL statement, then issuing
            a fetch before reexecuting the statement.
 *Action: 1) Do not issue a fetch statement after the last row has been
             retrieved - there are no more rows to fetch.
          2) Do not issue a COMMIT inside a fetch loop for a cursor
             that has been opened FOR UPDATE.
          3) Reexecute the statement after rebinding, then attempt to
             fetch again.

Do not commit inside the loop, only when the functional transaction is completed that is at then end of your code.

Quote:
also do I need to do null termination once i fetch data from the database like this

It depends on Pro*C version but it is better to do it anyway.

Regards
Michel


Re: ORA-01002: fetch out of sequence [message #398771 is a reply to message #398597] Fri, 17 April 2009 08:54 Go to previous messageGo to next message
oracle123
Messages: 56
Registered: March 2009
Member
Michel,

Quote:
2) Do not issue a COMMIT inside a fetch loop for a cursor
that has been opened FOR UPDATE.


I do not have a FOR UPDATE clause in my insert statement. So it should work fine right?

Thanks,
Scott.
Re: ORA-01002: fetch out of sequence [message #398778 is a reply to message #398771] Fri, 17 April 2009 09:05 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
It depends on your compilation option.
Oracle mode allows it.
ANSI mode doesn't.

Regards
Michel
Re: ORA-01002: fetch out of sequence [message #398794 is a reply to message #398597] Fri, 17 April 2009 10:33 Go to previous messageGo to next message
oracle123
Messages: 56
Registered: March 2009
Member
Michel,

Here is my compilation option:

Quote:
proc mode=?

Pro*C/C++: Release 10.2.0.4.0 - Production on Fri Apr 17 10:21:20 2009

Copyright (c) 1982, 2007, Oracle. All rights reserved.

System default option values taken from: /oracle_client/product/v10r2/precomp/admin/pcscfg.cfg

Option name : mode=string
Current value : oracle
Restrictions : ansi, iso, oracle
Description : Code conformance to Oracle or ANSI rules
PCC-F-02135, CMD-LINE: User asked for help


Still I wonder why it is giving me that error. Please help. I am sincerely greatful for your prompt responses in these forums.

Thanks,
Scott.
Re: ORA-01002: fetch out of sequence [message #398796 is a reply to message #398794] Fri, 17 April 2009 11:11 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I don't know why you get this error, are you sure you didn't overwrite the mode value on the command line?
Anyway you must NOT commit inside the loop.

Regards
Michel
Re: ORA-01002: fetch out of sequence [message #398823 is a reply to message #398597] Fri, 17 April 2009 15:44 Go to previous messageGo to next message
oracle123
Messages: 56
Registered: March 2009
Member
Michel,

Lets suppose I have 100 records to insert into the database through that loop.

So if I commit after the loop and if there is an oracle error while inserting 50th record, how does it handle it?
Is it going to insert rest of the 99 records or else undo everything.

Thanks,
Scott.
Re: ORA-01002: fetch out of sequence [message #398829 is a reply to message #398823] Sat, 18 April 2009 00:33 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
If the error is on the statement, only the statement is rolled back, the previous inserts stays "pending" until you commit (or roll back).
If the error is on the session (for instance it is killed), the whole transaction is rolled back.

Regards
Michel
Re: ORA-01002: fetch out of sequence [message #398872 is a reply to message #398597] Sat, 18 April 2009 21:14 Go to previous message
oracle123
Messages: 56
Registered: March 2009
Member
Michel,

I used the COMMIT after loop and did not see this error. Thanks for the help.

Scott.
Previous Topic: SQLnet + c guides?
Next Topic: Transferring data between two databases
Goto Forum:
  


Current Time: Thu Mar 28 12:54:43 CDT 2024