DB – LINK – SERVER
Connection from Oracle to SQL Server with DBLINK
Oracle has a generic connectivity methodology that allows for the Oracle database server to access non-Oracle database systems through ODBC and SQL*Net services. This article explores in a step-by-step fashion setting up this connection so that we may query from a SQL Server database.
1. Define a Data Source Name (DSN) for SQL Server
The first step is to define a system DSN within the Windows ODBC Data Sources. For 64 Bit Operating System choose odbcad32.exe in folder Windows\SysWOW64
Click on the System DSN tab and then click the Add button. Choose the SQL Server driver since this will be a connection to SQL Server. Click Finish to continue with the data source definition.
Key in any name you would like to reference this ODBC data source. I have chose MYSQLSERVERDSN for simplistic reasons but it should be descriptive to the database you may be connecting to within SQL Server. You may also describe the data source in any way you wish. This is my local SQL Server that I will be connecting to. Click Next to continue.
Typically, this window is populated with the default SQL Server database of “master.” Click the check box to change the default database this ODBC connection should connect to and use the drop down list to select. I have chosen to use the sample HPCL database. Click Next to continue.
This window then appears for you to look at the settings you have configured for the data source. Click Test Data Source to validate your definition.
This window should appear, in which case you have successfully configured the data source. Click OK to close all windows as you are done with the data source definition.
2. Create a Services Initialization File
Oracle has provided a sample heterogeneous services init file within the $ORACLE_HOME/hs/admin directory. You will need to copy that file to a new file name within the same directory and edit it for the ODBC DSN you have just created. Below you will find the sample heterogeneous services file Oracle provides and then an edited version, which I have given a new name “inithsconnect.ora” that corresponds to my DSN name “MYSQLSERVERDSN”.
The Oracle Listener listens for incoming requests from the Oracle database. For the Oracle Listener to listen for DG4ODBC, information about DG4ODBC must be added to the Oracle Listener configuration file, listener.ora. You need to:
- Create a SID_NAME for DG4ODBC.
- Specify the executable that the listener should start in response to DG4ODBC connection requests.
ORACLE_HOME/hs/admin/inithsconnect.ora altered file
# This is a sample agent init file that contains the HS parameters that are
# needed for the Database Gateway for ODBC
# HS init parameters
HS_FDS_CONNECT_INFO = MYSQLSERVERDSN
HS_FDS_TRACE_LEVEL = OFF
# Environment variables required for the non-Oracle system
3. Alter your listener.ora file
Here again Oracle has given us a sample listener.ora file to follow for heterogeneous services within the $ORACLE_HOME/hs/admin directory. Below you will find the sample file and the additions I made to my listener.ora file. I made five distinct changes..
- Created my own listener name of LISTENERMYSQLSERVERDSN
- Changed the Port number to 1522
- Changed the SID_NAME to my DSN (hsconnect) * the file name inithsconnect.ora we have to give the “hsconnect” after init
- Changed the ORACLE_HOME location
- Changed the PROGRAM to dg4odbc for 64 bit version of Oracle 11.2 XE version.
$ORACLE_HOME/hs/admin/listener.ora. sample file
(ORACLE_HOME = C:\oraclexe\app\oracle\product\11.2.0\server)
4. Alter your tnsnames.ora file
Here again Oracle has given us a sample tnsnames.ora file to follow for heterogeneous services within the $ORACLE_HOME/hs/admin directory. Below you will find the sample file and the additions I made to my tnsnames.ora file.
1. I made four distinct changes. Created a TNS entry named “hsconnectid “
2. Changed the Port number to 1522
3. Changed the SID to my (hsconnect)
4. Added OK to the HS= parameter
$ORACLE_HOME/hs/admin/tnsnames.ora sample file
(ADDRESS=(PROTOCOL=tcp)(HOST = localhost )(PORT=1522))
(CONNECT_DATA=(SID = hsconnect ))
5. Start the new Listener
This should be self-explanatory but I provide the output here so that you can know what to expect when you start yours.
C:\…….> lsnrctl start listenermysqlserverdsn
or you can start in the services.msc also
6. Validate the connection to your DSN
You can now validate the connection to your SQL Server database by the normal Oracle tnsping utility.
C:\> ……. \> tnsping hsconnectid
7. Create a Database Link within Your Oracle Database
Since we would like to connect from our Oracle database and select information from the SQL Server database, we need to create a database link just as if we were connecting to any other remote Oracle database. I have used the default sa login from SQL Server but you may wish to create your own.
SQL>CREATE PUBLIC DATABASE LINK hslink CONNECT TO
“sa” IDENTIFIED BY “gemini123” using ‘hsconnectid’;
8. Select some data
This is the fun part as it is the culmination of what we were trying to do. You may describe the tables from SQL Server just as you have done with Oracle in the past and then use a SELECT command. Note that my select statement has double quotes and exact upper and lower cases for the SQL Server query. This is required, at least I tried every combination and none worked except this way.
SQL> select * from SMS_OUTBOX@hslink;