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

C

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

Comments Welcome .