Ora2Pg v23.1 released

Antananarivo, Madagascar - February 10, 2022

## Ora2Pg

Version 23.1 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 four months and
adds some new major features and improvements.

* Add use of greatest() / least() functions from new version of Orafce when
required to return NULL on NULL input like Oracle.
* ALLOW and EXCLUDE configuration values can now be read from a file.
This is useful if you have a lot of table to filter.
* Add possibility to use of System Change Number (SCN) for data export or
data validation by providing a specific SCN. It can be set at command
line using the `-S` or `--scn` option. You can give a specific SCN or if you
want to use the current SCN at first connection time set the value to
'current'. Example of use:<br />
&nbsp;&nbsp;&nbsp;&nbsp;`ora2pg -c ora2pg.conf -t COPY --scn 16605281`
<br />
You can also use the `--scn` option to use the Oracle flashback capability by
specifying a timestamp expression instead of a SCN. For example:<br />
&nbsp;&nbsp;&nbsp;&nbsp;`ora2pg -c ora2pg.conf -t COPY --scn "TO_TIMESTAMP('2021-12-01 00:00:00', 'YYYY-MM-DD HH:MI:SS')"`
<br />
or for example to only retrieve yesterday's data:<br />
&nbsp;&nbsp;&nbsp;&nbsp;`ora2pg -c ora2pg.conf -t COPY --scn "SYSDATE - 1"`
<br />
* Add json output format to migration assessment.
* Add new TO_CHAR_NOTIMEZONE configuration directive to remove any timezone
information into the format part of the TO_CHAR() function. Disabled by default.
Note that the new default setting breaks backward compatibility, old behavior
was to always remove the timezone part.
* Add new configuration directive FORCE_IDENTITY_BIGINT. Usually identity
column must be bigint to correspond to an auto increment sequence so
Ora2Pg always force it to be a bigint. If, for any reason, you want
Ora2Pg to respect the DATA_TYPE that was set for identity column then
disable this directive.
* Allow to export only invalid objects when EXPORT_INVALID is set to 2
* Disable per partition data export when a WHERE clause is define on the
partitioned table or that a global WHERE clause is defined.

There is also two new options to command ora2pg.

* New command line option `--lo_import`. By default Ora2Pg imports Oracle
BLOB as bytea, the destination column is created using the bytea data
type. If you want to use large object instead of bytea, just add the
--blob_to_lo option to the ora2pg command. It will create the destination
column with data type Oid and will save the BLOB as a large object using
the lo_from_bytea() function. The Oid returned by the call to lo_from_bytea()
is inserted in the destination column instead of a bytea. This option can
only be used with actions SHOW_COLUMN, TABLE and INSERT, action COPY is not
supported.
If you want to use COPY or have huge size BLOB ( > 1GB ) than can not be
imported using lo_from_bytea() you can add option `--lo_import` to the
ora2pg command. This will allow to import data in two passes:

- 1) Export data using COPY or INSERT will set the Oid destination column
for BLOB to value 0 and save the BLOB value into a dedicated file. It
will also create a Shell script to import the BLOB files into the
database using psql command \lo_import and to update the table Oid
column to the returned large object Oid. The script is named
lo_import-TABLENAME.sh
- 2) Execute all scripts lo_import-TABLENAME.sh after setting the
environment variables PGDATABASE and optionally PGHOST, PGPORT, PGUSER,
etc. if they do not correspond to the default values for libpq.
You might also execute manually a VACUUM FULL on the table to remove
the bloat created by the table update.
<br />
<br />
Limitation: the table must have a primary key, it is used to set the
WHERE clause to update the Oid column after the large object import.
Importing BLOB using this second method (`--lo_import`) is very slow so it
should be reserved to rows where the BLOB > 1GB for all other rows use
the option --blob_to_lo. To filter the rows you can use the WHERE
configuration directive in ora2pg.conf.

* New command line option `--cdc_ready` to use current SCN per table when
exporting data and register them into a file named TABLES_SCN.log This
can be used for Change Data Capture (CDC) tools.

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 ora2pg(at)darold(dot)net(dot)

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