Oracle Database Gateway for ODBC

I’ve known about Heterogeneous Services and Gateways for a while but only recently got around to playing with them.  Like others, I chose the ODBC Gateway because the database license includes the ODBC usage, unlike the rest of the Gateways that are each licensed separately.  First, create an ODBC DSN for whatever database and platform you want.  Oracle doesn’t supply the drivers for you though.

Once you have the DSN, a few config files are all that is needed to use it.

In your listener.ora

(SID_DESC =
(PROGRAM = dg4odbc)   #for 10g you would use "hsodbc" instead of "dg4odbc"
(SID_NAME = xxx)
(ORACLE_HOME = D:\oracle\product\11.2.0)   #Obviously, change this to your home
)

In your tnsnames.ora (or ldap server if you use OID lookups) create an entry like you would for any other db except add the (HS=OK ) stanza.

xxx.your.domain =
(DESCRIPTION=
(ADDRESS=(PROTOCOL=tcp)(HOST=mydbserver)(PORT=1521))
(CONNECT_DATA=(SID=xxx))
(HS=OK)
)

Create a heterogeneous services initialization file:

$ORACLE_HOME/hs/admin/initxxx.ora

It could be as simple as one line, but I include the TRACE and SUPPORT lines as placeholders but disabled

HS_FDS_CONNECT_INFO = xxx
HS_FDS_TRACE_LEVEL = off
HS_FDS_SUPPORT_STATISTICS=FALSE

That’s all there is to it.  Now you can create a db link just as you would for an Oracle database.

CREATE DATABASE LINK xxx_link CONNECT TO "user" IDENTIFIED BY "password" USING 'xxx';

There are, of course, some limitations, but I’ve been happy so far with the easy of setup and the ability to transfer data easily without the need for flat file export/import or 3rd party migration tools.

The instructions work for both 11g XE and Enterprise Editions I’ve tested with