Home » SQL & PL/SQL » SQL & PL/SQL » ora-010002 fetch out of sequence.
ora-010002 fetch out of sequence. [message #202317] Thu, 09 November 2006 01:57 Go to next message
somisetty.sridhar
Messages: 1
Registered: November 2006
Location: pune
Junior Member
hi friends,
I have stuck up with a problem. The problem is that i am getting fetch out of sequence(ORA-01002) sql exception.
This error is occurring when i am trying to get the resultset object from the ref cursor. By using the following code

stmt = conn.prepareCall(procedure(?,?));
stmt.registerOutParameter(2, OracleTypes.CURSOR);
stmt.setString(1,ttid);
stmt.execute();
rs=(ResultSet) stmt.getObject(2);

Actually in the procedure i am just opening the cursor and using select statement. I am not using any update statements or
commit statements.
So in my case this 'fetch out of sequence' is occuring when a fetch is attempted against a cursor that is no longer valid. As our code is ignoring any and all exceptions. And our calling code still attempts to fetch from the cursor.

Previous code for handling exceptionException
When others THEN
null;
END

So if we rewrite the code for handling the exception like the below means will it solve the problem or not.
EXCEPTION
WHEN OTHERS THEN
err_code := SQLCODE;
err_msg := substr(SQLERRM, 1, 200);
INSERT INTO tt_audit_table (procedure_name, load_date, error_number, error_message)
VALUES ('get_dispatch_tasks', sysdate, err_code, err_msg);
END;

If you think this is not the right approach means can you please suggest the right approach.So that i can escape this exception in future.By handling it in the pl/sql package level.
thanks in advance.

Regards
Sridhar Somisetty
Re: ora-010002 fetch out of sequence. [message #202406 is a reply to message #202317] Thu, 09 November 2006 10:47 Go to previous message
andrew again
Messages: 2577
Registered: March 2000
Senior Member
you need to make sure that your code is using the same connection from the time it opens the cursor until it retrieves the last row. Aslo - make sure that the ODBC connection (or whatever you're using doesn't have any kind of autocommit enabled.

If you start a sql trace before you open the resultset and turn it off again retrieving it, you'll be able to count the commits in the trace file to confirm what's happening.

execute immediate 'alter session set tracefile_identifier = ''TEST''';

-- trace file directory
SELECT VALUE 
  FROM v$parameter
 WHERE NAME = 'user_dump_dest';

then just look in that location for your trace file(s) *_TEST.trc


This script is from oramag in 2000
#!/usr/bin/ksh
# Script to calculate # of commits and rollbacks from a trace file
# Takes trace file as input
#
# XCTEND rlbk=%d rd_only=%d
# ----------------------------------------------------------------------------
#  XCTEND      A transaction end marker.
#  rlbk        1 if a rollback was performed, 0 if no rollback (commit).
#  rd_only     1 if transaction was read only, 0 if changes occurred.
grep XCTEND $1  > /tmp/read.lst
commit=`grep XCTEND /tmp/read.lst | grep "rlbk=0, rd_only=0" | wc -l`
trans=`grep XCTEND /tmp/read.lst | wc -l`
rollback=`grep XCTEND /tmp/read.lst | grep "rlbk=1, rd_only=0" | wc -l`
echo " "
echo "Transactions"
echo "~~~~~~~~~~~~"
echo "Transaction Ends  $trans"
echo "Real Commits      $commit"
echo "Rollbacks         $rollback"


to trap debug messages, use an autonomous transaction.


CREATE OR REPLACE PROCEDURE put_debug_sql (i_seq IN INTEGER, i_msg VARCHAR)
IS
   PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
   INSERT INTO DEBUG_SQL
        VALUES (i_seq, SYSDATE, i_msg);
   COMMIT;
END;
/


...
EXCEPTION
WHEN OTHERS THEN
put_debug_sql (123, SQLERRM);
RAISE;

Previous Topic: Creating a Total per hour
Next Topic: difference between Join and Inner Join
Goto Forum:
  


Current Time: Fri Dec 02 12:32:08 CST 2016

Total time taken to generate the page: 0.09544 seconds