Sometimes there are needs to fetch data from other different RDBMS.
Working with postgres, its easy to establish a database connection to oracle but the other round can involved a lot of steps to create a database from the oracle side to connect to postgres. And that is the purpose of this blog, to give you a step by step process to archive this.
Oracle supports heterogeneous services to allow data in non-Oracle database to be queried using SQL. This support has been in the form of transparent gateways, which are vendor specific, or generic connectivity which uses ODBC or OLEDB to make the connections. The functionality supported by generic connectivity is typically more limited than that possible when using vendor specific gateways, but it is quick and simple to configure. To read more, click here
DEMO ENVIRONMENT
Postgres server: 192.168.91.128
Oracle Database: 192.168.91.151
I PERFORMED THE BELOW ON MY POSTGRES SERVER
-bash-4.2$ psql
psql.bin (10.15.24)
Type "help" for help.
edb=#
edb=# create database benstore owner benson;
CREATE DATABASE
edb=#
edb=# \c benstore
You are now connected to database "benstore" as user "enterprisedb".
benstore=#
benstore=# create table account as select * from pg_description;
SELECT 4899
benstore=#
benstore=# create table fin as select * from pg_description;
SELECT 4899
benstore=#
benstore=# \dt
List of relations
Schema | Name | Type | Owner
--------+---------+-------+--------------
public | account | table | enterprisedb
public | fin | table | enterprisedb
(2 rows)
benstore=#
BELOW STEPS IN MY ORACLE SERVER
–I INSTALL ODBC
[root@primary ~]# yum install postgresql-odbcvi /etc/odbc.ini
[benstore]
Description = benstore
Driver = /usr/lib64/psqlodbc.so
ServerName = 192.168.91.128
Username = benson
Password = postgres
Port = 5432
Database = benstore
[Default]
Driver = /usr/lib64/liboplodbcS.so.2[oracle@primary ~]$
[oracle@primary ~]$ exit
logout
[root@primary ~]# su - oracle
Last login: Wed Dec 2 20:43:31 EST 2020 on pts/0
-bash-4.2$ cat .bash_profile
export PATH=$ORACLE_HOME/bin:$PATH
export ORACLE_HOME=/u01/app/oracle/product/12.1.0/dbhome_1
export ORACLE_BASE=/u01/app/oracle
export ORACLE_SID=benson
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib:/usr/local/lib
-bash-4.2$
-bash-4.2$
-bash-4.2$ cd $ORACLE_HOME/hs/admin
-bash-4.2$ pwd
/u01/app/oracle/product/12.1.0/dbhome_1/hs/admin
-bash-4.2$ ll
total 16
-rw-r--r--. 1 oracle oinstall 1109 Jul 7 2014 extproc.ora
-rw-r--r--. 1 oracle oinstall 489 Jul 7 2014 initdg4odbc.ora
-rw-r--r--. 1 oracle oinstall 411 Jul 15 13:41 listener.ora.sample
-rw-r--r--. 1 oracle oinstall 244 Jul 15 13:41 tnsnames.ora.sample
-bash-4.2$
-bash-4.2$ cp initdg4odbc.ora initbenstore.ora
-bash-4.2$
-bash-4.2$ vi initbenstore.ora
-bash-4.2$
-bash-4.2$ cat initbenstore.ora |grep -v ^#
HS_FDS_CONNECT_INFO = benstore
HS_FDS_TRACE_LEVEL = on
HS_FDS_SHAREABLE_NAME = /usr/lib64/psqlodbc.so
set ODBCINI=/etc/odbc.ini-bash-4.2$
-bash-4.2$ cd $ORACLE_HOME/
-bash-4.2$ cd network/admin/
-bash-4.2$ ll
total 28
-rw-r--r-- 1 oracle oinstall 337 Dec 2 19:31 listener2012027PM3103.bak
-rw-r--r-- 1 oracle oinstall 335 Dec 2 19:31 listener.ora
drwxr-sr-x. 2 oracle oinstall 64 Jul 15 13:27 samples
-rw-r--r--. 1 oracle oinstall 373 Oct 31 2013 shrept.lst
-rw-r--r-- 1 oracle oinstall 195 Dec 2 19:31 sqlnet2012027PM3103.bak
-rw-r--r--. 1 oracle oinstall 195 Jul 15 13:50 sqlnet.ora
-rw-r--r-- 1 oracle oinstall 332 Dec 2 19:31 tnsnames2012027PM3103.bak
-rw-r-----. 1 oracle oinstall 332 Jul 15 14:16 tnsnames.ora
-bash-4.2$ vi listener.ora
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = primary)(PORT = 1521))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)
)
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(ORACLE_HOME = /u01/app/oracle/product/12.1.0/dbhome_1)
(SID_NAME = benson)
)
(SID_DESC =
(ORACLE_HOME = /u01/app/oracle/product/12.1.0/dbhome_1)
(ENV="LD_LIBRARY_PATH=/u01/app/oracle/product/12.1.0/dbhome_1/lib:/usr/lib")
(SID_NAME = benstore)
(PROGRAM = dg4odbc)
)
)
lsnrctl stop
lsnrctl start
lsnrctl status
Services Summary...
Service "benson" has 2 instance(s).
Instance "benson", status UNKNOWN, has 1 handler(s) for this service...
Instance "benson", status READY, has 1 handler(s) for this service...
Service "bensonXDB" has 1 instance(s).
Instance "benson", status READY, has 1 handler(s) for this service...
Service "benstore" has 1 instance(s).
Instance "benstore", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
-bash-4.2$ vi tnsnames.ora
BENSON =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.91.151)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = benson)
)
)
benstore =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.91.151)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SID = benstore)
)
(HS=OK
)
-bash-4.2$ tnsping benstore
TNS Ping Utility for Linux: Version 12.1.0.2.0 - Production on 02-DEC-2020 21:16:40
Copyright (c) 1997, 2014, Oracle. All rights reserved.
Used parameter file
/u01/app/oracle/product/12.1.0/dbhome_1/network/admin/sqlnet.ora
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.91.151)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SID = benstore)) (HS=OK))
OK (0 msec)
-bash-4.2$
-bash-4.2$
-bash-4.2$ sqlplus / as sysdba
SQL*Plus: Release 12.1.0.2.0 Production on Wed Dec 2 21:17:38 2020
Copyright (c) 1982, 2014, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
SQL> create public database link ben_link connect to "benson" identified by "postgres" using 'benstore';
Database link created.
SQL> —-Next go to postgres and allow conection in the pg_hba.conf file
# "local" is for Unix domain socket connections only
local all all trust
# IPv4 local connections:
host all all 192.168.91.128/32 md5
host all all 192.168.91.151/32 md5
host all all 127.0.0.1/32 md5
-bash-4.2$
-bash-4.2$ psql -U benson -d benstore
psql.bin (10.15.24)
Type "help" for help.
benstore=#
benstore=# \dt
List of relations
Schema | Name | Type | Owner
--------+---------+-------+--------------
public | account | table | enterprisedb
public | dept | table | benson
public | emp | table | benson
public | fin | table | enterprisedb
public | jobhist | table | benson
(5 rows)
benstore=# select * from emp;
empno | ename | job | mgr | hiredate | sal | comm | deptno
-------+--------+-----------+------+--------------------+---------+---------+--------
7369 | SMITH | CLERK | 7902 | 17-DEC-80 00:00:00 | 800.00 | | 20
7499 | ALLEN | SALESMAN | 7698 | 20-FEB-81 00:00:00 | 1600.00 | 300.00 | 30
7521 | WARD | SALESMAN | 7698 | 22-FEB-81 00:00:00 | 1250.00 | 500.00 | 30
7566 | JONES | MANAGER | 7839 | 02-APR-81 00:00:00 | 2975.00 | | 20
7654 | MARTIN | SALESMAN | 7698 | 28-SEP-81 00:00:00 | 1250.00 | 1400.00 | 30
7698 | BLAKE | MANAGER | 7839 | 01-MAY-81 00:00:00 | 2850.00 | | 30
7782 | CLARK | MANAGER | 7839 | 09-JUN-81 00:00:00 | 2450.00 | | 10
7788 | SCOTT | ANALYST | 7566 | 19-APR-87 00:00:00 | 3000.00 | | 20
7839 | KING | PRESIDENT | | 17-NOV-81 00:00:00 | 5000.00 | | 10
7844 | TURNER | SALESMAN | 7698 | 08-SEP-81 00:00:00 | 1500.00 | 0.00 | 30
7876 | ADAMS | CLERK | 7788 | 23-MAY-87 00:00:00 | 1100.00 | | 20
7900 | JAMES | CLERK | 7698 | 03-DEC-81 00:00:00 | 950.00 | | 30
7902 | FORD | ANALYST | 7566 | 03-DEC-81 00:00:00 | 3000.00 | | 20
7934 | MILLER | CLERK | 7782 | 23-JAN-82 00:00:00 | 1300.00 | | 10
(14 rows)
benstore=#
benstore=# select count(*) from account ;
count
-------
4899
(1 row)
benstore=# select count(*) from emp ;
count
-------
14
(1 row)
benstore=# select count(*) from dept ;
count
-------
4
(1 row)
benstore=# —-BACK ON ORACLE SERVER, WE CAN HOW TEST OUR CONNECTION
-bash-4.2$
-bash-4.2$ sqlplus / as sysdba
SQL*Plus: Release 12.1.0.2.0 Production on Wed Dec 2 21:43:07 2020
Copyright (c) 1982, 2014, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
SQL>
SQL> select count(*) from "public"."emp"@ben_link;
COUNT(*)
----------
14
SQL> select count(*) from "public"."account"@ben_link;
COUNT(*)
----------
4899
SQL> select count(*) from "public"."dept"@ben_link;
COUNT(*)
----------
4
SQL>
SQL> select * from "public"."emp"@ben_link;
empno ename
---------- ------------------------------------------------------------
job mgr hiredate
------------------------------------------------------ ---------- ---------
sal comm deptno
---------- ---------- ----------
7369 SMITH
CLERK 7902 17-DEC-80
800 20
7499 ALLEN
SALESMAN 7698 20-FEB-81
1600 300 30
empno ename
---------- ------------------------------------------------------------
job mgr hiredate
------------------------------------------------------ ---------- ---------
sal comm deptno
---------- ---------- ----------
7521 WARD
SALESMAN 7698 22-FEB-81
1250 500 30
7566 JONES
MANAGER 7839 02-APR-81
empno ename
---------- ------------------------------------------------------------
job mgr hiredate
------------------------------------------------------ ---------- ---------
sal comm deptno
---------- ---------- ----------
2975 20
7654 MARTIN
SALESMAN 7698 28-SEP-81
1250 1400 30
7698 BLAKE
empno ename
---------- ------------------------------------------------------------
job mgr hiredate
------------------------------------------------------ ---------- ---------
sal comm deptno
SO AS SEEN, WE CAN FETCH THE DATA AND ALSO EVEN DO DML OR UPDATES AS WELL
