Skip to content


Subversion checkout URL

You can clone with
Download ZIP
Automatically creates oracle foreign data wrapper tables and allows materialized view snapshots
Tree: 0cadf974e4

Fetching latest commit…

Cannot retrieve the latest commit at this time

Failed to load latest commit information.


This set of functions is for creating materialized view snapshots on PostgreSQL from an Oracle database using the Oracle Foreign Data Wrapper (

This was made to replace a replication system currently in place using dbi_link between an old Oracle 8i system and PostgreSQL 9.1. The Oracle setup file is only known for certain to work with 8i. If anyone else can supply setup files (or confirm this file works) for more modern versions of Oracle, that would be appreciated.

NOTE: You will have to edit setup_pg.sql to set the FDW server name and the schema where the oracle view is located for your systems. Also, the oracle setup file is using the ALL_TAB_COLUMNS system view. This only shows columns for tables that are accessible to the Oracle user you are using. If you want to be able to see all columns for all ables, you can try DBA_TAB_COLUMNS instead, but you'll need special permissions set for the Oracle user.

create_oracle_fdw_table('OWNER.TABLENAME') - Function for creating an FDW table. If you comment out 'EXECUTE v_insert_sql' near the bottom, this function can be used as a standalone function for easily creating FDW tables.

perform_snapshot('OWNER.TABLENAME') - Grabs the most recent data from Oracle. Will automatically create the needed snap tables and view if they don't exist yet.

snapshot_maker('OWNER.TABLENAME') - Automatically create an FDW table, create both snap tables & create the view all in one command. 

snapshot_destroyer('OWNER.TABLENAME', 'ARCHIVE') - Remove a set of snap tables & remove the FDW table. Send the 'ARCHIVE' option if you'd like to make a permanent table on PostgreSQL with the same name as the view before. Send anything else (ex, 'noARCHIVE') to remove everything including data.
Something went wrong with that request. Please try again.