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](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 ora...@darold.net.
Links:
- Website: [https://www.ora2pg.com/](https://www.ora2pg.com/)
- Download1:
[https://github.com/darold/ora2pg/releases](https://github.com/darold/ora2pg/releases)
- Download2:
[https://sourceforge.net/projects/ora2pg/](https://sourceforge.net/projects/ora2pg/)
- Development:
[https://github.com/darold/ora2pg](https://github.com/darold/ora2pg)
- Changelog:
[https://github.com/darold/ora2pg/blob/master/changelog](https://github.com/darold/ora2pg/blob/master/changelog)
- Documentation:
[https://www.ora2Pg.com/documentation.html](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/](http://www.ora2pg.com/)
