This is about calling an ms-sql-function (or procedure) from Oracle using the Heterogeneous Services, via an ODBC-connection.
A database-link is made in Oracle named GTLAB.
NOTE: It all works fine on a system at one customer, but on a different system for another customer it gives this problem, see below.
This link works OK when doing a query like:
select * from tblOperators@GTLAB
But when calling an external function/procedure made in MS-SQL it always gives the error:
ORA-28511: lost RPC connection to heterogeneous remote agent using SID=ORA-28511: lost RPC connection to heterogeneous remote agent using SID=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=tcp)(host=FSDBXABSDB01.int.fisherservices.co.uk)(port=1521)))(CONNECT_DATA=(SID=MSSQL)))
ORA-02055: distributed update operation failed; rollback required
ORA-02063: preceding lines from GTLAB
The call is like this:
declare
ret integer;
begin
begin
ret := DBMS_HS_PASSTHROUGH.EXECUTE_IMMEDIATE@GTLAB('dbo.fnGetSiteID');
dbms_output.put_line('ret=' || ret);
commit;
exception
when others then
begin
dbms_output.put_line(sqlerrm);
rollback;
end;
end;
end;
This function (dbo.fnGetSiteID) just gives an integer back via a settings-table. Also other functions/procedures are used for testing, but it always gives the same error.
When using trace-option it gives the following messages in trace-file:
Oracle Corporation --- THURSDAY MAR 30 2017 10:35:18.445
Heterogeneous Agent Release
- 11.2.0.3.0
Oracle Corporation --- THURSDAY MAR 30 2017 10:35:18.445
Version 11.2.0.3.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"
setting HS_NLS_NCHAR to default of "UCS2"
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"
HOSGIP returned value of "1" for HS_FDS_FETCH_ROWS
setting HS_FDS_RESULTSET_SUPPORT to default of "FALSE"
setting HS_FDS_RSET_RETURN_ROWCOUNT to default of "FALSE"
HOSGIP returned value of "TRUE" for HS_FDS_PROC_IS_FUNC
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"
HOSGIP returned value of "LOCAL" for HS_KEEP_REMOTE_COLUMN_SIZE
setting HS_FDS_GRAPHIC_TO_MBCS to default of "FALSE"
setting HS_FDS_MBCS_TO_GRAPHIC to default of "FALSE"
HOSGIP returned value of "32" 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"
setting HS_FDS_DELAYED_OPEN to default of "TRUE"
setting HS_FDS_WORKAROUNDS to default of "0"
Exiting hgosdip, rc=0
ORACLE_SID is "MSSQL"
Product-Info:
Port Rls/Upd:3/0 PrdStat:0
Agent:Oracle Database Gateway for ODBC
Facility:hsa
Class:ODBC, ClassVsn:11.2.0.3.0_0011, Instance:MSSQL
Exiting hgogprd, rc=0
hostmstr: 8795959717888: HOA After hoagprd
hostmstr: 8795959717888: HOA Before hoainit
Entered hgoinit
HOCXU_COMP_CSET=1
HOCXU_DRV_CSET=31
HOCXU_DRV_NCHAR=1000
HOCXU_DB_CSET=873
HS_LANGUAGE is AMERICAN_AMERICA.WE8ISO8859P1
rc=1239980 attempting to get LANG environment variable.
HOCXU_SEM_VER=112000
Entered hgolofn at 2017/03/30-10:35:18
Exiting hgolofn, rc=0 at 2017/03/30-10:35:18
HOSGIP for "HS_OPEN_CURSORS" returned "50"
HOSGIP for "HS_FDS_FETCH_ROWS" returned "1"
HOSGIP for "HS_LONG_PIECE_TRANSFER_SIZE" returned "65536"
HOSGIP for "HS_NLS_NUMERIC_CHARACTER" returned ".,"
HOSGIP for "HS_KEEP_REMOTE_COLUMN_SIZE" returned "LOCAL"
HOSGIP for "HS_FDS_DELAYED_OPEN" returned "TRUE"
HOSGIP for "HS_FDS_WORKAROUNDS" returned "0"
HOSGIP for "HS_FDS_MBCS_TO_GRAPHIC" returned "FALSE"
HOSGIP for "HS_FDS_GRAPHIC_TO_MBCS" returned "FALSE"
treat_SQLLEN_as_compiled = 0
Exiting hgoinit, rc=0 at 2017/03/30-10:35:18
hostmstr: 8795959717888: HOA After hoainit
hostmstr: 8795959717888: HOA Before hoalgon
Entered hgolgon at 2017/03/30-10:35:18
reco:0, name:GOTLI, tflag:0
Entered hgosuec at 2017/03/30-10:35:18
Exiting hgosuec, rc=0 at 2017/03/30-10:35:18
HOSGIP for "HS_FDS_RECOVERY_ACCOUNT" returned "RECOVER"
HOSGIP for "HS_FDS_TRANSACTION_LOG" returned "HS_TRANSACTION_LOG"
HOSGIP for "HS_FDS_TIMESTAMP_MAPPING" returned "DATE"
HOSGIP for "HS_FDS_DATE_MAPPING" returned "DATE"
HOSGIP for "HS_NLS_LENGTH_SEMANTICS" returned "CHAR"
HOSGIP for "HS_FDS_MAP_NCHAR" returned "TRUE"
HOSGIP for "HS_FDS_RESULTSET_SUPPORT" returned "FALSE"
HOSGIP for "HS_FDS_RSET_RETURN_ROWCOUNT" returned "FALSE"
HOSGIP for "HS_FDS_PROC_IS_FUNC" returned "TRUE"
HOSGIP for "HS_FDS_REPORT_REAL_AS_DOUBLE" returned "FALSE"
using GOTLI as default value for "HS_FDS_DEFAULT_OWNER"
HOSGIP for "HS_SQL_HANDLE_STMT_REUSE" returned "FALSE"
Entered hgocont at 2017/03/30-10:35:18
HS_FDS_CONNECT_INFO = "MSSQL"
RC=-1 from HOSGIP for "HS_FDS_CONNECT_STRING"
Entered hgogenconstr at 2017/03/30-10:35:18
dsn:MSSQL, name:GOTLI
optn:
Entered hgocip at 2017/03/30-10:35:18
dsn:MSSQL
Exiting hgocip, rc=0 at 2017/03/30-10:35:18
##>Connect Parameters (len=36)<##
## DSN=MSSQL;
#! UID=GOTLI;
#! PWD=*
Exiting hgogenconstr, rc=0 at 2017/03/30-10:35:18
Entered hgopoer at 2017/03/30-10:35:18
hgopoer, line 231: got native error 5701 and sqlstate 01000; message follows...
[Microsoft][ODBC SQL Server Driver][SQL Server]Changed database context to 'JEGR_DB'. {01000,NativeErr = 5701}[Microsoft][ODBC SQL Server Driver][SQL Server]Changed language setting to us_english. {01000,NativeErr = 5703}
Exiting hgopoer, rc=0 at 2017/03/30-10:35:18
hgocont, line 2687: calling SqlDriverConnect got sqlstate 01000
Entered hgolosf at 2017/03/30-10:35:18
ODBC Function-Available-Array 0xFFFE 0x01FF 0xFF00 0xFFFF 0x03FF 0x0000
0x0000 0x0000 0x0000 0x0000 0x0000 0x0000
0x0000 0x0000 0x0000 0x0000 0x0000 0x0000
0x0000 0x0000 0x0000 0x0000 0x0000 0x0000
0x0000 0x0000 0x0000 0x0000 0x0000 0x0000
0x0000 0x0000 0x0000 0x0000 0x0000 0x0000
0x0000 0x0000 0x0000 0x0000 0x0000 0x0000
0x0000 0x0000 0x0000 0x0000 0x0000 0x0000
0x0000 0x0000 0x0000 0x0000 0x0000 0x0000
0x0000 0x0000 0x0000 0x0000 0x0000 0x0000
0x0000 0x0000 0xFE00 0x3F5F
Exiting hgolosf, rc=0 at 2017/03/30-10:35:18
DriverName:SQLSRV32.DLL, DriverVer:06.01.7601
DBMS Name:Microsoft SQL Server, DBMS Version:10.50.1746
Exiting hgocont, rc=0 at 2017/03/30-10:35:18 with error ptr FILE:hgocont.c LINE:2687 ID:SQLDriverConnect
SQLGetInfo returns Y for SQL_CATALOG_NAME
SQLGetInfo returns 128 for SQL_MAX_CATALOG_NAME_LEN
Exiting hgolgon, rc=0 at 2017/03/30-10:35:18
hostmstr: 8795954126848: HOA After hoalgon
RPC Calling nscontrol(0), rc=0
hostmstr: 8795954126848: RPC Before Upload Caps
hostmstr: 8795954126848: HOA Before hoaulcp
Entered hgoulcp at 2017/03/30-10:35:18
Entered hgowlst at 2017/03/30-10:35:18
Exiting hgowlst, rc=0 at 2017/03/30-10:35:18
SQLGetInfo returns 0x1f for SQL_OWNER_USAGE
TXN Capable:2, Isolation Option:0xf
SQLGetInfo returns 128 for SQL_MAX_SCHEMA_NAME_LEN
SQLGetInfo returns 128 for SQL_MAX_TABLE_NAME_LEN
SQLGetInfo returns 134 for SQL_MAX_PROCEDURE_NAME_LEN
HOSGIP returned value of "TRUE" for HS_FDS_QUOTE_IDENTIFIER
SQLGetInfo returns " (0x22) for SQL_IDENTIFIER_QUOTE_CHAR
2 instance capabilities will be uploaded
capno:1989, context:0x00000000, add-info: 0
capno:1992, context:0x0001ffff, add-info: 1, translation:"""
Exiting hgoulcp, rc=0 at 2017/03/30-10:35:18
hostmstr: 8795952918528: HOA After hoaulcp
hostmstr: 8795952918528: RPC After Upload Caps
hostmstr: 8795952918528: RPC Before Upload DDTR
hostmstr: 8795952918528: HOA Before hoauldt
Entered hgouldt at 2017/03/30-10:35:18
NO instance DD translations were uploaded
Exiting hgouldt, rc=0 at 2017/03/30-10:35:18
hostmstr: 8795952918528: HOA After hoauldt
hostmstr: 8795952918528: RPC After Upload DDTR
hostmstr: 8795952918528: RPC Before Begin Trans
hostmstr: 8795952918528: HOA Before hoabegn
Entered hgobegn at 2017/03/30-10:35:18
tflag:0 , initial:1
hoi:0x12ee18, ttid (len 27) is ...
00: 41425331 2E663366 36356161 382E3130 [ABS1.f3f65aa8.10]
10: 2E34312E 31363034 363438 [.41.1604648]
tbid (len 24) is ...
00: 41425331 5B31302E 34312E31 36303436 [ABS1[10.41.16046]
10: 34385D5B 312E345D [48][1.4]]
Exiting hgobegn, rc=0 at 2017/03/30-10:35:18
hostmstr: 8795952918528: HOA After hoabegn
hostmstr: 8795952918528: RPC After Begin Trans
hostmstr: 8795952918528: RPC Before SQL Bundling
hostmstr: 8795952918528: HOA Before hoxpars
Entered hgopars, cursor id 1 at 2017/03/30-10:35:18
type:1
SQL text from hgopars, id=1, len=15 ...
0: 64626F2E 666E4765 74536974 654944 [dbo.fnGetSiteID]
What can be the problem here?
If you need more info please let me know.