What is the purpose?

It enables to perform
01. DML activity
02. DDL activity and
03. PLSQL procedures can be run against the database for data management and processing
04. To copy the database from SQL server to Oracle

How do we know that the database is eligible for the configuration of Heterogeneous Connectivity?

The data dictionary tables, views, and packages may already be installed on your Oracle9i server. Check for the existence of Heterogeneous Services data dictionary views, for example, SYS.HS_FDS_CLASS.

Log into the Oracle Database as SYSTEM or SYS or as the ID that is eligible to access the data dictionary and then issue the following statement.

Select * from SYS.HS_FDS_CLASS;

This returns no rows if no HS configuration are done.

How to configure the Heterogeneous connectivity??
Step 01
Creation of .udl file (udl stands for universal data link)
On Windows Platforms

a. Decide a directory where you shall have the .udl file on the Oracle Server where the Oracle Database is living.
b. Right click on the right plane and select new and the new text document and create a file
c. Rename the file with the extension .udl (ex: MYSQL.udl)
d. Go to the SQL Server and create a global user with administrative rights to log into any database that is living on that server
e. Test the connectivity and go back to Oracle Server
f. Go back to the file which is renamed in step c to MYSQL.udl
g. Double click on that file to open the Data Link Properties Window
h. There you see 4 tabs as in the following screen shot

i. Go to the provider window and select OLE DB provider for SQL server and then click next

j. in the connection window

k. Test the connection and then click OK

Step 02

Configuring SQL*Net/Oracle Net

Set up a Oracle Net service name for the agent that can be used by the Oracle9i server. The Oracle Net service name descriptor includes protocol-specific information needed to access the Oracle Net listener. The service name descriptor must include the (HS=OK) clause to ensure the connection uses Oracle9i Heterogeneous Services. The description of this service name is defined in tnsnames.ora, the Oracle Names server, or in third-party name servers using the Oracle naming adapter.

The following is a sample entry for service name in the tnsnames.ora file:

MYSQLSERVER= # ALIAS GIVEN TO THE SQL SERVER DATABASE
(DESCRIPTION=
(ADDRESS=(PROTOCOL=TCP)
(HOST=HOST_SERVER_NAME_WHERE_ORACLE_DB_IS_SITTING)
(PORT=1524) #This port number is to match with the port # in listener.ora
)
(CONNECT_DATA = (SERVICE_NAME=SQL_SERVER_DB_NAME)
)
(HS = OK)
)

Set up the listener on the gateway to listen for incoming request from the Oracle9i server and spawn Heterogeneous Services agents. Then, start the listener on the gateway machine.

The following is a sample entry for the listener in listener.ora:

LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS =
(PROTOCOL = TCP)
(HOST = ORACLE_DB_SERVER)
(PORT = 1524) >>> TNS Names entry should match with this port number
)
)
)
)

SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = SQL_SERVER_DB_NAME) # TNS Names entry is to match with this
(ORACLE_HOME = ORACLE_HOME_DIR_PATH) # Oracle Home
(PROGRAM = ORACLE_HOME_DIR_PATH \bin\hsolesql.exe) # this is HS OLE Executable
(ENVS=LD_LIBRARY_PATH=non_oracle_system_lib_directory)
)
)

The value associated with PROGRAM keyword defines the name of the agent executable.

The full path of the directory, which contains the DLLs that are loaded by the Heterogeneous Services agent, is specified by LD_LIBRARY_PATH. This is required only when you are configuring the connectivity between an Oracle Database that is on UNIX system and SQL Server Database.

Presently only Solaris and Windows Platforms are working fine. I have not tested on other platforms. I saw some docs related to Oracle 8iR3 that this was then available only for Windows and SUN.

Typically, you use SID_NAME to define the initialization parameter file for the agent. Remember that this name is going to be used in future when we create an initialization parameter file for HS connectivity.

Step 03

Test the TNS Names and Listener Entries

Testing TNS Names Entries:

Go to DOS command prompt and type tnsping service_name

If this output is like this you have no issue to handle

K:\>tnsping service_name

TNS Ping Utility for 32-bit Windows: Version 9.2.0.5.0 - Production on 28-SEP-20
04 16:12:43

Copyright (c) 1997 Oracle Corporation. All rights reserved.

Used parameter files:
C:\oracle\ora92\network\admin\sqlnet.ora

Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION= (ADDRESS=(PROTOCOL=tcp) (HOST= server_which_hosts_oracle)(PORT=1521)) (CONNECT_DATA = (SERVICE_NAME=sql_server_db_name)) (HS = OK))
OK (80 msec)
K:\>

If the Oracle is not set in the path you may get a message saying that the command is not recognized as an internal or external command, operable program or batch file. Go and set oracle in the path