| ora-010002 fetch out of sequence. [message #202317] |
Thu, 09 November 2006 01:57  |
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  |
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;
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
