6 Replies Latest reply: Aug 18, 2012 2:35 AM by Dharma_ RSS

    ORA-28500: connection from ORACLE to a non-Oracle system returned this mess

    Dharma_
      ORA-28500: connection from ORACLE to a non-Oracle system returned this message:
      [

      Dear all,

      I got above error,while i connecting to sqlserver.please guide me,


      For your Information:
      I refer this Metalink ID

      Error Ora-28500 and Sqlstate I Issuing Selects From a Unicode Oracle RDBMS With Dg4odbc To Mysql, SQL*Server or DB2 [ID 756186.1]

      *+<Moderator edit - deleted MOS Doc content - pl do NOT post contents of MOS Docs - this is a violation of your Support contract>+*

      But,my Oracle Database NLS_CHARACTERSET = UTF8

      which parameter is the UTF8 is equal to HS_LANGUAGE ?

      Please guide me after that,

      Regards
      Dharma
        • 1. Re: ORA-28500: connection from ORACLE to a non-Oracle system returned this mess
          Dharma_
          Dear all

          Additional Information below,


          *****ODBC log at /tmp/sql.log*******

          [ODBC][19723][1345126352.357085][SQLGetDiagRecW.c][508]
          Entry:
          Connection = 0xec4500
          Rec Number = 2
          SQLState = 0x7fffa27deb88
          Native = 0x7fffa27dec00
          Message Text = 0x7fffa27de970
          Buffer Length = 510
          Text Len Ptr = 0x7fffa27dec04
          [ODBC][19723][1345126352.357252][SQLGetDiagRecW.c][550]
          Exit:[SQL_NO_DATA]
          [ODBC][19723][1345126352.357386][SQLDisconnect.c][208]
          Entry:
          Connection = 0xec4500
          [ODBC][19723][1345126352.357426][SQLDisconnect.c][237]Error: 08003
          [ODBC][19723][1345126352.357530][SQLFreeHandle.c][286]
          Entry:
          Handle Type = 2
          Input Handle = 0xec4500
          [ODBC][19723][1345126352.357575][SQLFreeHandle.c][337]
          Exit:[SQL_SUCCESS]
          [ODBC][19723][1345126352.358774][SQLFreeHandle.c][219]
          Entry:
          Handle Type = 1
          Input Handle = 0xec2ea0


          *********Trace file at hs/log/*********

          Entered hgolgon at 2012/08/16-19:35:08
          reco:0, name:erp, tflag:0
          Entered hgosuec at 2012/08/16-19:35:08
          Exiting hgosuec, rc=0 at 2012/08/16-19:35:08
          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"
          using erp as default value for "HS_FDS_DEFAULT_OWNER"
          HOSGIP for "HS_SQL_HANDLE_STMT_REUSE" returned "FALSE"
          Entered hgocont at 2012/08/16-19:35:08
          HS_FDS_CONNECT_INFO = "dblink4sqlserver"
          RC=-1 from HOSGIP for "HS_FDS_CONNECT_STRING"
          Entered hgogenconstr at 2012/08/16-19:35:08
          dsn:dblink4sqlserver, name:erp
          optn:
          ##>Connect Parameters (len=42)<##
          ## DSN=dblink4sqlserver;
          #! UID=erp;
          #! PWD=*
          Exiting hgogenconstr, rc=0 at 2012/08/16-19:35:08


          **********initBACKOFF.ora***********


          HS_FDS_CONNECT_INFO = "dblink4sqlserver"
          HS_FDS_TRACE_LEVEL=255
          HS_FDS_TRACE_FILE = /$ORACLE_HOME/hs/log/hstrace.txt
          HS_FDS_SHAREABLE_NAME = /usr/local/lib/libodbc.so
          #HS_FDS_SHAREABLE_NAME = /home/oradev/usr/local/lib/libodbc.so
          #HS_NLS_NCHAR = AMERICAN_AMERICA.WE8ISO8859P1
          #HS_LANGUAGE=AMERICAN_AMERICA.WE8MSWIN1252


          #HS_LANGUAGE = AMERICAN_AMERICA.WE8ISO8859P15
          HS_NLS_NCHAR = UCS2
          #HS_KEEP_REMOTE_COLUMN_SIZE=LOCAL
          #HS_NLS_LENGTH_SEMANTICS=CHAR



          set ODBCINI=/etc/odbc.ini


          ********************************************
          I got Below Error,

          SQL> select *from "store"@ORACLE_SQL;
          select *from "store"@ORACLE_SQL
          *
          ERROR at line 1:
          ORA-28500: connection from ORACLE to a non-Oracle system returned this message:
          [

          Please do needful help

          Regards
          Dharma
          • 2. Re: ORA-28500: connection from ORACLE to a non-Oracle system returned this mess
            Mkirtley-Oracle
            Dharma,
            Have you actually tried the workround suggested in the note ?

            What happens if you set -

            HS_LANGUAGE=AMERICAN_AMERICA.WE8ISO8859P1

            in the gateway init<sid>.ora file ?

            Regards,
            Mike
            • 3. Re: ORA-28500: connection from ORACLE to a non-Oracle system returned this mess
              Dharma_
              Dear mike,

              I have change parameter at mention at MOS document.It will through different error,

              select *from "store"@ORACLE_SQL
              *
              ERROR at line 1:
              ORA-28511: lost RPC connection to heterogeneous remote agent using
              SID=ORA-28511: lost RPC connection to heterogeneous remote agent using
              SID=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.2.14)(port=1526))(CONNECT_
              DATA=(SID=BACKOFF)))
              ORA-02063: preceding line from ORACLE_SQL
              Process ID: 18103
              Session ID: 2836 Serial number: 1835

              Thats why i asking,in mos document,mention db charcterset AL32UTF8

              But my db charcterset UTF8

              In my db characterser,what is the parameter at HS_LANGUAGE = ?


              Regards
              Dharma

              Edited by: 933950 on Aug 16, 2012 7:38 AM
              • 4. Re: ORA-28500: connection from ORACLE to a non-Oracle system returned this mess
                Dharma_
                hai mike,

                I am waiting for ur replay

                Regards

                Dharma
                • 5. Re: ORA-28500: connection from ORACLE to a non-Oracle system returned this mess
                  Dharma_
                  Dear mike and klaus,

                  Please guide me.I checked all configuration,but i dont see any mistake.Please verify my setting and,if any mistake point out.

                  Regards
                  Dharma
                  • 6. Re: ORA-28500: connection from ORACLE to a non-Oracle system returned this mess
                    Dharma_
                    Dear mike and klaus,

                    I have find some error,it is useful to troubleshoot.

                    Using test isql its is connected
                    [oradev@testebs bin]$pwd
                    /usr/local/bin/
                    [oradev@testebs bin]$ isql -v dblink4sqlserver erp 123456789
                    ---------------------------------------
                    | Connected! |
                    | |
                    | sql-statement |
                    | help [tablename] |
                    | quit |
                    | |
                    ---------------------------------------
                    SQL> quit

                    Please refer below Error

                    [oradev@testebs bin]$ isql -v dblink4sqlserver
                    isql: symbol lookup error: /opt/microsoft/sqlncli/lib64/libsqlncli-11.0.so.1790.0: undefined symbol: SQLGetPrivateProfileStringW
                    [oradev@testebs bin]$ pwd
                    /usr/local/bin

                    [oradev@testebs bin]$ dltest Oracle SQLConnect
                    [dltest] ERROR dlopen: Oracle: cannot open shared object file: No such file or directory

                    [oradev@testebs bin]$ dltest /opt/microsoft/sqlncli/lib64/libsqlncli-11.0.so.1790.0
                    SUCCESS: Loaded /opt/microsoft/sqlncli/lib64/libsqlncli-11.0.so.1790.0

                    [oradev@testebs bin]$ ldd /opt/microsoft/sqlncli/lib64/libsqlncli-11.0.so.1790.0
                    linux-vdso.so.1 => (0x00007ffffb5ff000)
                    libcrypto.so.6 => /lib64/libcrypto.so.6 (0x00007f22c433d000)
                    libdl.so.2 => /lib64/libdl.so.2 (0x00007f22c4138000)
                    librt.so.1 => /lib64/librt.so.1 (0x00007f22c3f2f000)
                    libssl.so.6 => /lib64/libssl.so.6 (0x00007f22c3ce3000)
                    libuuid.so.1 => /lib64/libuuid.so.1 (0x00007f22c3ade000)
                    libodbcinst.so.1 => /usr/lib64/libodbcinst.so.1 (0x00007f22c38cb000)
                    libkrb5.so.3 => /usr/lib64/libkrb5.so.3 (0x00007f22c3636000)
                    libgssapi_krb5.so.2 => /usr/lib64/libgssapi_krb5.so.2 (0x00007f22c3407000)
                    libstdc++.so.6 => /usr/lib64/libstdc++.so.6 (0x00007f22c3107000)
                    libm.so.6 => /lib64/libm.so.6 (0x00007f22c2e84000)
                    libgcc_s.so.1 => /lib64/libgcc_s.so.1 (0x00007f22c2c75000)
                    libpthread.so.0 => /lib64/libpthread.so.0 (0x00007f22c2a5a000)
                    libc.so.6 => /lib64/libc.so.6 (0x00007f22c2702000)
                    libz.so.1 => /lib64/libz.so.1 (0x00007f22c24ed000)
                    /lib64/ld-linux-x86-64.so.2 (0x000000376ea00000)
                    libcom_err.so.2 => /lib64/libcom_err.so.2 (0x00007f22c22eb000)
                    libk5crypto.so.3 => /usr/lib64/libk5crypto.so.3 (0x00007f22c20c6000)
                    libkrb5support.so.0 => /usr/lib64/libkrb5support.so.0 (0x00007f22c1ebd000)
                    libkeyutils.so.1 => /lib64/libkeyutils.so.1 (0x00007f22c1cbb000)
                    libresolv.so.2 => /lib64/libresolv.so.2 (0x00007f22c1aa6000)
                    libselinux.so.1 => /lib64/libselinux.so.1 (0x00007f22c188d000)
                    libsepol.so.1 => /lib64/libsepol.so.1 (0x00007f22c1647000)
                    [oradev@testebs bin]$

                    AND

                    I have take trace at tns

                    [oradev@testebs bin]$ ps -ef |grep tns

                    oradev 22601 1 0 10:49 ? 00:00:00 /Oracle/CRP/DEV/db/tech_st/11.1.0/bin/tnslsnr DEV -inherit

                    [oradev@testebs bin]$ strace -fae -o dg4odbc.log -p 22601

                    dg4odbc.log**************************************************

                    1221 write(9, "@2|2|sk-5NFeQ1\"1221_139894549198"..., 56) = 56
                    1221 lseek(8, 0, SEEK_CUR) = 157
                    1221 write(8, "OCI-21500: internal error code, "..., 90) = 90
                    1221 write(9, "M/2a42NFeQ1~T2Q1\n", 17) = 17
                    1221 fstat(1, {st_mode=S_IFCHR|0666, st_rdev=makedev(1, 3), ...}) = 0


                    1221 write(1, "E
                    s in file :\nOCI-21500: inte"..., 122) = 122
                    1221 lseek(8, 0, SEEK_CUR) = 247
                    1221 write(8, "********** Internal heap ERROR 1"..., 62) = 62
                    1221 write(9, "3?OBw3~0-\n", 10) = 10
                    1221 write(8, "***** Dump of memory around addr"..., 44) = 44


                    1221 write(1, "E
                    s in file :\nOCI-21500: inte"..., 122) = 122
                    1221 lseek(8, 0, SEEK_CUR) = 247
                    1221 write(8, "********** Internal heap ERROR 1"..., 62) = 62
                    1221 write(9, "3?OBw3~0-\n", 10) = 10
                    1221 write(8, "***** Dump of memory around addr"..., 44) = 44


                    1221 write(1, "E
                    s in file :\nOCI-21500: inte"..., 122) = 122
                    1221 lseek(8, 0, SEEK_CUR) = 247
                    1221 write(8, "********** Internal heap ERROR 1"..., 62) = 62
                    1221 write(9, "3?OBw3~0-\n", 10) = 10
                    1221 write(8, "***** Dump of memory around addr"..., 44) = 44





                    Please refer above error pls told me solution

                    Regards
                    Dharma