Ora2Pg

Ora2Pg can be used in two ways.

The first and obvious one is for moving or replicating information from Oracle databases to PostgreSQL installations.

The second is a little bit complicated, but Ora2Pg can also be used to reverse engineer large Oracle database structures, by creating a dump file that closely reconstructs the database's schema in an easy to follow manner.

Ora2Pg works based on a configuration file, where developers can control a bunch of settings and tweak the export process to fit their databases and the recorded data.

Currently, Ora2Pg can be used to export database schemas, user nd grup privileges, ranges and list partitions, just some of the database's tables, functions, triggers, procedures, packages, and of course, the full data.

Since the SQL syntax used with the two database differs a little bit, Ora2Pg will also transform PLSQL code to PLPGSQL markup.

Installation and more in-depth instructions are provided with the package's README file.

What is new in this release:

• ORA_INITIAL_COMMAND to be able to execute a custom command just after the connection to Oracle, for example to unlock a security policy.
• INTERNAL_DATE_MAX to change the behavior of Ora2Pg with internal date found in user defined types.

What is new in version 16.1:

• ORA_INITIAL_COMMAND to be able to execute a custom command just after the connection to Oracle, for example to unlock a security policy.
• INTERNAL_DATE_MAX to change the behavior of Ora2Pg with internal date found in user defined types.

What is new in version 15.3:

• ORA_INITIAL_COMMAND to be able to execute a custom command just after the connection to Oracle, for example to unlock a security policy.
• INTERNAL_DATE_MAX to change the behavior of Ora2Pg with internal date found in user defined types.

What is new in version 15.2:

• ORA_INITIAL_COMMAND to be able to execute a custom command just after the connection to Oracle, for example to unlock a security policy.
• INTERNAL_DATE_MAX to change the behavior of Ora2Pg with internal date found in user defined types.

What is new in version 15.1:

• Fixed replacement of function name which include SELECT in their name by PERFORM. Thanks to Frederic Bamiere for the report.
• Fixed creation of sources subdirectories when initializing a new migration project.

What is new in version 15.0:

• Added support to the PostgreSQL external_file extension to mimic BFILE type from Oracle. See https://github.com/darold/external_file for more information.
• Allow export of Oracle's DIRECTORY as external_file extension objects. This will also try to export read/write privilege on those directories.
• Allow export of Oracle's DATABASE LINK as Oracle foreign data wrapper server using oracle_fdw.
• Allow function with PRAGMA AUTONOMOUS_TRANSACTION to be exported through a dblink wrapper to achieve the autonomous transaction.
• Allow export of Oracle's SYNONYMS as views. Views can use foreign table to create "synonym" on object of a remote database.
• Added trimming of data when DATA_TYPE is used to convert CHAR(n) Oracle column into varchar(n) or text. Default is to trim both side any space character. This behavior can be controlled using two new configuration directives TRIM_TYPE and TRIM_CHAR.
• Added auto detection of geometry constraint type and dimensions through spatial index parameters. This avoid the overhead of sequential scan of the geometric column.
• Added support to export Oracle sub partition and create sub partition for PostgreSQL with the corresponding trigger.
• ALLOW and EXCLUDE directives are now able to apply filter on the object type. Backward compatibility can not be fully preserved, older definition will apply to current export type only, this could change your export in some conditions. See documentation update for more explanation.

What is new in version 14.0:

• Export of declaration of language C function. Previous version was not exporting function with no code body like external C function.
• Export of COMMENT from views.
• Function to replace some call to SYS_CONTECT(USERENV, ...) by the PostgreSQL equivalent.
• Added POSTGIS_SCHEMA configuration directive to add the dedicated PostGis schema into the search_path.
• Added PG_SUPPORTS_IFEXISTS configuration directive to be able to suppress IF EXISTS call in DDL statement generated by Ora2Pg.
• Triggers are now all excluded/allowed following the table names specified in the ALLOW and EXCLUDED directives.
• Allow automatic export of nested tables (TYPE+TABLE+COPY).

What is new in version 13.0:

• Force export type to be INSERT when COPY is used and a table have a GEOMETRY column. I can not find a solution to export as copy statement for the moment.
• Fixed export of user defined type as object.
• Limit look up of objects to the ALLOW or EXCLUDE filter into the SQL query instead of the Perl code to avoid retrieving huge list of objects on such database. Thanks to menardorama for the feature request.
• Added support to spatial data export in INSERT mode. Still need some work in COPY export mode if possible.
• Fixed query to retrieve SRID that broken with patch on CONVERT_SRID.
• Fixed wrong filter with ALLOW directive when getting list of partition.
• Added GRANT export read from an input file.
• Fixed data type conversion when using input file and data type such varchar2(10 BYTE).
• Added export of comment with TABLE and VIEW exports using an input file.
• Added extraction of TABLESPACE from an input file.
• Added support to SEQUENCE extraction from input file.
• Fixed wrong filter with ALLOW directive when exporting partition. The filter was done on partition name instead of table name, that mean that setting ALLOW directive was resulting in no export at all.
• Added CONVERT_SRID configuration directive to control the automatic conversion of SRID to standard EPSG using the Oracle SDO function sdo_cs.map_oracle_srid_to_epsg() Oracle function.
• Fixed a typo in the create index prefix on partitioned tables. Thanks to menardorama for the patch.
• Fixed non replacement of destination during SHOW_COLUMN and COPY export. Using MODIFY_TYPE was only working in TABLE export.
• Force pl/sql conversion with TABLE export to replace advanced default values. Fixed code TRUNC(SYSDATE, MONTH) in default value and everywhere that should be: date_trunc(month,LOCALTIMESTAMP).
• Fixed code regarding unique partition index naming.
• Added PREFIX_PARTITION configuration directive. When enabled it will force renaming all partition table name with the name of the parent table.
• Added AUTODETECT_SPATIAL_TYPE in configuration file and documentation about this new directive.
• Added export of SDO_GEOMETRY column type. They are basically exported to the non-constrained "geometry" type with SRID if defined.