Oracle: Access PostgreSQL from Oracle

Many years ago, enterprise databases were the only databases used in companies. But there are many applications that do not need a big expensive database. And fortunately, Open Sources competitors, like PostgreSQL, grew up to be an alternative to "old" RDBMS, like Oracle or IBM DB/2.

Running all my databases on Oracle has some benefits: If I need to access data from another database, I just create a database link. Using that, accessing and copying data between these databases is quite easy (as long as there's no LONG data type).

Creating a database link to a PostgreSQL database does not work out-of-the-box. But there's a way, which uses UnixODBC for that. Oracle calls it HS ("Heterogenous Services").

Installation

First, we need to install UnixODBC packages - the generic ones, and the ones for the database we want to access:

yum install unixODBC
yum install postgresql-18-odbc

Configuration

Then, we have to configure ODBC.

General database drivers configuration

In /etc/odbcinst.ini, we have to configure each database system - in this case: PostgreSQL:

[PostgreSQL]
Description=ODBC for PostgreSQL
Driver=/usr/pgsql-18/lib/psqlodbca.so
Setup=/usr/lib64/libodbcpsqlS.so
Driver64=/usr/pgsql-18/lib/psqlodbca.so
Setup64=/usr/lib64/libodbcpsqlS.so
FileUsage=1

In /etc/odbc.ini we configure access to each database using a unique name:

[pgdb]
Description = PostgreSQL pgdb
Driver = PostgreSQL
Servername = 127.0.0.1
Database = dvdrental
Port = 5432
Trace = yes
TraceFile = /tmp/odbctrace.txt

Oracle Configuration

Listener

We'll have to configure an Oracle TNS listener which redirects access to the non-Oracle databases. We can either use an existing TNS listener, or we configure a new one. In this case, we use the existing one (LISTENER).

Let's edit $ORACLE_HOME/network/admin/listener.ora:

SID_LIST_LISTENER =
 (SID_LIST =
  (SID_DESC=
   (SID_NAME=pgdb)
   (ORACLE_HOME=/u01/app/oracle/product/19c)
   (PROGRAM=dg4odbc)(ENVS="LD_LIBRARY_PATH=/usr/lib64:/u01/app/oracle/product/19c/lib")))

After that, the listener need to be restarted:

# lsnrctl stop
# lsnrctl start

HS Configuration

To allow access to PostgreSQL, we need to fake an Oracle database which then refers to ODBC. To do that, we need to edit $ORACLE_HOME/hs/admin/initpgdb.ora:

HS_FDS_CONNECT_INFO = pgdb
HS_FDS_TRACE_LEVEL = ON
HS_FDS_SHAREABLE_NAME = /usr/lib64/libodbc.so
HS_LANGUAGE = AMERICAN_AMERICA.WE8ISO8859P15
set ODBCINI=/etc

TNSNAMES

As last step, we need TNS entries in $ORACLE_HOME/network/admin/tnsnames.ora:

pgdb = 
  (DESCRIPTION=
    (ADDRESS=
      (PROTOCOL=tcp)
      (HOST=localhost)
      (PORT=1521)
    )
	(CONNECT_DATA=(SID=pgdb))
	(HS=OK)
  )

Then, we can create a database link:

SQL> create database link pgdb connect to "postgres" identified by “P4ssw0rd" using ‘pgdb’; 

Testing

Unfortunately, we cannot test it like we would do it with an Oracle database, as the DUAL object does not exist:

SQL> select 1 from dual@pgdb;

select count(*) from dual@pgdb
                     *
ERROR at line 1:
ORA-28500: connection from ORACLE to a non-Oracle system returned this message:
ERROR: relation “DUAL" does not exist;
No query has been executed with that handle {42P01,NativeErr = 1}
ORA-02063: preceding 3 lines from PGDB

Also, other views like DBA_TABLES or DBA_VIEWS don't exist in PostgreSQL. So we will need some information about the PostgreSQL structure. Knowing that my PostgreSQL table is actor, I can check it:

SQL> DESCRIBE actor@pgdb

 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 actor_id                                  NOT NULL NUMBER(10)
 first_name                                NOT NULL VARCHAR2(540)
 last_name                                 NOT NULL VARCHAR2(540)
 last_update                               NOT NULL DATE

Ok, doesn't look that bad. Let's select some data:

SQL> select first_name,last_name from actor@pgdb;

select first_name,last_name from actor@pgdb
                  *
ERROR at line 1:
ORA-00904: "LAST_NAME": invalid identifier

Hm, this doesn't work. The reason is, that Oracle objects are automatically created with upper case, unless specified in lower case.

My PostgreSQL object is created on lower case, so I have to use quotes:

SQL> select "first_name","last_name" from "actor"@pgdb;

first_name
--------------------------------------------------------------------------------
last_name
--------------------------------------------------------------------------------
Penelope
Guiness

Conclusion

After some configuration, there's a way to access PostgreSQL data from Oracle, which is not too complicated. But keep in mind that

• ODBC is not a high performing solution. If your data is too large, statements will take a long time.
• There will be data conversion between Oracle and PostgreSQL. Before moving to production, check your data, especially for characters like German Umlauts ä, ö, ü and ß. If it does not fit, consider changing the parameter HS_LANGUAGE