Oracle Database Discussions

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Missing nvarchar columns when querying a MSSQL table from Oracle

SébastienGAug 15 2013 — edited Aug 16 2013

Hi,

I've configured a dblink from Oracle to MSSQL and when I'm querying a table, some columns are missing. The missing columns are all MSSQL nvarchar(4000) and from the trace file of dg4odbc, it seems that the database gateway cannot handle that datatype or maybe the size of those problematic columns.

I'm wondering if there is an initialization parameter or any setting that could help hs to map correctly a MSSQL nvarchar(4000) knowing that other nvarchar columns with a smaller size can be mapped correctly ?

From the dg4odbc trace file, I get this error for each MSSQL nvarchar(4000) columns:

Column:10(TITLE) : dtype -9 (WVARCHAR), prc/scl:4000/3, nullbl:1, octet:8000, sign:1, radix:10

Column 10 (TITLE): dtype -9 (WVARCHAR): this datatype is not supoprted.

I'm suspecting oracle to not being able to handle "octet" bigger than 4000 knowing that after some testing on a DEV server, all MSSQL nvarchar columns smaller than 2000 in size are returned correctly by a query from Oracle.

Here is some info:

select * from v$nls_parameters

PARAMETER	VALUE
NLS_LANGUAGE	CANADIAN FRENCH
NLS_TERRITORY	CANADA
NLS_CURRENCY	$
NLS_ISO_CURRENCY	CANADA
NLS_NUMERIC_CHARACTERS	,
NLS_CALENDAR	GREGORIAN
NLS_DATE_FORMAT	RR-MM-DD
NLS_DATE_LANGUAGE	CANADIAN FRENCH
NLS_CHARACTERSET	WE8ISO8859P1
NLS_SORT	CANADIAN FRENCH
NLS_TIME_FORMAT	HH24:MI:SSXFF
NLS_TIMESTAMP_FORMAT	RR-MM-DD HH24:MI:SSXFF
NLS_TIME_TZ_FORMAT	HH24:MI:SSXFF TZR
NLS_TIMESTAMP_TZ_FORMAT	RR-MM-DD HH24:MI:SSXFF TZR
NLS_DUAL_CURRENCY	$
NLS_NCHAR_CHARACTERSET	AL16UTF16
NLS_COMP	BINARY
NLS_LENGTH_SEMANTICS	BYTE
NLS_NCHAR_CONV_EXCP	FALSE

MSSQL Synprod db collation:

Danish_Norwegian_CI_AS

HS init file:

# 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 = SYNPROD

#HS_FDS_TRACE_LEVEL = off

HS_FDS_TRACE_LEVEL=DEBUG

HS_NLS_LANGUAGE = UCS2

# Environment variables required for the non-Oracle system

#set <envvar>=<value>

HS trace file when querying a table description:

Oracle Corporation --- THURSDAY AUG 15 2013 15:42:08.007

Heterogeneous Agent Release

11.2.0.3.0

Oracle Corporation --- THURSDAY AUG 15 2013 15:42:08.007

Version 11.2.0.3.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_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"

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"

setting HS_FDS_DELAYED_OPEN to default of "TRUE"

setting HS_FDS_WORKAROUNDS to default of "0"

Exiting hgosdip, rc=0

ORACLE_SID is "dg4odbc"

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_0012, Instance:dg4odbc

Exiting hgogprd, rc=0

Entered hgoinit

HOCXU_COMP_CSET=1

HOCXU_DRV_CSET=178

HOCXU_DRV_NCHAR=1000

HOCXU_DB_CSET=31

HS_LANGUAGE not specified

rc=1239980 attempting to get LANG environment variable.

HOCXU_SEM_VER=102000

Entered hgolofn at 2013/08/15-15:42:08

Exiting hgolofn, rc=0 at 2013/08/15-15:42:08

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 ".,"

HOSGIP for "HS_KEEP_REMOTE_COLUMN_SIZE" returned "OFF"

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"

Invalid value of 64 given for HS_FDS_SQLLEN_INTERPRETATION

treat_SQLLEN_as_compiled = 1

Exiting hgoinit, rc=0 at 2013/08/15-15:42:08

Entered hgolgon at 2013/08/15-15:42:08

reco:0, name:synergiRead, tflag:0

Entered hgosuec at 2013/08/15-15:42:08

Exiting hgosuec, rc=0 at 2013/08/15-15:42:08

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_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 "FALSE"

HOSGIP for "HS_FDS_REPORT_REAL_AS_DOUBLE" returned "FALSE"

using synergiRead as default value for "HS_FDS_DEFAULT_OWNER"

HOSGIP for "HS_SQL_HANDLE_STMT_REUSE" returned "FALSE"

Entered hgocont at 2013/08/15-15:42:08

HS_FDS_CONNECT_INFO = "SYNPROD"

RC=-1 from HOSGIP for "HS_FDS_CONNECT_STRING"

Entered hgogenconstr at 2013/08/15-15:42:08

dsn:SYNPROD, name:synergiRead

optn:

Entered hgocip at 2013/08/15-15:42:08

dsn:SYNPROD

Exiting hgocip, rc=0 at 2013/08/15-15:42:08

Exiting hgogenconstr, rc=0 at 2013/08/15-15:42:08

Entered hgopoer at 2013/08/15-15:42:08

hgopoer, line 234: got native error 5701 and sqlstate 01000; message follows...

[Microsoft][SQL Server Native Client 10.0][SQL Server]Changed database context to 'Synprod'. {01000,NativeErr = 5701}[Microsoft][SQL Server Native Client 10.0][SQL Server]Changed language setting to us_english. {01000,NativeErr = 5703}

Exiting hgopoer, rc=0 at 2013/08/15-15:42:08

hgocont, line 2690: calling SqlDriverConnect got sqlstate 01000

Entered hgolosf at 2013/08/15-15:42:08

Exiting hgolosf, rc=0 at 2013/08/15-15:42:08

DriverName:sqlncli10.dll, DriverVer:10.50.4000

DBMS Name:Microsoft SQL Server, DBMS Version:10.50.4000

Exiting hgocont, rc=0 at 2013/08/15-15:42:08 with error ptr FILE:hgocont.c LINE:2690 ID:SQLDriverConnect

SQLGetInfo returns Y for SQL_CATALOG_NAME

SQLGetInfo returns 128 for SQL_MAX_CATALOG_NAME_LEN

Exiting hgolgon, rc=0 at 2013/08/15-15:42:08

Entered hgoulcp at 2013/08/15-15:42:08

Entered hgowlst at 2013/08/15-15:42:08

Exiting hgowlst, rc=0 at 2013/08/15-15:42:08

SQLGetInfo returns 0x1f for SQL_OWNER_USAGE

TXN Capable:2, Isolation Option:0x2f

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

14 instance capabilities will be uploaded

capno:1989, context:0x00000000, add-info: 0

capno:1992, context:0x0001ffff, add-info: 0

capno:3042, context:0x00000000, add-info: 0, translation:"42"

capno:3047, context:0x00000000, add-info: 0, translation:"57"

capno:3049, context:0x00000000, add-info: 0, translation:"59"

capno:3050, context:0x00000000, add-info: 0, translation:"60"

capno:3066, context:0x00000000, add-info: 0

capno:3067, context:0x00000000, add-info: 0

capno:3068, context:0x00000000, add-info: 0

capno:3069, context:0x00000000, add-info: 0

capno:3500, context:0x00000001, add-info: 91, translation:"42"

capno:3501, context:0x00000001, add-info: 93, translation:"57"

capno:3502, context:0x00000001, add-info: 107, translation:"59"

capno:3503, context:0x00000001, add-info: 110, translation:"60"

Exiting hgoulcp, rc=0 at 2013/08/15-15:42:10

Entered hgouldt at 2013/08/15-15:42:10

NO instance DD translations were uploaded

Exiting hgouldt, rc=0 at 2013/08/15-15:42:10

Entered hgobegn at 2013/08/15-15:42:10

tflag:0 , initial:1

hoi:0x12ee18, ttid (len 38) is ...

00: 43415350 45522E45 414D2E45 4C4B454D [CASPER.EAM.ELKEM]

10: 2E303030 33303039 662E3131 2E31362E [.0003009f.11.16.]

20: 31343533 3631 [145361]

tbid (len 10) is ...

0: 0B001000 D1370200 0104 [.....7....]

Exiting hgobegn, rc=0 at 2013/08/15-15:42:11

Entered hgodtab at 2013/08/15-15:42:11

count:1

table: ENERGY.D_TRANS

Allocate hoada[0] @ 0000000005725F40

Entered hgopcda at 2013/08/15-15:42:11

Column:1(TRANS): dtype:4 (INTEGER), prc/scl:10/0, nullbl:0, octet:0, sign:1, radix:10

Exiting hgopcda, rc=0 at 2013/08/15-15:42:11

Entered hgopcda at 2013/08/15-15:42:11

Column:2(UNIT_RESPONSIBLE): dtype:4 (INTEGER), prc/scl:10/0, nullbl:0, octet:0, sign:1, radix:10

Exiting hgopcda, rc=0 at 2013/08/15-15:42:11

Entered hgopcda at 2013/08/15-15:42:11

Column:3(TRANS_ID): dtype:-5 (BIGINT), prc/scl:19/0, nullbl:1, octet:0, sign:1, radix:10

Exiting hgopcda, rc=0 at 2013/08/15-15:42:11

Entered hgopcda at 2013/08/15-15:42:11

Column:4(MODIFIED): dtype:93 (TIMESTAMP), prc/scl:23/3, nullbl:0, octet:0, sign:1, radix:10

Exiting hgopcda, rc=0 at 2013/08/15-15:42:11

Entered hgopcda at 2013/08/15-15:42:11

Column:5(UNIT_IN_CHARGE): dtype:4 (INTEGER), prc/scl:10/0, nullbl:0, octet:0, sign:1, radix:10

Exiting hgopcda, rc=0 at 2013/08/15-15:42:11

Entered hgopcda at 2013/08/15-15:42:11

Column:6(PERSON_IN_CHARGE): dtype:4 (INTEGER), prc/scl:10/0, nullbl:0, octet:0, sign:1, radix:10

Exiting hgopcda, rc=0 at 2013/08/15-15:42:11

Entered hgopcda at 2013/08/15-15:42:11

Column:7(APPLICATION): dtype:4 (INTEGER), prc/scl:10/0, nullbl:0, octet:0, sign:1, radix:10

Exiting hgopcda, rc=0 at 2013/08/15-15:42:11

Entered hgopcda at 2013/08/15-15:42:11

Column:8(STATUS): dtype:4 (INTEGER), prc/scl:10/0, nullbl:0, octet:0, sign:1, radix:10

Exiting hgopcda, rc=0 at 2013/08/15-15:42:11

Entered hgopcda at 2013/08/15-15:42:11

Column:9(TRANS_DATE): dtype:93 (TIMESTAMP), prc/scl:23/3, nullbl:1, octet:0, sign:1, radix:10

Exiting hgopcda, rc=0 at 2013/08/15-15:42:11

Entered hgopcda at 2013/08/15-15:42:11

Column:10(TITLE): dtype:-9 (WVARCHAR), prc/scl:4000/3, nullbl:1, octet:8000, sign:1, radix:10

Column 10 (TITLE): dtype -9 (WVARCHAR): this datatype is not supported.

Entered hgopcda at 2013/08/15-15:42:11

Column:10(DESCRIPTION): dtype:-9 (WVARCHAR), prc/scl:4000/3, nullbl:1, octet:8000, sign:1, radix:10

Column 10 (DESCRIPTION): dtype -9 (WVARCHAR): this datatype is not supported.

Entered hgopcda at 2013/08/15-15:42:11

Column:10(COMMENTS): dtype:-9 (WVARCHAR), prc/scl:4000/3, nullbl:1, octet:8000, sign:1, radix:10

Column 10 (COMMENTS): dtype -9 (WVARCHAR): this datatype is not supported.

Entered hgopcda at 2013/08/15-15:42:11

Column:10(DEADLINE): dtype:93 (TIMESTAMP), prc/scl:23/3, nullbl:1, octet:8000, sign:1, radix:10

Exiting hgopcda, rc=0 at 2013/08/15-15:42:11

Entered hgopcda at 2013/08/15-15:42:11

Column:11(IMPORT_DATABASE_ID): dtype:4 (INTEGER), prc/scl:10/0, nullbl:1, octet:8000, sign:1, radix:10

Exiting hgopcda, rc=0 at 2013/08/15-15:42:11

Entered hgopcda at 2013/08/15-15:42:11

Column:12(IMPORT_TRANS_ID): dtype:-9 (WVARCHAR), prc/scl:254/0, nullbl:1, octet:508, sign:1, radix:10

Exiting hgopcda, rc=0 at 2013/08/15-15:42:11

Entered hgopcda at 2013/08/15-15:42:11

Column:13(PERIOD_START): dtype:93 (TIMESTAMP), prc/scl:23/3, nullbl:1, octet:508, sign:1, radix:10

Exiting hgopcda, rc=0 at 2013/08/15-15:42:11

Entered hgopcda at 2013/08/15-15:42:11

Column:14(CREATED): dtype:93 (TIMESTAMP), prc/scl:23/3, nullbl:0, octet:508, sign:1, radix:10

Exiting hgopcda, rc=0 at 2013/08/15-15:42:11

Entered hgopcda at 2013/08/15-15:42:11

Column:15(PERIOD_END): dtype:93 (TIMESTAMP), prc/scl:23/3, nullbl:1, octet:508, sign:1, radix:10

Exiting hgopcda, rc=0 at 2013/08/15-15:42:11

Entered hgopcda at 2013/08/15-15:42:11

Column:16(FLG_CONFIDENTIAL): dtype:4 (INTEGER), prc/scl:10/0, nullbl:0, octet:508, sign:1, radix:10

Exiting hgopcda, rc=0 at 2013/08/15-15:42:11

Entered hgopcda at 2013/08/15-15:42:11

Column:17(TMP_MAIL_CMT_CIRCULATION): dtype:-9 (WVARCHAR), prc/scl:4000/0, nullbl:1, octet:8000, sign:1, radix:10

Column 17 (TMP_MAIL_CMT_CIRCULATION): dtype -9 (WVARCHAR): this datatype is not supported.

Entered hgopcda at 2013/08/15-15:42:11

Column:17(TMP_MAIL_CMT_IN_CHARGE_OF): dtype:-9 (WVARCHAR), prc/scl:4000/0, nullbl:1, octet:8000, sign:1, radix:10

Column 17 (TMP_MAIL_CMT_IN_CHARGE_OF): dtype -9 (WVARCHAR): this datatype is not supported.

Entered hgopcda at 2013/08/15-15:42:11

Column:17(TMP_MAIL_CASE_COMMENT): dtype:-9 (WVARCHAR), prc/scl:4000/0, nullbl:1, octet:8000, sign:1, radix:10

Column 17 (TMP_MAIL_CASE_COMMENT): dtype -9 (WVARCHAR): this datatype is not supported.

Entered hgopcda at 2013/08/15-15:42:11

Column:17(TMP_MAIL_RECEIVERS_TO): dtype:-9 (WVARCHAR), prc/scl:4000/0, nullbl:1, octet:8000, sign:1, radix:10

Column 17 (TMP_MAIL_RECEIVERS_TO): dtype -9 (WVARCHAR): this datatype is not supported.