Oracle DB Link to SQL Server

Sometimes we need connect Oracle with third parties such as SQL Server. 
Here i will show the easy steps to achieve this type of integration.

1.) Create an ODBC Connection
On My case my Oracle DB is on Windows 2008 Server, 
but even you are on Linux/Unix is possible create ODBC Connections.
To create an ODBC connection on Windows:
Start->Programs->Administrative Tools-> ODBC Data Sources
SQLServer1

SQLServer2
Put your SQL Server IP address above:

Note: The field "Name" will be used on the configuration :

SQLServer3

2.) Heterogeneous Services (HS) Configuration:

We need configure the init.ora for HS, inside the Oracle Home we have:

cd %ORACLE_HOME%
cd hs
cd admin

notepad initdg4odbc.ora

Set the parameters below:

HS_FDS_CONNECT_INFO = dg4odbc  <-- Here is the name of data source
HS_FDS_TRACE_LEVEL = off

After, we need configure the TNSNAMES.ORA and LISTENER.ORA

cd %ORACLE_HOME%/network/admin

--> tnsnames.ora

dg4odbc =
  (DESCRIPTION=
    (ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=1521))
      (CONNECT_DATA=(SID=dg4odbc))
      (HS=OK)
    )

--> listener.ora

SID_LIST_LISTENER=
  (SID_LIST=
      (SID_DESC=
         (SID_NAME=dg4odbc)
         (ORACLE_HOME=C:\Oracle\product\11.2.0\dbhome_2)
         (PROGRAM=dg4odbc)
      )
  )
  
--> lsnrctl reload

3.) Database Link 
sqlplus / as sysdba
CREATE PUBLIC DATABASE LINK dg4odbc 
CONNECT TO "SA" IDENTIFIED BY "xxxx" USING 'dg4odbc';
Note: the username and password must be between double quotes.

Now you can use the database link:

select * from <table-name>@dg4odbc;

 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s