Oracle Database Gateway for ODBC


I’ve known about Heterogeneous Services and Gateways for a while but 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.  I’ll post a link to a full article later, but I wanted to post just how easy it really is.  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 if you use that) create an entry like you would for any other db except add the (HS=OK ) stanza.

xxx.your.domain.com =
(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

Advertisements

One Response to Oracle Database Gateway for ODBC

  1. Sean says:

    As promised…

    A more complete description of the instructions and example usage of the pass-through virtual package can be found in my article here: http://www.experts-exchange.com/A_9850.html

    I hope you find it helpful, thanks for reading.

    Like

Questions and Comments always welcome

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: