6 Replies Latest reply: Jul 19, 2012 5:31 AM by Mkirtley-Oracle RSS

    Heterogeneous 11g Error

    950467
      Hi,


      I am trying to configure HS for Excel file. I am getting following error:
      SQL> select table_name from all_tables@ODBCEXCEL;
      select table_name from all_tables@ODBCEXCEL
      *
      ERROR at line 1:
      ORA-28513: internal error in heterogeneous remote agent
      ORA-02063: preceding line from ODBCEXCEL
      Heres my setup:
      Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit (Linux)
      
      Excel file (ODBC "ODBCEXCEL" setup in Windows XP)
      initODBCEXCEL.ora
      HS_FDS_CONNECT_INFO = ODBCEXCEL
      HS_FDS_TRACE_LEVEL = Debug
      
      #HS_FDS_SHAREABLE_NAME = <full path name of odbc driver manager or driver>
      #HS_FDS_SHAREABLE_NAME = /usr/lib64/libodbc.so
      #
      # ODBC specific environment variables
      #
      set ODBCINI=/asis/mis/oracle11g/product/11.1.0/db_1/hs/admin/initODBCEXCEL.ora
      tnsnames.ora
      ODBCEXCEL =
        (DESCRIPTION =
          (ADDRESS_LIST =
            (ADDRESS = (PROTOCOL = TCP)(HOST = SG6666i1)(PORT = 1521))
          )
          (CONNECT_DATA =
            (SID = ODBCEXCEL)
          )
         (HS = OK)
        )
      listener.ora
      (SID_DESC =
      (SID_NAME = ODBCEXCEL)
      (ORACLE_HOME = /asis/mis/oracle11g/product/11.1.0/db_1)
      (PROGRAM = dg4odbc)
      )
      TNSPING
      [oracle11@asistest hs]$ tnsping ODBCEXCEL
      
      TNS Ping Utility for Linux: Version 11.1.0.7.0 - Production on 19-JUL-2012 15:56:55
      
      Copyright (c) 1997, 2008, Oracle.  All rights reserved.
      
      Used parameter files:
      /asis/mis/oracle11g/product/11.1.0/db_1/network/admin/sqlnet.ora
      
      
      Used TNSNAMES adapter to resolve the alias
      Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = SG6666i1)(PORT = 1521))) (CONNECT_DATA = (SID = ODBCEXCEL)) (HS = OK))
      OK (0 msec)
      Heres the output from ODBC Tracing
      dg4odbcODBCEXCE 1e88-1cd4 ENTER SQLAllocHandle 
      SQLSMALLINT 1 <SQL_HANDLE_ENV>
      SQLHANDLE 00000000
      SQLHANDLE * 01E5FFD4
      
      dg4odbcODBCEXCE 1e88-1cd4 EXIT SQLAllocHandle with return code 0 (SQL_SUCCESS)
      SQLSMALLINT 1 <SQL_HANDLE_ENV>
      SQLHANDLE 00000000
      SQLHANDLE * 0x01E5FFD4 ( 0x01b21c18)
      
      dg4odbcODBCEXCE 1e88-1cd4 ENTER SQLSetEnvAttr 
      SQLHENV 01B21C18
      SQLINTEGER 200 <SQL_ATTR_ODBC_VERSION>
      SQLPOINTER 0x00000003
      SQLINTEGER -6 
      
      dg4odbcODBCEXCE 1e88-1cd4 EXIT SQLSetEnvAttr with return code 0 (SQL_SUCCESS)
      SQLHENV 01B21C18
      SQLINTEGER 200 <SQL_ATTR_ODBC_VERSION>
      SQLPOINTER 0x00000003 (BADMEM)
      SQLINTEGER -6 
      
      dg4odbcODBCEXCE 1e88-1cd4 ENTER SQLAllocHandle 
      SQLSMALLINT 2 <SQL_HANDLE_DBC>
      SQLHANDLE 01B21C18
      SQLHANDLE * 01E5FFD8
      
      dg4odbcODBCEXCE 1e88-1cd4 EXIT SQLAllocHandle with return code 0 (SQL_SUCCESS)
      SQLSMALLINT 2 <SQL_HANDLE_DBC>
      SQLHANDLE 01B21C18
      SQLHANDLE * 0x01E5FFD8 ( 0x01b21cc0)
      
      dg4odbcODBCEXCE 1e88-1cd4 ENTER SQLSetConnectAttr 
      SQLHDBC 01B21CC0
      SQLINTEGER 102 <SQL_ATTR_AUTOCOMMIT>
      SQLPOINTER 0x00000000
      SQLINTEGER -5 
      
      dg4odbcODBCEXCE 1e88-1cd4 EXIT SQLSetConnectAttr with return code 0 (SQL_SUCCESS)
      SQLHDBC 01B21CC0
      SQLINTEGER 102 <SQL_ATTR_AUTOCOMMIT>
      SQLPOINTER 0x00000000
      SQLINTEGER -5 
      
      dg4odbcODBCEXCE 1e88-1cd4 ENTER SQLFreeHandle 
      SQLSMALLINT 2 <SQL_HANDLE_DBC>
      SQLHANDLE 01B21CC0
      
      dg4odbcODBCEXCE 1e88-1cd4 EXIT SQLFreeHandle with return code 0 (SQL_SUCCESS)
      SQLSMALLINT 2 <SQL_HANDLE_DBC>
      SQLHANDLE 01B21CC0
      
      dg4odbcODBCEXCE 1e88-1cd4 ENTER SQLFreeHandle 
      SQLSMALLINT 1 <SQL_HANDLE_ENV>
      SQLHANDLE 01B21C18
      
      dg4odbcODBCEXCE 1e88-1cd4 EXIT SQLFreeHandle with return code 0 (SQL_SUCCESS)
      SQLSMALLINT 1 <SQL_HANDLE_ENV>
      SQLHANDLE 01B21C18
      Please help. Many Thanks!

      Regards,
      Eric
        • 1. Re: Heterogeneous 11g Error
          Mkirtley-Oracle
          Eric,
          Thanks for opening the new thread, it is easier to work on the problem.
          Your configuration is not correct.
          In initODBCEXCEL.ora you have -

          set ODBCINI=/asis/mis/oracle11g/product/11.1.0/db_1/hs/admin/initODBCEXCEL.ora

          but this should point to an odbc.ini file on the Linux system that has details of the ODBC driver that is used for the connection.

          As far as I know there is no ODBC driver for Excel available for Linux but I may be wrong.

          But, for the connection to work you need an ODBC driver for Excel installed on Linux and then refer to it in the configuration.

          Have a look at this note in My Oracle Support -

          How to Setup DG4ODBC on 64bit Unix OS (Linux, Solaris, AIX, HP-UX) (Doc ID 561033.1)

          If you don't have an suitable ODBC driver on Linux then this won't work.
          If there is no driver for Excel on Linux then you could install DG4ODBC on the Windos machine where Excel is installed and connect to the gateway there from your RDBMS on Linux.
          See these notes depending on your Windows version -

          How to Setup DG4ODBC (Oracle Database Gateway for ODBC) on Windows 32bit (Doc ID 466225.1)
          How to Setup DG4ODBC (Oracle Database Gateway for ODBC) on 64bit Windows Operating Systems (Doc ID 1266572.1)

          There should be an Excel driver on the Windows machine where it is installed, or you should be able to download it from Microsoft.

          Regards,
          Mike
          • 2. Re: Heterogeneous 11g Error
            Kgronau-Oracle
            Can you please clarify WHERE did you set up DG4ODBC?

            According to the listener snippet you use:
            (SID_DESC =
            (SID_NAME = ODBCEXCEL)
            (ORACLE_HOME = /asis/mis/oracle11g/product/11.1.0/db_1)
            (PROGRAM = dg4odbc)
            )

            => so you have set up Dg4ODBC on Unix.

            But then this set up won't make any sense:
            HS_FDS_CONNECT_INFO = ODBCEXCEL
            HS_FDS_TRACE_LEVEL = Debug

            #HS_FDS_SHAREABLE_NAME = <full path name of odbc driver manager or driver>
            #HS_FDS_SHAREABLE_NAME = /usr/lib64/libodbc.so
            #
            # ODBC specific environment variables
            #
            set ODBCINI=/asis/mis/oracle11g/product/11.1.0/db_1/hs/admin/initODBCEXCEL.ora


            => set ODBCINI needs to point to an odbc.ini file that resides on the Unix machine and which conatins the settings to connect to the Excel file as well as the excel ODBC driver.


            I'm not aware of a suitable Excel ODBC driver running on Unix that is working with DG4ODBC. For your environment I would recommend you to install DG4ODBC on Windows and to use the Ms Excel ODBC driver to connect to the Excel sheet.

            Edited by: kgronau on Jul 19, 2012 11:09 AM


            There's a note on My Oracle Support portal describing the set up: How To Access MS EXCEL Data From Oracle (Doc ID 465971.1)
            • 3. Re: Heterogeneous 11g Error
              950467
              @kgronau

              Thanks for the response.


              Yes, the listener is in linux:
              (SID_DESC =
              (SID_NAME = ODBCEXCEL)
              (ORACLE_HOME = /asis/mis/oracle11g/product/11.1.0/db_1)
              (PROGRAM = dg4odbc)
              )
              This code is also in linux:
              HS_FDS_CONNECT_INFO = ODBCEXCEL
              HS_FDS_TRACE_LEVEL = Debug
              
              #HS_FDS_SHAREABLE_NAME = <full path name of odbc driver manager or driver>
              #HS_FDS_SHAREABLE_NAME = /usr/lib64/libodbc.so
              #
              # ODBC specific environment variables
              #
              set ODBCINI=/asis/mis/oracle11g/product/11.1.0/db_1/hs/admin/initODBCEXCEL.ora
              Do i need to remove set ODBCINI=/asis/mis/oracle11g/product/11.1.0/db_1/hs/admin/initODBCEXCEL.ora?

              The MS EXCEL is on a different machine running on windows xp.

              I saw the How To Access MS EXCEL Data From Oracle [ID 465971.1] but the setup is in windows only.


              Is it possible to have an Oracle in Linux with odbc (planning to use OWB) that connects to windows ODBC link to an Excel file?

              Linux Windows
              Oracle odbc ------> Excel odbc

              Edited by: user1091284 on Jul 19, 2012 5:51 PM
              • 4. Re: Heterogeneous 11g Error
                Kgronau-Oracle
                When running DG4ODBC on Unix you need an ODBC driver on this Unix box which can connect to the MS Excel file. I'm not aware of any suitable driver so far that fulfills the ODBC level 3 standard required for DG4ODBC.

                So instead of using DG4ODBC, get the Windows software of DG4ODBC and install it on a Windows machine. You can then connect from your Oracle database to the Dg4ODBC listener on Windows which then spawns the DG4ODBC process. This DG4ODBC process then uses the MS Excel ODBC driver to connect to the Excel sheet.
                • 5. Re: Heterogeneous 11g Error
                  950467
                  @mkirtley

                  its weird, kgronau's reply is the only post i saw at first. :)


                  both your recommendation are the same so it must be the best solution i need. I'll try it. Thanks
                  • 6. Re: Heterogeneous 11g Error
                    Mkirtley-Oracle
                    Eric,
                    No problem. Without an ODBC driver for Excel on Linux the only alternative is to install Dg4ODBC on Windows and use the ODBC driver there.

                    Regards,
                    Mike