Create Dblink From Oracle to PostgreSQL Database

Since fetching data from PostgreSQL database to Oracle database, you can create database link between two database systems. For that you can apply below operations.

Test Systems

PostgreSQL DB
IP : 192.168.10.10
OS : Centos 7 x64
Service : postgresql.service

Oracle DB
IP : 192.168.10.20
OS : OEL 6 x64

PostgreSQL Side

psql# create user dblinkuser encrypted password ‘dblinkuserpass’;

psql# grant usage on schema “testschema” to dblinkuser;

psql# grant select on “testschema”.tbl_test to dblinkuser;

Add new line to pg_hba.conf

vi /pgdir/data/pg_hba.conf

host testdb dblinkuser 192.168.10.20/32 md5

systemctl reload postgresql.service

Oracle Side

yum install postgresql-odbc

vi /etc/odbc.ini

[PG]
Description = PG
Driver = /usr/lib64/psqlodbc.so
ServerName = 192.168.10.10
Username = dblinkuser
Password = dblinkuserpass
Port = 5432
Database = testdb
[Default]
Driver = /usr/lib64/liboplodbcS.so

vi $ORACLE_HOME/hs/admin/initPG.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 = PG
HS_FDS_TRACE_LEVEL = 0
HS_FDS_SHAREABLE_NAME = /usr/lib64/psqlodbc.so
HS_LANGUAGE = AMERICAN_AMERICA.WE8ISO8859P9
#
# ODBC specific environment variables
#
set ODBCINI=/etc/odbc.ini

#
# ODBC specific environment variables
#

#
# Environment variables required for the non-Oracle system
#
#set =

vi $ORACLE_HOME/network/admin/tnsnames.ora

PG =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.10.20)(PORT = 1521))
(CONNECT_DATA =
(SID = PG)
)
(HS = OK)
)

vi $GRID_HOME/network/admin/listener.ora

SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME=PG)
(ORACLE_HOME=/oracle/db/11.2.0/db_home)
(PROGRAM=dg4odbc)
)
)

lsnrctl reload

Listener reload can be adacuate to apply changes;but, sometimes not.

lsnrctl stop
lsnrctl start
lsnrctl status

Create Database Link on Oracle

CREATE DATABASE LINK PG
CONNECT TO “dblinkuser”
IDENTIFIED BY ‘dblinkuserpass’
USING ‘PG’;

Test

select * from “testschema”.”tbl_test”@PG