This content has been marked as final. Show 11 replies
Could you please post the gateway init file you're using and the source table definition as the table is defined at the MS SQL Server side?1 person found this helpful
Hi here is the content of initdg4msql file from: C:\product\11.2.0\tg_1\dg4msql\admin
# This is a customized agent init file that contains the HS parameters # that are needed for the Database Gateway for Microsoft SQL Server # # HS init parameters # HS_FDS_CONNECT_INFO=[AA-PC]/SQLEXPRESS/FEPT HS_FDS_TRACE_LEVEL=OFF HS_FDS_RECOVERY_ACCOUNT=RECOVER HS_FDS_RECOVERY_PWD=RECOVER
The parsing tables in SQL Server 2008 has 2 columns of type nvarchar and int respectively. The Oracle table which just reads off this table (no counts group bys etc), comes up as long for both columns.
Could you please share the SQl Server table definition as depending on the precision of the varchar/nvarchar and char/nchar data types they are mapped to Oracle longs.
Here is the definition of the int column: http://i.imgur.com/e2qAR.png
And here is the definition of the nvarchar column: http://i.imgur.com/DaBWJ.png
The nvarchar length is reported as -1 which is equivalent to nvarchar(max). Nvarchar(max) is always mapped to an Oracle long column as the precision of the SQL Server nvarchar(max) exceeds the maximum length or an Oracle nvarchar2.1 person found this helpful
If you want to avoid the mapping to an Oracle long you need to change the data type precision at the source database side (SQL Server) or you can define a view on the SQL Server side that splits the nvarchar(max) into chunks and then collect the info from the view instead of the original table.
Hi Kgronau, thanks for your help.
I have created a view in SQL Server against the table, and cast the column as varchar(4000) during creation like below:
When I recreate my view in Oracle, I still get it as long. The column type of the PATIENT_LOCATION is varchar(4000) in the view, I have verified that in SQL Server like before.
CAST(Patient_Location as VARCHAR(4000))
There are 2 gateway parameters that have an impact on the data type mapping, they are1 person found this helpful
HS_KEEP_REMOTE_COLUMN_SIZE and HS_NLS_LENGTH_SEMANTICS. To give you a suggestion how to set these parameters I would need the character set of the Oracle database. Could you please provide me the output of "select * from v$nls_parameters"?
Here is the output of the command
NLS_LANGUAGE AMERICAN NLS_TERRITORY AMERICA NLS_CURRENCY $ NLS_ISO_CURRENCY AMERICA NLS_NUMERIC_CHARACTERS ., NLS_CALENDAR GREGORIAN NLS_DATE_FORMAT mm/dd/yyyy NLS_DATE_LANGUAGE AMERICAN NLS_CHARACTERSET AL32UTF8 NLS_SORT BINARY NLS_TIME_FORMAT HH.MI.SSXFF AM NLS_TIMESTAMP_FORMAT DD-MON-RR HH.MI.SSXFF AM NLS_TIME_TZ_FORMAT HH.MI.SSXFF AM TZR NLS_TIMESTAMP_TZ_FORMAT DD-MON-RR HH.MI.SSXFF AM TZR NLS_DUAL_CURRENCY $ NLS_NCHAR_CHARACTERSET AL16UTF16 NLS_COMP BINARY NLS_LENGTH_SEMANTICS BYTE NLS_NCHAR_CONV_EXCP FALSE
Add these 2 parameters to your gateway init file:
It allows me to describe the casted nvarchar to varchar(4000) column in Oracle as varchar2(4000).
P.S.: Please make sure you open a new gateway connection (for example start SQL*Plus again) when you have changed a gateway parameter as changes in the gateway file are only read at the beginning of a new gateway connection handshake.
Dude that worked, thanks heaps for your help.