Example of Oracle using Dblink to connect SQL Server

Usage Scenario: When you need to access data from another SQL Server database from an Oracle database, Oracle provides a tool: gateways. With this tool, you can create dblink to connect to SQL Server or a different company's database----depending on the options you install.

After you install gateways, you can create dblink using the following 2 ways

Mode A:

Create DATABASE link Bslink
Connect to "username" identified by "password"
Using ' (DESCRIPTION =
(address = (PROTOCOL = TCP) (HOST = Remoteip) (PORT = 1433))
(Connect_data = (

SID = SQL Server database name)) (Hs=ok)) '

This way it is not necessary to configure Initdg4msql.ora and TNSNames.

Mode B:

1. In the $oracle_home/dg4msql/admin/initdg4msql----This step can be omitted to use the way a substitution

Configure Initdg4msql.ora to my example, as follows:
******************************

# This is a customized agent init file that contains the HS parameters
# That's are needed for the ' Database Gateway for Microsoft SQL Server

#
# HS init parameters
#
Hs_fds_connect_info=[192.168.101.4]//bsdata----Only need to modify this ip//database name
Hs_fds_trace_level=off
Hs_fds_recovery_account=recover
Hs_fds_recovery_pwd=recover

2. Configure the Listener.ora under the Network\admin directory under the Oracle home directory. Take my example, as follows:
******************************
# LISTENER. ORA Network Configuration File:c:\oracle\ora92\network\admin\listener.ora
# Generated by Oracle configuration tools.

LISTENER =----Here LISTENER is named LISTENER
(Description_list =
(DESCRIPTION =
(Address_list =
(address = (PROTOCOL = TCP) (HOST = 192.168.1.180) (PORT = 1521))
)
)
)

Sid_list_listener =-----Here Sid_list_lintener name Ditto as LISTENER
(Sid_list =
(Sid_desc =
(Sid_name = dg4msql)
(Oracle_home = D:\app\oracle\product\11.2.0\dbhome_1)
(program = dg4msql)----Here The program corresponds to the Dg4msql in TNSNames
)
)

3. Configure the Oracle home directory under the Network\admin directory under the Tnsnames.ora, in my example, as follows:
******************************
Dg4msql =---ditto corresponding
(description=
(Address= (PROTOCOL=TCP) (Host=localhost) (port=1521))
(Connect_data= (Sid=dg4msql))
(Hs=ok)
)
******************************