Release of Ora2Pg v19.0

Grenoble, France - August 18th, 2018

**Ora2Pg 19.0 released**

Version 19.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 release fix several issues reported by users during last year.
It also adds several new features and configuration directives.

New features:

  * Add export of Oracle HASH partitioning when PG_SUPPORTS_PARTITION
    is enabled. This is a PostgreSQL 11 feature.
  * Add SUBTYPE translation into DOMAIN with TYPE and PACKAGE export.
  * Add automatic translation of
      KEEP (DENSE_RANK FIRST|LAST ORDER BY ...) OVER (PARTITION BY ...)
    into
      FIRST|LAST_VALUE(...) OVER (PARTITION BY ... ORDER BY ...).
   * Add PCTFREE to FILLFACTOR conversion when PCTFREE is upper than
     the default value: 10.
  * Replace DELETE clause not followed with FROM (optional in Oracle).
  * Remove Oracle extra clauses in TRUNCATE command.
  * Allow use of NUMBER(*) in DATA_TYPE directive to convert all
    NUMBER(*) into the given type whatever is the length. Ex:
        DATA_TYPE    NUMBER(*):bigint.
  * Add a PARALLEL hint to all Oracle queries used to migrate data.
  * Add export of Identity Columns from Oracle Database 12c.
  * Add translation of UROWID datatype and information in documentation
    about why default corresponding type OID will fail at data import.
  * Remove unwanted and unused keywords from CREATE TABLE statements:
    PARALLEL and COMPRESS.
  * Remove TEMPORARY in DROP statements.
  * Improve speed of escape_copy() function used for data export.
  * Add translation of Oracle functions NUMTOYMINTERVAL() and
    NUMTODSINTERVAL().
  * Add counting of jobs defined in Oracle scheduler in the migration
    assessment feature.
  * Add CSMIG in the list of Oracle default system schema
  * Fully rewrite data export for table with nested user defined types
    DBD::Oracle fetchall_arrayref() is not able to associate complex
    custom types to the returned arrays, changed this call to use
    fetchrow_array() also used to export BLOB.
  * QUERY export will now output translated queries as well as
    untranslated ones. This break backward compatibility, previously
    only translated query was dumped.
  * Auto detect UTF-8 input files to automatically use utf8 encoding.
  * Support translation of MySQL global variables.
  * Add translation of preprocessor in Oracle external table into
    program in foreign table definition. Allow translation of external
    table from file.
  * Add translation to NVL2() Oracle function.
  * Translate CONVERT() MySQL function.
  * Translate some form of GROUP_CONCAT() that was not translated.
  * Remove call to CHARSET in cast() function, replace it by COLLATE
    every where else. This must cover most of the cases but some
    specials use might not, so please reports any issue with this
    behavior.
  * Add -c | --config command line option to ora2pg_scanner to set
    custom configuration file to be used instead of ora2pg default:
    /etc/ora2pg/ora2pg.conf
  * Improve CONNECT BY and OUTER JOIN translation.
  * And lot of MySQL to PostgreSQL improvements.

Several new configuration directives have been added:

  * Add DEFAULT_PARALLELISM_DEGREE to control PARALLEL hint use
    when exporting data from Oracle. Default is disabled.
  * Make documentation about KEEP_PKEY_NAMES more explicit about
    kind of constraints affected by this directive.
  * Add PG_SUPPORTS_IDENTITY configuration directive to enable
    export of Oracle identity columns into PostgreSQL 10 feature.
    If PG_SUPPORTS_IDENTITY is disabled and there is IDENTITY column
    in the Oracle table, they are exported as serial or bigserial
    columns. When it is enabled they are exported as IDENTITY columns
    like:

          CREATE TABLE identity_test_tab (
                  id bigint GENERATED ALWAYS AS IDENTITY,
                  description varchar(30)
          ) ;

    If there is non default sequence option set in Oracle, they will
    be appended after the IDENTITY keyword. Additionally in both cases
    Ora2Pg will create a file AUTOINCREMENT_output.sql with a function
    to update the associated sequences with the restart value set to
    "SELECT max(colname)+1 FROM tablename". Of course this file must
    be imported after data import otherwise sequence will be kept to
    start value.
  * Add DISABLE_UNLOGGED configuration directive. By default Ora2Pg
    export Oracle tables with the NOLOGGING attribute into UNLOGGED
    tables. You may want to fully disable this feature because you
    will lost all data from unlogged table in case of PostgreSQL crash.
    Set it to 1 to export all tables as normal table. When creating a
    new migration project using --init_project, this directive is
    activated by default. This is not the case in the default
    configuration file for backward compatibility.
  * Add FORCE_SECURITY_INVOKER configuration directive. Ora2Pg use
    the function's security privileges set in Oracle and it is often
    defined as SECURITY DEFINER. To override those security privileges
    for all functions and use SECURITY DEFINER instead, enable this
    directive.
  * Add AUTONOMOUS_TRANSACTION in configuration to enable translation
    of autonomous transactions into a wrapper function using dblink
    or pg_background extension. If you don't want to use this feature
    and just want to export the function as a normal one without the
    pragma call, disable this directive.
  * Add documentation about COMMENT_SAVEPOINT configuration directive.
  * Major rewrite in PACKAGE parser to better support global variables
    detection. Global variable that have no default values are now
    always initialized to empty string in file global_variables.conf
    so that we see that they exists. This might not change the global
    behavior.

I especially want to thank Pavel Stehule and Eric Delanoe who spent
lot of time this year to help me to improve the PL/SQL to plpgsql
translation and also Krasiyan Andreev who help a lot to finalize
the MySQL to PostgreSQL migration features.

For a complete list of change see
https://github.com/darold/ora2pg/blob/master/changelog

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

Useful Links:

    - Website: http://www.ora2pg.com/
    - Download: http://sourceforge.net/projects/ora2pg/
    - Development: https://github.com/darold/ora2pg
    - Changelog: https://github.com/darold/ora2pg/blob/master/changelog
    - Documentation: https://github.com/darold/ora2pg/blob/master/README

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
table).

Ora2Pg works on any platform and is available under the GPL v3 licence.

Docs, Download & Support at http://www.ora2pg.com/

--
Gilles Darold