In today’s article, I will tell you how to Create Dblink From Oracle To Postgresql Database.<\/p>\n
Using the dblink we will create, we can query a table in our Postgres database from the Oracle database.<\/p>\n
Oracle DB: 192.168.56.12\r\nPostgres DB: 192.168.56.13<\/pre>\nThings to do in the Postgres database:<\/h4>\n
1.First we will create a user for dblink.<\/p>\n
$ psql\r\npostgres# create user dblinkuser encrypted password 'userpass';<\/pre>\n2. After creating the user, we authorize her according to which schema and table we want it to use.<\/p>\n
postgres# grant usage on schema \"melek\" to dblinkuser;\r\npostgres# grant select on \"melek\".deneme to dblinkuser;<\/pre>\n3. Add the following line to the pg_hba.conf file.<\/p>\n
$ vi \/var\/lib\/pgsql\/13\/data\/pg_hba.conf\r\nhost postgres dblinkuser 192.168.56.12\/32 md5<\/pre>\n4. After making the change, we reload the service.<\/p>\n
$ systemctl reload postgresql-13.service<\/pre>\nThings to do in Oracle database:<\/h4>\n
1.First, we install the following package.<\/p>\n
$ yum install postgresql-odbc<\/pre>\n2. We edit the contents of the odbcinst.ini file according to the settings of our own postgres server.<\/p>\n
$ vi \/etc\/odbcinst.ini\r\n# Example driver definitions\r\n# Driver from the postgresql-odbc package\r\n# Setup from the unixODBC package\r\n[PG]\r\nDescription = PG\r\nDriver = \/usr\/lib64\/liboplodbcS.so.2.0.0\r\nServerName = 192.168.56.13\r\nUsername = dblinkuser\r\nPassword = userpass\r\nPort = 5432\r\nDatabase = postgres\r\nSetup = \/usr\/lib64\/libodbcpsqlS.so\r\nDriver64 = \/usr\/lib64\/psqlodbcw.so\r\nSetup64 = \/usr\/lib64\/libodbcpsqlS.so\r\nFileUsage = 1<\/pre>\n3. Create the initPG.ora file in the ORACLE_HOME\/hs\/admin directory and add the following lines to it.<\/p>\n
$ vi $ORACLE_HOME\/hs\/admin\/initPG.ora\r\n# HS init parameters\r\n#\r\nHS_FDS_CONNECT_INFO = PG\r\nHS_FDS_TRACE_LEVEL = 4\r\nHS_FDS_SHAREABLE_NAME = \/usr\/lib64\/psqlodbcw.so\r\nHS_LANGUAGE = AMERICAN_AMERICA.WE8ISO8859P9\r\nHS_FDS_TRACE_LEVEL=ON\r\n#\r\n# ODBC specific environment variables\r\n#\r\nset ODBCINI=\/etc\/odbcinst.ini<\/pre>\n4. Add the following lines to the tnsnames.ora file.<\/p>\n
$ vi $ORACLE_HOME\/network\/admin\/tnsnames.ora\r\nPG =\r\n (DESCRIPTION =\r\n (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.12)(PORT = 1521))\r\n (CONNECT_DATA =\r\n (SID = PG)\r\n )\r\n (HS = OK)\r\n )<\/pre>\n5. Add the following lines to the listener.ora file.<\/p>\n
$ vi $ORACLE_HOME\/network\/admin\/listener.ora\r\nSID_LIST_LISTENER =\r\n (SID_LIST=\r\n (SID_DESC=\r\n (SID_NAME=PG)\r\n (ORACLE_HOME=\/u01\/app\/oracle\/product\/19.0.0\/db_1)\r\n (PROGRAM=dg4odbc)\r\n )\r\n )<\/pre>\n6. Reload the Listener.<\/p>\n
$ lsnrctl reload<\/pre>\n7. The dblink is created.<\/p>\n
SQL> CREATE DATABASE LINK PG\r\nCONNECT TO \"dblinkuser\"\r\nIDENTIFIED BY userpass\r\nUSING 'PG';<\/pre>\nTest:<\/h4>\n
After doing all the steps, I will query a table in the Postgres database in the Oracle database to test it.<\/p>\n
SQL> select * from \"melek\".\"deneme\"@PG;<\/pre>\n<\/p>\n
<\/div>\n