Ora2Pg v21.1 released!, April 1st 2021

This release fix several issues reported since past six months and adds some new features and improvements.

  * Now that Orafce 3.15.0 has a definition for the REGEXP_* function,
    makes the translation optional to USE_ORAFCE directive.
  * Add set application name in connection to Oracle/MySql/PostgreSQL.
  * Add translation of REGEXP_COUNT() and change assessment cost.
  * Rewrite the way REGEXP_LIKE() is translated into regexp_match to
    support modifiers. This rewrite also fix default behavior between
    Oracle and PostgreSQL.
  * Replace DBMS_LOB.GETLENGTH() by PostgreSQL octet_length() function.
  * Add types correspondences for VARCHAR2 and NVARCHAR2 in DATA_TYPE
    configuration directive.
  * Add autodetection and support of geometry type, srid and dimension
    for ArcGis geometries.
  * Add conversion of default value in function parameters.
  * Add -u | --unit option to ora2pg_scanner to be able to set the
    migration cost unit value globally.
  * Replace DBMS_LOB.SUBSTR() by SUBSTR()
  * Remove TO_CLOB() it is useless, manual cast could be necessary.
  * Replace IS JSON validation clause in CHECK constraints by
        (CASE WHEN $1::json IS NULL THEN true ELSE true END)
    When the code is invalid an error is fired.
  * DISTINCT and UNIQUE are synonym on Oracle.

Backward compatibility changes:

  - Force a column to be bigint if this is an identity column. Thanks
    to for the patch.
  - Fix EMPTY_LOB_NULL, enable/disable was inverted, keeping default
    to enabled. Take care that in old ora2pg.conf it is disabled so it
    will break backward compatibility with old configuration.
    with ENABLE_BLOB_EXPORT to avoid confusion with double negative
    variable. Backward compatibility is preserved with a warning. 
  - SRID for SDO_GEOMETRY export is now taken from the value not forced
    from the metadata table. 

For a complete list of change see

Ora2Pg v21.0 released!, October 12th 2020

This release fix several issues reported since last release and adds several new features and improvements.

New features, options and configuration directives in this release:

  * Add clause OVERRIDING SYSTEM VALUE to INSERT statements when the
    table has an IDENTITY column.
  * Considerably increase the speed to generate the report about the
    migration assessment, especially for database with huge number of
  * Reduce time passed in the progress bar. Following the number of
    database objects we were spending too much time in refreshing the
    progress bar.
  * Add number of identity columns in migration assessment report.
  * Make assessment details report initially hidden using HTML5 tags
* Improve speed of BLOB/CLOB data export. Oracle recommends reading from and writing to a LOB in batches using a multiple of the LOB chunk size. This chunk size defaults to 8k (8192). Recent tests show that the best performances can be reach with higher value like 512K or 4Mb. * Add progress bar when --oracle_speed is use in single process mode. * Automatically activate USER_GRANTS when the connection user has no DBA privilege. A warning is displayed. * Complete port to Windows by using the Windows separator on stdout redirection into a file at ora2pg command line call and improve ora2pg_scanner port on Windows OS. * Add rewrite of MySQL JOIN with WHERE clause instead of ON. * Add MGDSYS (Oracle E-Business Suite) and APEX_040000 to the list of schemas excluded from the export. * Supply credentials interactively when a password is not defined in the configuration file. Need the installation of a new Perl module Term::ReadKey. * Add supports oracle connections "as sysdba" with username "/" and an empty password to connect to a local oracle instance. * Add translation of PRIVATE TEMPORARY TABLE from Oracle 18c into PostgreSQL basic temporary table, only the default behavior for on commit change.

New command line options:

  * Add new command line option to ora2pg_scanner: -b | --binpath DIR
    to set the full path to directory where the ora2pg binary stays.
    Might be useful only on Windows OS.
  * Add -r | --relative command line option and PSQL_RELATIVE_PATH
    configuration directive. By default Ora2Pg use \i psql command to
    execute generated SQL files if you want to use a relative path
    following the script execution file enabling this option will use
    \ir. See psql help for more information.

New configuration directives:

      By default Ora2Pg try to order views to avoid error at import time
      with nested views. With a huge number of views this can take a very
      long time, you can bypass this ordering by enabling this directive.
      Force Ora2Pg to not look for function declaration. Note that this
      will prevent Ora2Pg to rewrite function replacement call if needed.
      Do not enable it unless looking forward at function breaks other
      See explanation in the new features and improvement list.
      To support the Alternative Quoting Mechanism ('Q' or 'q') for String
      Literals set the regexp with the text capture to use to extract the
      text part. For example with a variable declared as
            c_sample VARCHAR2(100 CHAR) := q'{This doesn't work.}';
      the regexp to use must be:
            ALTERNATIVE_QUOTING_REGEXP     q'{(.*)}'
      ora2pg will use the $$ delimiter, with the example the result will
            c_sample varchar(100) := $$This doesn't work.$$;
      The value of this configuration directive can be a list of regexp
      separated by a semi colon. The capture part (between parenthesis) is
      mandatory in each regexp if you want to restore the string constant.

Backward compatibility changes:

  - Default for NO_LOB_LOCATOR is now 1 to benefit from the LOB_CHUNK_SIZE
    performances gain.
  - Enable schema compilation (COMPILE_SCHEMA set to 1) by default to
    speed up DDL extraction.
  - Change the behavior of Ora2Pg with the parameters that follows a
    parameter with a default value. Ora2Pg used to change the order of the
    parameter's function to put all parameters with a default value at end
    of the list which need a function call rewrite. This have been abandoned
    now any parameter without default value after a parameter with a default
    value will be appended DEFAULT NULL.

For a complete list of change see

Ora2Pg v20.0 released!, January 18th 2019

This release fix several issues reported during the last three months and adds several new features and improvement. The change of major version is related to backward compatibility break with the removed of most PG_SUPPORTS_* configuration directives and their replacement with the new PG_VERSION directive.

The release adds some new features and configuration directives:

  * Add PG_VERSION configuration directive to set the PostgreSQL major
    version number of the target database. Ex: 9.6 or 10. Default is
    current major version at time of a new release. This replace the
    old PG_SUPPORTS_* configuration directives.
  * Removed all PG_SUPPORTS_* configuration directives minus
    PG_SUPPORTS_SUBSTR that is related to Redshift engine.
  * Export of BFILE as bytea is now done through a PL/SQL function to
    extract the content of a BFILE and generate a bytea data suitable
    for insert or copy into PostgreSQL.
  * Foreign keys that reference a partitioned table are no more
  * Show table name on Oracle side during export using at connection
  * When the date format is ISO and the value is a constant the call
    to to_date() is removed and only the constant is preserved. For
    example: to_date(' 2013-04-01 00:00:00','SYYYY-MM-DD HH24:MI:SS')
    is replaced by a simple call to: ' 2013-04-01 00:00:00'.
    This rewrite is limited to PARTITION export type when directive
  * Add DATA_EXPORT_ORDER configuration directive. By default data
    export order will be done by sorting on table name. If you have
    huge tables at end of alphabetic order and are using multiprocess,
    it can be better to set the sort order on size so that multiple
    small tables can be processed before the largest tables finish.
    In this case set this directive to size. Possible values are name
    and size. Note that export type SHOW_TABLE and SHOW_COLUMN will
    use this sort order too, not only COPY or INSERT export type.
  * Add NO_BLOB_EXPORT configuration directive. Exporting BLOB could
    take time and you may want to export all data except the BLOB
    columns. In this case enable this directive and the BLOB columns
    will not be included into data export. The BLOB column must not
    have a NOT NULL constraint. Thanks to Ilya Vladimirovich for the
  * Add PREFIX_SUB_PARTITION to enable/disable sub-partitioning table
    prefixing in case of the partition names are a part of the sub-
    partition names.
  * Add special replacement for case of epoch syntax in Oracle:
        (sysdate - to_date('01-Jan-1970', 'dd-Mon-yyyy'))*24*60*60
    is replaced by the PostgreSQL equivalent:
        (extract(epoch from now()))

For a complete list of change see