darold/ora2pg v23.1

Version 23.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.