Heterogeneous Services (Generic Connectivity) : Oracle to Non-Oracle Database Links Using ODBC
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). You can see how to create ODBC data source names on Linux here.
Create a file called: "$ORACLE_HOME/hs/admin/initSQLSERVER1.ora" which contains.
# Windows HS_FDS_CONNECT_INFO = SQLSERVER1 HS_FDS_TRACE_LEVEL = OFF # Linux HS_FDS_CONNECT_INFO = SQLSERVER1 HS_FDS_TRACE_LEVEL = OFF HS_FDS_SHAREABLE_NAME = /usr/lib64/libodbc.so # Depending on where you defined your ODBC settings. set ODBCINI=/etc/odbc.ini #set ODBCINI=/home/oracle/.odbc.ini # Following parameters are SQL Server environment dependant. HS_KEEP_REMOTE_COLUMN_SIZE = LOCAL HS_NLS_NCHAR=UCS2 HS_LANGUAGE=AMERICAN_AMERICA.UTF8 #HS_LANGUAGE=AMERICAN_AMERICA.WE8ISO8859P1 #HS_FDS_SQLLEN_INTERPRETATION=32
A big thank you to Richard Harrison who helped me get to the bottom of some of these settings.
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=/u01/app/oracle/product/22.214.171.124/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;
Depending on your environment, you may have to play around with the settings in the "$ORACLE_HOME/hs/admin/initSQLSERVER1.ora" file. To make sure you are testing these changes properly, remember to use the following process.
- Change the config.
- Restart the listener.
- Reconnect to the Oracle database.
- Test the database link.
Some config changes will not be visible until you reconnect to the database, so play it safe and always use this procedure.
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)
- Create an ODBC Data Source Name (DSN) on Linux
Hope this helps. Regards Tim...