10 Replies Latest reply on Aug 23, 2007 7:50 PM by JustinCave

    Fetch out of sequence error

    587785
      Hi,
      In my stored procedure I have a select statement like the below
      Create procedure testDBLink
      cur OUT Sys_RefCursor
      AS
      Begin
      Select * from TABLE@DBLINK;
      End

      Now I am trying to call the procedure from .NET application then I am getting this error.
      ORA-01002: fetch out of sequence.
      Idid some googling I found that when using Commit in Procedure then we get this type of error, but in my procedure iam not using any commit. or I need to set the AutoCommitt OFF.
      If I am caling the just only the Select * from Table@DBLINK from .Net application then I am not getting any error.
      If I put that select statement in Procedure then I am getting Fetch out of Sequence error.

      Thanks
        • 1. Re: Fetch out of sequence error
          546595
          this?
          Create or replcae procedure testDBLink (cur OUT Sys_RefCursor)
          AS
          Begin
          open cur for Select * from TABLE@DBLINK;
          End;
          http://www.oracle-base.com/articles/8i/UsingRefCursorsToReturnRecordsets.php

          added link
          Message was edited by:
          devmiral
          • 2. Re: Fetch out of sequence error
            587785
            Hi,
            The link is about the how to call the procedure returning refcursors in .Net/Java.
            I don't have any problem in calling the procedure which is returing Cursors from .Net. But the fetch out of sequence error is coming for the procedure which is getting data by using Database Link (to SQLServer) only. If the procedure is returing data from Oracle table I don't have any problem.
            Is anyone encounter this problem when they are getting data by Database Link.

            Thanks
            • 3. Re: Fetch out of sequence error
              JustinCave
              It smells to me like your application is involving some sort of distributed transaction coordinator-- that generally causes problems because Oracle itself is trying to coordinate a distributed transaction over the database link. Can you disable any transaction coordination on the client side, or is that a necessary part of your application?

              Justin
              • 4. Re: Fetch out of sequence error
                587785
                Hi,
                What is distribution transaction coordinator. I don't know about that. Our DBA guys created the DBLink gave it to developers.
                I think there is problem with DBlink Driver.
                Thnaks
                • 5. Re: Fetch out of sequence error
                  JustinCave
                  Most likely, this has nothing to do with how the database link was created and everything to do with the sort of connection your application is making to the database and/or the design of your application. Assuming that you've validated that queries over the database link work via something like SQL*Plus, that would indicate that the link is fine.

                  If you are using ODBC to connect, for example, and using the Oracle ODBC driver, there is a "Disable MTS" option in the DSN configuration that will prevent Microsoft's distributed transaction coordinator from involving itself and causing this particular error.

                  Justin
                  • 6. Re: Fetch out of sequence error
                    Himanshu Kandpal
                    Hi,

                    Check if the user has permission to execute the SQL server object. Try if you can execute from a Oracle procedure.Check the isolation level in SQL server.

                    Thanks
                    • 7. Re: Fetch out of sequence error
                      587785
                      Hi,
                      The select from DBLink working in SQLPLUS
                      From .Net also if I execute only the select statement it is working fine. If I put the select * from Table@dblik in procedure and execute it from .Net it is giving error.
                      Thanks
                      • 8. Re: Fetch out of sequence error
                        JustinCave
                        Can you call the stored procedure from SQL*Plus?

                        Justin
                        • 9. Re: Fetch out of sequence error
                          587785
                          Hi,
                          The Procedure is working from SQLPLUS.
                          If I call the Select * from Table@Dblink from .NET it is working.
                          If I call the Procedure which contain above Select from .Net i am getting
                          Fetch out of sequence error.

                          Thanks
                          • 10. Re: Fetch out of sequence error
                            JustinCave
                            If you can call the procedure from SQL*Plus, that tends to reinforce my suspicion that your app is trying to use a distributed transaction coordinator. If that's not something you need, you probably want to disable it.

                            Justin