Database Tutorials MSSQL, Oracle, PostgreSQL, MySQL, MariaDB, DB2, Sybase, Teradata, Big Data, NOSQL, MongoDB, Couchbase, Cassandra, Windows, Linux 
In today’s article, I will tell you how to Create Dblink From Oracle To Postgresql Database.
Using the dblink we will create, we can query a table in our Postgres database from the Oracle database.
1 2 | Oracle DB: 192.168.56.12 Postgres DB: 192.168.56.13 |
Things to do in the Postgres database:
1.First we will create a user for dblink.
1 2 | $ psql postgres# create user dblinkuser encrypted password 'userpass'; |
2. After creating the user, we authorize her according to which schema and table we want it to use.
1 2 | postgres# grant usage on schema "melek" to dblinkuser; postgres# grant select on "melek".deneme to dblinkuser; |
3. Add the following line to the pg_hba.conf file.
1 2 | $ vi /var/lib/pgsql/13/data/pg_hba.conf host postgres dblinkuser 192.168.56.12/32 md5 |
4. After making the change, we reload the service.
1 | $ systemctl reload postgresql-13.service |
Things to do in Oracle database:
1.First, we install the following package.
1 | $ yum install postgresql-odbc |
2. We edit the contents of the odbcinst.ini file according to the settings of our own postgres server.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 | $ vi /etc/odbcinst.ini # Example driver definitions # Driver from the postgresql-odbc package # Setup from the unixODBC package [PG] Description = PG Driver = /usr/lib64/liboplodbcS.so.2.0.0 ServerName = 192.168.56.13 Username = dblinkuser Password = userpass Port = 5432 Database = postgres Setup = /usr/lib64/libodbcpsqlS.so Driver64 = /usr/lib64/psqlodbcw.so Setup64 = /usr/lib64/libodbcpsqlS.so FileUsage = 1 |
3. Create the initPG.ora file in the ORACLE_HOME/hs/admin directory and add the following lines to it.
1 2 3 4 5 6 7 8 9 10 11 12 | $ vi $ORACLE_HOME/hs/admin/initPG.ora # HS init parameters # HS_FDS_CONNECT_INFO = PG HS_FDS_TRACE_LEVEL = 4 HS_FDS_SHAREABLE_NAME = /usr/lib64/psqlodbcw.so HS_LANGUAGE = AMERICAN_AMERICA.WE8ISO8859P9 HS_FDS_TRACE_LEVEL=ON # # ODBC specific environment variables # set ODBCINI=/etc/odbcinst.ini |
4. Add the following lines to the tnsnames.ora file.
1 2 3 4 5 6 7 8 9 | $ vi $ORACLE_HOME/network/admin/tnsnames.ora PG = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.12)(PORT = 1521)) (CONNECT_DATA = (SID = PG) ) (HS = OK) ) |
5. Add the following lines to the listener.ora file.
1 2 3 4 5 6 7 8 9 | $ vi $ORACLE_HOME/network/admin/listener.ora SID_LIST_LISTENER = (SID_LIST= (SID_DESC= (SID_NAME=PG) (ORACLE_HOME=/u01/app/oracle/product/19.0.0/db_1) (PROGRAM=dg4odbc) ) ) |
6. Reload the Listener.
1 | $ lsnrctl reload |
7. The dblink is created.
1 2 3 4 | SQL> CREATE DATABASE LINK PG CONNECT TO "dblinkuser" IDENTIFIED BY userpass USING 'PG'; |
Test:
After doing all the steps, I will query a table in the Postgres database in the Oracle database to test it.
1 | SQL> select * from "melek"."deneme"@PG; |
As for the connection to database, I use different tools such as bigcommerce connector, oracle connector etc. It depends on the databases.