November 14, 2024: PostgreSQL 17.1, 16.5, 15.9, 14.14, 13.17, and 12.21 Released!

Ora2Pg 24.0 have been released

Posted on 2023-07-12 by Gilles Darold
Related Open Source

Montreal, Canada - July 5th, 2023

Ora2Pg

Version 24.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 major release adds official support to migration of SQL Server database to PostgreSQL. It also fixes several issues reported since past height months and adds some new features and improvements.

  • Add SQL Server migration to Ora2Pg. Most of the SQL Server objects are supported as well as data export. Translation of the TSQL stored procedures to plpgsql is complicated because of the lack of statement separator in TSQL but as usual Ora2Pg is doing is best to do as much work as possible. Migration assessment is also possible with SQL Server database. There is some dedicated configuration directives added to ora2pg.conf.
  • Enable the use of ALLOW/EXCLUDE directive with SHOW_* reports and throw a fatal error if global filters in ALLOW/EXCLUDE are set.
  • Add replacement of DBMS_LOCK.SLEEP with pg_sleep.
  • Split estimate cost details per function/procedure/and package function.
  • Add cmin, cmax, ctid to reserved keywords list.
  • Add cost for presence of ADD CONSTRAINT in PLSQL code. It needs constraint name stability.
  • Allow COPY and TABLE export type to use the NULLIF construct.
  • Add new SEQUENCE_VALUES export type to export DDL to set the last values of sequences from current Oracle database last values like the following statements: ALTER SEQUENCE departments_seq START WITH 290;
  • Add replacement of Oracle variable : varname into PG :'varname'.
  • Add support to MySQL PARTITION BY KEY() with a translation to HASH partitioned table using the PK/UK definition of the table or the columns specified in the KEY() clause.
  • Make EXPORT_INVALID configuration directive works with TRIGGER export. Until now disabled triggers were not exported, setting EXPORT_INVALID to 1 will force the export of disabled triggers.
  • Add support of MySQL generated default value on update. For example: CREATE TABLE t1 ( dt DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP ); Ora2Pg will translate this syntax into a trigger on the table to force the value of the column on an update event.
  • Add translation of ST_GEOMETRY data type to PostGis geometry datatype.
  • Replace ROWNUM in target list with a row_number() over () clause.

New configuration directives:

  • Add CLOB_AS_BLOB configuration directive to treat CLOB as BLOB when exporting data. When enabled Ora2Pg will apply same behavior on CLOB than BLOB with BLOB_LIMIT setting. This could be useful if you have large CLOB data. Enabled by default.
  • Add configuration directive ST_GEOMETRYTYPE_FUNCTION to be able to set the function to use to extract the geometry type from a ST_Geometry column. Default: ST_GeometryType, example it should be set to sde.ST_GeometryType for ArcSDE.
  • Add four new configuration directives to be able to change or prefix the functions used to extract information from ST_Geometry object and values.
    • ST_SRID_FUNCTION: Oracle function to use to extract the srid from ST_Geometry meta information. Default: ST_SRID, for example it should be set to sde.st_srid for ArcSDE.
    • ST_DIMENSION_FUNCTION: Oracle function to use to extract the dimension from ST_Geometry meta information. Default: ST_DIMENSION, for example it should be set to sde.st_dimention for ArcSDE.
    • ST_ASBINARY_FUNCTION: Oracle function to used to convert an ST_Geometry value into WKB format. Default: ST_ASBINARY, for example it should be set to sde.st_asbinary for ArcSDE.
    • ST_ASTEXT_FUNCTION: Oracle function to used to convert an ST_Geometry value into WKT format. Default: ST_ASTEXT, for example it should be set to sde.st_astext for ArcSDE.
  • Add INSERT_ON_CONFLICT configuration directive. When enabled this instruct Ora2Pg to add an ON CONFLICT DO NOTHING clause to all INSERT statements generated for this type of data export.

Backward compatibility:

  • Change the behavior of CASE_INSENSITIVE_SEARCH to allow the use of a collation instead of the citext extension. To disable the feature the value none can be used. If the migration is not MSSQL this feature is disabled.
  • Remove PREFIX_PARTITION configuration directive, it is now replaced by the RENAME_PARTITION directive. Previous behavior was to construct the partition name from the table name, the partition name and the sub partition name if any. The problem is that we often reach the max length for an object name and this leads to duplicate partition name. Now, when RENAME_PARTITION is enabled the partition tables will be renamed following rules: <tablename>_part<pos> where "pos" is the partition number. For subpartition this is: <tablename>_part<pos>_subpart<pos> If this is partition/subpartition default: <tablename>_part_default <tablename>_part<pos>_subpart_default This change will break backward compatibility, if PREFIX_PARTITION is still set, it will simply enable RENAME_PARTITION.
  • Set START value to MINVALUE when a sequence is cycled and that the START value is upper that MAXVALUE.

For a complete list of change see https://github.com/darold/ora2pg/blob/master/changelog

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:

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 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/