3 Replies Latest reply: Dec 3, 2010 3:18 PM by 821051 RSS

    ORA-01002: fetch out of sequence

      hi All,

      When I call Stored Procedure from .Net C# using ODP.NET 9.2 it is giving me an error. ORA-01002: fetch out of sequence.. I am not using trasaction in that procedure.

      can anyone please help me out in this:

        • 1. Re: ORA-01002: fetch out of sequence
          gdarling - oracle

          Usually that occurs when the stored procedure fetches from a database link. You may want to try wrapping the ODP.net code in a transaction to elimanate the error.

          Hope it helps,
          • 2. Re: ORA-01002: fetch out of sequence
            Mark Hoxey
            From the Oracle database (10g & 11g) documentation:

            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.

            It's hard to diagnose without seeing the stored procedure code.


            • 3. Re: ORA-01002: fetch out of sequence
              Greg your solution really worked fine.

              I just created a transaction object from that connection and assigned to the command object and then executed the stored procedure.

              It really worked just like that and I am able to fetch the data in a dataset.

              Thanks Greg!!!