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