opendbteam workspace

opendbteam workspace
opendbteam workspace

Monday, January 30, 2017

#PostgreSQL #Ora2Pg 18.0 released

Version 18.0 of Ora2Pg, a free and reliable tool used to migrate an
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
    a trigger.
  * 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:
            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
been added:

  * 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
        gin(to_tsvector('pg_catalog.english', title));
  * 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
    detected stemmer.

Thanks to all contributors, they are all cited in the changelog file.

Useful Links:

    - Website:
    - Download:
    - Development:
    - Changelog:
    - Documentation:

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.