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](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 [email protected].
Links:
- Website: [https://www.ora2pg.com/](https://www.ora2pg.com/)
- Download:
[https://github.com/darold/ora2pg/releases](https://github.com/darold/ora2pg/releases)
- 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://github.com/darold/ora2pg/blob/master/README](https://github.com/darold/ora2pg/blob/master/README)
--------------
**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/](http://www.ora2pg.com/)
