Accessing Postgresql / MySQL / MS SQL Server from Oracle

Gone are the days , where a business has only one DBMS to maintain its operations.

Most of the times , you are dealing with multiple DBMS in your organization.

The vendors provide some means of connecting to distributed databases . For instance ,

Oracle provides “database link” ( via transparent gateway), MS SQL Server provides “Linked Server” mechanism to connect to heterogeneous databases and other data sources.

Here , we will look at connecting to PostgreSQL from Oracle by using transparent ODBC gateway . This process is applicable any ODBC complaint data sources .

Oracle does provides transparent gateways for MS SQL Server , DB2 . But those needs to be licensed.

As of today , ODBC transparent gateway is not separately licensed . Please check with your rep for the licensing details.

Here are the steps.

Step 1 :

Configure ODBC connection and a create a system DSN for PostgreSQL

Let us call it as “pgsales”

Note : This needs to be configured as “System DSN” , not as User DSN.

Step2 :

Create a file under %ORACLE_HOME%\hs\admin in the form initpgsalesodbc.ora . The naming convention is “init” followed by desired name . In my case , I have it as initpgsalesodbc.ora.

Set the HS_FDS_CONNECT_INFO to the DSN name that was configured in ODBC setup .

I would recommend to keep the trace level to ‘ON’ for the initial setup . Once the setup is complete ; connectivity is established ; we can turn off the trace .

Oracle provides sample file . We can use clone this file to suit our needs.

Here are the contents of initpgsalesodbc.ora.

# This is a sample agent init file that contains the HS parameters that are
# needed for the Database Gateway for ODBC
#
# HS init parameters
#
HS_FDS_CONNECT_INFO = pgsales
HS_FDS_TRACE_LEVEL = ON
#
# Environment variables required for the non-Oracle system
#
#set =

Step 3 :

Add a entry in listener.ora to register this data source. The SID name must match with the init file created in the above step.

(SID_DESC =
  (SID_NAME = pgsalesodbc)
  (ORACLE_HOME = c:\app\oracle\product\12.1.0\dbhome_1)
  (PROGRAM = dg4odbc)
)

Step 4:

Add entry in tnsnames.ora . The SID in connect_data should match SID defined in the listerner.ora and the file in hs\admin.

pgsqllink =

(DESCRIPTION=
(enable=broken)
(ADDRESS=(PROTOCOL=tcp)(HOST=ZAHIRSER)(PORT=1521))
(CONNECT_DATA=(SID= pgsalesodbc))
(HS=OK)
)

Step 5:

Restart the listener.You should see to make sure that the listener is servicing the services we created.

Service “pgsalesodbc” has 1 instance(s).
Instance "pgsalesodbc", status UNKNOWN, has 1 handler(s) for this service…

Step 6:

Create a database link .

In the following example , LINK2PGSQL is our db link , ‘zahir’ is the user id in the postgresql database and “passwd1234” is the password .

SQL> CREATE DATABASE LINK LINK2PGSQL CONNECT TO “zahir” IDENTIFIED BY “passwd1234” USING ‘pgsqllink’ ;
Database link created.

To test the connectivity , issue a select statement as below . Please note , for Postgresql databases , you will need to enclose the table/view names in quotes.

SQL> select * from "t"@LINK2PGSQL;

c
--
A
B
CWith this feature , we can manipulate the data stored in heterogeneous databases .

With this feature , we can manipulate the data stored in heterogeneous databases .

Comments Welcome .

Originally published at https://zahirmohideen.blogspot.com/2016/04/accessing-postgresql-mysql-ms-sql.html