Hi,
I am having a problem with the connection of an oracle database to ms sql database.
The way I'm doing the connection is with Heterogeneous Services, through dg4odbc.
The OS is Oracle Linux 7.6 x86-64 and Oracle Database 12c 12.1.0.2.0
I have been trying for 2 days to connect the sql db to the oracle db. I cannot figure out exactly what the problem is. Any help would be appreciated.
Also I have ODBC 17 and 18 installed. I'm not sure if that creates a problem or not.
This is the error:
ORA-28500: connection from ORACLE to a non-Oracle system returned this message:
ORA-02063: preceding line from LBLDB
28500. 00000 - "connection from ORACLE to a non-Oracle system returned this message:"
*Cause: The cause is explained in the forwarded message.
*Action: See the non-Oracle system's documentation of the forwarded
message.
ODBC.ini
[LBLDB]
Description = LBLDB
Driver = /usr/lib64/libmsodbcsql-17.so
Server = "sql server FQDN"
Port = 1433
Database = LBLDb
initLBLDB.ora
# This is a sample agent init file that contains the HS parameters that are
# needed for the Database Gateway for ODBC
#
# HS init parameters
#
HS_FDS_CONNECT_INFO = LBLDB
HS_FDS_TRACE_LEVEL = 255
HS_FDS_SHAREABLE_NAME = /usr/lib64/libmsodbcsql-17.so
HS_FDS_TRACE_FILE_NAME=/tmp/ora_hs_trace.log
HS_NLS_NCHAR = UCS2
#
# ODBC specific environment variables
#
set ODBCINI=/etc/odbc.ini
#
# Environment variables required for the non-Oracle system
#
TNSNAMES.ora
# tnsnames.ora Network Configuration File: /home/oracle/app/oracle/product/12.1.0/dbhome_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.
LBLDB =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = "oracle FQDN")(PORT = 1521))
)
(CONNECT_DATA =
(SID = LBLDB)
)
(HS = OK)
)
LISTENER.ora
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL=TCP)(HOST="oracle server name")(PORT=1521))
)
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL=IPC)(KEY=EXTPROC0))
)
)
)
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(PROGRAM=dg4odbc)
(SID_NAME=LBLDB)
(ORACLE_HOME=/home/oracle/app/oracle/product/12.1.0/dbhome_1)
(ENVS=LD_LIBRARY_PATH=/opt/microsoft/msodbcsql17/lib64)
)
)
USE_SID_AS_SERVICE_listener=on
SUBSCRIBE_FOR_NODE_DOWN_EVENT_LISTENER=OFF
LBLDB_agt_log.trc
Oracle Corporation --- TUESDAY OCT 04 2022 08:47:55.266
Heterogeneous Agent Release
12.1.0.2.0
Oracle Corporation --- TUESDAY OCT 04 2022 08:47:55.266
Version 12.1.0.2.0
Entered hgogprd
HOSGIP for "HS_FDS_TRACE_LEVEL" returned "255"
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_IDLE_TIMEOUT to default of 0
setting HS_FDS_TRANSACTION_ISOLATION to default of "READ_COMMITTED"
HOSGIP returned value of "UCS2" for HS_NLS_NCHAR
setting HS_FDS_TIMESTAMP_MAPPING to default of "DATE"
setting HS_FDS_DATE_MAPPING to default of "DATE"
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_RSET_RETURN_ROWCOUNT to default of "FALSE"
setting HS_FDS_PROC_IS_FUNC 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_FDS_QUOTE_IDENTIFIER to default of "TRUE"
setting HS_KEEP_REMOTE_COLUMN_SIZE to default of "OFF"
setting HS_FDS_GRAPHIC_TO_MBCS to default of "FALSE"
setting HS_FDS_MBCS_TO_GRAPHIC to default of "FALSE"
Default value of 64 assumed for HS_FDS_SQLLEN_INTERPRETATION
setting HS_CALL_NAME_ISP to "gtw$:SQLTables;gtw$:SQLColumns;gtw$:SQLPrimaryKeys;gtw$:SQLForeignKeys;gtw$:SQLProcedures;gtw$:SQLStatistics;gtw$:SQLGetInfo;gtw$:OPTTables;gtw$:OPTColumns;gtw$:OPTPrimaryKeys;gtw$:OPTForeignKeys;gtw$:OPTProcedures;gtw$:OPTStatistics"
setting HS_FDS_DELAYED_OPEN to default of "TRUE"
setting HS_FDS_WORKAROUNDS to default of "0"
setting HS_WORKAROUNDS to default of "0"
Exiting hgosdip, rc=0
ORACLE_SID is "LBLDB"
Product-Info:
Port Rls/Upd:2/0 PrdStat:0
Agent:Oracle Database Gateway for ODBC
Facility:hsa
Class:ODBC, ClassVsn:12.1.0.2.0_0023, Instance:LBLDB
Exiting hgogprd, rc=0
hostmstr: 0: HOA After hoagprd
hostmstr: 0: HOA Before hoainit
Entered hgoinit
HOCXU_COMP_CSET=1
HOCXU_DRV_CSET=178
HOCXU_DRV_NCHAR=1000
HOCXU_DB_CSET=178
HS_LANGUAGE not specified
LANG=en_US.UTF-8
HOCXU_SEM_VER=121000
HOCXU_VC2_MAX=32767
HOCXU_RAW_MAX=32767
Entered hgolofn at 2022/10/04-08:47:55
HOSGIP for "HS_FDS_SHAREABLE_NAME" returned "/usr/lib64/libmsodbcsql-17.so"
Entered hgolofns at 2022/10/04-08:47:55
libname=/usr/lib64/libmsodbcsql-17.so, funcname=SQLAllocHandle
symbol_peflctx=0xec255930
hoaerr:0
Exiting hgolofns at 2022/10/04-08:47:55
Entered hgolofns at 2022/10/04-08:47:55
libname=/usr/lib64/libmsodbcsql-17.so, funcname=SQLBindCol
symbol_peflctx=0xec27c360
hoaerr:0
Exiting hgolofns at 2022/10/04-08:47:55
Entered hgolofns at 2022/10/04-08:47:55
libname=/usr/lib64/libmsodbcsql-17.so, funcname=SQLBindParameter
symbol_peflctx=0xec288eb0
hoaerr:0
Exiting hgolofns at 2022/10/04-08:47:55
Entered hgolofns at 2022/10/04-08:47:55
libname=/usr/lib64/libmsodbcsql-17.so, funcname=SQLCancel
symbol_peflctx=0xec299330
hoaerr:0
Exiting hgolofns at 2022/10/04-08:47:55
Entered hgolofns at 2022/10/04-08:47:55
libname=/usr/lib64/libmsodbcsql-17.so, funcname=SQLDescribeParam
symbol_peflctx=0xec289f80
hoaerr:0
Exiting hgolofns at 2022/10/04-08:47:55
Entered hgolofns at 2022/10/04-08:47:55
libname=/usr/lib64/libmsodbcsql-17.so, funcname=SQLDisconnect
symbol_peflctx=0xec256bd0
hoaerr:0
Exiting hgolofns at 2022/10/04-08:47:55
Entered hgolofns at 2022/10/04-08:47:55
libname=/usr/lib64/libmsodbcsql-17.so, funcname=SQLEndTran
symbol_peflctx=0xec2b3400
hoaerr:0
Exiting hgolofns at 2022/10/04-08:47:55
Entered hgolofns at 2022/10/04-08:47:55
libname=/usr/lib64/libmsodbcsql-17.so, funcname=SQLExecute
symbol_peflctx=0xec22dfe0
hoaerr:0
Exiting hgolofns at 2022/10/04-08:47:55
Entered hgolofns at 2022/10/04-08:47:55
libname=/usr/lib64/libmsodbcsql-17.so, funcname=SQLFetch
symbol_peflctx=0xec2a76e0
hoaerr:0
Exiting hgolofns at 2022/10/04-08:47:55
Entered hgolofns at 2022/10/04-08:47:55
libname=/usr/lib64/libmsodbcsql-17.so, funcname=SQLFreeHandle
symbol_peflctx=0xec255d40
hoaerr:0
Exiting hgolofns at 2022/10/04-08:47:55
Entered hgolofns at 2022/10/04-08:47:55
libname=/usr/lib64/libmsodbcsql-17.so, funcname=SQLFreeStmt
symbol_peflctx=0xec21c680
hoaerr:0
Exiting hgolofns at 2022/10/04-08:47:55
Entered hgolofns at 2022/10/04-08:47:55
libname=/usr/lib64/libmsodbcsql-17.so, funcname=SQLGetData
symbol_peflctx=0xec26f280
hoaerr:0
Exiting hgolofns at 2022/10/04-08:47:55
Entered hgolofns at 2022/10/04-08:47:55
libname=/usr/lib64/libmsodbcsql-17.so, funcname=SQLGetEnvAttr
symbol_peflctx=0xec299aa0
hoaerr:0
Exiting hgolofns at 2022/10/04-08:47:55
Entered hgolofns at 2022/10/04-08:47:55
libname=/usr/lib64/libmsodbcsql-17.so, funcname=SQLGetFunctions
symbol_peflctx=0xec2964f0
hoaerr:0
Exiting hgolofns at 2022/10/04-08:47:55
Entered hgolofns at 2022/10/04-08:47:55
libname=/usr/lib64/libmsodbcsql-17.so, funcname=SQLMoreResults
symbol_peflctx=0xec2a8080
hoaerr:0
Exiting hgolofns at 2022/10/04-08:47:55
Entered hgolofns at 2022/10/04-08:47:55
libname=/usr/lib64/libmsodbcsql-17.so, funcname=SQLNumResultCols
symbol_peflctx=0xec27f6e0
hoaerr:0
Exiting hgolofns at 2022/10/04-08:47:55
Entered hgolofns at 2022/10/04-08:47:55
libname=/usr/lib64/libmsodbcsql-17.so, funcname=SQLParamData
symbol_peflctx=0xec229360
hoaerr:0
Exiting hgolofns at 2022/10/04-08:47:55
Entered hgolofns at 2022/10/04-08:47:55
libname=/usr/lib64/libmsodbcsql-17.so, funcname=SQLPutData
symbol_peflctx=0xec222c80
hoaerr:0
Exiting hgolofns at 2022/10/04-08:47:55
Entered hgolofns at 2022/10/04-08:47:55
libname=/usr/lib64/libmsodbcsql-17.so, funcname=SQLRowCount
symbol_peflctx=0xec277580
hoaerr:0
Exiting hgolofns at 2022/10/04-08:47:55
Entered hgolofns at 2022/10/04-08:47:55
libname=/usr/lib64/libmsodbcsql-17.so, funcname=SQLSetEnvAttr
symbol_peflctx=0xec299960
hoaerr:0
Exiting hgolofns at 2022/10/04-08:47:55
Entered hgolofns at 2022/10/04-08:47:55
libname=/usr/lib64/libmsodbcsql-17.so, funcname=SQLSetDescRec
symbol_peflctx=0xec285de0
hoaerr:0
Exiting hgolofns at 2022/10/04-08:47:55
Entered hgolofns at 2022/10/04-08:47:55
libname=/usr/lib64/libmsodbcsql-17.so, funcname=SQLColAttribute
peflerr=6521, libname=/usr/lib64/libmsodbcsql-17.so, funcname=SQLColAttribute
hoaerr:28500
Exiting hgolofns at 2022/10/04-08:47:55
Failed to load ODBC library symbol: /usr/lib64/libmsodbcsql-17.so(SQLColAttribute)
Exiting hgolofn, rc=28500 at 2022/10/04-08:47:55
Exiting hgoinit, rc=28500 with error ptr FILE:hgoinit.c LINE:436 ID:Loading ODBC aray of function ptrs
hostmstr: 0: HOA After hoainit
RPC Calling nscontrol(0), rc=0
hostmstr: 0: RPC Before Exit Agent
hostmstr: 0: HOA Before hoaexit
Entered hgoexit
HS Gateway: NULL connection context at exit
Exiting hgoexit, rc=0 with error ptr FILE:hgoexit.c LINE:113 ID:Connection context
hostmstr: 0: HOA After hoaexit
hostmstr: 0: RPC After Exit Agent
Entered horcrces_CleanupExtprocSession at 2022/10/04-08:47:55
Entered horcrpooe_PopOciEnv at 2022/10/04-08:47:55
Entered horcrfoe_FreeOciEnv at 2022/10/04-08:47:55
Exiting horcrfoe_FreeOciEnv at 2022/10/04-08:47:55
Entered horcrfse_FreeStackElt at 2022/10/04-08:47:55
Exiting horcrfse_FreeStackElt at 2022/10/04-08:47:55
Exiting horcrpooe_PopOciEnv at 2022/10/04-08:47:55
Exiting horcrces_CleanupExtprocSession at 2022/10/04-08:47:55