Oracle FDW

or… making data in an oracle database appear to be in PostGIS!

Foreign Data Wrapper?

In 2003, a new specification "SQL Management of External Data" (SQL/MED) was added to the SQL standard.
It is a standardized way of handling access to remote objects from SQL databases.

In 2011, PostgreSQL 9.1 was released with read-only support

In 2013 write support was added with PostgreSQL 9.3.

There are now a variety of Foreign Data Wrappers (FDW) available which enable PostgreSQL Server to different remote data stores,
ranging from other SQL databases through to flat file.

Oracle FDW

http://laurenz.github.io/oracle_fdw/
https://github.com/laurenz/oracle_fdw

Install

Download the appropriate zip file

Copy files into the appropriate folders

Demo time!

So how does it work?

In Oracle - Simple table called AH_TEST (imaginatively!)

In PostgreSQL

Create Foreign Table

Gotchas!

No SDO_GEOMETRY :(

but...

does support BLOBS :)

So how do we do Geometry?

In oracle we create a view which converts SDO_GEOMETRY to WKB

Little gotcha! WKB does not include the SRID

Then in PostGIS we create a view on the foreign table which converts it from WKB to Geometry :)

What about editing in PostgreSQL 9.3?

Possible Solution:

Write an instead of trigger on both the views used to make the updates on the underlying tables.

Problem:

You can't update a derived column in a view

and we are using derived columns!

Issue:

Only works for updating non-spatial data. Will not work for inserting, deleting or updating spatial data.

Oracle instead of triggers do not support returning clause :(

Gotcha!

You can see data held in an Oracle database as if it was in the PostGIS database!
If you update non-spatial data in PostGIS it automatically updates the oracle database