Creating a database link from oracle to oracle database is easy. But setting up db_link from oracle to mssql server isn’t straight forward. We need to do odbc setup. Its knows as heterogeneous db_link.  We can achive this either by using third party odbc drivers or using oracle provided gateways utility.

Here in this article i will show how to create db_link using oracle gateway.

Download the gateway file from OTN.

unzip the file in oracle database server and execute runInstaller

[oracle@localhost HouseKeeping]$ pwd
[oracle@localhost HouseKeeping]$ unzip

[oracle@localhost HouseKeeping]$ cd gateways
[oracle@localhost gateways]$ ls -ltr
total 56
drwxr-xr-x  4 oracle oinstall  4096 Aug 26  2013 install
drwxr-xr-x  4 oracle oinstall  4096 Aug 26  2013 legacy
-rwxr-xr-x  1 oracle oinstall  3267 Aug 26  2013 runInstaller
drwxr-xr-x  2 oracle oinstall  4096 Aug 26  2013 response
drwxr-xr-x 14 oracle oinstall  4096 Aug 26  2013 stage
-rw-r--r--  1 oracle oinstall 30016 Aug 27  2013 readme.html
-rw-r--r--  1 oracle oinstall   500 Aug 27  2013 welcome.html





Select the oracle_home same as that of the oracle database.








Here we are connecting to microsoft sql server. So select that sql server gateway option.


Enter the sql server details  to which we are creating the database link.







Run the file from root user . Now the gateway installation is done.


Now add the below entry in listener.ora file and start the listener

( HOST= — This is the oracle database server ip


lsnrctl start LISTENER_dg4mssql

Now add the tns entry in tnsnames.ora file

dg4msql =

[oracle@localhost admin]$ tnsping dg4msql

TNS Ping Utility for Linux: Version - Production on 15-JUL-2015 07:24:06

Copyright (c) 1997, 2013, Oracle.  All rights reserved.

Used parameter files:

Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION= (ADDRESS=(PROTOCOL=tcp)(HOST= (CONNECT_DATA=(SID=dg4msql)) (HS=OK))
OK (10 msec)


Lets create the database link and test it.

SQL> Create public database link MSTEST connect to MSTEST identified by mstest124 using 'dg4msql';

Database link created.

SQL>  select count(*) from "INFORMATION_SCHEMA"."TABLES"@MSTEST;

2 rows selected