Heterogeneous Services - Generic Connectivity
Oracle supports heterogeneous services to allow data in non-Oracle database to be queried using SQL. This support has been in the form of transparent gateways, which are vendor specific, or generic connectivity which uses ODBC or OLEDB to make the connections. The functionality supported by generic connectivity is typically more limited than that possible when using vendor specific gateways, but it is quick and simple to configure. The steps listed below can be used to connect Oracle to any ODBC compliant database (MS Access, SQL Server, MySQL etc.).
In the following example, "SQLSERVER1" is the ODBC Data Source Name (DSN) of the non-Oracle database.
Make sure a valid username and password are present for the non-Oracle database (MyUser/MyPassword).
Create an ODBC Data Source Name (DSN) on the Oracle database server for the non-Oracle database (SQLSERVER1).
Create a file called: "ORACLE_HOME\hs\admin\initSQLSERVER1.ora" which contains.
# ODBC DSN HS_FDS_CONNECT_INFO = SQLSERVER1 HS_FDS_TRACE_LEVEL = OFF
Add the following entry to the "tnsnames.ora" file.
SQLSERVER1 = (DESCRIPTION= (ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=1521)) (CONNECT_DATA=(SID=SQLSERVER1)) (HS=OK) )
Add the relevant version-specific entry into the "listener.ora" file. Remember to adjust your
# Up to and including Oracle 10gR2 use this. (SID_DESC= (SID_NAME=SQLSERVER1) (ORACLE_HOME=D:\Oracle\Ora\9011) (PROGRAM=hsodbc) ) # From Oracle 11gR1 onwards use this. (SID_DESC= (SID_NAME=SQLSERVER1) (ORACLE_HOME=C:\app\oracle\product\220.127.116.11\db_1) (PROGRAM=dg4odbc) )
Reload the listener.
Create a database link as follows. Remember to follow any rules regarding username/password case for the remote server.
CREATE DATABASE LINK SQLSERVER1 CONNECT TO "MyUser" IDENTIFIED BY "MyPassword" USING 'SQLSERVER1';
Query the remote table.
SELECT * FROM mytable@SQLSERVER1;
For more information see:
- Oracle Database Heterogeneous Connectivity Administrator's Guide 9i Release 2 (9.2)
- Oracle Database Heterogeneous Connectivity Administrator's Guide 10g Release 2 (10.2)
- Oracle Database Heterogeneous Connectivity User's Guide 11g Release 2 (11.2)
Hope this helps. Regards Tim...