Skip to content
September 5, 2011 / Alex Nedoboi

Tiny tips: Use Gateways to connect Oracle database to Microsoft SQL Server


When you have two Oracle databases and want to exchange data between them, it is easily done via database links.

So for your local database you would go select * from local_table and for the remote one select * from remote_table@link_to_remote_db

But what if the remote database isn’t Oracle, what if it’s (for example) Microsoft SQL Server?

Well, almost no difference. You can create a database link to MSSQL just as easily.

For this, we will use Oracle Gateways – a piece of software that translates Oracle calls to MSSQL and vice versa.

Install Gateways

First we need to install it of course. You can download Oracle Gateways from Oracle website. As usual, unzip it and execute runInstaller.sh

The installation process is mostly a matter of clicking the Next button. At the last step you will be asked the details of your MSSQL Server.

In this scenario, our MSSQL Server is at machine called vb-m8r2, instance name INSTNAME, and database name DBNAME. Port 1433, default.

For testing purposes, I created a table called PEOPLE (id, first_name, last_name), and inserted two lines into it.

MSSQL 1

After Gateways installation is complete, check the config file – <gateways_home>/dg4msql/admin/initdg4msql.ora

It should have the line HS_FDS_CONNECT_INFO=vb-m8r2:1433//DBNAME, to avoid confusion, change it to …=vb-m8r2/INSTNAME/DBNAME

Make sure you remember the underlined bit – dg4msql – this is the SID we will be using for the listener.

Configure the gateway

Assuming your lister is called LISTENER, add the following entry to listener.ora

SID_LIST_LISTENER =
  (
    SID_LIST =
      (SID_DESC =
        (SID_NAME = dg4msql)
        (ORACLE_HOME = <gateways_home>)
        (ENVS = LD_LIBRARY_PATH=<gateways_home>/dg4msql/driver/lib:<gateways_home>/lib)
        (PROGRAM = <gateways_home>/bin/dg4msql)
      )
  )

Restart the listener.

If you specified the paths incorrectly, you will get an error – TNS-01201: Listener cannot find executable.

Otherwise, you should see the following lines -

Services Summary...
Service "dg4msql" has 1 instance(s).
  Instance "dg4msql", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully

Congratulations – you have configured the gateway.

Configure the database

First, add the following to tnsnames.ora

dg4msql =
(
  DESCRIPTION =
    (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    (CONNECT_DATA = (SID = dg4msql))
    (HS = OK)
)

Try to tnsping it.

Second, create the database link.

create public database link micro 
   connect to "ms_username" 
   identified by "ms_password" 
   using 'dg4msql'
;

Test it

Eventually, time for testing.

SQL> select * from people@micro;

id first_name last_name
-- ---------- ---------
 1 John       Smith
 2 Jean       Cook

The other way around -

SQL> insert into people@micro values (3,'A','B');

1 row created.

SQL> commit;

Commit complete.

SQL> select * from people@micro;

id first_name last_name
-- ---------- ---------
 1 John       Smith
 2 Jean       Cook
 3 A          B

Now check on the MSSQL side -

MSSQL 2

Troubleshooting

If instead of this you’re getting an error, something like -

ORA-28545: error diagnosed by Net8 when connecting to an agent
Unable to retrieve text of NETWORK/NCR message 65535
ORA-02063: preceding 2 lines from MICRO

It means you’ve misconfigured something, make sure there are no typos in initdg4msql.ora, listener.ora, tnsnames.ora.

Then make sure you can telnet from your Oracle box to vb-m8r2 on 1433, the listener starts, and you can tsnping dg4msql.

About these ads

Leave a Reply

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

Follow

Get every new post delivered to your Inbox.

%d bloggers like this: