darold/ora2pg v23.1 on GitHub

2022 02 10 - v23.1

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.
Use -a filename or -e filename to specify the list of tables that need
to be filtered. 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'. To use this last case the connection user must have the role
"SELECT ANY DICTIONARY" or "SELECT_CATALOG_ROLE", the current SCN is
looked at the v$database view.
Example of use:
ora2pg -c ora2pg.conf -t COPY --scn 16605281
This adds the following clause to the query used to retrieve data for example:
AS OF SCN 16605281
You can also use th --scn option to use the Oracle flashback capability by
specifying a timestamp expression instead of a SCN. For example:
ora2pg -c ora2pg.conf -t COPY --scn "TO_TIMESTAMP('2021-12-01 00:00:00', 'YYYY-MM-DD HH:MI:SS')"
This will add the following clause to the query used to retrieve data:
AS OF TIMESTAMP TO_TIMESTAMP('2021-12-01 00:00:00', 'YYYY-MM-DD HH:MI:SS')
or for example to only retrieve yesterday's data:
ora2pg -c ora2pg.conf -t COPY --scn "SYSDATE - 1"
Add json output format to migration assessment. Thanks to Ted Yu for the patch.
Add new TO_CHAR_NOTIMEZONE configuration directive to remove any timezone
information into the format part of the TO_CHAR() function. Disabled by default.
Thanks to Eric Delanoe for the report.
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 you have set for identity column then
disable this directive.
Add 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 as 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.
Because of the use of the function this option can only be used with actions
SHOW_COLUMN, TABLE and INSERT. Action COPY is not allowed.
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.
  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.
Add 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.
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.

Backward compatibility:

Ora2Pg used to removr any timezone information from the TO_CHAR() format
function. To recover this behavior set TO_CHAR_NOTIMEZONE to 1 in ora2pg.conf

Complete list of changes:

Replace PERFORM by CALL when the stored procedure is a procedure. Thanks
to Rui Pereira for the report.
Fix open cursor translation when using is in the query but not as keyword.
Thanks to taptarap for the report.
Fix replacement of global variables in DECLARE section. Thanks to taptarap
for the report.
Fix missing suffix in function name with autonomous transaction when export
schema was enabled and fix revoke and owner to wrapper function. Thanks to
Sergey Grinko for the report.
Fix export of type declaration in packages without body. Thanks to Sergey
Grinko.
Fix column name duplicates when exporting data of partition. Thanks to
Sergey Grinko for the report.
Fix BLOB export with INSERT mode, call decode() was missing.
Fix applying of DEFAULT_PARALLELISM_DEGREE hint that was not working
anymore for a long time. Thanks to Marcel Pils for the patch.
Update documentation about PARALLEL_TABLES and view export. Thanks to
xinferum for the report.
Fix unwanted quote escaping in global variable constant. Thanks to
sergey grinko for the report.
Fix export of global variable when there is function in the default value.
Fix end of statements in last merged PR.
Add json output format to migration assessment. Thanks to Ted Yu for
the patch.
Fix parsing of package when a comment follow the AS keyword. Thanks to
Eric Delanoe for the report.
Adapt MAXVALUE for identity columns if the datatype has been changed to
integer.
Fix a regression on data validation introduced with commit to fix data
export of virtual column.
Fix Can't locate object method is_pk via package Ora2Pg error
Exclude unique keys using expression to validate data.
Fix ORDER BY clause for data validation.
Fix error on open pragma when encoding is not set.
Fix a regression in data export of virtual column. Thanks to Code-UV and
IgorM12 for the report.
Fix a second regression with empty column name in target list to
retrieve data.
Fix PG version to enable virtual column.
Fix binmode when it is set to raw or locale to not call encoding() in
open pragma.
Fix regression in export view as table. Thanks to Sebastian Albert for
the report.
Update Copyright year.
Quote tables names when necessary during TEST action.
Fix undefined call to auto_set_encoding().
Add test count of column per table and add output of the PG table struct
modified to be used with MODIFY_STRUCT.
Fix handling of PRESERVE_CASE with update au sequences values
Fix handling of PRESERVE_CASE with TEST_DATA
Fix unwanted replacement of sysdate operation to epoch. Thanks to taptarap
for the report.
Remove extra END clause at end of package function when a space or a
comment was present. Thanks to taptarap for the report.
Fix missing import of module Encode. Thanks to Menelaos Perdikeas for
the report.
Fix case where data type defined in function was not exported when
EXPORT_SCHEMA was enabled. Thanks to Eric Bourlon for the report.
Fix missing EXECUTE on OPEN CURSOR statements. Thanks to taptarap for
the report.
Fix missing declaration of min() function in Oracle.pm. Thanks to
nicscanna for the report.
Fix SYSDATE subtract of seconds instead of days
Fix PERFORM replacement in CTE. Thanks to taptarap for the report.
Fix wrong stored procedure code conversion when use types named with
"default" and broken decode to case translation. Thanks to taptarap
for the report.
Add missing import of FTS indexes in script import_all.sh. Thanks to
vijaynsheth for the report.
Fix another procedure parsing with return. Thanks to Eric Bourlon for
the report.
Fix case where parenthesis are not added to index creation.
Add creation of the uuid extension when it is used.
Add HTML report of tables and columns with name > 63 characters.
Add report of DBMS_ERROR and Quartz Scheduler tables found.
Add mark (date?) on columns of DATE data type in Oracle to check if
it should be translated into date instead of default timestamp.
SHOW_COLUMN: mark column data type with (numeric?) when it is a NUMBER
without precision.
SHOW_TABLE+SHOW_COLUMN: Add mark of tables and columns name > 63
characters
Fix translation of TYPE ... AS TABLE OF ...
Fix parsing of function call in check constraints. Thanks to Menelaos
Perdikeas for the report.
Fix missing data export file for partitioned tables when TRUNCATE_TABLE
was disabled. Thanks to Menelaos Perdikeas for the report.
Fix named parameter inserted in procedure call with inout parameters.
Thanks to Rui Pereira for the report.
Fix unwanted quoting of index columns clause when there is an operation.
Thanks to Menelaos Perdikeas for the report.
Move comment in procedure parameters before the BEGIN. Thanks to Eric
Bourlon for the report.
Fix parsing of FOR CUSOR followed by a parenthesis. Thanks to Eric Bourlon
for the report.
Fix parsing of TYPE ... IS REF CUSOR declaration in procedures. Thanks to
Eric Bourlon for the report.
Add replacement of SDO_CS.TRANSFORM into ST_Transform. Thanks to mukesh3388
for the report.
Add missing table namer to index renaming.
Create a function for index renaming for code reuse.
Fix support translation of type VARRAY from store procedure. Thanks to
Eric Bourlon for the report.
Fix conversion of SQL%ROWCOUNT when part of a string concatenation. Thanks
to boubou191911 for the report.
Remove other non alphanumeric character from index name. Thanks to Menelaos
Perdikeas for the report.
Fix date formatting when error is logged with INSERT failure. Thanks to
xinjirufen for the report.
Remove possible comma from index renaming.
Fix drop of indexes with renaming when there is a function call. Thanks to
Menelaos Perdikeas for the report.
Fix empty geometry type since the move of ORA2PG_SDO_GTYPE into
lib/Ora2Pg/Oracle.pm
Move most of the Oracle specific code to a dedicated Perl library
lib/Ora2Pg/Oracle.pm with the same functions as lib/Ora2Pg/MySQL.pm
This will help to maintain and extend Ora2Pg to other RDMS. There is
still Oracle database related specific code in the main library but
it will be also moved later. There should not be any regression or
usage change with this huge patch.
Fix translation of type with not null clause. Thanks to Yasir1811 for
the report.

darold/ora2pg v23.1 Version 23.1 on GitHub

darold/ora2pg v23.1
Version 23.1

on GitHub