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.

Download the appropriate zip file

Copy files into the appropriate folders

In Oracle - Simple table called AH_TEST (imaginatively!)

In PostgreSQL

Create Foreign Table

No SDO_GEOMETRY :(

but...

does support BLOBS :)

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 :)

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!

Summary

You CAN'T edit spatial data yet!

but .....