Connect to sqlserver using Oracle generic connectivity

Source: Internet
Author: User
Tags intl

Recently, the ETL project involved data import from the SQL Server database to the Oracle database. Many articles on the Internet use ORACLE transparent gateways, but since it is not free, I decided to use generic connectivity first. Most of the documents are transferred from owb's casestudy document. If you are interested, go to the official documents. Since the company uses English, let's take a look.

Step1. create a system DSN


Set up a system data source name (DSN) using the Microsoft ODBC Administrator.

1. Select Start, followed by programs, owb_oracle_home, Network

Administration, and then Microsoft ODBC Administrator.

2. navigate toSystem DSNTab and clickAddTo create a system DSN.

3. SelectMicrosoft SQL ServerAs the driver for which you want to set up the data source.

4. specify the name of the DSNSqlsource

5. Test the connection

Step2. create the heterogeneous services Initialization File

Create the initsqlserver. ora file in the $ ORACLE_HOME/HS/Admin directory

As follows:

Hs_fds_connect_info = sqlsource

Hs_fds_trace_level = 0

Hs_autoregister = true

Hs_db_name = hsodbc

Step3. modify the listener. ora File

To set up the listener, modify the entries in the listener. ora file located in the $ ORACLE_HOME/Network/Admin directory as follows:

Sid_list_listener =

(Sid_list =

(Sid_desc =

(Sid_name = plsextproc)

(ORACLE_HOME =E: "oracle" product "10.2.0" db_1)

(Program = EXTPROC)


(Sid_desc =

(Sid_name = sqlserver)

(ORACLE_HOME =E: "oracle" product "10.2.0" db_1)

(Program = hsodbc)



Listener =

(Description_list =

(Description =

(Address = (Protocol = IPC) (Key = extproc1 ))

(Address = (Protocol = TCP) (host (Port = 1521 ))



1. For the sid_name parameter, use the SID that you specified in step 2 when

Creating the initialization parameter file for the heterogeneous services.

2. Ensure that the ORACLE_HOME parameter value is the path to your Oracle Home


3. the value associated with the program keyword defines the name of the agent


Remember to restart the listener after making these modifications.

Step 4. Create a database link


1. login asiadb database as DBA

2. Running Script: Alter system set global_names = false;

3. running script:

Create database link "asiauat. Regress. RDBMS. Dev. Us. Oracle. com"

Connect to asiadb

Identified by asiadb

Using '(description = (address_list = (address = (Protocol = TCP)

(Host = localhost) (Port = 1521 )))

(CONNECT_DATA = (SID = sqlserver ))

(HS = OK)


4. Test

Select * from "action_logs" @ asiauat. Regress. RDBMS. Dev. Us. Oracle. com

Notes:Using "" and same case with SQL Server Object Name.

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.