Create a Database link in Oracle to access the MSSQL Server database on Windows
In my case, both SQL Server and Oracle database are on the same server. So I don’t need to set up the Oracle Database Gateway for ODBC. You need to download and install the database gateway software if your Oracle Database is not patched but now it is almost there so no need.
Following are the steps we are going to follow:
- Configure the ODBC DNS in the windows ODBC Datasource(64-bit).
- Configure the configuration files in HS.
- Configure the Network folder files as listener.ora or tnsnames.ora.
- Create the Database link with SQLServer.
Step 1: Configure the ODBC DNS in windows:
- Open the ODBC Data Source.
2. Press add button to add SQL Server ODBC Connection as DNS. Choose SQL Server or SQL Server Native Client for establish SQL Server connection.
3. Enter the DNS name and Server detail in which your SQL Server is present as IP address, hostname or if local then localhost or . etc. Press Next.
4. Choose the SQL Server Authentication and type username and password to login in SQL Server. Press Next.
5. Choose the default database on which this DNS works. Press Next.
6. Click Finish.
7. Test the connection
8. After successful its created.
Step 2: Configure the configuration files in HS.
Go to the location %ORACLE_HOME%\hs\admin Create new file from sample file already present initdg4odbc.ora to init<dnsname>.ora Open the new file and edit like following: HS_FDS_CONNECT_INFO = <DNS NAME> HS_FDS_TRACE_LEVEL = OFF Example: 1. Go to directory: C:\Oracle\dbhomeXE\hs\admin 2. Create new file by Copy file from initdg4odbc.ora to initSQLSERVER5.ora. 3. Open file in notepad and change the following entries: HS_FDS_CONNECT_INFO = SQLSERVER5 HS_FDS_TRACE_LEVEL = OFF
Step 3: Configure the Network folder files
-- Edit the TNSNAMES.ORA file: Open the TNSnames.ora file and create a new entry for SQLSERVER DNS Note: ODBC DNS name created in first step from windows system dns. tnsentry = (DESCRIPTION= (ADDRESS=(PROTOCOL=tcp)(HOST=addressoforacle)(PORT = portoforacle)) (CONNECT_DATA=(SID=odbc_dns_name)) (HS=OK) ) Example: I created SQLSERVER5 as DNS Name in SYSTEM tab of ODBC in windows: SQLSERVER = (DESCRIPTION= (ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT = 1521)) (CONNECT_DATA=(SID=SQLSERVER5)) (HS=OK) )
Edit the Listener.ora file: -- Add a SID_DESC entry in existing file like: (SID_DESC = (SID_NAME = odbc_dns_name) (ORACLE_HOME = oracle_home_path) (PROGRAM = dg4odbc) ) Example: In my existing file i make a entry at last in SID_LIST_LISTENER section, not change anything else in listener.ora SID_LIST_LISTENER = (SID_LIST = (SID_DESC = (SID_NAME = CLRExtProc) (ORACLE_HOME = C:\Oracle\dbhomeXE) (PROGRAM = extproc) (ENVS = "EXTPROC_DLLS=ONLY:C:\Oracle\dbhomeXE\bin\oraclr18.dll") ) (SID_DESC = (GLOBAL_DBNAME = PDB1) (ORACLE_HOME = C:\Oracle\dbhomeXE) (SID_NAME = PDB1) ) (SID_DESC = (SID_NAME = SQLSERVER5 ) (ORACLE_HOME = C:\oracle\dbhomexe) (PROGRAM = dg4odbc) ) ) Restart the Listener after adding SID entry:
Test the tnsping:
Test TNSPING: tnsping sqlserver; TNS Ping Utility for 64-bit Windows: Version 188.8.131.52.0 - Production on 06-APR-2022 17:58:16 Copyright (c) 1997, 2018, Oracle. All rights reserved. Used parameter files: C:\Oracle\dbhomeXE\network\admin\sqlnet.ora Used TNSNAMES adapter to resolve the alias Attempting to contact (DESCRIPTION= (ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT = 1521)) (CONNECT_DATA=(SID=SQLSERVER5)) (HS=OK)) OK (20 msec)
Step 4: Create the Database link with SQLServer
Go to SQLPLUS and try to create the database link: SQLPLUS / as sysdba CREATE DATABASE LINK sqlserver CONNECT TO "USERNAME" IDENTIFIED BY "PASSWORD" USING 'tnsentry'; Example: SQL> CREATE DATABASE LINK sqlserver CONNECT TO "sa" IDENTIFIED BY "pass" USING 'SQLSERVER'; Database link created. SQL> select * from test1@sqlserver; name ------------------------------ RAM SHAM