Montreal, Canada - July 5th, 2023
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.
DBMS_LOCK.SLEEP with pg_sleep.
ADD CONSTRAINT in PLSQL code. It needs constraint
TABLE export type to use the
SEQUENCE_VALUES export type to export DDL to set the last values
of sequences from current Oracle database last values like the following
ALTER SEQUENCE departments_seq START WITH 290;
: varname into PG
PARTITION BY KEY() with a translation to HASH
partitioned table using the PK/UK definition of the table or the
columns specified in the
EXPORT_INVALID configuration directive works with TRIGGER export.
Until now disabled triggers were not exported, setting
to 1 will force the export of disabled triggers.
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.
row_number() over () clause.
New configuration directives:
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.
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
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.
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.
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
PREFIX_PARTITION configuration directive, it is now replaced by
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
where "pos" is the partition number. For subpartition this is:
If this is partition/subpartition default:
This change will break backward compatibility, if
still set, it will simply enable
START value to
MINVALUE when a sequence is cycled and that the
value is upper that
For a complete list of change see https://github.com/darold/ora2pg/blob/master/changelog
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 firstname.lastname@example.org.
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/