Oracle database to PostgreSQL, has been officially released and is
publicly available for download.
This new major release adds several new useful features and lot of
* Automatic rewrite of simple form of (+) outer join Oracle's
syntax. This major feature makes Ora2Pg become the first free
tool that is able to rewrite automatically some (+) outer join
in command line mode. It works with simple form of outer join
but this is a beginning.
* Add export of Oracle's virtual column using a real column and
* Allow conversion of RAW/CHAR/VARCHAR2 type with precision in
DATA_TYPE directive. Useful for example to transform all RAW(32)
or VARCHAR2(32) columns into PostgreSQL special type uuid.
* Add export NOT VALIDATED state from Oracle foreign keys and check
constraints into NOT VALID constraints in PostgreSQL.
* Replace call to SYS_GUID() with uuid_generate_v4() by default.
* Add "CREATE EXTENSION IF NOT EXISTS dblink;" before an autonomous
transaction or "CREATE EXTENSION IF NOT EXISTS pg_background;".
* Major rewrite of the way Ora2Pg parse PL/SQL to rewrite function
calls and other PL/SQL to plpgsql replacement. There should not
be any limitation in rewriting when a function contains a sub
query or an other function call inside his parameters.
* Refactoring of ora2pg to not requires any dependency other than
the Perl DBI module by default. All DBD drivers are now optionals
and ora2pg will expect an Oracle DDL file as input by default.
* Add export of Oracle's global variables defined in package. They
are exported as user defined custom variables and available in
a session. If the variable is a constant or have a default value
assigned at declaration, ora2pg will create a new file with the
declaration (global_variables.conf) to be included in the main
configuration file postgresql.conf file.
* Create full text search configuration when USE_UNACCENT directive
is enabled using the auto detected stemmer or the one defined in
FTS_CONFIG. For example:
CREATE TEXT SEARCH CONFIGURATION fr (COPY = french);
ALTER TEXT SEARCH CONFIGURATION fr ALTER MAPPING FOR
hword, hword_part, word WITH unaccent, french_stem;
CREATE INDEX place_notes_cidx ON places
USING gin(to_tsvector('fr', place_notes));
Changes and incompatibilities from previous release:
* FTS_INDEX_ONLY is now enabled by default because the addition of
a column is not always possible and not always necessary where a
simple function-based index is enough.
* Remove use to setweigth() on single column FTS based indexes.
* Change default behaviour of Ora2Pg in Full Text Search index
A new command line option and some configuration directive have
* Option -D | --data_type to allow custom data type replacement
at command line like in configuration file with DATA_TYPE.
* UUID_FUNCTION to be able to redefined the function called to
replace SYS_GUID() Oracle function. Default to uuid_generate_v4.
* REWRITE_OUTER_JOIN to be able to disable the rewriting of Oracle
native syntax (+) into OUTER JOIN if rewritten code is broken.
* USE_UNACCENT and USE_LOWER_UNACCENT configuration directives to
use the unaccent extension with pg_trgm with the FTS indexes.
* FTS_INDEX_ONLY, by default Ora2Pg creates an extra tsvector column
with a dedicated triggers for FTS indexes. Enable this directive
if you just want a function-based index like:
CREATE INDEX ON t_document USING
* FTS_CONFIG, use this directive to force the text search stemmer
used with the to_tsvector() function. Default is to auto detect
the Oracle FTS stemmer. For example, setting FTS_CONFIG to
pg_catalog.english or pg_catalog.french will override the auto
Thanks to all contributors, they are all cited in the changelog file.
- Website: http://ora2pg.darold.net/
- Download: http://sourceforge.net/project
- Development: https://github.com/darold/ora2
- Changelog: https://github.com/darold/ora2
- Documentation: https://github.com/darold/ora2
About Ora2Pg :
Ora2Pg is an easy and reliable tool to migrate from Oracle to PostgreSQL.
It is developed since 2001 and can export most of the Oracle objects
(table, view, materialized view, tablespace, sequence, indexes, trigger,
grant, function, procedure, package, partition, data, blob and external
Ora2Pg works on any platform and is available under the GPL v3 licence.