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.
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.
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' ;
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 -
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.