Release of Ora2Pg v19.0 - Mailing list pgsql-announce

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