Ora2Pg v23.1 released!, February 10 2022

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

  * Add use of greatest/least functions from new version of Orafce when
    required to return NULL on NULL input like Oracle.
  * ALLOW and EXCLUDE configuration values can now be read from a file.
    Use -a filename or -e filename to specify the list of tables that need
    to be filtered. This is useful if you have a lot of table to filter.
  * Add possibility to use of System Change Number (SCN) for data export or
    data validation by providing a specific SCN.  It can be set at command
    line using the -S or --scn option. You can give a specific SCN or if you
    want to use the current SCN at first connection time set the value to
    'current'. To use this last case the connection user must have the role
    looked at the v$database view.
    Example of use:
        ora2pg -c ora2pg.conf -t COPY --scn 16605281
    This adds the following clause to the query used to retrieve data for example:
        AS OF SCN 16605281
    You can also use th --scn option to use the Oracle flashback capability by
    specifying a timestamp expression instead of a SCN. For example:
        ora2pg -c ora2pg.conf -t COPY --scn "TO_TIMESTAMP('2021-12-01 00:00:00', 'YYYY-MM-DD HH:MI:SS')"
    This will add the following clause to the query used to retrieve data:
        AS OF TIMESTAMP TO_TIMESTAMP('2021-12-01 00:00:00', 'YYYY-MM-DD HH:MI:SS')
    or for example to only retrieve yesterday's data:
        ora2pg -c ora2pg.conf -t COPY --scn "SYSDATE - 1"
  * Add json output format to migration assessment. Thanks to Ted Yu for the patch.
  * Add new TO_CHAR_NOTIMEZONE configuration directive to remove any timezone
    information into the format part of the TO_CHAR() function. Disabled by default.
    Thanks to Eric Delanoe for the report.
    Note that the new default setting breaks backward compatibility, old behavior
    was to always remove the timezone part.
  * Add new configuration directive FORCE_IDENTITY_BIGINT. Usually identity
    column must be bigint to correspond to an auto increment sequence so
    Ora2Pg always force it to be a bigint. If, for any reason you want
    Ora2Pg to respect the DATA_TYPE you have set for identity column then
    disable this directive.
  * Add command line option --lo_import. By default Ora2Pg imports Oracle BLOB
    as bytea, the destination column is created
    using the bytea data type. If you want to use large object instead of bytea,
    just add the --blob_to_lo option to the ora2pg command. It will create the
    destination column as data type Oid and will save the BLOB as a large object
    using the lo_from_bytea() function. The Oid returned by the call to
    lo_from_bytea() is inserted in the destination column instead of a bytea.
    Because of the use of the function this option can only be used with actions
    SHOW_COLUMN, TABLE and INSERT. Action COPY is not allowed.
    If you want to use COPY or have huge size BLOB ( > 1GB) than can not be
    imported using lo_from_bytea() you can add option --lo_import to the
    ora2pg command. This will allow to import data in two passes:
    1) Export data using COPY or INSERT will set the Oid destination column
       for BLOB to value 0 and save the BLOB value into a dedicated file. It
       will also create a Shell script to import the BLOB files into the
       database using psql command \lo_import and to update the table Oid
       column to the returned large object Oid. The script is named
    2) Execute all scripts after setting the
       environment variables PGDATABASE and optionally PGHOST, PGPORT, PGUSER,
       etc. if they do not correspond to the default values for libpq.
       You might also execute manually a VACUUM FULL on the table to remove
       the bloat created by the table update.
    Limitation: the table must have a primary key, it is used to set the
    WHERE clause to update the Oid column after the large object import.
    Importing BLOB using this second method (--lo_import) is very slow so it
    should be reserved to rows where the BLOB > 1GB for all other rows use
    the option --blob_to_lo. To filter the rows you can use the WHERE
    configuration directive in ora2pg.conf.
  * Add command line option --cdc_ready to use current SCN per table when
    exporting data and register them into a file named TABLES_SCN.log This
    can be used for Change Data Capture (CDC) tools.
  * Allow to export only invalid objects when EXPORT_INVALID is set to 2
  * Disable per partition data export when a WHERE clause is define on the
    partitioned table or that a global WHERE clause is defined.

Backward compatibility:

Ora2Pg used to removr any timezone information from the TO_CHAR() format function. To recover this behavior set TO_CHAR_NOTIMEZONE to 1 in ora2pg.conf

For a complete list of change see

Ora2Pg v23.0 released!, November 15 2021

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

  * Add new option --blob_to_lo that can be used to export BLOB as large
    objects. It can only be used with action SHOW_COLUMN, TABLE and INSERT.
    When used with TABLE action, the BLOB column will be translated into oid
    PostgreSQL data type. When used with the INSERT export action BLOB data
    will be store as large object in the pg_largeobjects table and the oid
    referencing this large object will be stored in the main table instead
    of a bytea.
    It is not possible to use oid with COPY because this feature use function
    lo_from_bytea() that stores the large object in the external table and
    returns the oid.
    This feature works with or without the use of oracle_fdw to import the
    data and option -J can be used to improve the speed of the INSERT import
    provide that there is a numeric unique key on the table.
    Thanks to rodiq for the feature request.
  * Add command line option -W | --where clause to set the WHERE clauses to
    apply to the Oracle query to retrieve data. It can be used multiple time.
    It will override the WHERE configuration directive if there is a global
    WHERE clause or the same table WHERE clause definition. Otherwise the
    clause will be appended.
  * Add data validation feature consisting in comparing data retrieved from a
    foreign table pointing to the source Oracle table and a local PostgreSQL
    table resulting from the data export. By default Ora2Pg will extract 10000
    rows from both side, you can change this value using DATA_VALIDATION_ROWS.
    When it is set to zero all rows of the tables will be compared.
    Data validation requires that the table has a primary key or unique index
    and that the key columns is not a LOB.
    Due to differences in sort behavior between Oracle and PostgreSQL, if the
    collation of unique key columns in PostgreSQL is not 'C', the sort order of
    is different compared to Oracle. In this case the data validation will fail.
    Ora2Pg will stop comparing two tables after 10 errors, result is dumped to
    an output file named data_validation.log.
  * Add DATA_VALIDATION_ORDERING configuration directive enabled by default.
    Order of rows between both sides are different once the data have been
    modified. In this case data must be ordered using a primary key or a
    unique index, that mean that a table without such object can not be
    compared. If the validation is done just after data import in mode single
    process and without any data modification the validation can be done on all
    tables without any ordering.
  * Add DATA_VALIDATION_ERROR to stop validating data from a table after a
    certain amount of row mismatch. Default is to stop after 10 rows
    validation errors.
  * Allow multiprocess for TEST_DATA action to validate data import. Use -P
    or PARALLEL_TABLES to set the number of parallel tables checked. Output
    is now done to a file named data_validation.log saved in the current
  * Add replacement of UTL_RAW.CAST_TO_RAW with encode().
  * Add rewrite of XMLTYPE() with xmlparse(DOCUMENT convert_from(..., 'utf-8')).
  * Add VARCHAR_TO_TEXT configuration directive. By default VARCHAR2 without
    size constraint are tranlated into text PG data type. If you want to use
    varchar instead, disable this directive.
  * Add detection of XML function for migration assessment cost.
  * Add DBMS_RANDOM to the list of Oraclism handled by Orafce.
  * Add support to mysql_fdw foreign data wrapper to export data
    PostgreSQL tables. Thanks to Yoni Sade for the feature request.
  * Allow to transform all NUMBER(*,scale) to an other data type by a
    redefinition like NUMBER(*\,2):decimal in the DATA_TYPE configuration
    directive. Thanks to Florent Jardin for the patch.
  * Add information on how to use SSL encrypted connection to documentation.
  * Add TEST_COUNT action to just report the row count diff between Oracle and

Backward compatibility changes:

  - Add FORCE_PLSQL_ENCODING configuration directive. In previous version Ora2Pg
    was encoding all functions code to ut8, this is no more the case because it
    could result in double encoding. To recover the old behavior (not recommanded)
    enable this directive. Thanks to rynerisraid and lee-jongbeom for the report.
  - Change behavior regarding RAW columns. Now RAW(16) and RAW(32) columns or
    RAW columns with "SYS_GUID()" as default value are now automatically
    translated into uuid. Data will be automatically migrated as PostgreSQL
    uuid data type provided by the "uuid-ossp" extension. To recover the old
    behavior to export data as bytea whatever is the precision, the following
    must be set with DATA_TYPE configuration:  RAW(16):bytea,RAW(32):bytea

For a complete list of change see

Ora2Pg v22.1 released!, July 2nd 2021

This is a maintenance release to extend the feature of data export through the oracle_fdw PostgreSQL extension to migration that use the public schema and do not preserve case.

There is also some other fixes:

  - Fix compile_schema() call that breaks valid function based indexes by
    adding compile_all => FALSE to DBMS_UTILITY.compile_schema().
    Thanks to Pawel Fengler for the patch.
  - Force foreign table for data export as readonly to avoid accidental
    write if import schema is not cleaned.
  - Fix data export to file not possible since last changes for oracle_fdw
    export. Thanks to Niels Jespersen for the report.

Ora2Pg v22.0 released!, June 28th 2021

This release fix several issues reported since past three months and adds some new features and improvements. I must thanks MigOps Inc who hire me to drive Oracle to PostgreSQL migrations and to develop Ora2Pg. It's been a long time that I was looking for such a company and it is an amazing gift for the 20 years of Ora2Pg. All improvements and new new features developed during my work at MigOps will be available in the public GitHub repository, here are the new ones.

  - Add export of data using oracle_fdw when FDW_SERVER is set and export
    type is COPY or INSERT. Multi-process using -P or -J is fully supported
    but option -j is useless in this case. Boolean transformation of some
    columns or data type is also supported. Actually, expect that it works
    just like data migration without oracle_fdw. This can improve the data
    migration speed from 30 to 40% especially for BLOB export.

  - Improve export performances with huge number of objects by avoiding join
    between Oracle catalog tables.

  - Set a maximum of assessment score for tables, indexes, sequences,
    partitions, global temporary table and synonym following the number of

  - Add detection of XML functions to the assessment cost.

  - Allow to change the assessment cost unit value in the script
    when ora2pg is used with options --init_project and --cost_unit_value.

  - Remove pragma restrict_references from P/PSQL code, it is useless.

  - Add the oracle schema to search_path in SQL files generated and improve
    the migration assessment when USE_ORAFCE is enabled.

  - Apply ALLOW and EXCLUDED filtered stored procedures at package extraction
    level. Previous this patch there was no way to not export some package
    functions or to exclude them from assessment.

  - Add new tests to check sequences last values and number of identity columns
    in both side.

  - Apply ALLOW/EXCLUDE without object to table object by default in TEST

New configuration directives:

  - Add ORACLE_FDW_TRANSFORM configuration directive to apply a transformation
    to a column when exporting data. Value must be a semicolon separated list of
        TABLE[COLUMN_NAME, >replace code in SELECT target list<]
    For example to replace string 'Oracle' by 'PostgreSQL' in a varchar2 column
    use the following.
    Thanks to MigOps for the patch.

  - Add DROP_IF_EXISTS configuration directive to add a statement
    "DROP >OBJECT< IF EXISTS" before creating the object. Can be
    useful in an iterative work. Default is disabled. Thanks to
    dherzhau for the feature request.

Backward compatibility:

There is a backward compatibility issue with old configuration files where FDW_SERVER is set by default. This directive was not used when exporting data, this is not the case anymore as it instruct Ora2Pg to use the given foreign server to use oracle_fdw to migrate the data.

For a complete list of change see

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