Forum Stats

  • 3,782,956 Users
  • 2,254,715 Discussions
  • 7,880,228 Comments

Discussions

ORA-01002 fetch out of sequence

user545194
user545194 Member Posts: 585 Bronze Badge
edited May 20, 2020 1:51PM in General Database Discussions

Hi,

Getting the above error when trying to add a linked table in Access 2010 from Oracle 11.2.0.1. Note: The source is a view with a dblink to an external database.

Works fine within SQL Developer.

Using Microsoft ODBC Driver for Oracle. In Addition, ORA-02063 appears in message.

Any ideas?

Thanks!

Tagged:

Answers

  • L. Fernigrini
    L. Fernigrini Data Engineer Sr Consultant Member Posts: 3,702 Silver Crown
    edited May 13, 2020 8:38AM

    Please include the DBLINK information (creation script) and the sentence you are using to review it, no one can debug code without seeing it.

    Include also the full error message, usually when dealing with dblinks the message form the source system comes at the end...

  • Mark D Powell
    Mark D Powell Member Posts: 5,914 Blue Diamond
    edited May 13, 2020 10:26AM

    user545194, the error is from the remote Oracle database.

    - -

    02063, 00000, "preceding %s%s from %s%s"

    // *Cause: an Oracle error was received from a remote database link.

    // *Action: refer to the preceding error message(s)

    - -

    Do you have any more error messages available?  If yes, you should always post the full error stack, and the cause of your problem may appear in the stack.  If no, or you do not see the error in the stack then try to connect to the target Oracle and query the view that uses the database link.  Hopefully this will bring back the remote error.

    - -

    I am not an ACCESS person so while I knew you could create linked tables to Oracle I did not know you could create linked tables to views.

    - -

    HTH -- Mark D Powell --

  • user545194
    user545194 Member Posts: 585 Bronze Badge
    edited May 19, 2020 3:54AM

    The dblink is in the select statement behind the view.

    The full error message:

    ODBC-Call failed

    [Microsoft][ODBC driver for Oracle][Oracle]ORA-01002: FETCH out of sequence

    ORA-02063: previous line from <DBLINK>

    Maybe this is related more to Access than Oracle.

  • Mark D Powell
    Mark D Powell Member Posts: 5,914 Blue Diamond
    edited May 19, 2020 12:01PM

    user545194, this may well be an ACCESs/ODBC issue rather than a true Oracle issue but it would help if you used the Oracle username being used by the application to connect to the Oracle instance and issue the SQL statement being ran by the application, which I take is a query on a view.  If it runs that points to ACCESS/OBDC while if it fails on a permission issue then the ORA-01002 could be a side effect that fixing the permissions error resolves.

    - -

    You listed the target database as 11.2 but what is the version of Oracle on the other side of the database link?

    - -

    What is the version of the Oracle client in use with ACCESS?  It should be at least 11.2  If lower than this upgrading the client would seem worth a try if the view works fine in Oracle.

    - -

    HTH -- Mark D Powell --

    user545194
  • user545194
    user545194 Member Posts: 585 Bronze Badge
    edited May 20, 2020 4:18AM

    We are connecting to a MS SQL Server 2016 SP2 database in the link. Oracle Client 12.1.0 is installed.

  • Mark D Powell
    Mark D Powell Member Posts: 5,914 Blue Diamond
    edited May 20, 2020 1:51PM

    user545194, what mechanism is in use from Oracle to SQL Server (DG4ODBC, Transparent Gateway, …)?

    - -

    Because the previous line is returned from the link and the link is to SQL Server the error appears to be on the SQL Server access from Oracle rather than on the ACCESS connection to Oracle, not that in a situation like this the two are totally separated.

    - -

    Have you checked the Windows Event Log?

    Have you checked the SQL Server Error Log?

    Have you checked the Oracle database alert log and trace file directories?

    Have you searched for any Oracle client trace files on the client machine?

    - -

    The above is about all I can think of.

    - -

    HTH -- Mark D Powell --