Skip to content

Version 23.1
Compare
Choose a tag to compare
@darold darold released this 10 Feb 19:09
· 552 commits to master since this release
v23.1
fa74058

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.
Assets 2
Loading