This discussion is archived
6 Replies Latest reply: Jul 19, 2012 3:31 AM by mkirtley-Oracle RSS

Heterogeneous 11g Error

950467 Newbie
Currently Being Moderated
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 Expert
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    @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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    @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 Expert
    Currently Being Moderated
    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

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points