Ora2Pg Files

2025 04 20 - v25.0

This major release fix several issues reported since last release and adds some new features and improvements.

• Add multiple assessment report format output at once. Thanks to Jean-Christophe Arnu for the patch.
• Support more Oracle to PostgrSQL exception mapping.
• Allow overriding of PG_SUPPORTS_* settings as they are set in the configuration file. PG_VERSION will have no effect in this case. Thanks to Pavel Stehule for the feature request.
• Add parsing of ALTER statement from file for the QUERY action.
• Add support for oracle_fdw COPY using CSV format
• Add parallel export of each partition. Until now all partitions of a partitioned table was exported serialy in a single process.
• Allow online data migration to continue if the destination table does not exist when ON_ERROR_STOP is disabled. Thanks to chetan2211 for the feature request.
• Tables data export is now done using the current SCN to have the same snapshot of data between multiprocess export.
• Add replacement of USERENV call with MODULE by current_setting with application_name.
• Add support for data movement using psql copy selecting over oracle_fdw
  • Type INSERT (when FDW_SERVER is set) preserves prior behaviour of both INSERT and COPY
  • Type COPY (when FDW_SERVER is set) uses a new mode of using the psql with \copy with TO PROGRAM...FROM STDIN BINARY...BINARY Thanks to Martin Nash for the patch.
• Add support for oracle_fdw in combination with psql "\copy" and server-side COPY using BINARY stream for data movement. Brings both local and server-side oracle_fdw binary copy into one branch. Control over which mode is used is provided via ORACLE_FDW_COPY_MODE configuration, which defaults to "local". Thanks to Martin Nash for the patch.
• Convert DBMS_SQL cursor/parsing/execute simple form to PostgreSQL dynamic query execution. Thanks to tanguydelignieresaccenture for the patch with some regex improvement by me to handle comments.
• Add replacement of the MOD operator. Thanks to ec-digit-dbecoe for the report.
• Add some additional objects/difficulties assessment migration scores:
  • WHEN OTHER THEN exception clause that hide the real problem during migration.
  • 'SSSSS' and 'J' for to_char() that could need rewrites.
• Add SCRIPT action to process sqlplus scripts as a whole, not line per line.
• Change default settings in config file generated by the --init_project option with:
  • PG_NUMERIC_TYPE 0
  • NULL_EQUAL_EMPTY 1

New options and configuration directives:

• Add --no_clean_comment option to not remove comments in source file before parsing. With huge DDL file with comments, it could take a very long time.
• Add -O | --options used to override any configuration parameter, it can be used multiple time. Syntax: -O "PARAM1_NAME=value" -O "PARAM2_NAME=value" or -O "PARAM1_NAME=value|PARAM2_NAME=value"
• Add option --no_start_scn to force Ora2Pg to not use a SCN to export data unless --snc is used. By default the current SCN is used to export data from all tables.
• Add ORACLE_FDW_COPY_MODE configuration directive. When using Ora2Pg COPY with oracle_fdw it is possible to use two different modes: 1) "local", which uses psql on the host running Ora2Pg for the "TO" binary stream; 2) "server", which uses PostgreSQL server-side COPY for the "TO" binary stream. Both modes use psql for the "FROM STDIN BINARY". However, "local" runs the psql "FROM STDIN BINARY" on host Ora2Pg is run from, whereas "server" runs the psql "FROM STDIN BINARY" on the PostgreSQL server. "local" mode should work on any PostgreSQL-based system, including managed offerings, which are not expected to support use of "server" mode due to permissions. The default is "local" as this is compatible with more configurations.
• Add ORACLE_FDW_COPY_FORMAT configuration directive. When using Ora2Pg COPY with oracle_fdw it is possible to use either BINARY or CSV data format. BINARY provides better performance, however, requires exact data type matching between the FDW and destination table. CSV provides greater flexibiliity with respect to data type matching: if the FDW and destination data types are functionally-compatible the columns can be copied. The default is "binary".

Bug fixes:

• Fix always returns row count from stats, even if --count_rows flag is used. Thanks to alavrent2022 for the report.
• Fix multiprocess with TEST_DATA action. Thanks to tanguydelignieresaccenture for the report.
• Fix another if condition issue. Thanks to skm9380 for the report.
• Fix assignment sign in UPDATE statements. Thanks to ec-digit-dbecoe for the report.
• Fix mysql # comments. Thanks to ec-digit-dbecoe fir the report.
• Fix enum column with value "inf" that is expanded to the word "Infinity". Thanks to nbromage for the report.
• Fix MySQL labels not converted to PostgreSQL syntax. Thanks to ec-digit-dbecoex for the report.
• Fix MySQL DECLARE declaration after BEGIN clause. Thanks to ec-digit-dbecoe for the report.
• Fix error Invalid Identifier error for DBMS_LOB.GETLENGTH() by checking to user privilege. Thanks to tanguydelignieresaccenture for the patch.
• Fix TO_DATE translation when a NLS_setting is present. Thanks to anguydelignieresaccenture for the patch. Fix export type TEST reporting 0 for PostgreSQL functions count if using default PACKAGE_AS_SCHEMA. Thanks to tanguydelignieresaccenture for the patch.
• Use double quote instead of single quote in ora2pg command to fix an issue on windows with ora2pg_scanner. Thanks to ec-digit-dbecoe for the patch.
• Fix TEST_DATA foreign table ordering consistency. Thanks to tanguydelignieresaccenture.
• Fix ordering of Oracle data for TEST_DATA export. Thanks to tanguydelignieresaccenture for the patch.
• Handling Group By and Having Clause inside subqueries (HAVING Clause misplacement in Ora2Pg). Thanks to newtora2pg for the patch.
• Remove import of tmpdir function of Perl File::Spec module, it fails on Windows.
• Limit search for spatial type in column to the first line. Thanks to Amit Kumar for the report.
• Fix case where rewrite of listagg to string_agg is not done. Thanks to bbellsct for the report.
• Apply reserved word quoting in PG query to for data validation. Thanks to mmay9CO for the report.
• Prevent full scan when looking for spatial srid and dim. Thanks to Amit Kumar for the report.
• Fix output format detection condition
• Fix double declaration of variable
• Append CREATE SCHEMA only if CREATE_SCHEMA is enabled for SEQUENCE export. Thanks to Brian Hayden for the report.
• Fix schema name for procedures/functions when PG_SCHEMA is set. Thanks to mgole001 for the report.
• Fix data export filename for partition when RENAME_PARTITION is enabled. Thanks to Simon Pane for the report.
• Fix replacement of SYSTIMESTAMP by statement_timestamp() instead of CURRENT_TIMESTAMP. Thanks to dstinit for the report.
• Fix methode import when using File::Spec. Thanks to niteshn24 for the report.
• Apply MOFIDY_TYPE rewriting after REPLACE_AS_BOOLEAN. Thanks to jstmx for the report.
• Fix inner query IN keyword missing after converting. Thanks to rajatjha28 for the report.
• Fix export of procedure with out parameter when PG_version < 11. Thanks to Pavel Stehule for the report.
• Remove rewrite of numeric operation with TRUNC() to use interval, too much false positive. Thanks to Pavel Stehule for the report.
• Force binmode when output is compressed with bzip2. Thanks to Vladimir Roganov for the report.
• Fix eval() error detection. Thanks to Vladimir Roganov for the report.
• Fix incorrect translation from OUT to INOUT param
• Handling Multiline COMMENT in Ora2Pg.
• Handling Referencing in Triggers.
• Handling Timestamp Datatype.
• Handling XMLELEMENT Conversion.
• [MSSQL] Add parsing of a DDL file to migrate tablesi with indexes and constraints. The GO keyword that ends a statements must be replaced by a semi-colon (;) and all brackets must be removed from the source file before.
• Fix parsing of foreign keys with input file. Thanks to raulcejas04 for the report.
• Fix schema for trigger function when read from file.
• Remove double quote on custom type when readind table definition from a file.
• Fix use of REPLACE_TABLES and REPLACE_COLS with input files.
• Fix export of procedure to function when pg_supports_procedure is off by removing the unwanted extra_param parameter. Force pg_supports_outparams to off when pg_supports_procedure is off. Thanks to Pavel Stehule for the report.
• Remove goldengate suplemental table logging
• Fix pg_supports_ifexists with change in previous commit
• Keep schema in function/procedure name when it is read from file an we are not in PACKAGE action.
• Fix infinit loop in NLS_SORT replacement.
• Avoid duplicate name in foreign keys constraints
• Fix override of any Ora2Pg configuration directive from command line for complex values like REPLACE_TABLES.
• Remove sinh, cosh and tanh from unsupported oracle function list
• Fix parsing of stored procedure from file.
• Fix if condition in export_schema.ps1. Thanks to Robin Pringle for the patch.
• Fix replacement by PERFORM
• Add "Schema Export Complete" and "Ora2Pg ending" message to provide enhancement documented in Issue #1806. Thanks to Simon Pane for the patch.
• Quote PG_USER in ORACLE_FDW user mapping. Thanks to Simon Pane for the patch.
• Remove PASSWORD from keywork list
• Make sure that the column alias for trim() is not prefixed by the schema.
• Fix TRUNCATE TABLE command when the table name needed to be quoted
• Fix column list in COPY statements, columns was doubled.
• Fix port setting for FDW server. Thanks to Aymen Zaiter for the report.
• Added ORACLE_FDW_COPY_FORMAT: binary or csv
• [mysql] Replace json_extrat() with json_extrat_path. Thanks to mgole001 for the report.
• Remove table alias from column alias after TRIM function. Thanks to Simon Pane for the patch.
• Fix validation of nullable boolean columns. Thanks to Eckhart Worner for the patch.
• Fix regular expression for function-based indexes in validation. Thanks to Eckhart Worner for the patch.
• Fix regression in NULL_EQUAL_EMPTY feature. Thanks to Eric Delanoe for the report.
• Fix export of foreign keys for PG <= 10
• Fix rename of partitions when REPLACE_TABLES is used on the parent table and when PREFIX_PARTITION is enabled.
• Set environment variable PGPASSWORD for ORACLE_FDW_COPY_FORMAT and remove PGPASSWORD from OFBC psql commands. Thanks to Martin Nash for the patch. Use of PGPASSWORD results in the password being exposed to anyone with host access. Switching to relying on .pgpass, which can be located in a custom location for Ora2Pg by setting PGPASSFILE in the session running Ora2Pg, avoids password exposure.
• Update to use explicit column list for OFBC. Thanks to Martin Nash for the patch.
• Fix conversion of data format for TO_TIMESTAMP function. Thanks to Priyanshi Gupta for the report.
• Second fix for bug migrating data in json column. Thanks to mat-efluid for the report.
• Fix migration of data from CLOB to jsonb isung COPY mode to preserve json escaping. Thanks to Thomas Herzog for the report.
• Fix quoting for index creation on reserved keyword. Thanks to moonbeamglitterblossom for the report.
• Fix case where values was transformed as boolean when when a table has the same name as a data type. Thanks to twiti7 for the report.
• Fix use of TRANSFORM_VALUE when a function is used. Thanks to Thomas Herzog to the report
• Fix for subquery where clause issue
• Fix incorrect "unsupported partition type" warning for oracle_fdw copy. Thanks to Martin Nash for the patch.