Grenoble, France - November 15, 2021

Ora2Pg 23.0 released

Version 23.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 since last release and adds several new features and improvements.

New features and improvements:

• 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.
• Add replacement of UTL_RAW.CAST_TO_RAW with encode().
• Add rewrite of XMLTYPE() with xmlparse(DOCUMENT convert_from(..., 'utf-8')).
• 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.
• Allow to transform all NUMBER(,scale) to an other data type by a redefinition like NUMBER(\,2):decimal in the DATA_TYPE configuration directive.
• 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 PostgreSQL tables.
• Allow multiprocess for TEST_DATA action to validate data migration. Use -P or PARALLEL_TABLES to set the number of parallel tables checked.

New command line options:

• Add new option --blob_to_lo that can be used to export BLOB as large objects. 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 stored as large objects in the pg_largeobjects table and the oid referencing this large object will be stored in the main table instead of a bytea.
• 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.

New configuration directives:

• 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.
• 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.

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.
• Change behavior regarding RAW columns. Now RAW(16) and RAW(32) columns or a 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.

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.

Links & Credits

I would like to thank all users who submitted patches and users who reported bugs and feature requests, they are all cited the ChangeLog file.

Ora2Pg is an open project. Any contribution to build a better tool is welcome. You just have to send your ideas, features requests or patches using the GitHub tools or directly to ora2pg@darold.net.

Links:

• Website: https://www.ora2pg.com/
• Download1: https://github.com/darold/ora2pg/releases
• Download2: https://sourceforge.net/projects/ora2pg/
• Development: https://github.com/darold/ora2pg
• Changelog: https://github.com/darold/ora2pg/blob/master/changelog
• Documentation: https://www.ora2Pg.com/documentation.html

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 and data into PostgreSQL compatible code.

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

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