Creating Oracle to SQLServer Database Link (32-Bit Win)

Oracle Heterogeneous Services let you create a database link from Oracle to SQL Server. This method may be preferred over using a JDBC connection because it is significantly faster. Below is the list of steps you need to follow to establish a link from Oracle Database to an SQL Server so that you can query it from your Oracle Database.

Step 1: Creating Data Source (ODBC)

Open Start Menu and select Programs/Administrative Tools/DataSources/Data Sources(ODBC). Go to System DSN tab. Click the Add button to create a new Data Source



Select SQL Server from the driver list and click Finish.



The Create a New Data Source to SQL Server Dialog will show up.

Enter a name for the new Datasource (ibexLink in our example).

Optionally enter a description for your Datasource.

Select the server which you want to connect to from the list. The list displays all the computers in your network that have SQL Server installed.

Click Next.



Select radio button With Sql Server authentication using a login ID and password entered by the user. Then fill in the Login ID and Password to connect to the SQL Server. Click Next.



Check the box Change the default database to:. Select the SQL Server database that you want to connect to from the list. Click Next.



Uncheck the box Perform translation for character data. This check box is there for multilingual support and does not work correctly if the ODBC Driver cannot convert between code page used by the SQL Server and Unicode on the client computer. For more information about this topic please refer to the Help file of the ODBC Datasource Administrator.



Click Test Data Source.



Click OK.

Step 2: Edit tnsnames.ora file

Open the file tnsnames.ora located in your [Oracle Home]\network\admin folder.

Add SQL Server Datasource connection information to the tnsnames.ora file. In the entry, use your Oracle Database Server port as the port number. The SID must be the same ODBC name you specified in step one. We also must set the HS parameter to OK to indicate that we are connecting to a heterogenous server. The TNS Entry for the example we used in this tutorial is below.



ibexLink.world =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = tcp)(HOST = LOCALHOST)(PORT = 1521))

(CONNECT_DATA =

(SID = ibexLink)

)

(HS = OK)

)

Save the tnsnames.ora file.

Step 3: Edit listener.ora file

Open the file listener.ora located in your [Oracle Home]\network\admin folder. Add a new entry to "SID_LIST_LISTENER". Use the ODBC name in step one as the SID_NAME. Enter your Oracle Home Directory. The value for the PROGRAM parameter is hsodbc. When all done, the entry to the listener.ora file for the example we are using for this tutorial looks like the following:

(SID_DESC =

(SID_NAME=ibexLink)

(ORACLE_HOME = C:\oracle\product\10.2.0\db_2)

(PROGRAM = hsodbc)

)

Save the listener.ora file.

Step 4: Create new inithsodbc.ora file

Open folder [Oracle Home]\db_2\hs\admin. Create a copy of the inithsodbc.ora file and rename it as init[odbc name].ora. For the example we are using in this tutorial the file name is initibexLink.ora. Open this new file and change the text. In the example used here, the text changed from

HS_FDS_CONNECT_INFO =

HS_FDS_TRACE_LEVEL =

to

HS_FDS_CONNECT_INFO = ibexLink

HS_FDS_TRACE_LEVEL = off

Save the file.

Step 5: Connect Oracle Server as sysdba

Run the script below to create a database link to the SQL Server.

create public database link [LINK_NAME] connect to [USER] identified by [PASSWORD] using 'ibexLink.world';

Use SQL Server username and password for [USER] and [PASSWORD].

Step 6: Restart Service Oracle Listener

--------------------------

Now, you can query SQL Server data from Oracle with queries in the following format:

Select *

from [TABLE_NAME]@[[LINK_NAME];

H. Kutay Çilingiroğlu

Comments

Popular posts from this blog

Monitoring Oracle Database with Zabbix

Powerful Free Webinar Network for Oracle Developers