Infrastructure at your Service

Daniel Westermann

Connecting your PostgreSQL instance to an Oracle database – The PostgreSQL 9.5 way

As you might know PostgreSQL 9.5 is currently in alpha 2. One of the new features will be the possibility to import foreign schemas. Having this
it is no longer required to create all the foreign tables which saves a lot of work.

Setting up oracle_fdw was done in exactly the same way as in the previous post. Make sure you use the latest version of oracle_fdw as some internals changed in PostgreSQL 9.5 which oracle_fdw depends on. The latest zipped version can be downloaded here

For this little demo I created the SCOTT user in my Oracle database ( $ORACLE_HOME/rdbms/admin/utlsampl.sql ):

SQL> show user
USER is "SCOTT"
SQL> select table_name from user_tables;

TABLE_NAME
--------------------------------------------------------------------------------
BONUS
DEPT
EMP
SALGRADE

SQL> 

On the PostgreSQL side lets create a schema and setup oracle_fdw:

postgres@oel7:/home/postgres/ [PG6] psql postgres
psql (9.5alpha2)
Type "help" for help.

postgres=# create schema scott;
CREATE SCHEMA
postgres=# create server oracle foreign data wrapper oracle_fdw options (dbserver '//192.168.22.242/PROD.local' );
CREATE SERVER
postgres=# create user mapping for postgres server oracle options (user 'SCOTT', password 'tiger');
CREATE USER MAPPING

Before PostgreSQL 9.5 now would be the time to create the foreign tables as they are defined in the Oracle database. But starting with PostgreSQL 9.5 we are able to do this:

postgres=# import foreign schema "SCOTT" from server oracle into scott;
IMPORT FOREIGN SCHEMA
postgres=# 

This should have imported all the table definitions:

postgres=# set search_path='scott';
SET
postgres=# \d 
              List of relations
 Schema |   Name   |     Type      |  Owner   
--------+----------+---------------+----------
 scott  | bonus    | foreign table | postgres
 scott  | dept     | foreign table | postgres
 scott  | emp      | foreign table | postgres
 scott  | salgrade | foreign table | postgres
(4 rows)

postgres=# \d+ dept
                                    Foreign table "scott.dept"
 Column |         Type          | Modifiers | FDW Options  | Storage  | Stats target | Description 
--------+-----------------------+-----------+--------------+----------+--------------+-------------
 deptno | smallint              | not null  | (key 'true') | plain    |              | 
 dname  | character varying(14) |           |              | extended |              | 
 loc    | character varying(13) |           |              | extended |              | 
Server: oracle
FDW Options: (schema 'SCOTT', "table" 'DEPT')

postgres=# \d+ emp
                                          Foreign table "scott.emp"
  Column  |              Type              | Modifiers | FDW Options  | Storage  | Stats target | Description 
----------+--------------------------------+-----------+--------------+----------+--------------+-------------
 empno    | smallint                       | not null  | (key 'true') | plain    |              | 
 ename    | character varying(10)          |           |              | extended |              | 
 job      | character varying(9)           |           |              | extended |              | 
 mgr      | smallint                       |           |              | plain    |              | 
 hiredate | timestamp(0) without time zone |           |              | plain    |              | 
 sal      | numeric(7,2)                   |           |              | main     |              | 
 comm     | numeric(7,2)                   |           |              | main     |              | 
 deptno   | smallint                       |           |              | plain    |              | 
Server: oracle
FDW Options: (schema 'SCOTT', "table" 'EMP')

What happens if we try to insert a row that violates the primary key on emp?

postgres=# insert into emp values (7639,'aa','aa',7902,current_date,123,11,20);
INSERT 0 1
postgres=# insert into emp values (7639,'aa','aa',7902,current_date,123,11,20);
ERROR:  error executing query: OCIStmtExecute failed to execute remote query
DETAIL:  ORA-00001: unique constraint (SCOTT.PK_EMP) violated
postgres=# 

Really, really cool. One command to get a whole schema definition out of an Oracle database. Makes life a lot easier.

6 Comments

  • Marce says:

    Muchísimas gracias. Me ha funcionado perfectamente (excepto un tema de librerías que tuve que copiar a mano. Mi versión de fdw es la 1.4.

  • Adam says:

    Please help me with the problem below “pg_ctl: command not found”, when try to “create extension oracle_fdw;”
    I’m new to linux.

    psql (9.5.10)
    Type “help” for help.
    postgres=# create extension oracle_fdw;
    ERROR: could not load library “/usr/lib/postgresql/9.5/lib/oracle_fdw.so”: libclntsh.so.12.1: cannot open shared object file: No such file or directory
    postgres=# \q
    postgres@adam-HP-Pavilion-dv3-Notebook-PC:~$ export LD_LIBRARY_PATH=/usr/lib/oracle/12.1/client64/lib/
    postgres@adam-HP-Pavilion-dv3-Notebook-PC:~$ pg_ctl -D /u02/pgdata/PG1 restart -m fast
    pg_ctl: command not found

    • Daniel Westermann says:

      Hi Adam,

      for the first issue: You have to set LD_LIBRARY_PATH before starting PostgreSQL as you did it. Otherwise the extension can not work as it will not find the Oracle libraries. For the second one: When pg_ctl is not found you need to adjust your PATH environment variable of use the full path to pg_ctl.

      Cheers,
      Daniel

  • Sethu says:

    I am getting the below error when I connect to remote Oracle database. Whether I need to create a handler function specific to this import?

    ERROR: foreign-data wrapper “oracle_fdw” has no handler
    ********** Error **********

    ERROR: foreign-data wrapper “oracle_fdw” has no handler
    SQL state: 55000

    TIA,
    Sethu

    • Daniel Westermann says:

      HI Sethu,
      you do not provide much information. What do you exactly execute when you get this error message? Does a connection with sqlplus work from that server?
      Cheers,
      Daniel

Leave a Reply

Daniel Westermann
Daniel Westermann

Senior Consultant and Technology Leader Open Infrastructure