1 2 Previous Next 15 Replies Latest reply: Jul 19, 2012 3:08 AM by 950467 RSS

    HS in 11g error

    Hesh
      Oracle 11.1
      OS Windows XP

      I am trying to configure HS for Excel file. I am getting following
      SQL> create public database link testlink connect to "nkesana" identified by "k*ssddwwgg" using 'excl';
      
      Database link created.
      
      SQL> select * from all_tables@testlink;
      select * from all_tables@testlink
                               *
      ERROR at line 1:
      ORA-28513: internal error in heterogeneous remote agent
      ORA-02063: preceding line from TESTLINK
      i did following steps for HS configuration..

      create a DNS with name EXCL

      ODBC connection-->SYS DNS->Excel ODBC-->create-->EXCL

      Gateway..................initEXCL.ora

      # Important parameters
      
      HS_FDS_CONNECT_INFO = excl
      
      #HS_AUTOREGISTER = TRUE
      # Possible parameters
      #HS_DB_NAME = Employe
      
      HS_FDS_TRACE_LEVEL = 0
      My listener file
      LISTENER =
       (DESCRIPTION_LIST =
          (DESCRIPTION =
            (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
           (ADDRESS = (PROTOCOL = TCP)(HOST = aba3br102iba.corp.ads.vl.net)(PORT = 1521))
         )
        )
      
      SID_LIST_LISTENER=
         (SID_LIST=
            (SID_DESC= 
               (SID_NAME=EXCL)
               (ORACLE_HOME=D:\app\nkesana\product\11.1.0\db_1)
               (PROGRAM=dg4odbc)
            )
         )
      TNS file
      ORCL =
        (DESCRIPTION =
          (ADDRESS = (PROTOCOL = TCP)(HOST = aba3br102iba.corp.ads.vl.net)(PORT = 1521))
          (CONNECT_DATA =
            (SERVER = DEDICATED)
            (SERVICE_NAME = orcl)
          )
        )
      
      
      EXCL=
         (DESCRIPTION=
            (ADDRESS=
               (PROTOCOL=TCP)
               (HOST=localhost)
               (PORT=1521)
            )
            (CONNECT_DATA=
               (SID=EXCL))
            (HS=OK))
      Output of listner status
      LSNRCTL for 32-bit Windows: Version 11.1.0.6.0 - Production on 26-APR-2012 15:23:15
      
      Copyright (c) 1991, 2007, Oracle.  All rights reserved.
      
      Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
      STATUS of the LISTENER
      ------------------------
      Alias                     LISTENER
      Version                   TNSLSNR for 32-bit Windows: Version 11.1.0.6.0 - Production
      Start Date                26-APR-2012 14:46:21
      Uptime                    0 days 0 hr. 36 min. 54 sec
      Trace Level               off
      Security                  ON: Local OS Authentication
      SNMP                      OFF
      Listener Parameter File   D:\app\nkesana\product\11.1.0\db_1\network\admin\listener.ora
      Listener Log File         d:\app\nkesana\diag\tnslsnr\aba3br102iba\listener\alert\log.xml
      Listening Endpoints Summary...
        (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC1521ipc)))
        (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=aba3br102iba.corp.ads.vl.net)(PORT=1521)))
        (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=aba3br102iba.corp.ads.vl.net)(PORT=8080))(Presentation=HTTP)(Session=RAW)
      Services Summary...
      Service "EXCL" has 1 instance(s).
        Instance "EXCL", status UNKNOWN, has 1 handler(s) for this service...
      Service "orcl" has 1 instance(s).
        Instance "orcl", status READY, has 1 handler(s) for this service...
      Service "orclXDB" has 1 instance(s).
        Instance "orcl", status READY, has 1 handler(s) for this service...
      Service "orcl_XPT" has 1 instance(s).
        Instance "orcl", status READY, has 1 handler(s) for this service...
      The command completed successfully
      output of tnsping

      TNS Ping Utility for 32-bit Windows: Version 11.1.0.6.0 - Production on 26-APR-2012 15:23:49
      
      Copyright (c) 1997, 2007, Oracle.  All rights reserved.
      
      Used parameter files:
      D:\app\nkesana\product\11.1.0\db_1\network\admin\sqlnet.ora
      
      
      Used TNSNAMES adapter to resolve the alias
      Attempting to contact (DESCRIPTION= (ADDRESS= (PROTOCOL=TCP) (HOST=localhost) (PORT=1521)) (CONNECT_DATA= (SID=EXCL)) (HS=OK
      OK (20 msec)
      Please help.

      Thanks,
      Hesh.

      Edited by: Hesh on Apr 26, 2012 3:56 AM
        • 1. Re: HS in 11g error
          Mkirtley-Oracle
          Hesh,
          Do you get the same error if you try a select from an actual user table instead of 'all_tables' ?
          Could you trun on gateway debug tracing -

          HS_FDS_TRACE_LEVEL = debug

          and try the select from a new SQLPLUS session.
          Either post the trace here or look for any obvious errors, such as -

          Exiting hgolgon, rc=28513

          See if there are any trace files in the RDBMS trace directory that may also show errors.

          Regards,
          Mike

          Edited by: mkirtley on Apr 26, 2012 2:13 PM
          • 2. Re: HS in 11g error
            Hesh
            Thanks for the response,

            on level zero trace i got
            unable to open init file 'initExcl.ora' ....
            on 'Debug' mod I got this
            Oracle Corporation --- THURSDAY  APR 26 2012 18:29:56.729
            
            
            Heterogeneous Agent Release 
            11.1.0.6.0 
            
            
            
            
            Oracle Corporation --- THURSDAY  APR 26 2012 18:29:56.729
            
                Version 11.1.0.6.0
            
            Entered hgogprd
            HOSGIP for "HS_FDS_TRACE_LEVEL" returned "debug"
            Entered hgosdip
             setting HS_OPEN_CURSORS to default of 50
             setting HS_FDS_RECOVERY_ACCOUNT to default of "RECOVER"
             setting HS_FDS_RECOVERY_PWD to default value
             setting HS_FDS_TRANSACTION_LOG to default of "HS_TRANSACTION_LOG"
             setting HS_FDS_TRANSACTION_ISOLATION to default of "READ_COMMITTED"
             setting HS_NLS_NCHAR to default of "AL16UTF16"
             setting HS_FDS_TIMESTAMP_AS_DATE to default of "TRUE"
             setting HS_RPC_FETCH_REBLOCKING to default of "ON"
             setting HS_FDS_FETCH_ROWS to default of "100"
             setting HS_FDS_RESULTSET_SUPPORT to default of "FALSE"
             setting HS_FDS_PROC_IS_FUNC to default of "FALSE"
             setting HS_FDS_CHARACTER_SEMANTICS to default of "FALSE"
             setting HS_FDS_MAP_NCHAR to default of "TRUE"
             setting HS_NLS_DATE_FORMAT to default of "YYYY-MM-DD HH24:MI:SS"
             setting HS_FDS_REPORT_REAL_AS_DOUBLE to default of "FALSE"
             setting HS_LONG_PIECE_TRANSFER_SIZE to default of "65536"
             setting HS_SQL_HANDLE_STMT_REUSE to default of "FALSE"
             setting HS_FDS_QUERY_DRIVER to default of "TRUE"
             setting HS_FDS_SUPPORT_STATISTICS to default of "FALSE"
             setting HS_CALL_NAME_ISP to "gtw$:SQLTables;gtw$:SQLColumns;gtw$:SQLPrimaryKeys;gtw$:SQLForeignKeys;gtw$:SQLProcedures;gtw$:SQLStatistics"
            Exiting hgosdip, rc=0
             ORACLE_SID is "EXCL"
             Product-Info:
              Port Rls/Upd:6/0 PrdStat:0
              Agent:Oracle Database Gateway for ODBC
              Facility:hsa
              Class:ODBC, ClassVsn:11.1.0.6.0_0006, Instance:EXCL
            Exiting hgogprd, rc=0
            Entered hgoinit
            HOCXU_COMP_CSET=1
            HOCXU_DRV_CSET=178
            HOCXU_DRV_NCHAR=2000
            HOCXU_DB_CSET=178
            HOCXU_SEM_VER=110000
            Entered hgolofn at 2012/04/26-18:29:57
            Exiting hgolofn, rc=0 at 2012/04/26-18:29:57
            HOSGIP for "HS_OPEN_CURSORS" returned "50"
            HOSGIP for "HS_FDS_FETCH_ROWS" returned "100"
            HOSGIP for "HS_LONG_PIECE_TRANSFER_SIZE" returned "65536"
            HOSGIP for "HS_NLS_NUMERIC_CHARACTER" returned ".,"
            Exiting hgoinit, rc=0 at 2012/04/26-18:29:57
            Entered hgolgon at 2012/04/26-18:29:57
             reco:0, name:, tflag:0
            Entered hgosuec at 2012/04/26-18:29:57
            Exiting hgosuec, rc=0 at 2012/04/26-18:29:57
            HOSGIP for "HS_FDS_RECOVERY_ACCOUNT" returned "RECOVER"
            HOSGIP for "HS_FDS_TRANSACTION_LOG" returned ""HS_TRANSACTION_LOG""
            HOSGIP for "HS_FDS_TIMESTAMP_AS_DATE" returned "TRUE"
            HOSGIP for "HS_FDS_CHARACTER_SEMANTICS" returned "FALSE"
            HOSGIP for "HS_FDS_MAP_NCHAR" returned "TRUE"
            HOSGIP for "HS_FDS_RESULT_SET_SUPPORT" returned "FALSE"
            HOSGIP for "HS_FDS_PROC_IS_FUNC" returned "FALSE"
            HOSGIP for "HS_FDS_REPORT_REAL_AS_DOUBLE" returned "FALSE"
            Exiting hgolgon, rc=28513 at 2012/04/26-18:29:58 with error ptr FILE:hgolgon.c LINE:550 ID:Memory allocate
            Entered hgoexit at 2012/04/26-18:29:58
            Exiting hgoexit, rc=0 at 2012/04/26-18:29:58
            Thanks,
            • 3. Re: HS in 11g error
              Mkirtley-Oracle
              Hi,
              When you say you set level zero trace what parameter did you use ?
              You should have something like
              HS_FDS_TRACE_LEVEL = off
              or
              HS_FDS_TRACE_LEVEL = 0

              However, the debug trace shows a problem connecting to Excel -

              Exiting hgolgon, rc=28513 at 2012/04/26-18:29:58 with error ptr FILE:hgolgon.c LINE:550 ID:Memory allocate

              Are you sure the user and password in the database link are correct ? What type of user is it ?
              Could you also make sure you do not have a private database link with the same name as the public database link.

              Regards,
              Mike
              • 4. Re: HS in 11g error
                Mkirtley-Oracle
                Hi,
                Do you also see the same error if you create the link as -

                create public database link testlink using 'excl';

                Regards,
                Mike
                • 5. Re: HS in 11g error
                  Hesh
                  Do you also see the same error if you create the link as -
                  create public database link testlink using 'excl';
                  Same error occured even with public ..

                  Its a windows xp maching with user name and passwords as metioned in the link creation

                  uid : nkesana
                  pwd : k*ssddwwgg



                  sorry for responding late.

                  Thanks,

                  Edited by: Hesh on Apr 27, 2012 12:55 AM
                  • 6. Re: HS in 11g error
                    Mkirtley-Oracle
                    Hesh,
                    The error in the trace indicates a problem connecting to Excel. Are you able to connect using the ODBC DSN directly from the Administrator panel ? Is there any security in Excel that may be preventing a connection ?

                    Regards,
                    Mike
                    • 7. Re: HS in 11g error
                      Hesh
                      Are you able to connect using the ODBC DSN directly from the Administrator panel ?
                      did not get you on this? I have configured ODBC using Control Panel---->Administrative tools-->ODBC Sources-->System DNS-->Microsoft Excel Driver
                      Is there any security in Excel that may be preventing a connection ?
                      No , its just an ordinary excel file without any password security

                      Thanks,
                      • 8. Re: HS in 11g error
                        Mkirtley-Oracle
                        Hesh,
                        this looks like it may be something specific to your setup as I can connect okay on my systems to a standard Excel file.
                        To try and egt more information could you setup ODBC tracing -

                        - from the ODBC Admin Panel choose 'Tracing'
                        - click on the 'Machine wide tracing for all user identities
                        - click on 'Start tracing now'
                        - run the select from SQLPLUS
                        - stop the tracing
                        - in most cases the trace file can be found in \winnt\temp and is called sql.log

                        Does the ODBC trace file show any errors or further information ?

                        Regards,
                        Mike
                        • 9. Re: HS in 11g error
                          Hesh
                          I got some success, I am able to view sheets of excel file as tables and also column names by DESC command but not able to query data using select command.
                          SQL>  SELECT * FROM ALL_CATALOG@excl;
                          
                          OWNER                          TABLE_NAME                     TABLE_TYPE
                          ------------------------------ ------------------------------ -----------
                                                         Sheet1$                        TABLE
                                                         Sheet2$                        TABLE
                                                         Sheet3$                        TABLE
                                                         etb                            TABLE
                          
                          SQL> desc "Sheet1$"@excl;
                           Name                                      Null?    Type
                           ----------------------------------------- -------- ----------------------------
                          
                           id                                                 FLOAT(53)
                           name                                               VARCHAR2(510)
                          
                          SQL> select "id" from "Sheet1$"@excl;
                          select "id" from "Sheet1$"@excl
                                           *
                          ERROR at line 1:
                          ORA-28500: connection from ORACLE to a non-Oracle system returned this message:
                          [Microsoft][ODBC Excel Driver]Optional feature not implemented
                          ORA-02063: preceding 2 lines from EXCL
                          Thanks,
                          • 10. Re: HS in 11g error
                            Kgronau-Oracle
                            Are you still using 11.1.0.6 release and is HS_FDS_SUPPORT_STATISTICS still set to FALSE?
                            There was a bug when accessing Excel sheets using 11.1.0.6 - I think patch 7009291 was required to get it working. The patch is available from Patches & Downloads section through My Oracle Support portal.
                            • 11. Re: HS in 11g error
                              Hesh
                              unfortunately I don't have oracle support :( , I work in DEV env

                              Is there any other way to do it?

                              Thanks,
                              • 12. Re: HS in 11g error
                                Kgronau-Oracle
                                11.1.0.7 is a patch which is also only available through My oracle Support portal - but you might use the 11.2 gateway release.

                                Edited by: kgronau on May 8, 2012 12:44 PM
                                Check out this OTN web site:
                                http://www.oracle.com/technetwork/database/enterprise-edition/downloads/index.html

                                Click on the See All link right after the platform of your choice (Windows 32bit or 64bit) and you'll find a separate gateway CD.
                                For 32bit: Oracle Database Gateways 11g Release 2 (11.2.0.1.0) for Microsoft Windows (32-bit)
                                and 64bit: Oracle Database Gateways 11g Release 2 (11.2.0.1.0) for Microsoft Windows (x64)
                                • 13. Re: HS in 11g error
                                  950467
                                  Hi,

                                  Im also doing the same thing and I encounter the same 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


                                  I saw the solution is to update oracle. The problem is im already using Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit


                                  Heres the ouput from $ORACLE_HOME/hs/log:

                                  [oracle11@asistest log]$ cat ODBCEXCEL_agt_27333.trc


                                  Oracle Corporation --- THURSDAY JUL 19 2012 09:48:10.198


                                  Heterogeneous Agent Release
                                  11.1.0.7.0


                                  Oracle Corporation --- THURSDAY JUL 19 2012 09:48:10.198

                                  Version 11.1.0.7.0

                                  HOSGIP for "HS_FDS_TRACE_LEVEL" returned "1"
                                  No ODBC library specified.




                                  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


                                  Thanks.

                                  Regards,
                                  Eric
                                  • 14. Re: HS in 11g error
                                    Mkirtley-Oracle
                                    Eric,
                                    It looks like you are hitting a different problem as the error is -

                                    HOSGIP for "HS_FDS_TRACE_LEVEL" returned "1"
                                    No ODBC library specified.

                                    This indicates a configuration problem. On which platform are you running ? It looks like a Unix platform as the trace file is in $ORACLE_HOME/hs/log. Do you have an ODBC driver for Excel installed on your OS ?
                                    It would be better to open a new thread and follow up in that and post your configuration files.

                                    Regards,
                                    Mike
                                    1 2 Previous Next