ORA-01002: fetch out of sequence

sql oracle oracle11g oracleclient
m.edmondson picture m.edmondson · Apr 25, 2013 · Viewed 13k times · Source

I'm getting the following oracle error when my app is deployed on the server only, and not when running the exact same executable on my local machine:

enter image description here

The stack trace points to a simple select statement with a join, that I can successfully execute via TOAD locally and via SQL Plus on the problem server.

The top half of the stacktrace is:

Oracle.DataAccess.Client.OracleException ORA-01002: fetch out of sequence
  at Oracle.DataAccess.Client.OracleException.HandleErrorHelper(Int32 errCode, OracleConnection conn, IntPtr opsErrCtx, OpoSqlValCtx* pOpoSqlValCtx, Object src, String procedure, Boolean bCheck)
  at Oracle.DataAccess.Client.OracleException.HandleError(Int32 errCode, OracleConnection conn, IntPtr opsErrCtx, Object src, Boolean bCheck)
  at Oracle.DataAccess.Client.OracleDataReader.Read()
  at System.Data.Common.DataAdapter.FillLoadDataRow(SchemaMapping mapping) 
  at System.Data.Common.DataAdapter.FillFromReader(DataSet dataset, DataTable datatable, String srcTable, DataReaderContainer dataReader, Int32 startRecord, Int32 maxRecords, DataColumn parentChapterColumn, Object parentChapterValue)
  at System.Data.Common.DataAdapter.Fill(DataTable[] dataTables, IDataReader dataReader, Int32 startRecord, Int32 maxRecords)
  at System.Data.Common.LoadAdapter.FillFromReader(DataTable[] dataTables, IDataReader dataReader, Int32 startRecord, Int32 maxRecords)
  at System.Data.DataTable.Load(IDataReader reader, LoadOption loadOption, FillErrorEventHandler errorHandler)
  at Reconciliation.Models.Legacy.EntityDbEnvironment.OpenEntity(String sql)
  at Reconciliation.Models.Legacy.EntityDbEnvironment.Open(String& sql, DataTable& datatable)
**at myProject.Checks.ExecuteSql()**

I can only imagine this is an oracle client problem, but where should I look to resolve this?

Answer

Antoine Dijoux picture Antoine Dijoux · Jul 19, 2017

I had that issue too. My application was working fine on a machine, and the exact same executable on another machine was getting that fetch out of sequence exception.

My query is a big one which takes a few minutes to run. It is ran by a COM+. I realised that my two environments had a slightly different Component Services configuration. The failing environment had a transaction timeout set to 60s, the other one was set to much more.

To solve this I simply had to go to Component Services, right click on My Computer, Properties, Options, and increase the Transaction timeout.

It makes sense now as the fetch out of sequence exception is happening when you try to access a closed cursor. I understand that the COM+ timeout closed the transaction, therefore the cursor, and thrown that Oracle exception.

Picture of the property to update

Related questions

How do I do top 1 in Oracle?

How do I do the following? select top 1 Fname from MyTbl In Oracle 11g?

Read More
Display names of all constraints for a table in Oracle SQL

I have defined a name for each of the constraint for the multiple tables that I have created in Oracle SQL. The problem is that to drop a constraint for the column of a particular table I need to know …

Read More
Timestamp conversion in Oracle for YYYY-MM-DD HH:MM:SS format

I'm trying to insert records with YYYY-MM-DD HH:MM:SS format into Oracle from Netezza, but I'm getting invalid date type. How can I accomplish this one? CREATE TABLE AM_PROGRAM_TUNING_EVENT_TMP1 ( ST TIMESTAMP, ET TIMESTAMP, MAS_DIV_…

Read More