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.serviceOracle 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.sovi $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
I’m trying to connect from oracle database to aws rds postgresql.
AWS rds postgresql instances will not have OS level access, when trying to connect getting following errors.
Any idea what these errors are?
==
SQL> select * from names@pg;
select * from names@pg
*
ERROR at line 1:
ORA-28500: connection from ORACLE to a non-Oracle system returned this message:
FATAL: password authentication failed for user “abcd”
{08001,NativeErr = 113}
ORA-02063: preceding 3 lines from PG
==
I could figure out the issue, wrong password was used to create the DB link.
Issues is fixed now.
Hi,
Google Apps Script does not support connection to Postgresql (unless in Google Cloud). It does support connection to Oracle. Does it make sense to develop the tables, views and triggers in Postgres and access them through Oracle Express/dblink ? I don’t know the implications in terms of performance, security and type compatibility. This would be temporary, the time for me to rewrite my GAS spreadsheets using node.js and jQuery.
I would appreciate if someone can shed some light on this.
Thx, Marc
Im trying to configure db link to a postgress cluster the same thing you have done only with port=9999 and oracle us throwing me an error, what can i do?
Thanks stepg