An organisation have lot of choices to choose from the RDBMS world.Oracle Database, Microsoft SQL Server, IBM DB2,PostgreSQL, MySQL are few of them. Most of the organisations out there do have a mix of all these which are used to store their data and in course of time it is inevitable that they would want to transfer data from ‘this database’ to ‘that database’. Oracle, the uncrowned king of this RDBMS world call this “Heterogeneous Connectivity” and have incorporated this facility into their database and this is installed by default.
Oracle documentation says:
The Heterogeneous Services component in the Oracle database server talks to a Heterogeneous Services agent process which, in turn, talks to the non-Oracle system. We can conceptually divide the code into three parts:
1)The Heterogeneous Services component in the Oracle database server.
Most of the processing related to heterogeneous connectivity is done in this module.
2)Agent generic code.
This is code in the agent that is generic to all Heterogeneous Services products. This consists, for the most part, of code to communicate with the database and multithreading support.
This is the module that communicates with the non-Oracle system. It is used to map calls from the Heterogeneous Services onto the native API of the non-Oracle system and it is non-Oracle system specific.
Connecting to PostgreSQL from Oracle using an ODBC data source
To connect to a PostgreSQL from Oracle, we will have to use the Oracle Generic Connectivity Agent(hsodbc).
A simple call would be like: Oracle DB -> Oracle Heterogeneous Service -> Oracle Generic Connectivity Agent -> ODBC Driver for PostgreSQL -> PostgreSQL DB
Oracle does not provide the ODBC driver for PostgreSQL, it should be acquired from third party. I downloaded the psqlOBDC driver from http://www.postgresql.org/download/products/2 .
1.PostgreSQL Server [PostgreSQL 8.3.5 on Windows]
1.1)Create the DB and user, which would be used by oracle to make connection.
2.Oracle Server [Oracle 10.2.0.1 on Windows]
2.1)Install Oracle(standard edition also will do)
2.2)Create a database
2.3)Go to $ORACLE_HOME/hs/admin/ and confirm the files
2.4)Download and install the psqlOBDC driver from postgresql.org
2.5)Create the ODBC data source
2.6)Configure initHSODBC.ora ($ORACLE_HOME/hs/admin/) and listener.ora and tnsnames.ora ($ORACLE_HOME/network/admin/)
2.7)Create the database link
This post will cover only the details to be done at the Oracle side.
2.1 – 2.3:
I installed oracle 10.2.0.1 standard edition and created a generic database.
The $ORACLE_HOME/hs/admin/ would contain the files inithsodbc.ora,inithsoledb.ora,listener.ora.sample and tnsnames.ora.sample.
The inithsodbc.ora is the one of our interest. We will configure it later.
I downloaded the “psqlodbc_08_04_0100.zip”(the latest when i installed) from http://www.postgresql.org/ftp/odbc/versions/msi/ .
(I could not access this site using IE and hence used Firefox.)
The installation is nothing big the typical “click next button”
Click Next -> Accept the terms -> Next -> Install -> Finish
Go to Control Panel -> Administrative Tools -> Data Sources -> System DSN -> Add
Select the “PostgreSQL ANSI” driver -> Finish.
Enter the ODBC Datasource name ( i gave PG), Server Name (put the IP address of PostgreSQL DB server), Database Name (PostgreSQL DB name) and User Name (PostgreSQL userid) and password.
Click “Test” to test the connection to PostgreSQL DB and it would show “Connection Succesful” and Save. Now we have an ODBC Data Source called “PG”.
Now we have the datasource PG which would use the ODBC driver psqlODBC.
We need to configure 3 files to complete the connectivity
Go to $ORACLE_HOME/hs/admin/ and make a copy of the initHSODBC.ora and name it init<SID>.ora, where sid is the system identifier you want to use for the instance of the non-Oracle system to which the agent connects and i renamed the file to “initPG.ora“. Edit the file and provide the ODBC Data Source Name which we created in step 2.5.
# This is a sample agent init file that contains the HS parameters that are # needed for an ODBC Agent. # # HS init parameters # HS_FDS_CONNECT_INFO = PG <<- ODBC Data Source Name HS_FDS_TRACE_LEVEL = ON # # Environment variables required for the non-Oracle system # #set <envvar>=<value>
SID_LIST_LISTENER = (SID_LIST = (SID_DESC = (SID_NAME = PG) <<- Should be same as the SID in $ORACLE_HOME/hs/admin/initSID.ora (ORACLE_HOME = D:\oracle\product\10.2.0\db_1) (PROGRAM = hsodbc) <<- Set the program as "hsodbc" ) (SID_DESC = (SID_NAME = PLSExtProc) (ORACLE_HOME = D:\oracle\product\10.2.0\db_1) (PROGRAM = extproc) ) ) LISTENER = (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS = (PROTOCOL = IPC)(KEY = PNPKEY)) <<- Set key as PNPKEY (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.20.20)(PORT = 1521)) ) )
Restart the listener.
The sid that you add to the listener.ora file must match the sid in an initsid.ora file, because the agent spawned by the listener searches for a matching initsid.ora file. When you copy and rename the initsid.ora file, ensure that it remains in the $ORACLE_HOME/hs/admin directory
Add an entry for the PostgreSQL DB into the tnsnames.ora for the DB link to work.
PG = (DESCRIPTION= (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.20.20)(PORT=1521)) (CONNECT_DATA=(SID=PG)) (HS=OK) )
Confirm the “tnsping PG” works.
Create the database link in oracle
create public database link topg connect to "postgreuser" identified by "password" using 'PG';
Confirm the DBlink to PostgreSQL using this query.
select * from all_tables@topg
Unlike Oracle, PostgreSQL is case sensitive. So for user tables(other than all_tables) use the double quotes like
select * from "tbl_user_data"@topg;