Ora2Pg 23.2 released

Antananarivo, Madagascar - October 8, 2022

Ora2Pg 23.2 released

Version 23.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 since past height months and adds some new features and improvements.

• Add export of MySQL KEY and LINEAR KEY partitioning, translated as HASH partitioning.
• Allow export of object with dollar sign is his name.
• Add export of CHECK constraints for MySQL >= 8.0.
• Add Functional/Expression indexes export from MYSQL.
• Add export of MySQL virtual column.
• Remove scale and precision of a numeric if the scale is higher than the precision. PostgreSQL does not support decimal/numeric attributes where the scale is explicitly higher than the precision.
• Add command line option --drop_if_exists to add statement to drop objects before creation if it exists. It corresponds to the DROP_IF_EXISTS configuration directive.
• Add option -C | --cdc_file to be able to change the name of the default file used to store/read SCN per table during export. Default is TABLES_SCN.log in the current directory. This is the file written by the --cdc_ready option.
• Add multi-process to count rows in PostgreSQL tables (TEST_COUNT) using -P command line option.
• Add support to PostgreSQL 14 procedure with out parameters.
• Set default PostgreSQL database target version to 14.

New configuration directives:

• Add configuration directive MVIEW_AS_TABLE and command line option --mview_as_table to set which materialized view to export as table. By default none. Value must be a list of materialized view name or regexp separated by space or comma. If the object name is a materialized view and the export type is TABLE, the view will be exported as a create table statement. If export type is COPY or INSERT, the corresponding data will be exported.
• Add configuration variable FDW_IMPORT_SCHEMA to rename the schema where foreign tables for data migration will be created. If you use several instances of ora2pg for data migration through the foreign data wrapper, you might need to change the name of the schema for each instance. Default: ora2pg_fdw_import
• Add TRANSFORM_VALUE configuration directive to apply an expression when retrieving data from Oracle. For example: TRANSFORM_VALUE CLOB_TABLE[CHARDATA:translate("CHARDATA", chr(0), ' ')] to replace all Oracle char(0) in a string by a space character.
• Add EXCLUDE_COLUMNS configuration directive. Unlike MODIFY_STRUCT that is used to redefine a table structure, this directive allow to specify a list of columns per table that must be excluded from the export. For example: EXCLUDE_COLUMNS T1(nocol1,nocol2) T2(nocol1,nocol2)
• Add new configuration directive EXPORT_GTT to export Oracle Global Temporary Table using syntax recognized by the pgtt extension. For more information see https://github.com/darold/pgtt Default is to not export global temporary table as they are not supported natively by PostgreSQL.
• Add new configuration option NO_EXCLUDED_TABLE. By default Ora2Pg exclude from export some Oracle "garbage" tables that should never be part of an export. This behavior generates a lot of REGEXP_LIKE expressions which are slowing down the export when looking at tables. To disable this behavior enable this directive, you will have to exclude or clean up later by yourself the unwanted tables. The regexp used to exclude the table are defined in the array @EXCLUDED_TABLES in lib/Ora2Pg.pm. Note this is behavior is independent to the EXCLUDE configuration directive.

Backward compatibility:

• Force rewrite of all invalid date starting with zero year 0000 into 1970-01-01 when it is a default value and NULL for data. Old behavior was to only replace 0000-00-00 date.
• Until now there was a lot of untranslated call to TRUNC(date) because Ora2Pg is unable to detect that the parameter is a date or a number. The problem is that Oracle has TRUNC(number) too and Ora2Pg try to not apply the transformation if there is a doubt. In most of the migration have met very few TRUNC(number) so now all call to TRUNC() will be converted to date_trunc(). There must be false positive rewrite but this should be far less work than the actual situation.

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/