[ANNOUNCE] Ora2Pg v18.2 has been released

Paris, France - September 1st, 2017

**Ora2Pg 18.2 released**

Version 18.2 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 during the last six months
by all users. It also adds lot of new features and configuration
directives:

  * Lot of improvement in outer join (+) translation even if there is
     still some case not or wrongly translated.
  * Add translation of SUBSTRB into substr.
  * Allow use of array in MODIFY_TYPE to export Oracle user defined
    type that are just array of some data type. For example:
      CREATE OR REPLACE TYPE mem_type IS VARRAY(10) of VARCHAR2(15);
    can be directly translated into text[] or varchar[]. In this case
    use the directive as follow: MODIFY_TYPE    CLUB:MEMBERS:text[]
    Ora2Pg will take care to transform all data of this column into
    the correct format. Only arrays of characters and numerics types
    are supported.
  * Add translation of Oracle function LISTAGG() into string_agg().
  * Add TEST_VIEW action to perform a simple count of rows returned by
    views on both database.
  * Translate SQL%ROWCOUNT into GET DIAGNOSTICS rowcount = ROW_COUNT
    and add translation of SQL%FOUND.
  * Add translation of column in trigger event test with IS DISTINCT,
    for example: IF updating('ID') THEN ... will be translated into:
    IF TG_OP = 'UPDATE' AND NEW.'ID' IS DISTINCT FROM OLD.'ID' then...
  * Replace UTL_MATH.EDIT_DISTANCE function by fuzzymatch levenshtein.
  * Allow use of MODIFY_STRUCT with TABLE export. Table creation DDL
    will respect the new list of columns and all indexes or foreign
    key pointing to or from a column removed will not be exported.
  * Add export of partition and subpartition using PostgreSQL native
    partitioning.
  * Auto detect encrypted columns and report them into the assessment.
    SHOW_COLUMN will also mark columns as encrypted.
  * Add information to global temporary tables in migration assessment.
  * Add experimental DATADIFF functionality.
  * Allow use of multiprocess with -j option or JOBS to FUNCTION and
    PROCEDURE export. Useful if you have thousands of these objects.
  * Force RAW(N) type with default value set to sys_guid() as UUID
    on PostgreSQL.
  * Replace function with out parameter using select into. For example
    a call to:       get_item_attr( attr_name, p_value );
    where p_value is an INOUT parameter, will be rewritten as

        p_value := get_item_attr( attr_name, p_value );

    If there is multiple OUT parameters, Ora2Pg will use syntax:

        SELECT get_item_attr( attr_name, p_value )
        INTO (attr_name, p_value);

  * Add translation of CONNECT BY using PostgreSQL CTE equivalent.
    This translation also include a replacement of LEVEL and
    SYS_CONNECT_BY_PATH native Oracle features. On complex queries
    there could still be manual editing but all the main work is done.
  * Add support to user defined exception, errcode affected to each
    custom exception start from 50001.
  * Translate call to to_char() with a single parameter into a cast
    to varchar. Can be disabled using USE_ORAFCE directive.
  * Improve ora2pg_scanner to automatically generates migration
    assessment reports for all schema on an Oracle instance. Before
    the schema name to audit was mandatory, now, when the schema
    is not set Ora2Pg will scan all schema. The connexion user need
    to have DBA privilege. Ora2Pg will also add the hostname and SID
    as prefix in the filename of the report. This last changee forbids
    ora2pg_scanner to overwrite a report if the same schema name is
    found in several databases.

Several new configuration directives have been added:

  * Add USE_ORAFCE configuration directive that can be enabled if you
    want to use functions defined in the Orafce library and prevent
    Ora2Pg to translate call to these functions. The Orafce library
    can be found here: https://github.com/orafce/orafce
    By default Ora2pg rewrite add_month(), add_year(), date_trunc()
    and to_char() functions, but you may prefer to use the Orafce
    functions that do not need any code transformation. Directive
    DATE_FUNCTION_REWRITE has been removed as it was also used to
    disable replacement of add_month(), add_year() and date_trunc()
    when Orafce is used, useless now.
  * Add FILE_PER_FKEYS configuration directive to allow foreign key
    declaration to be saved in a separate file during schema export.
    By default foreign keys are exported into the main output file or
    in the CONSTRAINT_output.sql file. If enabled foreign keys will be
    exported into a file named FKEYS_output.sql
  * Add new COMMENT_COMMIT_ROLLBACK configuration directive. Call to
    COMMIT/ROLLBACK in PL/SQL code are kept untouched by Ora2Pg to
    force the user to review the logic of the function. Once it is
    fixed in Oracle source code or you want to comment this calls
    enable the directive.
  * Add CREATE_OR_REPLACE configuration directive. By default Ora2Pg
    use CREATE OR REPLACE in function DDL, if you need not to override
    existing functions disable this configuration directive, DDL will
    not include OR REPLACE.
  * Add FUNCTION_CHECK configuration directive. Disable this directive
    if you want to disable check_function_bodies.

        SET check_function_bodies = false;

    It disables validation of the function body string during CREATE
    FUNCTION. Default is to use de postgresql.conf setting that enable
    it by default.
  * Add PG_SUPPORTS_PARTITION directive, disabled by default.
    PostgreSQL version prior to 10.0 do not have native partitioning.
    Enable this directive if you want to use PostgreSQL declarative
    partitioning instead of the old style check constraint and trigger.
  * Add PG_SUPPORTS_SUBSTR configuration directive to replace substr()
    call with substring() on old PostgreSQL versions or some fork
    like Redshift.
  * Add PG_INITIAL_COMMAND to send some statements at session startup.
    This directive is the equivalent used for Oracle connection,
    ORA_INITIAL_COMMAND. Both can now be used multiple time now.
  * Add DBLINK_CONN configuration directive. By default if you have
    an autonomous transaction translated using dblink extension the
    connection is defined using the values set with PG_DSN, PG_USER
    and PG_PWD. If you want to fully override the connection string
    use this directive to set the connection in the autonomous
    transaction wrapper function.
  * Add STRING_CONSTANT_REGEXP configuration directive. Ora2Pg replace
    all string constant during the pl/sql to plpgsql translation,
    string constant are all text include between single quote. If you
    have some string placeholder used in dynamic call to queries you
    can set a list of regexp to be temporary replaced to not break the
    parser. For example:

        STRING_CONSTANT_REGEXP         <cfqueryparam value=".*">

    The list of regexp must use the semi colon as separator.
  * Add FUNCTION_STABLE configuration directive. By default Oracle
    functions are marked as STABLE as they can not modify data unless
    when used in PL/SQL with variable assignment or as conditional
    expression. You can force Ora2Pg to create these function as
    VOLATILE by disabling this configuration directive.
  * Add new TO_NUMBER_CONVERSION configuration directive to control
    TO_NUMBER translation behavior. By default Oracle call to function
    TO_NUMBER will be translated as a cast into numeric. For example,
    TO_NUMBER('10.1234') is converted into PostgreSQL call:
        to_number('10.1234')::numeric.
    If you want you can cast the call to integer or bigint by changing
    the value of the configuration directive. If you need better
    control of the format, just set it as value, for example:
           TO_NUMBER_CONVERSION    99999999999999999999D9999999999
    will convert the code above as:
          TO_NUMBER('10.1234', '99999999999999999999D9999999999')
    Any value of the directive that it is not numeric, integer or
    bigint will be taken as a mask format. If set to none, then no
    conversion will be done.
  * Add LOOK_FORWARD_FUNCTION configuration directive which takes a
    list of schema to get functions/procedures meta information that
    are used in the current schema export. When replacing call to
    function with OUT or INOUT parameters, if a function is declared
    in an other package then the function call rewriting can not be
    done because Ora2Pg only knows about functions declared in the
    current schema. By setting a comma separated list of schema as
    value of the directive, Ora2Pg will look forward in these packages
    for all functions, procedures and packages declaration before
    proceeding to current schema export.
  * Add PG_SUPPORTS_NAMED_OPERATOR to control the replacement of the
    PL/SQL operator used in named parameter => with the PostgreSQL
    proprietary operator := Disable this directive if you are using
    PG < 9.5
  * Add a warning when Ora2Pg reorder the parameters of a function
    following the PostgreSQL rule that all input parameters following
    a parameter with a default value must have default values as well.
    In this case, Ora2Pg extracts all parameters with default values
    and put them at end of the parameter list. This is to warn you
    that a manual rewrite is required on calls to this function.

New command line options have been added:

  - Add -N | --pg_schema command line option to be able to override
    the PG_SCHEMA configuration directive. When this option is set
    at command line, EXPORT_SCHEMA is automatically activated.
  - Add --no_header option with equivalent NO_HEADER configuration
    directive to output the Ora2Pg header but just the translated
    code.

There is also some behavior changes from previous release:

  - Remove SysTimestamp() from the list of not translated function,
    it is replaced with CURRENT_TIMESTAMP for a long time now.
  - Change migration assessment cost to 84 units (1 day) for type
    TABLE, INDEX and SYNONYM and to 168 units (2 days) for TABLE
    PARTITION and GLOBAL TEMPORARY TABLE, this is more realistic.
  - Set minimum assessment unit to 1 when an object exists.
    Improve PL/SQL code translation speed.
  - Change behavior of COMPILE_SCHEMA directive used to force Oracle
    to compile schema before exporting code. When this directive is
    enabled and SCHEMA is set to a specific schema name, only invalid
    objects in this schema will be recompiled. When SCHEMA is not set
    then all schema will be recompiled. To force recompile invalid
    object in a specific schema, set COMPILE_SCHEMA to the schema name
    you want to recompile. This will ask to Oracle to validate the
    PL/SQL that could have been invalidate after a export/import for
    example. The 'VALID' or 'INVALID' status applies to functions,
    procedures, packages and user defined types.
  - Default transaction isolation level is now set to READ COMMITTED
    for all action excluding data export.
  - Oracle doesn't allow the use of lookahead expression but you may
    want to exclude some objects that match the ALLOW regexp you have
    defined. For example if you want to export all table starting
    with E but not those starting with EXP it is not possible to do
    that in a single expression.
    Now you can start a regular expression with the ! character to
    exclude all objects matching the regexp given just after. Our
    previous example can be written as follow:   ALLOW   E.* !EXP.*
    it will be translated into

         REGEXP_LIKE(..., '^E.*$') AND NOT REGEXP_LIKE(..., '^EXP.*$')

    in the object search expression.
  - Fix quoting of PG_SCHEMA with multiple schema in search path. The
    definition of the search path now follow the following behavior:
      * when PG_SCHEMA is define, always set search_path to its value.
      * when EXPORT_SCHEMA is enabled and SCHEMA is set, the search_path
        is set the name of the schema.
  - Remove forcing of export_schema when pg_schema is set at command
    line. This could change the behavior of some previous use of these
    variables and the resulting value of the search_path but it seems
    much better understandable.
  - Rewrite translation of raise_application_error to use RAISE
    EXCEPTION with a message and the SQLSTATE code. Oracle user
    defined code -20000 to -20999 are translated to PostgreSQL
    user define code from 45000 to 45999. Call to
      raise_application_error(mySQLCODE, myErrmsg);
    will be translated into
       RAISE EXCEPTION '%', myErrmsg USING ERRCODE = mySQLCODE;
  - Remove migration assessment cost for TG_OP and NOT_FOUND they
    might be fully covered now.

As usual there is also lot of bug fixes and Oracle to PostgreSQL
conversion adjustments, for a complete list see:
    https://github.com/darold/ora2pg/blob/master/changelog

===== Links & Credits =====

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

Useful Links:

    - Website: http://ora2pg.darold.net/
    - Download1: https://github.com/darold/ora2pg/releases/
    - Download2: 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, external table, etc.). Export of spatial data is also
supported with the PostGis extension.

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

Docs, Download & Support at http://ora2pg.darold.net/

--------------

--
Gilles Darold