Configure Oracle Gateway for ODBC with FreeTDS

Contents

In the last steps we have configured unixODBC and FreeTDS to access an MS SQL Server target database.
Now we will configure the Oracle Gateway.

Usually the gateway will run on the same system where the Oracle database in running.

Configure Oracle Gateway

For each connection we will configure

  • the gateway initialization file $ORACLE_HOME/hs/admin/initGWSID.ora
  • the oracle net listener
  • tnsnames.ora (optional)
  • a database link

Gateway initialization file

Oracle supplies a sample initialization file, initdg4odbc.ora. The sample file is stored in the $ORACLE_HOME/hs/admin directory.

For this example we will create a file $ORACLE_HOME/hs/admin/initGWMSSQLSID.ora

# HS_FDS_CONNECT_INFO: data source name from odbc.ini
HS_FDS_CONNECT_INFO = testdsn

# HS_FDS_TRACE_LEVEL: Values: OFF, ON, DEBUG, default: OFF. Activate to get a trace file in $ORACLE_HOME/hs/log/
#HS_FDS_TRACE_LEVEL = DEBUG

# HS_FDS_SHAREABLE_NAME : full path of the ODBC driver
HS_FDS_SHAREABLE_NAME = /usr/local/lib/libodbc.so

# HS_FDS_SUPPORT_STATISTICS: see Oracle Support Doc ID 744636.1
HS_FDS_SUPPORT_STATISTICS = FALSE

HS_LANGUAGE=AMERICAN_AMERICA.WE8ISO8859P15
HS_FDS_TIMESTAMP_MAPPING=DATE
HS_NLS_NCHAR=UCS2
#
# ODBC specific environment variables
#
# ODBCINI: full path of odbc.ini
set ODBCINI = /usr/local/etc/odbc.ini

Listener configuration

On the system where the gateway is configured, you need to add some lines to the file $ORACLE_HOME/network/admin/listener.ora

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      # full path of ORACLE_HOME here
      (ORACLE_HOME = /u01/app/oracle/product/12.1.0/dbhome_1)
      # SID_NAME from initGWMSQLSID.ora
      (SID_NAME = GWMSQLSID)
      # PROGRAM = dg4odbc tells the listener the use the database gateway for odbc
      (PROGRAM = dg4odbc)
    )
  )

If you have already the section SID_LIST= in your file listener.ora the you just add the marked lines.
If dont have the section, you add all lines.

Important: After changing the file listener.ora you have to reload the listener:

lsnrctl reload

Checking the listener:

lsnrctl status
# you should see the GWMSQLSID:
#  Service "GWMSQLSID" has 1 instance(s).
#    Instance "GWMSQLSID", status UNKNOWN, has 1 handler(s) for this service...

tnsnames.ora configuration

This step is optional, TNS information also may be specified in the database link.

On the system where the database is running, we add some lines to the file $ORACLE_HOME/network/admin/tnsnames.ora.

# TESTCONN is my name for the connection. This name will be used in the Database Link
TESTCONN=
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)
         # HOST: system where the gateway is running (or localhost if gateway and database host are the same)
         (HOST = localhost)
         # PORT: portnumber of listener (usually 1521)
         (PORT = 1521)
      )
    )
    (CONNECT_DATA =
      # Gateway SID from listener.ora
      (SID = GWMSQLSID)
    )
    # HS = OK : this is a gateway connection
    (HS = OK)
  )

Database Link configuration

The database link will be created by the oracle schema user who will use it. As an alternative you may create a public database link.

For creation we need:

  • Username and Password for the target database (e.g. the MS SQL Server database)
  • The TNS alias configured in tnsnames.ora

Your Oracle DB Admin should grant CREATE DATABASE LINK and ALTER DATABASE LINK to your schema user.

-- replace user and password with your values
create database link TESTLINK connect to "username" identified by "password" using 'TESTCONN';
-- alternative if you have not configured an TNS alias
create database link TESTLINK connect to "username" identified by "password" using '(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1521)))(CONNECT_DATA=(SID=GWMSQLSID))(HS=OK))';

Configuration finished!

Using the database link

Now we are ready to test the database link

-- the simplest test. if you get a result, the connection to the target database works.
select 'it works' as status from dual@TESTLINK;

-- query for all tables you can see on the target database
select * from all_tables@TESTLINK;

Case sensitive Object Names

SQL Server Object names are case-sensitive, Oracle Object Names normally only have uppercase letters.
In this example, the target SQL Server database has installed a sample schema Adventure Works 2014.
The query for all_tables shows us the names:

OWNER                          TABLE_NAME                   
------------------------------ ------------------------------
dbo                            AWBuildVersion                
dbo                            DatabaseLog                   
dbo                            ErrorLog                      
HumanResources                 Department                    
HumanResources                 Employee                      
HumanResources                 EmployeeDepartmentHistory     
HumanResources                 EmployeePayHistory            
HumanResources                 JobCandidate                  
HumanResources                 Shift                         
Person                         Address                       
Person                         AddressType                   
Person                         BusinessEntity                
Person                         BusinessEntityAddress         
Person                         BusinessEntityContact         
Person                         ContactType                   
Person                         CountryRegion                 
Person                         EmailAddress                  
Person                         Password                      
Person                         Person                        
Person                         PersonPhone                   
Person                         PhoneNumberType               
Person                         StateProvince           
...

To access Objects whith lowercase letters from Oracle, we habe to enclose the name with ” characters:
Example for select from Table Address owned by Person:

select * from "Person"."Address"@TESTLINK;

Data Type Conversion

Data Types are converted from MS SQL Types to ODBC Types and the to Oracle Types. More Info you find in the documentation for FreeTDS, unixODBC and Oracle Gateways.

To see, what data type we get, we can query all_tab_columns:

SQL> select column_name, data_type, data_length, data_precision, nullable
 from all_tab_columns@testlink
 where owner='Person' and table_name='Address';
COLUMN_NAME                    DATA_TYPE       DATA_LENGT DATA_PRECI NULLABLE 
------------------------------ --------------- ---------- ---------- ----------
AddressID                      NUMBER                   4         22 N         
AddressLine1                   NVARCHAR2              120            N         
AddressLine2                   NVARCHAR2              120            Y         
City                           NVARCHAR2               60            N         
StateProvinceID                NUMBER                   4         22 N         
PostalCode                     NVARCHAR2               30            N         
SpatialLocation                LONG RAW        2147483647            Y         
rowguid                                                16            N         
ModifiedDate                   DATE                    16            N

Synopsis of names used in this example

In the configuration many names are used. This will be sometime confusing. Here you see all names:

NamePurposedefined inused incase sensitive?
TESTLINKdatabase link namecreate database link ...sql queries, eg. select * from table@TESTLINKno
TESTCONNTNS aliastnsnames.oraUSING clause of database linkno
GWMSQLSIDGateway SIDlistener.oratnsnames.ora and filename of initGWSID.oraYES
TESTDSNODBC Datasource Nameodbc.iniHS_FDS_CONNECT_INFO in file initGWSID.orano
HOSTNAMEHostname or IP address of target database serverodbc.inino
DBNAMEMS SQL Server Database nameSQL Serverodbc.inino

There are some limitations on name length and case sensitiveness.
You are free to use own names, even you can use for all objects the same name but you should know what your are doing.

Troubleshooting

IMG_2878
For a gateway connection, there are many components involved:

  • the oracle database
  • the database link
  • the TNS Alias in tnsnames.ora
  • the network connection between database and gateway
  • the listener configuration in listener.ora
  • the gateway configuration file initGWSID.ora
  • the unixODBC installation
  • the unixODBC configuration file odbc.ini
  • the FreeTDS installation
  • the network connection to target database server
  • the target database

If the connection does not work, always first test with isql and tsql and try to isolate the problem.

Some typical errormessages

ErrormessageReason
ORA-12154: TNS:could not resolve the connect identifier specifiedTNS Alias used in the database link not found in tnsnames.ora
ORA-28545: error diagnosed by Net8 when connecting to an agent
Unable to retrieve text of NETWORK/NCR message 65535
ORA-02063: preceding 2 lines from TESTLINK
Mismatch between tnsnames.ora and listener.ora (e.g. wrong SID, wrong port number)
ORA-28500: connection from ORACLE to a non-Oracle system returned this message:
ORA-28541: Error in HS init file on line 39.
ORA-02063: preceding 2 lines from TESTLINK
error in file $ORACLE_HOME/hs/admin/initGWSID.ora
ORA-28500: connection from ORACLE to a non-Oracle system returned this message:
[unixODBC][Driver Manager]Data source name not found, and no default driver
specified {IM002}
ORA-02063: preceding 2 lines from TESTLINK
Mismatch between data source name in odbc.ini and HS_FDS_CONNECT_INFO in initGWSID.ora

Also check the ODBCINI setting ininitGWSID.ora
ORA-28500: connection from ORACLE to a non-Oracle system returned this message:
[unixODBC][Driver Manager]Can't open lib '/usr/local/lib/libtdsodbc.so' : file
not found {01000}
ORA-02063: preceding 2 lines from TESTLINK
This error should have found already when testing with isql