Skip to content

Version 23.0
Compare
Choose a tag to compare
@darold darold released this 15 Nov 17:41
· 643 commits to master since this release
v23.0
64f3425

2021 11 15 - v23.0

This release fix several issues reported since past five months and
adds some new major features and improvements.

  • Add new option --blob_to_lo that can be used to export BLOB as large
    objects. It can only be used with action SHOW_COLUMN, TABLE and INSERT.
    When used with TABLE action, the BLOB column will be translated into oid
    PostgreSQL data type. When used with the INSERT export action BLOB data
    will be store as large object in the pg_largeobjects table and the oid
    referencing this large object will be stored in the main table instead
    of a bytea.
    It is not possible to use oid with COPY because this feature use function
    lo_from_bytea() that stores the large object in the external table and
    returns the oid.
    This feature works with or without the use of oracle_fdw to import the
    data and option -J can be used to improve the speed of the INSERT import
    provide that there is a numeric unique key on the table.
    Thanks to rodiq for the feature request.
  • Add command line option -W | --where clause to set the WHERE clauses to
    apply to the Oracle query to retrieve data. It can be used multiple time.
    It will override the WHERE configuration directive if there is a global
    WHERE clause or the same table WHERE clause definition. Otherwise the
    clause will be appended.
  • Add data validation feature consisting in comparing data retrieved from a
    foreign table pointing to the source Oracle table and a local PostgreSQL
    table resulting from the data export. By default Ora2Pg will extract 10000
    rows from both side, you can change this value using DATA_VALIDATION_ROWS.
    When it is set to zero all rows of the tables will be compared.
    Data validation requires that the table has a primary key or unique index
    and that the key columns is not a LOB.
    Due to differences in sort behavior between Oracle and PostgreSQL, if the
    collation of unique key columns in PostgreSQL is not 'C', the sort order of
    is different compared to Oracle. In this case the data validation will fail.
    Ora2Pg will stop comparing two tables after 10 errors, result is dumped to
    an output file named data_validation.log.
  • Add DATA_VALIDATION_ORDERING configuration directive enabled by default.
    Order of rows between both sides are different once the data have been
    modified. In this case data must be ordered using a primary key or a
    unique index, that mean that a table without such object can not be
    compared. If the validation is done just after data import in mode single
    process and without any data modification the validation can be done on all
    tables without any ordering.
  • Add DATA_VALIDATION_ERROR to stop validating data from a table after a
    certain amount of row mismatch. Default is to stop after 10 rows
    validation errors.
  • Allow multiprocess for TEST_DATA action to validate data import. Use -P
    or PARALLEL_TABLES to set the number of parallel tables checked. Output
    is now done to a file named data_validation.log saved in the current
    directory.
  • Add replacement of UTL_RAW.CAST_TO_RAW with encode().
  • Add rewrite of XMLTYPE() with xmlparse(DOCUMENT convert_from(..., 'utf-8')).
  • Add VARCHAR_TO_TEXT configuration directive. By default VARCHAR2 without
    size constraint are tranlated into text PG data type. If you want to use
    varchar instead, disable this directive.
  • Add detection of XML function for migration assessment cost.
  • Add DBMS_RANDOM to the list of Oraclism handled by Orafce.
  • Add support to mysql_fdw foreign data wrapper to export data
    PostgreSQL tables. Thanks to Yoni Sade for the feature request.
  • Allow to transform all NUMBER(,scale) to an other data type by a
    redefinition like NUMBER(    ,2):decimal in the DATA_TYPE configuration
    directive. Thanks to Florent Jardin for the patch.
  • Add information on how to use SSL encrypted connection to documentation.
  • Add TEST_COUNT action to just report the row count diff between Oracle and PostgreSQL tables.

Backward compatibility changes:

  • Add FORCE_PLSQL_ENCODING configuration directive. In previous version Ora2Pg
    was encoding all functions code to ut8, this is no more the case because it
    could result in double encoding. To recover the old behavior (not recommanded)
    enable this directive. Thanks to rynerisraid and lee-jongbeom for the report.
  • Change behavior regarding RAW columns. Now RAW(16) and RAW(32) columns or
    RAW columns with "SYS_GUID()" as default value are now automatically
    translated into uuid. Data will be automatically migrated as PostgreSQL
    uuid data type provided by the "uuid-ossp" extension. To recover the old
    behavior to export data as bytea whatever is the precision, the following
    must be set with DATA_TYPE configuration: RAW(16):bytea,RAW(32):bytea

Here is the full list of changes and acknowledgements:

  - Fix USE_LOB_LOCATOR handling.
  - Fix data validation using oracle_fdw where zero after decimal is not strip
    unlike with PG.
  - Apply MODIFY_STRUCT redefinition to test actions
  - Fix PG filter when DATA_VALIDATION_ORDERING is disabled
  - Apply RAW to uuid transformation for data validation
  - Apply boolean transformation for data validation
  - Do not export data for virtual column for PG >= 13.
  - Fix wrong replacement function with name including a regexp_* function in
    his name. Thanks to Rui Pereira for the report.
  - Remove comments in the from clause before rewrite outer join (+), the entire
    FROM clause will be rewritten and we don't know where to restore.
  - Fix export of columns information for data verification.
  - Fix TEST_VIEW for row count returned by views to exclude views created in
    extensions.
  - Fix comment on procedures
  - Fix translation of MySQL type UNSIGNED
  - Fix test count of indexes for MySQL database.
  - Fix test MySQL sequence count.
  - Do not display error messages when user and db is first checked in the
    import_all.sh script
  - Fix ordering of check constraints
  - Fix mysql table scan when table name is using reserved word. Thanks to
    Stanley Sung for the report.
  - Fix double BOTH keyword in TRIM function. Thanks to Rui Pereira for the
    report.
  - Fix aliases placed in a wrong way. Thanks to Rui Pereira for the report.
  - Fix parsing of procedure broken on keyword RETURN. Thanks to Pavithra
    Jayasankar.
  - Fix case where default partition is taken as a value. Thanks to Karsten
    Lenz for the report.
  - Fix conversion of NUMBER without precision in PL/SQL code to respect
    settings PG_NUMERIC_TYPE, PG_INTEGER_TYPE and DEFAULT_NUMERIC. Fix
    translation of INTEGER/BINARY_INTEGER that was wrongly exported as
    numeric. Thanks to Philippe Beaudoin for the report.
  - Documentation fix. Thanks to mperdikeas for the patch.
  - Fix case where SQL%ROWCOUNT was not replaced by GET DIAGNOSTIC. Thanks to
    Awdotia Romanowna for the report.
  - Fix quote of unique constraints name. Thanks to Veka for the report.
  - Fix looking at package function metadata when there is a huge amount of
    package.
  - Fix error when trying to remove temporary files.
  - Fix wrong translation of a call to a procedure with PRAGMA AUTONOMOUS
    TRANSACTION through dblink. Thanks to Rui Pereira for the report.
  - Remove schema name in front of index name. Thanks to Menelaos Perdikeas
    for the report.
  - Fix virtual column generated from an other column of the table (supported
    in PG 12). Thanks to Veka for the report.
  - Fix case of columns names in boolean transformation when oracle_fdw is used
    to export data. Thanks to veka for the report.
  - Remove extra parenthesis with sub query and TABLE function. Thanks to Rui
    Pereira for the report.
  - Fix WHERE clause not removed in ROWNUM replacement. Thanks to Rui Pereira
    for the report.
  - Exclude extensions tables from table test count. Thanks to Yoni Sade for the
    report.
  - Fix pg_attribute column adsrc removed in PG 12. Thank to Thorsten Hochreuter
    for the patch.
  - Fix unwanted aliases after row_number() over(). Thanks to Rui Pereira for
    the report.
  - Fix several spelling issues. Thanks to Florian Eckert for the patch.
  - Fix wrong condition to import constraints in import_all.sh. Thanks to
    Thorsten Hochreuter for the report.
  - Fix BITMAP_AS_GIN detection. Thanks to Nishanth Bejgam for the patch.
  - Fix parsing of views from file and add PASSWORD, KEY and REF to the list
    of reserved keywords.
  - Fix replacement of CURSOR ... IS when there is comment after IS.
  - Fix comment in auto generated file global_variables.conf
  - Fix XML data export that was transformed by the call to function
    extract(/).getClobVal(), it is now replaced by a direct call to
    getClobVal().
  - Improve COPY FREEZE data export when FILE_PER_TABLE is enabled, the
    transactions are now managed per individual file and not following
    the main file. Thanks to Yoni Sade for the report.
  - Fix addition to UNLOGGED keyword on foreign table when exporting
    data using oracle_fdw. Thanks to Veka for the report.
  - Fix FK error when using TRUNCATE before data export with oracle_fdw.
  - Fix export of user defined type. Actually type definitions are extracted
    from ALL_SOURCE which contain the original CREATE TYPE and eventually
    all the ALTER TYPE commands. Previously those type as considered as not
    supported by Ora2Pg. 
  - replace date(n) by timestamp.
  - Always remove the fqdn SYS schema before functions call.
  - Add report of GTT in SHOW_TABLE action.
  - Fix empty partition values for Oracle 9i.
  - Add creation of schema in user defined type export when EXPORT_SCHEMA is
    enabled to fix an error when the schema has not already been created.
  - Fix some wordings and exclude from export user defined type starting
    with SYS_PLSQL_ found in a 9i export. It looks that they are internal
    to PL/SQL code.
  - Exclude DBMS_SQL from the DBMS count in migration assessment when
    USE_ORAFCE is enabled.
  - Handle case where indexes name include the schema at create time
  - Fix PL/SQL numeric datatype conversion
Assets 2
Loading