Environment
Oracle database 11.2Oracle Linux Server release 5.7
Microsoft® ODBC Driver 11 for SQL Server - RedHat Linux
Microsoft SQL Server 2005,2008R2
Installing ODBC driver
Install the driver according to MS http://www.microsoft.com/en-us/download/details.aspx?id=36437To verify that the ODBC Driver on Linux was registered successfully, execute the following command:
odbcinst -q -d -n "ODBC Driver 11 for SQL Server"
edit ~/.odbc.ini
and add
[infraUAT]
Driver=ODBC Driver 11 for SQL Server
Description=My Sample ODBC Database Connection
Trace=Yes
Server=gtpsql2
Port=1433
Database=InfraEnt_ITSD_UAT
"Driver" has to be same as output of odbcinst -q -d, which comes from /etc/odbcinst.ini
Test it
odbcinst -q -s
[infraUAT]
isql -v infraUAT user password
+---------------------------------------+
| Connected! |
| |
| sql-statement |
| help [tablename] |
| quit |
| |
+---------------------------------------+
SQL> quit
Configuring Gateway
cd $ORACLE_HOME/hs/admin
If you use initdg4odbc.ora, then dg4odbc will be SID.
edit initdg4odbc.ora
S_FDS_CONNECT_INFO = infraUAT
HS_FDS_SHAREABLE_NAME = /usr/lib64/libodbc.so
# you need to match HS_LANGUAGE to SQLS
HS_LANGUAGE=AMERICAN_AMERICA.WE8MSWIN1252
# you might need the following HS_
#HS_KEEP_REMOTE_COLUMN_SIZE=LOCAL
#HS_NLS_LENGTH_SEMANTICS=CHAR
#nvarchars of a SQL Server are UCS2 character set
#HS_NLS_NCHAR=UCS2
# ODBC specific environment variables
set ODBCINI=/home/oracle/.odbc.ini
HS_LANGUAGE needs to match code page of SQL Server.
When Oracle use unicode AL32UTF8, it will fail to connect. See Doc ID 756186.1
ERROR at line 1:
ORA-28500: connection from ORACLE to a non-Oracle system returned this message:
[
edit listener.ora and add new SID_DESC, where SID matches initdg4odbc.ora
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = dg4odbc)
(ORACLE_HOME = /opt/oracle/product/se1/11.2.0.3)
(PROGRAM = dg4odbc)
(ENVS='LD_LIBRARY_PATH=/usr/lib64:/opt/microsoft/msodbcsql/lib64:/opt/oracle/product/se1/11.2.0.3/lib')
)
)
Restart listener
configure tnsnames.ora
HS1 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = tcp)(HOST = )(PORT = 1521))
)
(CONNECT_DATA =
(SID = dg4odbc)
)
(HS=OK)
)
Configure DB Link
CREATE PUBLIC DATABASE LINK INFRA
CONNECT TO DSUSER
IDENTIFIED BY password
USING 'hs1';
Problem 1 - code page conversion
Some symbols are not converted correctly, e.g. Microsoft double quote or single quote. I have not tested non-latin alphabets.
SELECT short_problem FROM CL_CALL_LOGGING@infra where call_number=4234;
output
The printer on Level 5 is showing ‘perform printer maintenance’.
Solution 1
as Oracle NLS_CHARACTERSET = AL32UTF8 (check view NLS_DATABASE_PARAMETERS), create a view in SQLS to CAST this column to unicode, then you can select from this view from Oracle.
CREATE VIEW TEST_VIEW1 AS
SELECT CALL_NUMBER, CAST(SHORT_PROBLEM AS NVARCHAR(200)) AS SHORT_PROBLEM
FROM DBO.CL_CALL_LOGGING;
in Oracle
SELECT CALL_NUMBER, SHORT_PROBLEM FROM TEST_VIEW1@INFRA WHERE CALL_NUMBER=4234;
output
The printer on Level 5 is showing ‘perform printer maintenance’.
Problem 2 - multibyte characters
ORA-28500: connection from ORACLE to a non-Oracle system returned this message:
[Microsoft][ODBC Driver 11 for SQL Server]String data, right truncation {01004}
ORA-02063: preceding 2 lines from INFRA
If in SQLS a column is described as VARCHAR(200) and a value length is 200 and it contains some symbols which become multi-byte, then in Oracle it becomes longer than 200 bytes.
Solution 2
The same as Solution 1, CAST it to unicode on SQLS.
If you create similar or the same table in Oracle, define length in chars, e.g. VARCHAR(200 char)
It does not have to be NVARCHAR as in my case oracle DB is already UTF8.
Problem 3 - varchar(max)
When selecting varchar(max) from SQLS:
ORA-28500: connection from ORACLE to a non-Oracle system returned this message:
[Microsoft][ODBC Driver 11 for SQL Server]String data, right truncation {01004}
ORA-02063: preceding 2 lines from INFRA
Solution 3.1
I haven't found a good solution for this. You can CAST(COLNAME AS TEXT) or as NTEXT in SQLS, in thin case you receive LONG type in Oracle (can be saved and converted to CLOB). Which is ok for most values (Latin characters), but it still has wrong conversion for multi-byte/non-latin characters.
Solution 3.2 - other ODBC drivers
try other drivers, e.g. Easysoft or Datadirect. I haven't tested it.
Solution 3.3 - non Oracle ETL
IBM Datastage works ok, when selecting as unicode CAST(col as NVARCHAR(max)) and keeping as LongNVarchar in DataStage to avoid Datastage conversion. It inserts into Oracle CLOB and looks ok.
Microsoft SSIS converts correctly, if destination table in Oracle is defined as NCLOB.
Documentation
Error Ora-28500 and Sqlstate I Issuing Selects From a Unicode Oracle RDBMS With Dg4odbc To Non-Oracle Databases Using the UnixODBC Driver Manager (Doc ID 756186.1)
How to Configure DG4ODBC on 64bit Unix OS (Linux, Solaris, AIX, HP-UX Itanium) to Connect to Non-Oracle Databases Post Install (Doc ID 561033.1)
