darold/ora2pg

Releases

Version 25.0Latest

View release

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.

Version 24.3

View release

2024 03 29 - v24.3

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

Add option control the "prefetch" used by oracle_fdw COPY/INSERT Prior to this change Ora2Pg uses the default "prefetch" of oracle_fdw, which at the time of writing is 50. Allowing this to be controlled by an Ora2Pg configuration/option gives the option of increased performance at the cost of some additional memory on the PostgreSQL side. Thanks to Martin Nash for the patch.
Modify the behavior of triggers export with EXPORT_INVALID. It used to apply to ENABLED or DISABLED triggers instead of real VALID or INVALID triggers. Export of INVALID triggers will be controlled by EXPORT_INVALID like others objects like functions, packages, etc. This mean that disabled triggers that are valid will be exported by default now, this was not the case before. Thanks to dcgadmin for the feature request.
Add new configuration directive PGTT_NOSUPERUSER. By default the pgtt extension is loaded using the superuser privilege when EXPORT_GTT is activated. Enabled it if you run the SQL scripts generated using a non superuser user. It will use:
```
LOAD '$libdir/plugins/pgtt';
```
instead of default:
```
LOAD 'pgtt';
```
Thanks to Simon Martin for the feature request.

Here is the full list of changes and acknowledgements:

Fix Inf replacement that must only be done with numeric datatype. Thanks to gael-efluid for the report.
Fix some replacement of OUTER JOIN (+). Thanks to Carens Kurniawan Wijaya for the report.
Fix schema filter with test function count. Thanks to dcgadmin for the report.
Fix TEST count objects when a table name is modified. Thanks to korolan for the report.
Fix issue with multi style comments. Thanks to newtglobal.com for the patch.
Fix documentation about EXPORT_INVALID to precise that it also concern disabled triggers. Thanks to dcgadmin for the report.
Fix missing FOR EACH clause in trigger export after a regression introduced by commit fb6b0ad. Thanks to Carens Kurniawan Wijaya for the report.
Fix data export for table with a geometry column. Thanks to ruralqiu for the report.
Revert changes introduced by commit fc7008c, for some obscurs Oracle reasons the DBA_SDO_GEOM_METADATA doesn't always exist. Always uses ALL_SDO_GEOM_METADATA instead. Thanks to ruralqiu and Pierre3939 for the report.

Version 24.2

View release

2024 03 07 - v24.2

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

Allow DATA_EXPORT_ORDER to take a filename at value to be able to give a custom table order. The file must contain the ordered list of the tables. One table per line in upper case for Oracle. Thanks to DataCloudGaze for the feature request.
Add progress bar when --oracle_speed is used to avoid waiting for the whole data export ends.
Add replacement of the BITAND function by the & operator
Add option -f, --format to set the output format for the reports. It can be html or json. Default to html. Thanks to mgole001 for the feature request.
Add automatic addition of the partition key to the primary key. Prefix all columns with the alias in the query to get data from Oracle.
Add information about MSSQL masked columns in SHOW_COLUMN.
Add information about columnstore and compression on MSCSQL tables with action SHOW_TABLE.
Add new configuration directive PARTITION_BY_REFERENCE to defined how to export Oracle partition by reference. Possible values are none, duplicate or the number of hash partitions to create.

Value 'none' mean no translation and export of partition by reference like before. Value 'duplicate' will duplicate the referenced column in the partitioned table and apply the same partitioning from the referenced table to the partitioned table. If the value is a number, the table will be partitioned with the HASH method using the value as the modulo. For example if you set it to 4 it will create 4 HASH partitions.

Default is none to not export the partitions by reference definition.

Here is the full list of changes and acknowledgements:

Fix default values in MSSQL function declaration and missing END keyword.
Fix parsing of MSSQL function with a single query. Thanks to Saravanan Newt for the report.
Fix negative Oracle decimal when comparing data. Thanks to es99-24 for the report.
Fix typos in documentation. Thanks to Simon Martin for the report.
Avoid doubling the NAME keyword in the XMLELEMENT() function.
Remove clause "REFERENCING OLD AS OLD NEW AS NEW" that is useless and throw an error in PostgreSQL
Fix DSN for MSSQL in autogenerated configuration file using --init_project.
Fix parsing of REFERENCING clause in trigger. Thanks to Carens Kurniawan Wijaya for the report.
Fix case where ALL_TAB_COLUMNS and ALL_SDO_GEOM_METADATA was used instead of USER_TAB_COLUMNS. Thanks to rvanouter for the report.
Fix double quoting in partition by reference where clause.
Disallow setting of PARTITION_BY_REFERENCE to duplicate when FDW_SERVER is set.
Fix trigger export with duplicate FOR EACH clause when a REFERENCING clause is present. Thanks to Carens Kurniawan Wijaya for the report.
Fix typo in variable name in ora2pg_scanner
Fix export of default partition for MySQL. Thanks to Priyanshi Gupta for the report.
Fix TEST_DATA action with issues on foreign server and import foreign schema declaration. Thanks to Florent Jardin for the report.
Fix export of NOT NULL constraint with column replacement. Thanks to Florent Jardin for the report.
Prevent reading file ora2pg_stdout_locker when it does not exist. Thanks to Florent Jardin for the report.
Fix translation of timestamp with precision < 6. Thanks to Andrei Briukhov for the report.
Fix json_arrayagg with returning clause, type was not translated.
Fix data export for table partitioned by reference with duplicate method. The query to extract data on Oracle side add the duplicated column and perform the join with the referenced table using the FK definition.
Fix partition by LIST export with a useless cast to text. Thanks to Priyanshi Gupta for the report.
Added closing curly bracket for object details. Thanks to andreas42 for the patch.
Added quotes around value of "human days cost" and "migration level". Thanks to andreas42 for the patch.
Format object type detail output as JSON array. Thanks to andreas42 for thepatch.
Fix variable declaration in previous commit
Fix MSSQL table export with nonexistent column auto_created in version bellow 2017. Thanks to Florent Jardin for the report.
Use ADD CONSTRAINT syntax to specify name of primary key. Thanks to Martin Karlgren for the patch.
Skip unwanted work on PG database when option --oracle_speed is enabled, especially drop of constraints. Thanks to John Tian for the report.
Remove trailing ); from primary key statements when reading from file. Thanks to Martin Karlgren for the patch.
Fix option in CREATE USER MAPPING for export of DBLINK.
Fix translation of MSSQL floating point datatype to use float(n) notation.
Exclude from assessment objects in Oracle bin.
Add Oracle package HTP and HTF to migration assessment.
Fix MSSQL foreign key export with multiple columns referenced.
Fix MSSQL export of unique constraints that was merging columns of several unique constraints for the same table into a single constraint.
Fix case of MSSQL datetime default value 0 that must be converted to '1900-01-01 00:00:00'
Add rewrite of MSSQL getutcdate() function.
Fix MSSQL index type and add compression + columnstore information
Add clause IF NOT EXIST to all CREATE EXTENSION calls
Fix MSSQL bit data migration through tds_fdw, it is already exported as boolean by the fdw.
Fix duplicated indexes in MSSQL export.
Add export of MSSQL indexes with columns included (CREATE INDEX+INCLUDE).

Version 24.1

View release

2023 09 08 - v24.1

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

Add export of indexes created automatically by the Automatic Indexing feature of Oracle 19c
Replace "set feedback off" by "\set QUIET on;" and "set pagesize 0" with "\pset pager off". Thanks to Martin Gerhardy for the suggestion.
Always add package name into search_path of packages functions. Thanks to janopha for the report.
Allow to specify a password file to set PG_PWD at PostgreSQL database connection. If the specified file exists on the system, Ora2Pg will read the first line to get the password at each call of send_to_pgdb(). It can be useful in some situation where the password change during data migration. Thanks to Marius Hope for the feature request.
Added option --dump_as_json and fixed some json output errors. Thanks to Martin Gerhardy for the patch.

Here is the full list of others changes with acknowledgements:

Fix schema prefixing of type created in packages stored procedures. Thanks to janopha for the report.
Fix perl function get_schema_condition() to use quote_ident() in generated SQL filters. Thanks to franxav06 for the report.
Attempt to better test the not null constraint count in Oracle.
Fix regression with PSQL_RELATIVE_PATH. Thanks to Ryan Taylor for the report.
Do not add the partition key to PK if DISABLE_PARTITION is enabled. Thanks to pavel-moskotin-db for the report.
Filter list of indexes on name instead of the GENERATED column because we are missing the ones that have been created automatically by the Automatic Indexing feature of Oracle 19c. Thanks to Franck Pachot for the report.
Fix incompleteness in GRANT action, add grant usage on schema to owner and users. Thanks to elexus for the report.
Apply missing --blob_to_lo to import_all.sh script.
Fix regression in MySQL hash partitions export. Thanks to Shubham Dabriwala for the report.
Fix Oracle INTERVAL data export with negative value. Thanks to shubham-yb for the report.
Fix double count of not null constraints for TEST action. Thanks to Simon Pane for the patch.
Fix replacement of Oracle sys_refcursor in function return type.
Fix replacement of SQL script setting from Oracle.
Make scripts executable. Thanks to Martin Gerhardy for the patch.
Exclude data pump SYS_EXPORT_SCHEMA_.* tables from Oracle export.
ora2pg: use env based shebang for perl. Thanks to Martin Gerhardy for the patch.
Add unsupported clause message for PRAGMA AUTONOMOUS_TRANSACTION when it is not rewritten by Ora2Pg. Thanks to Martin Gerhardy for the patch.
Allow schema specific definitions of partitioning columns. Thanks to Martin Gerhardy for the patch.
Fixed invalid variable name in read_grant_from_file. Thanks to Martin Gerhardy for the patch.
Fix not double quoted column in alter set not null column statement. Thanks to leonteq-reisg for the patch.
Bug fixes and special treatment for Types with body. Thanks to Martin Gerhardy for the patch.
Avoid redundant definition of the version. Thanks to Martin Gerhardy for the patch.
Fix a MySQL exception when the column type is ENUM for version < 5.7. Thanks to Code-UV for the report.

Version 24.0

View release

2023 07 05 - v24.0

This major release adds support to migration of SQL Server database to PostgreSQL. It also fixes several issues reported since past height months and adds some new features and improvements.

Enable the use of ALLOW/EXCLUDE directive with SHOW_* reports and throw a fatal error if global filters in ALLOW/EXCLUDE are set.
Add replacement of DBMS_LOCK.SLEEP with pg_sleep
Split estimate cost details per function/procedure/and package function.
Add cmin, cmax, ctid to reserved keywords list.
Add cost for presence of ADD CONSTRAINT in PLSQL code. It needs constraint name stability.
Allow COPY and TABLE type to use the NULLIF construct. Thanks to Luke Davies for the patch.
Add new SEQUENCE_VALUES export type to export DDL to set the last values of sequences from current Oracle database last values like the following statements: ALTER SEQUENCE departments_seq START WITH 290; Thanks to sergey grinko for the feature request.
Add replacement of Oracle variable : varname into PG :'varname'.
Add SQL Server migration to Ora2Pg. Most of the SQL Server objects are supported as well as data export. Translation of the TSQL stored procedures to plpgsql is complicated because of the lack of statement separator in TSQL but as usual Ora2Pg is doing is best to do as much work as possible. Migration assessment is also possible with SQL Server database. There is some dedicated configuration directives added to ora2pg.conf.
Add support to MySQL PARTITION BY KEY() with a translation to HASH partitioned table using the PK/UK definition of the table or the columns specified in the KEY() clause. Thanks to Shubham Dabriwala for the report.
Make EXPORT_INVALID configuration directive works with TRIGGER export. Until now disabled triggers were not exported, setting EXPORT_INVALID to 1 will force the export of disabled triggers. Thanks to chetank-yb for the report.
Add support of MySQL generated default value on update. For example:
```
  CREATE TABLE t1 (
    dt DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
  );
```
Ora2Pg will translate this syntax into a trigger on the table to force the value of the column on an update event. Thanks to heysky for the report.
Add translation of ST_GEOMETRY data type to PostGis geometry datatype.
Replace ROWNUM in target list with a row_number() over () clause. Thanks to Rui Pereira for the report.

New configuration directives:

Add CLOB_AS_BLOB configuration directive to treat CLOB as BLOB when exporting data. When enabled Ora2Pg will apply same behavior on CLOB than BLOB with BLOB_LIMIT setting. This could be useful if you have large CLOB data. Enabled by default. Thanks to Omar Mebarki for the patch.
Add configuration directive ST_GEOMETRYTYPE_FUNCTION to be able to set the function to use to extract the geometry type from a ST_Geometry column. Default: ST_GeometryType, example it should be set to sde.ST_GeometryType for ArcSDE. Thanks to Albert88373 for the report.
Add four new configuration directive to be able to change or prefix the functions used to extract information from ST_Geometry object and values.
- ST_SRID_FUNCTION: Oracle function to use to extract the srid from ST_Geometry meta information. Default: ST_SRID, for example it should be set to sde.st_srid for ArcSDE.
- ST_DIMENSION_FUNCTION: Oracle function to use to extract the dimension from ST_Geometry meta information. Default: ST_DIMENSION, for example it should be set to sde.st_dimention for ArcSDE.
- ST_ASBINARY_FUNCTION: Oracle function to used to convert an ST_Geometry value into WKB format. Default: ST_ASBINARY, for example it should be set to sde.st_asbinary for ArcSDE.
- ST_ASTEXT_FUNCTION: Oracle function to used to convert an ST_Geometry value into WKT format. Default: ST_ASTEXT, for example it should be set to sde.st_astext for ArcSDE. Thanks to Albert88373 for the report.
Add INSERT_ON_CONFLICT configuration directive. When enabled this instruct Ora2Pg to add an ON CONFLICT DO NOTHING clause to all INSERT statements generated for this type of data export. Thanks to Clemens Rieder for the feature request.

Backward compatibility:

Change the behavior of CASE_INSENSITIVE_SEARCH to allow the use of a collation instead of the citext extension. To disable the feature the value none can be used. If the migration is not MSSQL this feature is disabled.
Remove PREFIX_PARTITION configuration directive, it is now replaced by the RENAME_PARTITION directive. Previous behavior was to construct the partition name from the table name, the partition name and the sub partition name if any. The problem is that we often reach the max length for an object name and this leads to duplicate partition name. Now, when RENAME_PARTITION is enabled the partition tables will be renamed following rules:
```
    <tablename>_part<pos>
```
where "pos" is the partition number. For subpartition this is:
```
   <tablename>_part<pos>_subpart<pos>
```
If this is partition/subpartition default:
```
    <tablename>_part_default
    <tablename>_part<pos>_subpart_default
```
This change will break backward comaptibilty, if PREFIX_PARTITION is still set, it will simply enable RENAME_PARTITION.
Set START value to MINVALUE when a sequence is cycled and that the START value is upper that MAXVALUE. Thanks to Shane Borden for the report.

Here is the full list of changes and acknowledgements:

  - Fix MODIFY_STRUCT that was not working with MySQL. Thanks to Code-UV for
    the report.
  - Fix license string in Makefile.PL. Thanks to RodRaen for the report.
  - Do not remove non alphanumeric character in index name. Thanks to gwidt
    for the report.
  - Reorder trigger event when the update of column is not the last one. Thanks
    to tayalarun1 for the report.
  - Fix export of MySQL function containing special characters and white spaces
    in names. Thanks to Shubham Dabriwala for the report.
  - Fix grant export for partitions. Thanks to elexus for the report.
  - Add some other transformation for sqlplus/psql scripts.
  - Remove comma as possible separator for values in DEFINED_PK, it was
    preventing the use of a function with multiple parameters.
  - Fix export of geometry tables when PG_SCHEMA is set.
  - Add rewriting of some sqlplus settings to psql settings.
  - Fix TABLESPACE export for partitioned tables. Thanks to elexus for the
    report.
  - Fix for Issue #1637. Thanks to Simon Pane for the patch.
  - Fix typo in --init_project directories tree generation for sequences
    values.
  - Fix alias in view target list for function call without alias defined in
    MySQL export. Thanks to Shubham Dabriwala for the report.
  - Fix Mysql procedure export when a datatype with precision is used in
    parameter list. Thanks to Shubham Dabriwala for the report.
  - Fix collation on string default values. Thanks to Shubham Dabriwalafor
    the report.
  - Exclude recycle bin object from ALL_TAB_COLUMNS lookup. Thanks to Dave
    Betterton for the report.
  - Fix data types translation (TINYINT|SMALLINT|MEDIUMINT|INTEGER|BIGINT|INT)
    for MySQL table export. Thanks to Shubham Dabriwala for the report.
  - Do not export synonym destination table with table_owner when EXPORT_SCHEMA
    is disabled. Thanks to Priyanshi Gupta for the report.
  - Fully qualify calls to get_sequence_last_values() when PG_SCHEMA is set.
    Thanks to Marius Hope for the report.
  - Fix regression on exporting view as table when VIEW_AS_TABLE contains
    regexp. Thanks to Neil Bower for the report.
  - Fix missing execution of initial command statements at start of TEST_DATA
    action and on both side, those applying to source and destination. Thanks
    to Petter Jacobsen for the report.
  - Fix script to get sequence last value with TEST action. Thanks to franxav06
    for the patch.
  - Prepend PERFORM before call to DBMS_OUTPUT.* when USE_ORAFCE is enabled.
  - Disable USE_ORAFCE when export type is SHOW_REPORT.
  - Extending the enhancement in Pull Request #1621 to the Oracle_FDW user
    mapping. Thanks to Simon Pane for the patch.
  - Changed prefix string to "DIFF:" in test report. Thanks to Simon Pane for
    the patch.
  - Fix cases where %ROWCOUNT was not correctly replaced. Thanks to Rui Pereira
    for the report.
  - Fix parsing of ORACLE_DSN when creating foreign server in COPY mode. Thanks
    to Luke Davies for the report.
  - Fix for Issue #1622, #1627. Thanks to Simon Pane for the patch.
  - Fix index creation with DESC order in COPY action when DROP_INDEXES is
    enabled. Thanks to Luke Davies for the report.
  - Fix for Issue #1610, #1612, #1617 and #1381. Thanks to Simon Pane for the
    patch.
  - Fix typo in sqlnet.ora name (was sqlnet.or). Thanks to Martin Nash for the
    patch.
  - Fix data export, REPLACE_QUERY was not applied. Thanks to Bachev Constantin
    for the report.
  - Fix call to replace_sys_context().
  - Fix timestamp(n) data type translation.
  - Remove use of column GENERATION_EXPRESSION for MySQL version < 5.7.0. Thanks
    to Hans Choi for the report.
  - Fix conversion of DATE datatype to timestamp(0) instead of timestamp. Thanks
    to Akhil Reddy for the report.
  - Add NVARCHAR/NCHAR defaut convertion data types to DATA_TYPE configuration
    directive in ora2pg.conf comments. Thanks to Akhil Reddy for the report.
  - Rename method _get_partitions_type function into _get_partitions_list.
  - Fix synonym export when no schema information is available.
  - Fix support of REFERENCING clause in triggers.
  - Fix partition output file renaming with new RENAME_PARTITION directive.
    Thanks to Rahul Barigidad for the report.
  - Fix export of the ROWNUM clause when there is a variable.
  - Fix sprintf placeholders in geometry queries.
  - Fix some others issues with row count report.
  - Fix row count with destination schema and when the PostgreSQL table
    doesn't exist.
    Thanks to bizen-ya for the report.
  - Fix tests comparison with the different settings of EXPORT_SCHEMA,
    SCHEMA and PG_SCHEMA. Thanks to Marius Hope and bizen-ya for the
    report.
  - Fix St_AsText() call for MySQL data extraction.
  - Add column count comparison for MySQL
  - Export multi column partition by list as an expression with concat
    operator. Multi column partition by list is not supported by PostgreSQL.
  - Fix creation of non existant indexes on partition. Thanks to Shubham
    Dabriwala for the report.
  - Fix MySQL function export when there is no BEGIN clause. Thanks to
    Shubham Dabriwala for the report.
  - Fix MySQL export of unsigned numeric. Thanks to Shubham Dabriwala for
    the report.
  - Fix MySQL output with wrong synthax for JOIN without ON clause. Thanks
    to Shubham Dabriwala for the report.
  - Fix virtual column export. Thanks to Rafal Hollins for the report.
  - Fix index creation on partition with no columns for MySQL export.
    Thanks to Shubham Dabriwala for the report.
  - Fix export of MySQL auto_increment when PG_INTEGER_TYPE is disabled.
    Thanks to Shubham Dabriwala for the report.
  - Fix MySQL subpartition export. Thanks to Sanyam Singhal for the report.
  - Move any INTO clause in CONNECT BY query to the final SELECT on the
    resulting CTE. Thanks to taptarap for the report.
  - Fix translation of MySQL curtime() function in default values. Thanks
    to Shubham Dabriwala for the report.
  - Fix possible "Nested quantifiers in regex" error when exporting package
    with package name containing regex special characters. Thanks to durandm70
    for the report.
  - Fix documentation about use of unique key for ORACLE_COPY.
  - Fix extra comma at end of a CHECK contraint. Thanks to Shubham Dabriwala
    for the report.
  - Always add DROP TYPE statements with package export even if DROP_IF_EXISTS
    is not enabled. Thanks to Rui Pereira for the report.
  - Fix default value of simple dot in MySQL export. Thanks to Shubham
    Dabriwala for the report.
  - Fix regression in data type translation after fix on unsigned numeric type.
    Thanks to Shubham Dabriwala for the report.

Version 23.2

View release

2022 10 08 - v23.2

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

Add export of MySQL KEY and LINEAR KEY partitioning, translated as HASH partitioning.
Allow export of object with dollar sign is his name.
Add export of CHECK constraints for MySQL >= 8.0.
Add Functional/Expression indexes export from MYSQL.
Add export of MySQL virtual column. Thanks to Shubham Dabriwala for the
Remove scale and precision of a numeric if the scale is higher than the precision. PostgreSQL does not support decimal/numeric attributes where the scale is explicitly higher than the precision.
Add command line option --drop_if_exists to add statement to drop objects before creation if it exists. It corresponds to the DROP_IF_EXISTS
Add option -C | --cdc_file to be able to change the name of the default file used to store/read SCN per table during export. Default is TABLES_SCN.log in the current directory. This is the file written by the --cdc_ready option.
Add multiprocess to count rows in PostgreSQL tables (TEST_COUNT) using -P command line option.
Add support to PostgreSQL 14 procedure with out parameters.
Set default PostgreSQL database target version to 14.

New configuration directives:

Add configuration directive MVIEW_AS_TABLE and command line option --mview_as_table to set which materialized view to export as table. By default none. Value must be a list of materialized view name or regexp separated by space or comma. If the object name is a materialized view and the export type is TABLE, the view will be exported as a create table statement. If export type is COPY or INSERT, the corresponding data will be exported.
Add configuration variable FDW_IMPORT_SCHEMA to rename the schema where foreign tables for data migration will be created. If you use several instances of ora2pg for data migration through the foreign data wrapper, you might need to change the name of the schema for each instance. Default: ora2pg_fdw_import
Add TRANSFORM_VALUE configuration directive to apply an expression when retrieving data from Oracle. For example: TRANSFORM_VALUE CLOB_TABLE[CHARDATA:translate("CHARDATA", chr(0), ' ')] to replace all Oracle char(0) in a string by a space character.
Add EXCLUDE_COLUMNS configuration directive. Unlike MODIFY_STRUCT that is used to redefine a table structure, this directive allow to specify a list of columns per table that must be excluded from the export. For example: EXCLUDE_COLUMNS T1(nocol1,nocol2) T2(nocol1,nocol2)
Add new configuration directive EXPORT_GTT to export Oracle Global Temporary Table using syntax recognized by the pgtt extension. For more information see https://github.com/darold/pgtt Default is to not export global temporary table as they are not supported natively by PostgreSQL.
Add new configuration option NO_EXCLUDED_TABLE. By default Ora2Pg exclude from export some Oracle "garbage" tables that should never be part of an export. This behavior generates a lot of REGEXP_LIKE expressions which are slowing down the export when looking at tables. To disable this behavior enable this directive, you will have to exclude or clean up later by yourself the unwanted tables. The regexp used to exclude the table are defined in the array @EXCLUDED_TABLES in lib/Ora2Pg.pm. Note this is behavior is independent to the EXCLUDE configuration directive.

Backward compatibility:

Force rewrite of all invalid date starting with zero year 0000 into 1970-01-01 when it is a default value and NULL for data. Old behavior was to only replace 0000-00-00 date.
Until now there was a lot of untranslated call to TRUNC(date) because Ora2Pg is unable to detect that the parameter is a date or a number. The problem is that Oracle has TRUNC(number) too and Ora2Pg try to not apply the transformation if there is a doubt. In most of the migration have met very few TRUNC(number) so now all call to TRUNC() will be converted to date_trunc(). There must be false positive rewrite but this should be far less work than the actual situation.

Here is the full list of changes and acknowledgements:

  - Fixed PostgreSQL "relation not found error" in _dump_fdw_table(), PostgreSQL
    search_path was not being used. Thanks to James Schriever for the patch.
  - Fix year and month quoting as reserved words when they are used as aliases.
    Thanks to duursma for the report.
  - Fix conversion of to_number(substr(...)) when PG substr() return empty
    string where Oracle return NULL which make the conversion to numeric fail.
    The fix using (nullif(substr(...), )::numeric) only concern TABLE export.
    Thanks to Menelaos Perdikeas for the report.
  - Add export of MySQL KEY and LINEAR KEY partitioning, now exported as HASH
    partitioning. Thanks to Sanyam Singhal for the report.
  - Remove $ sign from characters that require object name quoting.
  - Fix export of objects with the $ sign in the name. Thanks to yano-rxa and
    duursma for the report.
  - Prevent translation of EXEC when used as alias. Thanks to Rui Pereira for
    the report.
  - Fix MySQL enum data type export with regression introduced by commit 24a476.
    Thanks to Shivansh Gahlot for the report.
  - Rename ORACLE_FDW_TRANSFORM to TRANSFORM_VALUE to apply in all case an
    expression when retrieving data from Oracle. For example:
      TRANSFORM_VALUE   CLOB_TABLE[CHARDATA:translate("CHARDATA", chr(0), ' ')]
    to replace all Oracle char(0) in a string by a space character.
  - Fix add_month() translation failing with some use cases. Thanks to duursma
    for the report.
  - Add export of CHECK constraints for MySQL >= 8.0. Thanks to Rahul
    Barigidad for the report.
  - Fix MySQL unsigned numeric data type conversion. Thanks to Rahul Barigidad
    for the report.
  - Add Functional/Expression indexes fail while exporting from MYSQL. Thanks to
    Shubham Dabriwala for the report.
  - Fix export of descending indexes for MySQL. Thanks to Shubham Dabriwala for
    the report.
  - Force MySQL auto increment sequence to start at 1 when value is 0.
    Thanks to Rahul Barigidad for the report.
  - Fix conversion of MySQL decimal(p,s) to keep the original datatype
    instead of use of real or double.
  - Add export of MySQL virtual column. Thanks to Shubham Dabriwala for the
    report.
  - Fix export of MySQL function with return clause only. Thanks to Shubham
    Dabriwala for the report.
  - Remove scale and precision of a numeric if the scale is higher than the
    precision. PostgreSQL does not support decimal/numeric attributes where
    the scale is explicitly higher than the precision. Thanks to Rahul Barigidad
    for the report.
  - Fix export of comment for views. Thanks to gh-k-murata for the report.
  - Add command line option --drop_if_exists to add statement to drop objects
    before creation if tehy exists. It corresponds to the DROP_IF_EXISTS
    configuration directive. Thanks to Yoni Sade for the feature request.
  - Add option --mview_as_table to documentation.
  - Add configuration directive MVIEW_AS_TABLE and command line option
    --mview_as_table to set which materialized view to export as table.
    By default none. Value must be a list of materialized view name or
    regexp separated by space or comma. If the object name is a materialized
    view and the export type is TABLE, the view will be exported as a create
    table statement. If export type is COPY or INSERT, the corresponding data
    will be exported.
  - Disable EXPORT_GTT when export type is not TABLE. Thanks to gh-k-murata for
    the report.
  - Fix generated external servers wrongly placed in a schema. Thanks to duursma
    for the report.
  - Add configuration variable FDW_IMPORT_SCHEMA to rename the schema where
    foreign tables for data migration will be created. If you use several
    instances of ora2pg for data migration through the foreign data wrapper,
    you might need to change the name of the schema for each instance. Default
    to ora2pg_fdw_import. Thanks to James Schriever for the feature request.
  - Fix wrong conversion of rownum clause when a subquery is used. Thanks to
    Rui Pereira for the report.
  - Escape comma and backslashes in BFILE data export. Thanks to duursma for
    the patch.
  - Fix possible infinite loop in Oracle outer join parsing. Thanks a lot to
    yano-rxa for the report.
  - Remove privileges default settings on views exported from SYNONYMs
  - Add support for Rectangle geometry type. Thanks to duursma for the patch.
  - Fix double replacement of IS NULL/IS NOT NULL when NULL_EQUAL_EMPTY is
    enabled.
  - Add CHECK not null only constraints to not null constraint count.
  - Fix CHECK NOT NULL only constraints that was not exported by generating
    NOT NULL constraints instead. They are exclude from the count of CHECK
    constraint as suggested by Florent Jardin but a count difference persist
    for NOT NULL constraints.
  - Fix TYPE export when SCHEMA and PRESERVE_CASE have different values. Thanks
    to Florent Jardin for the report.
  - Fix custom exception replacement. Thanks to Rui Pereira for the report.
  - Fix Collection and Polygon geometry INTERNAL export.
    Thanks to duursma for the patch.
  - Fix export of efile with parenthesis. Thanks to duursma for the report.
  - Fix wrong column indices used in spatial index extraction. Thanks to duursma
    for the report.
  - Fix call of ST_GeomFromText() with WKT export. Thanks to duursma for the
    report.
  - Disable USE_LOB_LOCATOR with WKT geometry export type, ST_GeomFromText and
    SDO_UTIL.TO_WKTGEOMETRY functions return a CLOB instead of a geometry.
    Thanks to duursma for the report.
  - Fix INTERNAL conversion uses the srid from the object instead of the
    meta-data. Thanks to duursma for the report.
  - Fix regression in data export when REPLACE_AS_BOOLEAN is set. Thanks to
    Juri Berlanda for the report.
  - Fix call to procedure using dblink. Thanks to Rui Pereira for the report.
  - Keep untouched call to DBMS_OUTPUT functions if USE_ORAFCE is enabled.
    Thanks to Sanyam Singhal for the report.
  - Partial fix for MySQL subpartitioning export.
  - Fix partitions export for MySQL. Thanks to Sanyam Singhal for the report.
  - Fix generation of export_all.sh following the operating system.
  - Add information of use of PARALLEL_TABLES with COPY, INSERT and TEST_DATA
    actions. It is also useful with TEST, TEST_COUNT, and SHOW_TABLE if
    --count_rows is used for real row count.
  - Prevent calling real rows count twice with TEST action, and allow it for
    the SHOW_TABLE action.
  - Handle count errors when single process.
  - Move row count wait for all child die to the right place
  - Fix rewrite of nested replace() functions in CHECK constraint. Thanks to
    Menelaos Perdikeas for the report.
  - Fix call of procedures with out parameters when it is not declared in a
    package. Thanks to taptarap for the report.
  - Some minor code improvement. Thanks to Markus Elfring for the patch.
  - Set encoding to read configuration file to utf8.
  - Remove useless multiple semi-colon after END of a function.
  - Fix conversion of regexp_replace() by always appending the 'g' modifier.
    Thanks to Rui Pereira for the report.
  - Fix synonym detection to avoid listing public synonym when no schema is
    specified. Thanks to Dilan Salinda for the report.
  - Fix regexp error with multi-line comment in default value declaration. Thanks
    to taptarap for the report.
  - Add missing sub-partition key in partitioned table primary key. Thanks to
    downvoteit for the report.
  - Replace all invalid date starting with zero year 0000- to 1970-01-01 when it
    is a default value and NULL for data. Old behavior was to only replace
    0000-00-00 date. Thanks to duursma for the report.
  - Enclose \i path to data file beween quote to fix import of table with space
    in their name.
  - Add PARTITION to the list of reserved work and fix custom keywords list from
    ORA_RESERVED_WORDS that was not applied. Thanks to markhooper99 for the
    report.
  - Add LOAD of pgtt extension before creating global temporary table with TABLE
    :export. Thanks to duursma for the report.
  - Fix case where package names should be lower cased. Thanks to Sergey Petrov
    for the patch.
  - Cover more case where ALTER ... OWNER TO should not be generated.
  - Fix case where ALTER ... OWNER TO should not be generated when a view as
    table definition was not exported.
  - Fix sub-partition unique and primary keys that lacks columns part of the
    partition key.  Thanks to downvoteit for the report.
  - Path for function_per_file are mixed case enabled now. Thanks to Sergey
    Petrov for the patch.
  - Fix AUTOINCREMENT script to set last value to sequences for serial an
    identity column for PG version < 12. Thanks to Jaouad Bouras for the report.
  - Fix detection of ENUM data type for MySQL
  - Fix issue when exporting table with a geometry column. The search for
    the SDO_GTYPE need a FROM clause with a FQDN table when the connection
    user is not the same as the table schema. Thanks to Argo64 for the report.
  - Rewrite numeric operation with ADD_MONTH(), LAST_DAY() and TRUNC() to use
    interval. Thanks to duursma for the report.
  - Fix rewrite of CONNECT BY in cursors and just after a BEGIN. Thanks to
    taptarap for the report.
  - Add partition keys to unique index on partitioned table. Thanks to
    downvoteit for the report.
  - Fix case where global variable are tested against NULL. Thanks to duursma
    for the report.
  - Fix remove of %ROWTYPE in function argument and returned data type. Add
    regression test.  Thanks to Eric Delanoe for the report.
  - Fix case clause in autoincrement parameters. Thanks to jbouras for the
    report.
  - Fix typo in ORACLE_FDW_TRANSFORM example.
  - Fix progress bar output in quiet mode. Thanks to Sanyam Singhal for the
    report.
  - Fix error Can't locate object method "gzclose" via package "IO::File".
    Thanks to Sanyam Singhal for the report.
  - Fix cases where translation of function with out parameter was not done
    correctly.
  - Fix translation of function with out parameter that returns a value. For
    example the following Oracle function:
    
      CREATE FUNCTION foo(a int, OUT b int)
      RETURN boolean IS
      BEGIN
        b := a;
        RETURN true;
      END;
    
    is now translated by adding an extra out parameter for the return value:
    
      CREATE OR REPLACE FUNCTION foo (a integer, OUT b integer,
							OUT extra_param boolean)
      RETURNS record AS $body$
      BEGIN
        b := a;
        extra_param := true;
        RETURN;
      END;
      $body$ LANGUAGE PLPGSQL STABLE;
    
    Thanks to Akhil Reddy for the report.
  - Fix undefined database connection handle. Thanks to Alexander for the report
  - Fix case preservation for row count in Oracle side with TEST_COUNT action..
    Thanks to Veka for the report.
  - Only generate the Powershell script when we are running on a Windows
    operating system.
  - Fix #1400 and generate PowerShell script "export_schema.ps1". Thanks to
    moh-hassan for the report.
  - Fix rewriting assignment of a global variable using SELECT INTO. Thanks to
    duursma for the report.
  - Fix partition export for MySQL. Thanks to Sanyam Singhal for the report.
  - Apply WHERE clause to FDW data export.
  - Fix useless ST geometry parsing. Thanks to jieguolove for the report.
  - Replace backslash with slash in BFILE filename when destination data type is
    text or efile.
  - Fix RAW(16)/RAW(32) data export when MOFDIFY_TYPE is used on the column.
    Thanks to Sergey Evseev for the report.
  - Fix ST_SRID() call. Thanks to jieguolove for the report.
  - Skip table data export when the table has no column defined. This was
    generating a fatal error.
  - Fix untranslated function returned data type when there was a comment just
    after. The comment is removed. Thanks to taptarap for the report.
  - Fix other fetching all-column-all-table properties for every table.
    Thanks to Sergey Petrov for the report.
  - Fix fetching all-column-all-table properties for every table. Thanks to
    Sergey Petrov for the report.
  - Remove any comments between RETURN and returned type to not break parsing.
    Thanks to taptarap for the report.
  - Fix global variables in the DECLARE section are not replaced if used with
    a package name. Thanks to taptarap for the report.
  - Remove renaming of dist configuration file under Windows OS. Thanks to
    Julien Monticolo and ohamed Hassan for the report.
  - Fix remaining data export query failure. Thanks to Sung Woo Chang for the
    report.
  - Fix data export, query to retrieve data was broken since change for GTT.
  - Set function as VOLATILE when there is CALL in the body.
  - Add support to PG14 procedure out parameters. Thanks to Rui Pereira for the
    feature request.
  - Fix missing parenthesis in index column expression with input file.
  - Fix missing END keyword after embedded CASE clause in a package function.
    Thanks to taptarap for the report.
  - Fix conversion of dbms_lob.substr() where second and third parameters
    must be inverted. Thanks to taptarap for the report.
  - Fix an other case of wronf NOT NULL detection from input file.
  - Fix detection of NOT NULL constraint in input file.
  - Do not quit on error "Undefined subroutine &Ora2Pg::ReadLine", just
    continue to be able to leverage an Oracle Wallet (SEPS) when no Oracle
    user and password are provided. If you want to use the interactive mode
    to type the username and password at command line you must install the
    Perl package Term::ReadKey before. Thanks to Simon Pane for the report.
  - Fix partitioning by LIST, only the first value of a list was exported.
    Thanks to Sergey Grinko for the report.
  - Fix quoting of DEFAULT NULL. Thanks to Veka for the report.
  - Fix unwanted multiple CALL keywords. Thanks to taptarap for the report.
  - Add assessment counter for FND_* packages.
  - Fix LONG RAW export as bytea in COPY mode. Thanks to Helena Adiduyulmus for
    the report.
  - Add new configuration option NO_EXCLUDED_TABLE.    
    By default Ora2Pg exclude from export some Oracle "garbage" tables that
    should never be part of an export. This behavior generates a lot of
    REGEXP_LIKE expressions which are slowing down the export when looking at
    tables. To disable this behavior enable this directive, you will have to
    exclude or clean up later by yourself the unwanted tables. The regexp used
    to exclude tables are defined in the array @EXCLUDED_TABLES in lib/Ora2Pg.pm
    This behavior is independent to the EXCLUDE configuration directive. Thanks
    to Peter Humaj for the feature request.
  - Replace all remaining CURSORNAME%NOTFOUND with NOT FOUND
  - Change translation to SYSDATE from LOCALTIMESTAMP to statement_timestamp()
    in non PL/SQL code.
  - Prevent append of SECURITY DEFINER when a procedure execute transaction
    control statements (ex: COMMIT). When defined with this clause an error
    is thrown. Thanks to Suman Michael for the report.

Version 23.1

View release

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.

Version 23.0

View release

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

Version 22.1

View release

2021 07 02 - v22.1

This is a maintenance release to extend the feature of data export through the oracle_fdw PostgreSQL extension to migration that use the public schema and do not preserve case.

There is also some other fixes:

Fix compile_schema() call that breaks valid function based indexes by adding compile_all => FALSE to DBMS_UTILITY.compile_schema(). Thanks to Pawel Fengler for the patch.
Force foreign table for data export as readonly to avoid accidental write if import schema is not cleaned.
Fix data export to file not possible since last changes for oracle_fdw export. Thanks to Niels Jespersen for the report.

Version 22.0

View release

2021 06 26 - v22.0

This release fix several issues reported since past three months and adds some new features and improvements. I must thanks MigOps Inc who hire me to drive Oracle to PostgreSQL migrations and to develop Ora2Pg. It's been a long time that I was looking for such a company and it is an amazing gift for the 20 years of Ora2Pg. All improvements and new new features developed during my work at MigOps will be available in the public GitHub repository, here are the new ones.

Add export of data using oracle_fdw when FDW_SERVER is set and export type is COPY or INSERT. Multi-process using -P or -J is fully supported but option -j is useless in this case. Boolean transformation of some columns or data type is also supported. Actually, expect that it works just like data migration without oracle_fdw. This can improve the data migration speed from 30 to 40% especially for BLOB export.
Improve export performances with huge number of objects by avoiding join between Oracle catalog tables.
Set a maximum of assessment score for tables, indexes, sequences, partitions, global temporary table and synonym following the number of objects.
Add detection of XML functions to the assessment cost.
Allow to change the assessment cost unit value in the export_all.sh script when ora2pg is used with options --init_project and --cost_unit_value.
Remove pragma restrict_references from P/PSQL code, it is useless.
Add the oracle schema to search_path in SQL files generated and improve the migration assessment when USE_ORAFCE is enabled.
Apply ALLOW and EXCLUDED filtered stored procedures at package extraction level. Previous this patch there was no way to not export some package functions or to exclude them from assessment.
Add new tests to check sequences last values and number of identity columns in both side.
Apply ALLOW/EXCLUDE without object to table object by default in TEST action.

New configuration directives:

Add ORACLE_FDW_TRANSFORM configuration directive to apply a transformation to a column when exporting data. Value must be a semicolon separated list of TABLE[COLUMN_NAME, >replace code in SELECT target list<] For example to replace string 'Oracle' by 'PostgreSQL' in a varchar2 column use the following. ERROR_LOG[DBMS_TYPE:regexp_replace("DBMS_TYPE",'Oracle','PostgreSQL')]
Add DROP_IF_EXISTS configuration directive to add a statement "DROP >OBJECT< IF EXISTS" before creating the object. Can be useful in an iterative work. Default is disabled.

Backward compatibility:

There is a backward compatibility issue with old configuration files where FDW_SERVER is set by default. This directive was not used when exporting data, this is not the case anymore as it instruct Ora2Pg to use the given foreign server to use oracle_fdw to migrate the data.

Note that Ora2Pg 22.0 needs that PRESERVE_CASE and EXPORT_SCHEMA be enabled to be able to use data export through oracle_fdw. Short coming release 22.1 will allow the use of oracle_fdw without preserving case and schema export.

Here is the full list of changes and acknowledgements:

  - Fix replacement of TO_CLOB() function, now it is just removed and the
    parenthesis are kept. Thanks to Rui Pereira for the report.
  - Fix incorrect detection of cursor on dynamic query. Thanks to Rui
    Pereira for the report.
  - Fix quoting column names with spaces and dots. Dots are replaced by
    underscore. Thanks to Veka for the report.
  - Fix one case where DEFINED_PKEY with PRESERVE_CASE was not handled
    correctly. Thanks to Veka for the report.
  - Fix quoting of reserved keywords in CREATE INDEX columns names. Thanks
    to Veka for the report.
  - Fix column name starting with number not quoted in COMMENT. Thanks to
    Veka for the report.
  - Fix addition of PERFORM on call to stored procedures not prefixed by
    the package name. Thanks to Rui Pereira fo the report.
  - Fix search of ora2pg_conf.dist under Windows instead of ora2pg.conf.dist
    when --init_project is used. Thanks to Julien Monticolo for the report.
  - Fix translation from file of check constraint when created on same column,
    only the last one was exported. Also shortened the prefix for constraint
    naming, ora2pg_ckey becomes o2pc, ora2pg_ukey is now o2pu and ora2pg_fkey
    is renamed into o2pf. Thanks to anvithaprabhu8 for the report.
  - Replace wildcard precision * for numeric by 38.
  - Fix incomplete listagg() conversion. Thanks to avandras for the report.
  - Fix potential problem in last_day conversion when USE_ORAFCE is off and
    a number is added or subtracted to the last day. Thanks to atlterry for
    the report.
  - Add DROP_IF_EXISTS configuration directive. Thanks to dherzhau for the feature request.
  - Do not apply utf8 conversion of comments to input files.
  - Fix termination of last writer process when parallel and quiet mode are
    used together. Thanks to David Harper for the patch.
  - Remove precision in number of digit in timestamp microseconds when
    setting NLS_TIMESTAMP_FORMAT at session startup. Now use:
      ALTER SESSION SET NLS_TIMESTAMP_FORMAT='YYYY-MM-DD HH24:MI:SS.FF'
    instead of '.FF6'. This was possibly the cause of sporadic error:
      ORA-03106: fatal two-task communication protocol error (DBD ERROR: OCIStmtFetch)
    Default is precision 6 so it may not change anything. Thanks to
    Abhijeet Bonde for the report.
  - Fix call to deprecated MySQL spatial function when version is after 5.7.6.
    Thanks to naveenjul29 for the report.
  - Fix false positive detection for nested table with MySQL export.
  - Fix sequence export read from file.
  - Add PG_VERSION to the documentation. Thanks to xinjirufen for the report.
  - Fix error report when preparing query to ALL_IND_COLUMNS IC. Thanks to
    ganeshakorde for the report.
  - ora2pg_scanner: Fix detection of service_name in DSN.
  - Fix error raise_application_error() with named parameters.
  - Fix replacement of out parameters in triggers.
  - Change sequence export result storage to hash instead of array.
  - Fix package export when there is a comment between PACKAGE BODY and the
    name of the package.
  - Fix Perl error "malformed utf-8 character in substitution" when there is
    character not in utf8 in the comment or constants.
  - Fix detection of MySQL FUNCTION vs PROCEDURE for version >= 5.5. Thanks
    to naveenjul29 for the report.
  - Exclude nested tables from the export as it is not supported and it
    always generate an error. A warning is raised.
  - Fix column case in check constraints when PRESERVE_CASE is enabled.
  - Fix search_path in direct PG data export when PG_SCHEMA is set.
  - Fix -Infinity insert for direct PG data export.
  - Fix drop indexes when PRESERVE_CASE is enabled.
  - Remove potential double affectation for function with out parameter.
  - Create immutable to_char function when used in an index.
  - Replace dmake by gmake on Windows installation instruction. Thanks to
    Julien Monticolo for the report.
  - Fix MySQL version conditions. Thanks to Christoph Berg for the report.
  - Fix HASH partitioning for duplicate WITH clause.
  - Fix tests when no schema are set to compare all objects in all schemes.
    Thanks to gp4git and dlc75 for the report.
  - Apply ALLOW/EXCLUDE without object to table object by default in TEST
    action. Thanks to Yony Sade for the feature request.
  - Add DROP_IF_EXISTS configuration directive. Thanks to dherzhau for the
    feature request.
  - Fix regression in removing %ROWTYPE from function parameters. Thanks to
    Eric Delanoe for the report.
  - Fix not adding default values to parameters when this is an OUT parameter.
    Thanks to Eric Delanoe for the report.
  - Fix ALL_DIRECTORIES call be using table name relative to USER_GRANTS.
    Thanks to Yoni Sade for the report.
  - Change all remaining call to static ALL_* tables to a call relative to
    USER_GRANTS. Thanks to Yoni Sade for the report.
  - Fix export or partitioned table with unsupported partitioning type
    like PARTITION BY REFERENCE. The table is created without partition
    and a warning it fired as well as the following message in the output
    file as a comment: "-- Unsupported partition type, please check"

Version 21.1

View release

2021 04 01 - v21.1

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

Now that Orafce 3.15.0 has a definition for the REGEXP_* function, makes the translation optional to USE_ORAFCE directive.
Add set application name in connection to Oracle/MySql/PostgreSQL.
Add translation of REGEXP_COUNT() and change assessment cost.
Rewrite the way REGEXP_LIKE() is translated into regexp_match to support modifiers. This rewrite also fix default behavior between Oracle and PostgreSQL.
Replace DBMS_LOB.GETLENGTH() by PostgreSQL octet_length() function.
Add types correspondences for VARCHAR2 and NVARCHAR2 in DATA_TYPE configuration directive.
Add autodetection and support of geometry type, srid and dimension for ArcGis geometries.
Add conversion of default value in function parameters.
Add -u | --unit option to ora2pg_scanner to be able to set the migration cost unit value globally.
Replace DBMS_LOB.SUBSTR() by SUBSTR()
Remove TO_CLOB() it is useless, manual cast could be necessary.
Replace IS JSON validation clause in CHECK constraints by (CASE WHEN $1::json IS NULL THEN true ELSE true END). When the code is invalid an error is fired.
DISTINCT and UNIQUE are synonym on Oracle.

Backward compatibility changes:

Force a column to be bigint if this is an identity column. Thanks to MigOps.com for the patch.
Fix EMPTY_LOB_NULL, enable/disable was inverted, keeping default to enabled. Take care that in old ora2pg.conf it is disabled so it will break backward compatibility with old configuration.
Replace NO_LOB_LOCATOR with USE_LOB_LOCATOR and NO_BLOB_EXPORT with ENABLE_BLOB_EXPORT to avoid confusion with double negative variable. Backward compatibility is preserved with a warning.
SRID for SDO_GEOMETRY export is now taken from the value not forced from the metadata table.

Here is the full list of changes and acknowledgements:

  - Take Geometry SRID from the data and fallback to SRID defined in
    metadata when not found. Thanks to Sebastian Albert for the report.
  - Fix case where Ora2Pg temporary substitution of '' by placeholder
    was not restored. Thanks to MigOps.com for the patch.
  - Fix identity column export on unsupported Oracle 18c options.
    Thanks to MigOps.com for the patch.
  - Fix export of columns indexes created with single quote.
    Thanks to MigOps.com for the patch.
  - Fix replacement of keyword PROCEDURE by FUNCTION in constraints
    constants definition. Thanks to marie-joechahine for the report.
  - Replace IS JSON validation clause in CHECK constraints. Thanks to
    marie-joechahine for the report and MigOps.com for the patch.
  - Add support to ON OVERFLOW clause in LISTAGG replacement.
    Thanks to MigOps.com for the patch.
  - Fix incorrect handling of HAVING+GROUP BY rewriting.
    Thanks to MigOps.com for the patch.
  - Add replacement of TO_NCHAR by a cast to varchar. Thanks to
    MigOps.com for the patch.
  - Fix replacement of NOTFOUND when there is extra space or new line
    in the WHEN EXIT clause. Thanks to MigOps.com for the patch.
  - Fix a regression in NO_VIEW_ORDERING, it was not taken in account
    anymore. Thanks to RonJojn2 for the report.
  - Replace DATA_TYPE with DTD_IDENTIFIER in MySQL catalog queries for
    version prior 5.5.0. Thanks to zejeanmi for the report.
  - Fix import script to import sequences before tables. Thanks to
    MigOps.com for the patch.
  - Fix detail report of custom type in migration assessment. Thanks
    to MigOps.com for the patch.
  - Fix duplicate schema prefixed to SYNONYM. Thanks to dlc75 for the
    reports.
  - Replace NO_LOB_LOCATOR with USE_LOB_LOCATOR and NO_BLOB_EXPORT with
    ENABLE_BLOB_EXPORT to avoid confusion with double negative variable.
    Thanks to Rob Johnson for the report.
  - Fix some missing replacements of NVL and rewrite !=-1 into != -1.
    Thanks to MigOps.com for the patch.
  - Fix ROWNUM followed by + or - operator and when no aliases are
    provided. Thanks to MigOps.com for the patch.
  - Add DBSFWUSER to the list of user/schema exclusion. Thanks to
    MigOps.com for the patch.
  - Fix regexp to not append subquery aliases on JOIN clause. Thanks
    to Rui Pereira for the report.
  - Handle PRESERVE_CASE and EXPORT_SCHEMA in sequence name. Thanks
    to marie-joechahine for the report.
  - Add CREATE SCHEMA statement to sequence export when EXPORT_SCHEMA
    is enabled. Thanks to marie-joechahine for the report.
  - Fix duplicate index name on subpartition. Thanks to Philippe
    Beaudoin for the report.
  - Exclude sequences used for IDENTITY column (ISEQ$$_). Thanks to
    marie-joechahine for the report.
  - Fix parsing from file of CREATE SEQUENCE. Thanks to Rui Pereira
    for the report.
  - In export_all.sh script use the database owner provided if it is a
    superuser instead of postgres user. Thanks to jjune235 for the
    feature request.
  - Fix parsing of triggers when there is a CASE inside the code.
    Thanks to Rui Pereira for the report.
  - Add set application name in connection to Oracle/MySql/PostgreSQL.
    Thanks to Yoni Sade for the patch.
  - Fix double column alias when replacing ROWNUM. Thanks to Rui
    Pereira for the report.
  - Add translation of the REGEXP_COUNT function and change assessment
    cost.
  - Rewrite the way REGEXP_LIKE is translated into regexp_match to
    support modifiers. This rewrite also fix default behavior between
    Oracle and PostgreSQL.  Thanks to otterrisk for the report.
  - Add IS JSON to assessment. Thanks to marie-joe Chahine for the
    report.
  - Fix multi-columns RANGE partitioning. Thanks to Philippe Beaudoin
    for the report.
  - Improve reordering columns. Sort by fieldsize first, if same size
    then it sorts by original position. Thanks to Sebastien Caunes for
    the patch.
  - Append partition's column to the primary key of the table as it
    must be part of the PK on PostgreSQL. Thanks to xinjirufen for the
    report.
  - Fix partition export where PRESERVE_CASE was applied to Oracle
    side. Thanks to schleb1309 for the report.
  - Fix trigger export with column restriction. Thanks to Sebastien
    Caunes for the report.
  - Update installation information.
  - Fix table reordering following data type. Thanks to Sebastien
    Caunes for the patch.
  - Fix incorrect variable name corresponding to DATA_EXPORT_ORDER
    making this directive inefficient. Thanks to Ron Johnson for the
    report.
  - Fix translation of check constraint when read from file
  - Fix EMPTY_LOB_NULL, enable/disable as inverted, keep default to
    enabled. Take care that in old ora2pg.conf it is disabled so it
    will break backward compatibility with old configuration.
  - Fix false positive detection of input filename is the same as
    output file.
  - Rename variables SCHEMA_ONLY, DATA_ONLY and CONSTRAINTS_ONLY in
    script import_all.sh to conform to their real use. Thanks to
    Sebastien Caunes for the report.
  - Fix comment detection breaking the package header parsing and
    global variable detection.
  - Fix ROWNUM detection for replacement by LIMIT
  - Fix escaping of psql command in configuration file comment and
    set default value for PG_VERSION to 12.
  - Replace precision by exactness in documentation. Thanks to
    Sebastien Caunes for the report.
  - Prevent reducing DATA_LIMIT when NO_BLOB_EXPORT is enabled.
   Thanks to Thomas Reiss for the report.
  - Fix geometry type detection.
  - Add autodetection of geometry type, srid and dimension for
    ArcGis geometries. Thanks to changmao01 for the feature request.
  - Fix call to ST_GeomFromText when no SRID is found.
  - Fix case where OVERRIDE SYSTEM VALUE clause could be added if PG
    version is < 10. Thanks to changmao01 for the report.
  - Fix unwanted call to internal GEOM library for ArcGis geometries.
    Thanks to changmao01 for the report.
  - Exclude schema SDE (ArGis) from export. Thanks to changmao01 for
    the report.
  - prevent looking twice to same custom data type definition.
  - Fix previous patch to catch SDO_GEOMETRY on lowercase regexp.
  - Limit detection of geometry data type to SDO_GEOMETRY.
  - Fix column name replacement in view definition. Thanks to Amit
    Sanghvi for the report.
  - Fix REPLACE_COLS parsing to allow space in column name. Thanks
    to Amit Sanghvi for the report.
  - Fix translation from file of triggers with WHEN clause. Thanks
    to Rui Pereira for the report.
  - Fix column name kept lowercase in the MOD() clause when -J is
    used. Thanks to Code-UV for the report.
  - Keep case of PG_SCHEMA definition when used in TEST action.
  - Fix data export for columns with custom data type. Thanks to
    Aymen Zaiter for the report.
  - Fix missing bracket with || operator in CREATE INDEX. Thanks to
    traxverlis for the report.
  - Fix export of single row unique function base index. Example:
      CREATE UNIQUE INDEX single_row_idx ON single_row ((1));
    Thanks to unrandom123 for the report.
  - Update documentation about schemas used in TEST action.
  - Disable materialized view export with MySQL export it is not
    supported. Thanks to naveenjul29 for the report.
  - Fix table alias detection in Oracle (+) join rewrite.
  - Fix an infinite loop in Oracle (+) join rewrite when there is no
    table aliases and the table is prefixed by its schema. Thanks to
    Olivier Picavet for the report.
  - Fix MODIFY_STRUCT when column name need to be escaped. Thanks to
    helmichamsi10 for the report.
  - Fix empty PARTITION BY () clause. Thanks to Aymen Zaiter.
  - Fix export of global variable from package description when there
    is no package body. Thanks to naveenjul29 for the report.
  - Add package description export when dumping package source,
    previously only the package body was dump. This will allow to
    check global variables export.
  - Whilst working on the Reproducible Builds effort (https//reproducible-builds.org/)
    it appears that ora2pg could not be built reproducibly. Thanks to
    Chris Lamb for the patch.
  - Fix case of NUMBER(*,10) declaration. Oracle has a precision of 1 to 38
    for numeric. Even if PostgreSQL allow a precision of 1000 use 38 to
    replace junk parameter. Thanks to xinjirufen for the report.
  - Add conversion of default value in function parameters, like syssdate
    rewriting for example. Thanks to unrandom123 for the report.
  - Fix a regression in data encoding when exporting data introduced in
    commit fa8e9de. Thanks to gp4git for the report.
  - Add debug information about the environment variables used before
    connecting to Oracle.
  - Fix case of duplicate between unique index and unique constraint with
    multiple columns. Thanks to gp4git.

Version 21.0

View release

2020 10 12 - v21.0

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

New features, options and configuration directives in this release:

Add clause OVERRIDING SYSTEM VALUE to INSERT statements when the table has an IDENTITY column.
Considerably increase the speed to generate the report about the migration assessment, especially for database with huge number of objects.
Reduce time passed in the progress bar. Following the number of database objects we were spending too much time in refreshing the progress bar.
Add number of identity columns in migration assessment report.
Make assessment details report initially hidden using HTML5 tags details+summary.
Improve speed of BLOB/CLOB data export. Oracle recommends reading from and writing to a LOB in batches using a multiple of the LOB chunk size. This chunk size defaults to 8k (8192). Recent tests show that the best performances can be reach with higher value like 512K or 4Mb.
Add progress bar when --oracle_speed is use in single process mode.
Automatically activate USER_GRANTS when the connection user has no DBA privilege. A warning is displayed.
Complete port to Windows by using the Windows separator on stdout redirection into a file at ora2pg command line call and improve ora2pg_scanner port on Windows OS.
Add rewrite of MySQL JOIN with WHERE clause instead of ON.
Add MGDSYS (Oracle E-Business Suite) and APEX_040000 to the list of schemas excluded from the export.
Supply credentials interactively when a password is not defined in the configuration file. Need the installation of a new Perl module Term::ReadKey.
Add supports oracle connections "as sysdba" with username "/" and an empty password to connect to a local oracle instance.
Add translation of PRIVATE TEMPORARY TABLE from Oracle 18c into PostgreSQL basic temporary table, only the default behavior for on commit change.

New command line options:

Add new command line option to ora2pg_scanner: -b | --binpath DIR to set the full path to directory where the ora2pg binary stays. Might be useful only on Windows OS.
Add -r | --relative command line option and PSQL_RELATIVE_PATH configuration directive. By default Ora2Pg use \i psql command to execute generated SQL files if you want to use a relative path following the script execution file enabling this option will use \ir. See psql help for more information.

New configuration directives:

NO_VIEW_ORDERING: By default Ora2Pg try to order views to avoid error at import time with nested views. With a huge number of views this can take a very long time, you can bypass this ordering by enabling this directive.
NO_FUNCTION_METADATA Force Ora2Pg to not look for function declaration. Note that this will prevent Ora2Pg to rewrite function replacement call if needed. Do not enable it unless looking forward at function breaks other export.
LOB_CHUNK_SIZE See explanation in the new features and improvement list.
ALTERNATIVE_QUOTING_REGEXP To support the Alternative Quoting Mechanism ('Q' or 'q') for String Literals set the regexp with the text capture to use to extract the text part. For example with a variable declared as c_sample VARCHAR2(100 CHAR) := q'{This doesn't work.}'; the regexp to use must be: ALTERNATIVE_QUOTING_REGEXP q'{(.*)}' ora2pg will use the $$ delimiter, with the example the result will be: c_sample varchar(100) := $$This doesn't work.$$; The value of this configuration directive can be a list of regexp separated by a semi colon. The capture part (between parenthesis) is mandatory in each regexp if you want to restore the string constant.

Backward compatibility changes:

Default for NO_LOB_LOCATOR is now 1 to benefit from the LOB_CHUNK_SIZE performances gain.
Enable schema compilation (COMPILE_SCHEMA set to 1) by default to speed up DDL extraction.
Change the behavior of Ora2Pg with the parameters that follows a parameter with a default value. Ora2Pg used to change the order of the parameter's function to put all parameters with a default value at end of the list which need a function call rewrite. This have been abandoned now any parameter without default value after a parameter with a default value will be appended DEFAULT NULL.

Here is the full list of changes and acknowledgements:

  - Fix unwanted references to PK/UK when DROP_INDEXES is enabled.
  - Fix comparison between function name in TEST report.
  - Fix duplicates on retrieving partitions information.
  - Improve SHOW_TABLE report about partitioned tables information.
  - Drop code about removing DEFAULT NULL in functions parameters. Thanks to
    chaluvadi286 for the report.
  - Fix two other case where materialized view can be listed in the table list.
  - Fix case where materialized view can be listed in the table list. Thanks
    to Thomas Reiss for the report.
  - Fix %ROWTYPE removing to be restricted to REF CURSOR. Thanks to
    jagmohankaintura-tl for the report.
  - Fix PG functions count when comparing Oracle functions count in TEST action.
    Remove useless -l option to import_all.sh auto generated script.
  - Fix PRESERVE_CASE on schema name for functions extracted from a package.
  - Fix search_path adding public default schema.
  - Apply PRESERVE_CASE to partition by involved columns.
  - Add IF EXIXTS to create schema to avoid error when import_all.sh is run
    several time.
  - Fix sort order of comment on columns for tables and views.
  - Fix warning about data export from nonexistent table resulting of index
    lookup on nested table.
  - Fix infinite loop in global variables package extraction. Thanks to Thomas
    Reiss for the report.
  - Fix global variables and packages export when comments are present in the
    package description.
  - Add information about XML_PRETTY size limit to 4000
  - Fix column name in indexes when PRESERVE_CASE is enabled. Thanks 
    to Julien traxverlis for the report.
  - Fix Top 10 of largest tables sort order. Thanks to Tom Vanzieleghem
    for the patch.
  - Fix duplicates between indexes and constraints. Thanks to sdpdb and
    Jon Betts for the report.
  - Fix SYSDATE replacement and possible infinite loop in SYSDATE parsing.
    Thanks to pbidault for the report.
  - Fix export of Oracle TEXT indexes with USE_UNACCENT disabled. Thanks to
    Eric Delanoe for the report.
  - Add new configuration directive ALTERNATIVE_QUOTING_REGEXP to support
    the Alternative Quoting Mechanism ('Q' or 'q') for String Literals.
    Thanks to just-doit for the report.
  - Fix OF clause missing in update triggers. Thanks to just-doit for
    the report.
  - Fix IS NULL translation in WHERE clause of UPDATE statement. Thanks
    to Eric Delanoe for the report.
  - Remove DDL export of LOG indexes on materialized views.
  - Fix unexpected materialized view listed in table export. Thanks to
    jagmohankaintura-tl for the report.
  - Fix default values with single quote in create table DDL. Thanks to
    justdoit for the report.
  - Fix double quote in CREATE TRIGGER code and applying of preserve case
    on column name.
  - Supply credentials interactively when a password is not defined in
    configuration file. Thanks to rpeiremans for the patch.
  - Add supports oracle connections "as sysdba" with username "/" and
    an empty password to connect to a local oracle instance. Thanks to
    rpeiremans for the patch.
  - Fix documentation about materialized view export.
  - Fix export order of comments on columns.
  - Fix export of views comments when no schema is used for export and
    export schema is activated.
  - Fix cast in replacement with TO_NUMBER and TO_CHAR in indexes. Thanks
    to Kiran for the report.
  - Add MGDSYS (Oracle E-Business Suite) to the list of schemas excluded
    from the export. Thanks to naveenjul29 for the report.
  - Add more information about PG_DSN use. Thanks to Pepan7 for the report.
  - Update copyright year.
  - Fix regression where "SET client_encoding TO ..." was missing data file
    header. Thanks to Emmanuel Gaultier for the report.
  - Fix EDITABLE vs EDITIONABLE parsing. Thanks to Naveen Kumar for the report.
  - Fix typos in documentation. Thanks to swallow-life, ChrisYuan, Edward Betts,
    Jack Caperon and cavpollo for the patches.
  - Add OVERRIDING SYSTEM VALUE to INSERT statement when the table has an
    IDENTITY column. Thanks to Robin Windey for the report
  - Remove empty parenthesis of identity column options
  - Limit sequence/identity column value to bigint max
  - Add an example of DBD::Oracle DSN with 18c.
  - Fix parsing of identity column from file. Thanks to deepakp555 for the
    report.
  - Fix quoting of identifier when PRESERVE_CASE is enable and no particular
    schema is specified. Thanks to mkgrgis for the report.
  - Move setting of search_path before truncate table. Thanks to Michael Vitale
    for the report.
  - Add explanation about TEST and SIZE migration assessment values.
  - Mark XMLTYPE as having LOB locator.
  - Fix XMLTYPE columns that are exported as lob locator. Thanks to Tamas for
    the report.
  - Fix a problem of data export throughput that was slowing down all along
    the export when multiprocess for output was not used. Ora2Pg was forking
    a process for each chunk of data (see DATA_LIMIT) which is useless when
    write output is done on a single process (-j 1) and slow down the export.
    Thanks to markhooper99 and Tamas for reporting, testing and finding the
    source of the issue.
  - Fix progress bar in multiprocess mode, update was not displayed at each
    chunk of data processed.
  - Add internal debug information for progress bar.
  - Add debug information for SHOW_REPORT
  - Fix a long pending issue with custom data type export. Thanks to
    jhollandsworth for the patch.
  - Fix LOB data export with value changed to NULL when the CLOB value was 0.
    Thanks to jhollandsworth for the report.
  - Fix escape format issue with COPY and bytea. Thanks to Christoph Noel and
    dwbrock62 for the report.
  - Add LD_LIBRARY_PATH and PATH prerequisite to run ora2pg.
  - Fix use of the HIGH_VALUE column in partition listing with Oracle 9i. Thanks
    to Francisco Puga for the report.
  - Update the table row count logic to incorporate the PostgreSQL table FQN as
    established through the set_pg_relation_name routine. Thanks to Jacob
    Roberts for the patch.
  - Add the PostgreSQL FQN when printing the results in the TEST function. Thanks
    to Jacob Roberts for the patch.
  - Do not look forward function with the SHOW_* action
  - Fix BLOB export where \x was escaped. Thanks to Christophe Noel for the
    report.
  - Update Ora2Pg.pm to fix symbol in column name in create index statement.
    Thanks to kpoluektov for the patch.
  - Fix package function extraction when there is a start of comment (/*) in
    a constant string. Thanks to Tiago Anastacio for the report.
  - Fix type detection in package declaration. Thanks to Tiago Anastacio for
    the report.
  - Avoid displaying error ORA-22831 when exporting LOB. This error can
    appears when LOB chunk size is different from default 8192. The error
    has no incidence on the export so we can just ignore it. This patch
    also use DBD::Oracle ora_lob_chunk_size() method to gather chunk the
    chunk size of the LOB, fallback to 8192 if not available. Thanks to
    joedbadmin for the report.
  - Disable direct report of Oracle errors, all error should be handled at
    Ora2Pg level.
  - Fix MySQL data export with allow/exclude objects. Thanks to Manuel Pavy for
    the report.
  - Fix exclude/allow object feature in MySQL export that was not working since
    release 19.0. Thanks to Manuel Pavy for the report.
  - Add rewrite of MySQL JOIN with WHERE clause instead of ON. Thanks to Marc
    Rechte for the report.
  - Fix issue with custom type when multiprocess is used.
  - Fix progress bar on final total estimated data in multiprocess mode.
  - Fix ORACLE_HOME path in README.md. Thanks to Lubos Cisar for the patch.
  - Fix missing replacement with PERFORM in CASE ... WHEN statements. Thanks to
    Eric Delanoe for the report.
  - Fix duplicate ora2pg command in iteration.
  - Improve ora2pg_scanner port on Windows OS. Thanks to Marie Contencin for the
    report.
  - Add perl call to all ora2pg commands when the scanner is executed on
    Windows system as the shebang is not recognized. Thanks to Marie Contencin
    for the report.
  - Fix several issue with compressed output. Thanks to Bach Nga for the report.
  - Fix translation of CURSOR IS SELECT with a comment before the SELECT.
    Thanks to Izaak van Niekerk for the report.
  - Fix export of procedures as PostgreSQL procedures with version 11.
  - Add APEX_040000 to the schemas exclusion list.  Thanks to Don Seiler for the
    report.
  - Fix possible unquoted default values. Thanks to Marc Rechte for the report.
  - Fix MySQL SET TRANSACTION clause when TRANSACTION is set to readonly or
    readwrite this is not supported so fall back in READ COMMITTED isolation
    level in this case. Thanks to Marc Rechte for the report.
  - Fix export of functions, column DATA_TYPE does not exists in table
    INFORMATION_SCHEMA.ROUTINES before MySQL 5.5.0. Replace it with column
    DTD_IDENTIFIER for prior version. Thanks to Marc Rechte for the report.
  - Fix double quote in CREATE TRIGGER code and applying of preserve case on
    column name.

Version 20.0

View release

2019 01 18 - v20.0

This release fix several issues reported during the last three months and adds several new features and improvement. The change of major version is related to backward compatibility break with the removed of most PG_SUPPORTS_* configuration directives and their replacement with the new PG_VERSION directive.

The release adds some new features and configuration directives:

  * Add PG_VERSION configuration directive to set the PostgreSQL major
    version number of the target database. Ex: 9.6 or 10. Default is
    current major version at time of a new release. This replace the
    old PG_SUPPORTS_* configuration directives.
  * Removed all PG_SUPPORTS_* configuration directives minus
    PG_SUPPORTS_SUBSTR that is related to Redshift engine.
  * Export of BFILE as bytea is now done through a PL/SQL function to
    extract the content of a BFILE and generate a bytea data suitable
    for insert or copy into PostgreSQL.
  * Foreign keys that reference a partitioned table are no more
    exported.
  * Show table name on Oracle side during export using at connection
    time: DBMS_APPLICATION_INFO.SET_ACTION(table_name);
  * When the date format is ISO and the value is a constant the call
    to to_date() is removed and only the constant is preserved. For
    example: to_date(' 2013-04-01 00:00:00','SYYYY-MM-DD HH24:MI:SS')
    is replaced by a simple call to: ' 2013-04-01 00:00:00'.
    This rewrite is limited to PARTITION export type when directive
    PG_SUPPORTS_PARTITION is enabled.
  * Add DATA_EXPORT_ORDER configuration directive. By default data
    export order will be done by sorting on table name. If you have
    huge tables at end of alphabetic order and are using multiprocess,
    it can be better to set the sort order on size so that multiple
    small tables can be processed before the largest tables finish.
    In this case set this directive to size. Possible values are name
    and size. Note that export type SHOW_TABLE and SHOW_COLUMN will
    use this sort order too, not only COPY or INSERT export type.
  * Add NO_BLOB_EXPORT configuration directive. Exporting BLOB could
    take time and you may want to export all data except the BLOB
    columns. In this case enable this directive and the BLOB columns
    will not be included into data export. The BLOB column must not
    have a NOT NULL constraint. Thanks to Ilya Vladimirovich for the
  * Add PREFIX_SUB_PARTITION to enable/disable sub-partitioning table
    prefixing in case of the partition names are a part of the sub-
    partition names.
  * Add special replacement for case of epoch syntax in Oracle:
        (sysdate - to_date('01-Jan-1970', 'dd-Mon-yyyy'))*24*60*60
    is replaced by the PostgreSQL equivalent:
        (extract(epoch from now()))

Here is the full list of changes and acknowledgements:

  - Export indexes and constraints on partitioned table with pg >= 11.
  - Fix incorrect replacement of NLS_SORT in indexes.
  - Bring back DISABLE_UNLOGGED feature. Thanks to Jean-Christophe
    Arnu for the patch
  - Fix CREATE SCHEMA statement that was not written to dump file.
  - Fix DBMS_APPLICATION_INFO.set_action() call, old Oracle version
    do not support named parameters.
  - Fix duplicate index name on partition. Thanks to buragaddapavan
    for the report.
  - Add support to new configuration directive PG_VERSION to control
    the behavior of Ora2Pg following PostgreSQL version.
  - Fix error in creation of default partition with PostgreSQL 10.
    Thanks to buragaddapavan for the report.
  - Fix missing export of single MAXVALUE partition, this will produce
    the following range partition: ... FOR VALUES FROM (MINVALUE) TO
    (MAXVALUE) Previous behavior was to not export partition as it is
    better to not partition the table at all. However it is declared
    in Oracle so it is better to export it to see what can be done.
    Thanks to buragaddapavan for the report.
  - Do not export foreign keys that reference a partitioned table.
    Remove NOT VALID on foreign keys defined on a partitioned
    table if present. Thanks to Denis Oleynikov for the report.
  - Fix export of BFILE as bytea. Ora2Pg now use a PL/SQL function to
    extract the content of a BFILE and generate a bytea data suitable
    for insert or copy into PostgreSQL. Thanks to RickyTR for the
    report.
  - Add TIMEZONE_REGION and TIMEZONE_ABBR to migration assessment, no
    direct equivalent in PostgreSQL. Remove NSLSORT not used in
    migration assessment. Thanks to buragaddapavan for the report.
  - Fix output of multiple export type specifed in TYPE directive.
  - Rewrite and renaming of _get_sql_data() function into
    _get_sql_statements().
  - Limit CURSOR weight in migration assessment to REF CURSOR only,
    other case are all covered. REF CURSOR might need a review to see
    if they need to be replaced with a SET OF RECORD.
  - Fix replacement of EMPTY_CLOB() or EMPTY_BLOB() with empty string
    when EMPTY_LOB_NULL is disabled and NULL when it is enabled.
  - Prefix output file with the export type in multiple export type
    mode, ex: sequence_output.sql or table_output.sql. Thanks to
    buragaddapavan for the report.
  - Fix export of data from an Oracle nested table. Thanks to rejo
    oommen for the report.
  - Removed cast to timestamp from partition range. Thanks to
    buragaddapavan and rejo-oommen for the report.
  - Fix partition default syntax. Thanks to rejo-oommen for the
    report.
  - Apply missing SYSUSERS schemas exclusion on columns and partition
    listing. Thanks to rejo-oommen for the report.
  - Add warning about parameter order change in output file.
  - Show table name on Oracle side during export using at connection
    time: DBMS_APPLICATION_INFO.SET_ACTION(table_name);
    Thanks to Denis Oleynikov for the feature request.
  - Report change in ORA_RESERVED_WORDS into documentation.
  - Add references in the keyword list of ORA_RESERVED_WORDS.
  - Fix the missing white space in some lines while creating
    import_all.sh file. Thanks to Fabiano for the patch.
  - Fix translation of infinity value for float. Thanks to Damien
    Trecu for the report.
  - Fix default value in timestamp column definition when a timezone
    is given. Thanks to buragaddapavan for the report.
  - Fix missing export of index and constraint in a partitioned
    table when DISABLE_PARTITION is enabled. Thanks to Denis Oleynikov
    for the report.
  - Prevent PARTITION BY when DISABLE_PARTITION is enabled. Thanks to
    Denis Oleynikov for the report.
  - Add DATA_EXPORT_ORDER configuration directive. By default data
    export order will be done by sorting on table name. If you have
    huge tables at end of alphabetic order and are using multiprocess,
    it can be better to set the sort order on size so that multiple
    small tables can be processed before the largest tables finish.
    In this case set this directive to size. Possible values are name
    and size. Note that export type SHOW_TABLE and SHOW_COLUMN will
    use this sort order too, not only COPY or INSERT export type.
    Thanks to Guy Browne for the feature request.
  - Fix remove leading ':' on Oracle variable taking care of regex
    character class. Thanks to jselbach for the report.
  - Add NO_BLOB_EXPORT configuration directive. Exporting BLOB could
    take time and you may want to export all data except the BLOB
    columns. In this case enable this directive and the BLOB columns
    will not be included into data export. The BLOB column must not
    have a NOT NULL constraint. Thanks to Ilya Vladimirovich for the
    feature request.
  - Fix incorrect rewrote of the first custom type in a row. Thanks
    to Francesco Loreti for the patch.
  - Remove double quote in type definition en set type name in lower
    case when PRESERVE_CASE is disabled.
  - Add PREFIX_SUB_PARTITION to enable/disable sub-partitioning table
    prefixing in case of the partition names are a part of the sub-
    partition names.
  - Fix epoch replacement case in CREATE TABLE statements.
  - Apply epoch replacement to default value in table declaration.
  - Add special replacement for case of epoch syntax in Oracle:
        (sysdate - to_date('01-Jan-1970', 'dd-Mon-yyyy'))*24*60*60
    is replaced by the PostgreSQL equivalent:
        (extract(epoch from now()))
    Thanks to rejo-oommen for the feature request.
  - A few typos in --help sections. Thanks to Christophe Courtois
    for the report.
  - Fix export of primary key on partition table. Thanks to chmanu
    for the patch.
  - Fix malformed user defined type export. Thanks to Francesco Loreti
    for the report.

Version 19.1

View release

2018 09 27 - v19.1

This release fix several issues reported during the last month and add support to PostgreSQL 11 HASH partitioning.

It also adds some new features and configuration directives:

  * Add export of default partition and default sub partition. 
  * Add export of HASH partition type.
  * Add support of stored procedure object.
  * Add replacement of NLSORT in indexes or queries. For example:
        CREATE INDEX test_idx ON emp (NLSSORT(emp_name, 'NLS_SORT=GERMAN'));
    is translated into
        CREATE INDEX test_idx ON emp ((emp_name collate "german"));
    The collation still need to be adapted, here probably "de_DE".
    NLSSORT() in ORDER BY clause are also translated.
  * Prevent duplicate index with primary key on partition to be
    exported.
  * PostgreSQL native partitioning does not allow direct import of
    data into already attached partitions. We now force direct import
    into main table but we keep Oracle export of data from individual

This release also adds two new command line options:

   --oracle_speed: use to know at which speed Oracle is able to send
                   data. No data will be processed or written written
   --ora2pg_speed: use to know at which speed Ora2Pg is able to send
                   transformed data. Nothing will be written

Use it for debugging purpose. They are useful to see Oracle speed to send data and at what speed Ora2Pg is processing the data without reaching disk or direct import into PostgreSQL.

Two new configuration directive has been added:

  * PG_SUPPORTS_PROCEDURE : PostgreSQL v11 adds support to stored
    procedure objects. Disabled by default.
  - PARALLEL_MIN_ROWS: set the minimum number of tuples in a table
    before calling Oracle's parallel mode during data export. 
    Default to 100000 rows.

Note that PG_SUPPORTS_PARTITION and PG_SUPPORTS_IDENTITY are now enabled by default to use PostgreSQL declarative partitioning and identity column instead of serial data type.

Version 19.0

View release

2017 08 18 - v19.0

This release fix several issues reported by users during last year. It also adds several new features and configuration directives.

New features:

  - Add export of Oracle HASH partitioning when PG_SUPPORTS_PARTITION
    is enabled. This is a PostgreSQL 11 feature.
  - Add SUBTYPE translation into DOMAIN with TYPE and PACKAGE export.
  - Add automatic translation of
      KEEP (DENSE_RANK FIRST|LAST ORDER BY ...) OVER (PARTITION BY ...)
    into
      FIRST|LAST_VALUE(...) OVER (PARTITION BY ... ORDER BY ...).
   - Add PCTFREE to FILLFACTOR conversion when PCTFREE is upper than
     the default value: 10. 
  - Replace DELETE clause not followed with FROM (optional in Oracle).
  - Remove Oracle extra clauses in TRUNCATE command.
  - Allow use of NUMBER(*) in DATA_TYPE directive to convert all
    NUMBER(*) into the given type whatever is the length. Ex:
        DATA_TYPE    NUMBER(*):bigint.
  - Add a PARALLEL hint to all Oracle queries used to migrate data.
  - Add export of Identity Columns from Oracle Database 12c.
  - Add translation of UROWID datatype and information in documentation
    about why default corresponding type OID will fail at data import.
  - Remove unwanted and unused keywords from CREATE TABLE statements:
    PARALLEL and COMPRESS.
  - Remove TEMPORARY in DROP statements.
  - Improve speed of escape_copy() function used for data export.
  - Add translation of Oracle functions NUMTOYMINTERVAL() and
    NUMTODSINTERVAL().
  - Add counting of jobs defined in Oracle scheduler in the migration
    assessment feature. 
  - Add CSMIG in the list of Oracle default system schema
  - Fully rewrite data export for table with nested user defined types
    DBD::Oracle fetchall_arrayref() is not able to associate complex
    custom types to the returned arrays, changed this call to use
    fetchrow_array() also used to export BLOB.
  - QUERY export will now output translated queries as well as
    untranslated ones. This break backward compatibility, previously
    only translated query was dumped.
  - Auto detect UTF-8 input files to automatically use utf8 encoding.
  - Support translation of MySQL global variables.
  - Add translation of preprocessor in Oracle external table into
    program in foreign table definition. Allow translation of external
    table from file.
  - Add translation to NVL2() Oracle function.
  - Translate CONVERT() MySQL function.
  - Translate some form of GROUP_CONCAT() that was not translated.
  - Remove call to CHARSET in cast() function, replace it by COLLATE
    every where else. This must cover most of the cases but some
    specials use might not, so please reports any issue with this
    behavior.
  - Add -c | --config command line option to ora2pg_scanner to set
    custom configuration file to be used instead of ora2pg default:
    /etc/ora2pg/ora2pg.conf
  - Improve CONNECT BY and OUTER JOIN translation.
  - And lot of MySQL to PostgreSQL improvements.

Several new configuration directives have been added:

  - Add DEFAULT_PARALLELISM_DEGREE to control PARALLEL hint use
    when exporting data from Oracle. Default is disabled.
  - Make documentation about KEEP_PKEY_NAMES more explicit about
    kind of constraints affected by this directive.
  - Add PG_SUPPORTS_IDENTITY configuration directive to enable
    export of Oracle identity columns into PostgreSQL 10 feature.
    If PG_SUPPORTS_IDENTITY is disabled and there is IDENTITY column
    in the Oracle table, they are exported as serial or bigserial
    columns. When it is enabled they are exported as IDENTITY columns
    like:
    
          CREATE TABLE identity_test_tab (
                  id bigint GENERATED ALWAYS AS IDENTITY,
                  description varchar(30)
          ) ;
    
    If there is non default sequence option set in Oracle, they will
    be appended after the IDENTITY keyword. Additionally in both cases
    Ora2Pg will create a file AUTOINCREMENT_output.sql with a function
    to update the associated sequences with the restart value set to 
    "SELECT max(colname)+1 FROM tablename". Of course this file must
    be imported after data import otherwise sequence will be kept to
    start value.
  - Add DISABLE_UNLOGGED configuration directive. By default Ora2Pg
    export Oracle tables with the NOLOGGING attribute into UNLOGGED
    tables. You may want to fully disable this feature because you
    will lost all data from unlogged table in case of PostgreSQL crash.
    Set it to 1 to export all tables as normal table. When creating a
    new migration project using --init_project, this directive is
    activated by default. This is not the case in the default
    configuration file for backward compatibility.
  - Add FORCE_SECURITY_INVOKER configuration directive. Ora2Pg use
    the function's security privileges set in Oracle and it is often
    defined as SECURITY DEFINER. To override those security privileges
    for all functions and use SECURITY DEFINER instead, enable this
    directive.
  - Add AUTONOMOUS_TRANSACTION in configuration to enable translation
    of autonomous transactions into a wrapper function using dblink
    or pg_background extension. If you don't want to use this feature
    and just want to export the function as a normal one without the
    pragma call, disable this directive.
  - Add documentation about COMMENT_SAVEPOINT configuration directive.
  - Major rewrite in PACKAGE parser to better support global variables
    detection. Global variable that have no default values are now
    always initialized to empty string in file global_variables.conf
    so that we see that they exists. This might not change the global
    behavior.

I especially want to thank Pavel Stehule and Eric Delanoe who spent lot of time this year to help me to improve the PL/SQL to plpgsql translation and also Krasiyan Andreev who help a lot to finalize the MySQL to PostgreSQL migration features.

Here is a complete list of changes and acknowledgments:

  - Fix translation of "varname cursor%ROWTYPE;". Thanks to Philippe
    Beaudoin for the report.
  - Fix return of autonomous transaction dblink call when function has
    OUT parameter. Thanks to Pavel Stehule for the report.
  - Add Oracle to PostgreSQL translation of windows functions 
    KEEP (DENSE_RANK FIRST|LAST ORDER BY ...) OVER (PARTITION BY ...)
    Thanks to Swapnil bhoot929 for the feature request.
  - Fix "ORA-03113: end-of-file on communication channel" that what
    generated by a too long query send to Oracle. The size of queries
    sent to Oracle to retrieve object information depend of the ALLOW
    and EXCLUDE directives. If you have lot of objects to filter you
    can experience this kind of non explicit error. Now Ora2pg use
    bind parameter to pass the filters values to reduce the size of
    the prepared query. Thanks to Stephane Tachoire for the report.
  - Add SUBTYPE translation into DOMAIN with TYPE and PACKAGE export.
    Thanks to Francesco Loreti for the feature request.
  - Fix PLS_INTEGER replacement.
  - Remove precision for RAW|BLOB as type modifier is not allowed for
    type "bytea".
  - Fix call of schema.pckg.function() in indexes with a replacement
    with pckg.function(). Thanks to w0pr for the report.
  - Fix translation of UPDATE trigger based on columns:
        "BEFORE UPDATE OF col1,col2 ON table".
    Thanks to Eric Delanoe for the report.
  - Remove single / from input file that was causing a double END in
    some case. Thanks to Philippe Beaudoin for the report.
  - Limit translation of PCTFREE into FILLFACTOR when PCTFREE is upper
    than the Oracle default value: 10. With PostgreSQL 100 (complete
    packing) is the default.
  - Add PCTFREE to FILLFACTOR conversion. Thanks to Maxim Zakharov
    for the patch.
  - Remove TRUNCATE extra clauses. Thanks to e7e6 for the patch.
  - Fix type conversion when extra \n added after ;. Thanks to
    Maxim Zakharov for the patch.
  - Fix DELETE clause not followed with FROM (optional in Oracle).
    Thanks to Philippe Beaudoin for the patch.
  - Limit call to ALL_TAB_IDENTITY_COLS to version 12+. Thanks to
    Andy Garfield for the report.
  - Fix comment parsing. Thanks to Philippe Beaudoin for the report.
  - Allow use of NUMBER(*) in DATA_TYPE directive to convert all
    NUMBER(*) into the given type whatever is the length.
    Thanks to lingeshpes for the feature request.
  - Fix bug in function-based index export. Thanks to apol1234 for
    the report.
  - Add PARALLEL hint to all data export queries. Thanks to jacks33
    for the report.
  - Make documentation about KEEP_PKEY_NAMES more explicit about kind
    of constraints affected by this directive.
  - Fix export of identity columns by enclosing options between
    parenthesis and replacing CACHE 0 by CACHE 1. Thanks to swmcguffin
    devtech for the report.
  - Add parsing of identity columns from file.
  - Fix unwanted replacement of IF () in MySQL code. Thanks to
    Krasiyan Andreev for the report.
  - Fix to_char() translation, thanks to Eric Delanoe for the report.
  - Fix untranslated PERFORM into exception. Thanks to Pavel Stehule
    for the report.
  - Add _get_entities() function to MySQL export. It returns nothing,
    AUTO_INCREMENT column are translated with corresponding types,
    smallserial/serial/bigserial.
  - Fix look at encrypted column on Oracle prior to 10. Thanks to
    Stephane Tachoires for the patch.
  - Add export of Identity Columns from Oracle Database 12c. Thanks
    to swmcguffin-devtech for the feature request.
  - Prevent Ora2Pg to scan ALL_SCHEDULER_JOBS for version prior to 10
    Thanks to Stephane Tachoires for the patch.
  - Fix pull request #648 to log date only when debug is enabled and
    use POSIX strftime instead of custom gettime function.
  - Add system time to debug log info. Thanks to danghb for the patch.
  - Fix parsing of trigger from file and exception.
  - Fix very slow export of mysql tablespace when number of table is
    large. Thanks to yafeishi for the report.
  - Fix translation of CAST( AS unsigned). Thanks to Krasiyan Andreev.
  - Fix MySQL character length to use character_maximum_length
    instead of equal character_octet_length. Thanks to yafeishi for
    the report.
  - Fix custom replacement of MySQL data type. Thanks to Krasiyan
    Andreev for the report.
  - Fix replacement of call to open cursor with empty parenthesis.
    Thanks to Philippe Beaudoin for the report.
  - Fix MySQL data type conversion in function declaration. Thanks to
    Krasiyan Andreev for the report.
  - Fix error with -INFINITY as default value for date or timestamp
    columns.
  - Fix procedure call rewrite with unwanted comma on begin of
    parameter list. Thanks to Pavel Stehule for the report.
  - Fix handling of foreign keys when exporting data and DROP_FKEYS
    is enabled and ALLOW/EXCLUDE directive is set. Now Ora2Pg will
    first drop all foreign keys of a table in the export list and all
    foreign keys of other tables pointing to the table. After data
    import, it will recreate all of these foreign keys. Thanks to
    Eric Delanoe for the report.
  - Fix broken transformation of procedure call with default parameter
    Thanks to Pavel Stehule for the report.
  - Translate call to TIMESTAMP in partition range values into a cast.
    Thanks to markiech for the report.
  - Fix CONNECT BY translation when the query contain an UNION. Thanks
    to mohammed-a-wadod for the report.
  - Fix CONNECT BY with PRIOR on the right side of the predicat.
  - Fix outer join translation when the (+) was in a function, ex:
        WHERE UPPER(trim(VW.FRIDAY))= UPPER(trim(FRIDAY.NAME(+))).
  - Order outer join pending tables in from clause.
  - Order by object name comments and indexes export.
  - Fix outer join translation when the table is not in the from
    clause. Thanks to Cyrille Lintz for the report.
  - Try to fix potential Oracle schema prefixing PostgreSQL schema
    name in CREATE SCHEMA. Thanks to Cyrille Lintz for the report.
  - Fix error in TRIM() translation. Thanks to Cyrille Lintz for the
    report.
  - Add translation of UROWID datatype and information in documentation
    about why default corresponding type OID will fail at data import.
    Thanks to Cyrille Lintz for the report.
  - Fix bug in exporting boolean default values in column definition.
  - Fix bug in column parsing in CREATE TABLE.
  - Adapt default value for data type changed to boolean.
  - Fix bad handling of -D (data_type) option.
  - Change behavior in the attempt to set MySQL global variable type.
    Now variable type will be timestamp if the variable name contains
    datetime, time if the name contains only time and date for date.
    Thanks to Krasiyan Andreev for the report.
  - Fix function replacement in MySQL declare section. Thanks to
    Krasiyan Andreev fr the report.
  - Apply REPLACE_ZERO_DATE to default value in table declaration.
    Thanks to Krasiyan Andreev for the report.
  - Add support to embedded comment in table DDL.
  - Fix replacement of data type for MySQL code. Thanks to Krasiyan
    Andreev for the report.
  - Fix MySQL type replacement in function. Thanks to Krasiyan Andreev
    for the report.
  - Improve speed of escape_copy() function used for data export.
    Thanks to pgnickb for the profiling.
  - Add translation of Oracle functions NUMTOYMINTERVAL() and
    NUMTODSINTERVAL(). Thanks to Pavel Stehule for the report.
  - Counting jobs defined in Oracle scheduler. Thanks to slfbovey
    for the patch.
  - Fix several issue in create table DDL parser:
      - remove double quote of object name when a list of column is
        entered
      - split of table definition to extract column and constraint
        parts is now more efficient
      - remove dot in auto generated constraint name when a schema
        is given in table name
      - fix default values with space that was breaking the parser
  - Remove use of bignum perl module that reports error on some
    installation. Thanks to Cyrille Lintz for the report.
  - Fix a typo preventing perldoc to complete. Thanks to slfbovey
    for the patch.
  - Fully rewrite data export for table with nested user defined types
    DBD::Oracle fetchall_arrayref() is not able to associate complex
    custom types to the returned arrays, changed this call to use
    fetchrow_array() also used to export BLOB. Thanks to lupynos for
    the report.
  - Fix renaming of temporary files during partitions data export.
  - Fix Oracle use of empty string as default value for integers.
    Oracle allow such declaration: SOP NUMBER(5) DEFAULT '' which
    PostgreSQL does not support. Ora2Pg now detect this syntax and
    replace empty string with NULL. Thanks to ricdba for the report.
  - Add detection of Oracle version before setting datetime format,
    needed for Oracle 8i compatibility.
  - Export of tables from Oracle database are now ordered by name by
    default. Thanks to Markus Roth for the report.
  - Fix an other case of missing translation of UNSIGNED into bigint.
    Thanks to Krasiyan Andreev for the report.
  - Force replacement of double quote into single quote for MySQL view
    and function code.
  - Fix case when SET @varname := ... is used multiple time in the
    same function. Thanks to Krasiyan Andreev for the report.
  - Fix case where SET @varname := ... was not translated. Thanks to
    Krasiyan Andreev for the report.
  - Adjust the regex pattern of last patch.
  - Fix unwanted newline after hint replacement that could break
    comments. Thanks to Pavel Stehule for the report.
  - Fix if() replacement in query. Thanks to Krasiyan Andreev for the
    report.
  - Remove extra parenthesis in some form of JOIN. Thanks to Krasiyan
    Andreev for the report.
  - Fix untranslated call to UNSIGNED, now translated as bigint.
  - Thanks to Krasiyan Andreev for the report.
  - Fix translation of double(p,s) into decimal(p,s).
  - Remove use of SET when an assignment is done through a SELECT
    statement. Thanks to Krasiyan Andreev for the report.
  - Fix non-quoted reserved keywords in INSERT / COPY statements when
    exporting data. Thanks to Pavel Stehule for the report.
  - Fix partition data export to file, temporary files for partition
    output was not renamed at export end then data was not loaded.
  - Fix double operator := during function with out param rewrite.
  - Fix commit f1166e5 to apply changes when FILE_PER_FUNCTION is
    disable or when an input file is given.
  - Fix translation of LOCATE(). Thanks to Krasiyan Andreev for the
    report.
  - Fix case where MySQL GROUP_CONCAT() function was not translated.
    Thanks to Krasiyan Andreev for the report.
  - Fix :new and :old translation in triggers.
  - Fully rewrite function call qualification process, the second pass
    now is only use to requalify call to pkg.fct into pkg_ftc when
    PACKAGE_AS_SCHEMA is disable. The replacement of all function
    calls using double quote when a non supported character is used or
    when PRESERVE_CASE is enabled has been completely removed as this
    takes too much time to process for just very few case. So by
    default now Ora2Pg will not go through the second pass. This can
    change in the future especially if this is more performant to
    process PERFORM replacement. Thanks a lot to Eric Delanoe for his
    help on this part.
  - Exclude function and procedure not from package to be used in
    requalify call. Thanks to Eric Delanoe for the report.
  - Fix function name qualification in multiprocess mode.
  - Fix unqualified function call due to unclose file handle.
  - Prevent try to requalify function call if the function is
    not found in the file content.
  - Remove ALGORITHM=.*, DEFINER=.* and SQL SECURITY DEFINER from
    MySQL DDL code.
  - An other missing change to previous commit on qualifying function
    call.
  - Limit function requalification to export type: VIEW, TRIGGER,
    QUERY, FUNCTION, PROCEDURE and PACKAGE.
  - Auto detect UTF-8 input files to automatically use utf8 encoding.
  - Remove all SHOW ERRORS and other call to SHOW in Oracle package
    source as they was badly interpreted as global variable.
  - Fix MySQL CREATE TABLE ... SELECT statement.
  - Fix pending translation issue on some DATE_FORMAT() case.
    Thanks to Krasiyan Andreev for the report.
  - Fix translation of IN (..) in MySQL view. Thanks to Krasiyan
    Andreev for the report.
  - Fix MySQL date format with digit.
  - Fix DATE_FORMAT, WHILE and IFNULL translation issues.
  - Fix not translated MySQL IF() function.
  - Fix other MySQL translation issues for @variable. Thanks to
    Krasiyan Andreev for the report.
  - Fix issue in MySQL IF translation with IN clause. Thanks to
    Krasiyan Andreev for the report.
  - Clarify comment about XML_PRETTY directive. Thanks to TWAC
    for the report.
  - Fix remaining MySQL translation issues for @variable reported
    in issue #590.
  - Fix no translated := in SET statement.
  - Fix output order of translated function.
  - Fix non printable character or special characters that make
    file encoding to ISO-8859 instead of utf8. Thanks to twac for
    the report.
  - Prevent MySQL global variable to be declared twice. Thanks to
    Krasiyan Andreev for the report.
  - Support translation of MySQL global variables. Session variable
    @@varname are translated to PostgreSQL GUC variable and global
    variable @varname are translated to local variable defined in a
    DECLARE section. Ora2Pg tries to gather the data type by using
    integer by default, varchar if there is a constant string ('...')
    in the value and a timestamp if the variable name have the keyword
    date or time inside. Thanks to Krasiyan Andreev for the feature
    request.
  - Fix DATE_ADD() translation.
  - Add translation of preprocessor in Oracle external table into
    program in foreign table definition. Thanks to Thomas Reiss for
    the report. Allow translation of external table from file.
  - Fix case where IF EXISTS might not be append when it is not
    supported by PG.
  - Translate CONVERT() MySQL function. Thanks to Krasiyan Andreev
    for the report.
  - Translate some form of GROUP_CONCAT() that was not translated.
    Thanks to Krasiyan Andreev for the report.
  - Apply same principe with COMMIT in MySQL function code than in
    Oracle code. It is kept untouched to be able to detect a possible
    change of code logic. It can be automatically commented if
    COMMENT_COMMIT_ROLLBACK is enabled. Also I have kept the START
    TRANSACTION call but it is automatically commented.
  - Add mysql_enable_utf8 => 1 to MySQL connection to avoid issues
    with encoding. Thanks to Krasiyan Andreev for the report.
  - Prevent removing of comment on MySQL function and add a "COMMENT
    ON FUNCTION" statement at end of the function declaration. Thanks
    to Krasiyan Andreev for the report.
  - Fix translation of types in MySQL function parameter. Thanks to
    Krasiyan Andreev for the report.
  - Remove START TRANSACTION from MySQL function code. Thanks to
    Krasiyan Andreev for the report.
  - Fix previous patch, we do not need to look forward for function
    or procedure definition in VIEW export and there is no package
    with MySQL. Thanks to Krasiyan Andreev for the report.
  - Fix call to useless function for MySQL function.
  - Add rewrite of MySQL function call in function or procedure code
    translation and some other translation related to MySQL code.
  - Fix ora2pg_scanner when exporting schema with $ in its name.
    Thanks to Aurelien Robin for the report.
  - Disable number of microsecond digit for Oracle version 9. Thanks
    to Aurelien Robin for the report.
  - Do not look at encrypted column for DB version < 10. Thanks to
    Aurelien Robin for the report.
  - Fix MySQL call to charset in cast function. MySQL charset "utf8"
    is also set to COLLATE "C.UTF-8". Thanks to Krasiyan Andreev for
    the report.
  - Fix two bug in CONNECT BY and OUTER JOIN translation.
  - Forgot to handle exception to standard call to IF in MySQL IF()
    translation. Thanks to Krasiyan Andreev for the report.
  - Forgot to apply previous changes to procedure.
  - Fix IF() MySQL replacement when nested and when containing an
    IN (...) clause. Thanks to  Krasiyan Andreev for the report.
  - Fix double BEGIN on MySQL function export. Thanks to Krasiyan
    Andreev for the report.
  - Fix enum check constraint name when PRESERVE_CASE is enabled.
  - Fix case where object with LINESTRING and CIRCULARSTRING was
    exported as MULTILINESTRING instead of MULTICURVE.
  - Fix export of MULTICURVE with COMPOUNDCURVE. Thanks to Petr Silhak
    for the report.
  - Fix several issue in MySQL table DDL export. Thanks to Krasiyan
    Andreev for the report.
  - Fix MySQL auto_increment data type translation and columns export
    order.
  - Fix translation of MySQL function CURRENT_TIMESTAMP(). Thanks to
    Krasiyan Andreev for the report.
  - Fix export of MySQL alter sequence name when exporting auto
    increment column. Thanks to Krasiyan Andreev for the report.
  - Replace IF() call with CASE ... END in VIEW and QUERY export for
    MySQL. Thanks to Krasiyan Andreev for the feature request.
  - Replace backquote with double quote on mysql statements when read
    from file.
  - Fix bug in REGEXP_SUBSTR replacement.
  - Prevent replacement with same function name from an other package.
    Thanks to Eric Delanoe for the report.
  - Apply same STRICT rule for SELECT INTO to EXECUTE INTO. Thanks to
    Pavel Stehule for the report.
  - Fix extra parenthesis removing when a OR clause is present. Thanks
    to Pavel Stehule for the report.
  - Keep autonomous pragma commented when conversion is desactivated
    to be able to identify functions using this pragma.
  - Fix bug in replacement of package function in string constant.
  - Fix malformed replacement of array element calls. Thanks to Eric
    Delanoe for the report.
  - Fix unwanted replacement of TO_NUMBER function. Thanks to Torquem
    for the report.
  - Add an example of DSN for MySQL in ORACLE_DSN documentation.
    Thanks to François Honore for the report.
  - Fix typo in default dblink connection string. Thanks to Pavel
    Stehule for the report.
  - Add information about Oracle Instant Client installation. Thanks
    to Jan Birk for the report.
  - Replace Oracle array syntax arr(i).x into arr[i].x into PL/SQL
    code. Thanks to Eric Delanoe for the report.
  - Use a more generic connection string for DBLINK. It will use
    unix socket by default to connect and the password must be set
    in .pgpass. This will result in the following connection string:
        format('port=%s dbname=%s user=%', current_setting('port'),
		current_database(), current_user)
    If you want to redefine this connection string use DBLINK_CONN
    configuration directive. Thanks to Pavel Stehule for the feature
    request.
  - Fix missing RETURN NEW in some trigger translation. Thanks to
    Pavel Stehule for the report.
  - Fix a missing but non mandatory semi-comma.
  - Keep PKs/unique constraints which are deferrable in Oracle also
    deferrable in PostgreSQL. Thank to Sverre Boschman for the patch.
  - Fix parsing and translation of CONNECT BY. Thanks to bhoot929
    for the report.
  - Fix FDW export when exporting all schema. Thanks to Laurenz Albe
    for the report.
  - Add a note about multiple value in export type that can not
    include COPY or INSERT together with others export type.
  - Fix duplicate condition. Thanks to Eric Delanoe for the report.
  - Fix unwanted translation into PERFORM after INTERSECT.
  - Comment savepoint in code. Thanks to Pavel Stehule for the patch.
  - Fix "ROLLBACK TO" that was not commented. Thanks to Pavel Stehule
    for the report.
  - Fix restore of constant string when additional string constant
    regex are defined in configuration file.
  - Fix translation of nextval with sequence name prefixed with their
    schema.
  - Cast call to TO_DATE(LOCALTIMESTAMP,...) translated into
    TO_DATE(LOCALTIMESTAMP::text,...). Thanks to Keshav kumbham
    for the report.
  - Remove double quote added automatically by Oracle on view
    definition when PRESERVE_CASE is not enable. Thanks to JeeIPI for
    the report.
  - Fix translation of FROM_TZ with a call to function as first 
    parameter. Thanks to TrungPhan for the report.
  - Fix package export when FILE_PER_FUNCTION is set. Thanks to
    Julien Rouhaud for the report.
  - Add translation of REGEXP_SUBSTR() with the following rules:
    Translation of REGEX_SUBSTR( string, pattern, [pos], [nth])
    converted into
        SELECT array_to_string(a, '')
          FROM regexp_matches(substr(string, pos), pattern, 'g')
               AS foo(a)
          LIMIT 1 OFFSET (nth - 1);
    Optional fifth parameter of match_parameter is appended to 'g'
    when present.  Thanks to bhoot929 for the feature request.
  - Add count of REGEX_SUBSTR to migration assessment cost.
  - Add translation support of FROM_TZ() Oracle function. Thanks
    to trPhan for the feature request.
  - Forces ora2pg to output a message when a custom exception code
    has less than 5 digit.
  - Fix errcode when Oracle custom exception number have less than
    five digit. Thanks to Pavel Stehule for the report.
  - Fix case where custom errcode are not converted. Thanks to Pavel
    Stehule for the report.
  - Fix print of single semicolon with empty line in index export.
  - Fix problem with TO_TIMESTAMP_TZ conversion. Thanks to Keshav-
    kumbham for the report.
  - Fix unwanted double quote in index column with DESC sorting.
    Thanks to JeeIPI for the report.
  - Fix non detection case of tables in from clause for outer join
    translation. Thanks to Keshav for the report.
  - Fix unwanted replacement of = NULL into IS NULL in update
    statement. Thanks to Pavel Stehule for the report.
  - Force schema name used in TEST action to lowercase. Thanks to
    venkatabn for the report.
  - Fix export of spatial geometries with CURVEPOLYGON + COMPOUNDCURVE
    Thanks to kabog for the report.

Version 18.2

View release

2017 09 01 - v18.2

This release fix several issues reported during the last six months. It also adds lot of new features and configuration directives:

  - Add translation of SUBSTRB into substr.
  - Allow use of array in MODIFY_TYPE to export Oracle user defined
    type that are just array of some data type. For example:
      CREATE OR REPLACE TYPE mem_type IS VARRAY(10) of VARCHAR2(15);
    can be directly translated into text[] or varchar[]. In this case
    use the directive as follow: MODIFY_TYPE	CLUB:MEMBERS:text[]
    Ora2Pg will take care to transform all data of this column into
    the correct format. Only arrays of characters and numerics types
    are supported.
  - Add translation of Oracle function LISTAGG() into string_agg().
  - Add TEST_VIEW action to perform a simple count of rows returned by
    views on both database.
  - Translate SQL%ROWCOUNT into GET DIAGNOSTICS rowcount = ROW_COUNT
    and add translation of SQL%FOUND.
  - Add translation of column in trigger event test with IS DISTINCT,
    for example: IF updating('ID') THEN ... will be translated into:
    IF TG_OP = 'UPDATE' AND NEW.'ID' IS DISTINCT FROM OLD.'ID' then...
  - Replace UTL_MATH.EDIT_DISTANCE function by fuzzymatch levenshtein.
  - Allow use of MODIFY_STRUCT with TABLE export. Table creation DDL
    will respect the new list of columns and all indexes or foreign
    key pointing to or from a column removed will not be exported.
  - Add export of partition and subpartition using PostgreSQL native
    partitioning.
  - Auto detect encrypted columns and report them into the assessment.
    SHOW_COLUMN will also mark columns as encrypted.
  - Add information to global temporary tables in migration assessment.
  - Add experimental DATADIFF functionality.
  - Allow use of multiprocess with -j option or JOBS to FUNCTION and
    PROCEDURE export. Useful if you have thousands of these objects.
  - Force RAW(N) type with default value set to sys_guid() as UUID
    on PostgreSQL.
  - Replace function with out parameter using select into. For example
    a call to:       get_item_attr( attr_name, p_value );
    where p_value is an INOUT parameter, will be rewritten as
    
    	p_value := get_item_attr( attr_name, p_value );
    
    If there is multiple OUT parameters, Ora2Pg will use syntax:
    
    	SELECT get_item_attr( attr_name, p_value )
		INTO (attr_name, p_value);

  - Add translation of CONNECT BY using PostgreSQL CTE equivalent.
    This translation also include a replacement of LEVEL and
    SYS_CONNECT_BY_PATH native Oracle features. On complex queries
    there could still be manual editing but all the main work is done.
  - Add support to user defined exception, errcode affected to each
    custom exception start from 50001.
  - Translate call to to_char() with a single parameter into a cast
    to varchar. Can be disabled using USE_ORAFCE directive.
  - Improve ora2pg_scanner to automatically generates migration
    assessment reports for all schema on an Oracle instance. Before
    the schema name to audit was mandatory, now, when the schema
    is not set Ora2Pg will scan all schema. The connexion user need
    to have DBA privilege. Ora2Pg will also add the hostname and SID
    as prefix in the filename of the report. This last changee forbids
    ora2pg_scanner to overwrite a report if the same schema name is
    found in several databases.

Several new configuration directives have been added:

  - Add USE_ORAFCE configuration directive that can be enabled if you
    want to use functions defined in the Orafce library and prevent
    Ora2Pg to translate call to these functions. The Orafce library
    can be found here: https://github.com/orafce/orafce
    By default Ora2pg rewrite add_month(), add_year(), date_trunc()
    and to_char() functions, but you may prefer to use the Orafce
    functions that do not need any code transformation. Directive
    DATE_FUNCTION_REWRITE has been removed as it was also used to
    disable replacement of add_month(), add_year() and date_trunc()
    when Orafce is used, useless now.
  - Add FILE_PER_FKEYS configuration directive to allow foreign key
    declaration to be saved in a separate file during schema export.
    By default foreign keys are exported into the main output file or
    in the CONSTRAINT_output.sql file. If enabled foreign keys will be
    exported into a file named FKEYS_output.sql
  - Add new COMMENT_COMMIT_ROLLBACK configuration directive. Call to
    COMMIT/ROLLBACK in PL/SQL code are kept untouched by Ora2Pg to
    force the user to review the logic of the function. Once it is
    fixed in Oracle source code or you want to comment this calls
    enable the directive.
  - Add CREATE_OR_REPLACE configuration directive. By default Ora2Pg
    use CREATE OR REPLACE in function DDL, if you need not to override
    existing functions disable this configuration directive, DDL will
    not include OR REPLACE.
  - Add FUNCTION_CHECK configuration directive. Disable this directive
    if you want to disable check_function_bodies.
    
    	SET check_function_bodies = false;
    
    It disables validation of the function body string during CREATE
    FUNCTION. Default is to use de postgresql.conf setting that enable
    it by default.
  - Add PG_SUPPORTS_PARTITION directive, disabled by default.
    PostgreSQL version prior to 10.0 do not have native partitioning.
    Enable this directive if you want to use PostgreSQL declarative
    partitioning instead of the old style check constraint and trigger.
  - Add PG_SUPPORTS_SUBSTR configuration directive to replace substr()
    call with substring() on old PostgreSQL versions or some fork
    like Redshift.
  - Add PG_INITIAL_COMMAND to send some statements at session startup.
    This directive is the equivalent used for Oracle connection,
    ORA_INITIAL_COMMAND. Both can now be used multiple time now.
  - Add DBLINK_CONN configuration directive. By default if you have
    an autonomous transaction translated using dblink extension the
    connection is defined using the values set with PG_DSN, PG_USER
    and PG_PWD. If you want to fully override the connection string
    use this directive to set the connection in the autonomous
    transaction wrapper function. For example:
    
        DBLINK_CONN    port=5432 dbname=pgdb host=localhost user=pguser password=pgpass
    
  - Add STRING_CONSTANT_REGEXP configuration directive. Ora2Pg replace
    all string constant during the pl/sql to plpgsql translation,
    string constant are all text include between single quote. If you
    have some string placeholder used in dynamic call to queries you
    can set a list of regexp to be temporary replaced to not break the
    parser. For example:
    
    	STRING_CONSTANT_REGEXP         <cfqueryparam value=".*">
    
    The list of regexp must use the semi colon as separator.
  - Add FUNCTION_STABLE configuration directive. By default Oracle
    functions are marked as STABLE as they can not modify data unless
    when used in PL/SQL with variable assignment or as conditional
    expression. You can force Ora2Pg to create these function as
    VOLATILE by disabling this configuration directive.
  - Add new TO_NUMBER_CONVERSION configuration directive to control
    TO_NUMBER translation behavior. By default Oracle call to function
    TO_NUMBER will be translated as a cast into numeric. For example,
    TO_NUMBER('10.1234') is converted into PostgreSQL call:
        to_number('10.1234')::numeric.
    If you want you can cast the call to integer or bigint by changing
    the value of the configuration directive. If you need better
    control of the format, just set it as value, for example:
           TO_NUMBER_CONVERSION    99999999999999999999D9999999999
    will convert the code above as:
          TO_NUMBER('10.1234', '99999999999999999999D9999999999')
    Any value of the directive that it is not numeric, integer or
    bigint will be taken as a mask format. If set to none, then no
    conversion will be done.
  - Add LOOK_FORWARD_FUNCTION configuration directive which takes a
    list of schema to get functions/procedures meta information that
    are used in the current schema export. When replacing call to
    function with OUT or INOUT parameters, if a function is declared
    in an other package then the function call rewriting can not be
    done because Ora2Pg only knows about functions declared in the
    current schema. By setting a comma separated list of schema as
    value of the directive, Ora2Pg will look forward in these packages
    for all functions, procedures and packages declaration before
    proceeding to current schema export.
  - Add PG_SUPPORTS_NAMED_OPERATOR to control the replacement of the
    PL/SQL operator used in named parameter => with the PostgreSQL
    proprietary operator := Disable this directive if you are using
    PG < 9.5
  - Add a warning when Ora2Pg reorder the parameters of a function
    following the PostgreSQL rule that all input parameters following
    a parameter with a default value must have default values as well.
    In this case, Ora2Pg extracts all parameters with default values
    and put them at end of the parameter list. This is to warn you
    that a manual rewrite is required on calls to this function.

New command line options have been added:

  - Add -N | --pg_schema command line option to be able to override
    the PG_SCHEMA configuration directive. When this option is set
    at command line, EXPORT_SCHEMA is automatically activated.
  - Add --no_header option with equivalent NO_HEADER configuration
    directive to output the Ora2Pg header but just the translated
    code.

There is also some behavior changes from previous release:

  - Remove SysTimestamp() from the list of not translated function,
    it is replaced with CURRENT_TIMESTAMP for a long time now.
  - Change migration assessment cost to 84 units (1 day) for type
    TABLE, INDEX and SYNONYM and to 168 units (2 days) for TABLE
    PARTITION and GLOBAL TEMPORARY TABLE, this is more realistic.
  - Set minimum assessment unit to 1 when an object exists.
    Improve PL/SQL code translation speed.
  - Change behavior of COMPILE_SCHEMA directive used to force Oracle
    to compile schema before exporting code. When this directive is
    enabled and SCHEMA is set to a specific schema name, only invalid
    objects in this schema will be recompiled. When SCHEMA is not set
    then all schema will be recompiled. To force recompile invalid
    object in a specific schema, set COMPILE_SCHEMA to the schema name
    you want to recompile. This will ask to Oracle to validate the
    PL/SQL that could have been invalidate after a export/import for
    example. The 'VALID' or 'INVALID' status applies to functions,
    procedures, packages and user defined types.
  - Default transaction isolation level is now set to READ COMMITTED
    for all action excluding data export.
  - Oracle doesn't allow the use of lookahead expression but you may
    want to exclude some objects that match the ALLOW regexp you have
    defined. For example if you want to export all table starting
    with E but not those starting with EXP it is not possible to do
    that in a single expression.
    Now you can start a regular expression with the ! character to
    exclude all objects matching the regexp given just after. Our
    previous example can be written as follow:   ALLOW   E.* !EXP.*
    it will be translated into
    
    	 REGEXP_LIKE(..., '^E.*$') AND NOT REGEXP_LIKE(..., '^EXP.*$')
    
    in the object search expression.
  - Fix quoting of PG_SCHEMA with multiple schema in search path. The
    definition of the search path now follow the following behavior:
      * when PG_SCHEMA is define, always set search_path to its value.
      * when EXPORT_SCHEMA is enabled and SCHEMA is set, the search_path
        is set the name of the schema.
  - Remove forcing of export_schema when pg_schema is set at command
    line. This could change the behavior of some previous use of these
    variables and the resulting value of the search_path but it seems
    much better understandable.
  - Rewrite translation of raise_application_error to use RAISE
    EXCEPTION with a message and the SQLSTATE code. Oracle user
    defined code -20000 to -20999 are translated to PostgreSQL 
    user define code from 45000 to 45999. Call to
  	raise_application_error(mySQLCODE, myErrmsg);
    will be translated into
   	RAISE EXCEPTION '%', myErrmsg USING ERRCODE = mySQLCODE;
  - Remove migration assessment cost for TG_OP and NOT_FOUND they
    might be fully covered now.

For a complete list of bug fix see changelog file.

Version 18.1

View release

2017 02 17 - v18.1

This release fix several issues reported on outer join translation thanks to the help of Pavel Stehule and reapply the commit on virtual column export that was accidentally removed from v18.0. It also adds several new features:

Remove CHECK constraints for columns converted into boolean using REPLACE_AS_BOOLEAN column.
Oracle function are now marked as stable by default as they can not modify data.

Two new configuration directives have been added:

DATE_FUNCTION_REWRITE: by default Ora2pg rewrite add_month(), add_year() and date_trunc() functions set it to 0 to force Ora2Pg to not translate those functions if translated code is broken.
GRANT_OBJECT: when exporting GRANT you can now specify a comma separated list of objects which privileges must be exported. Default is to export privileges for all objects. For example set it to TABLE if you just want to export privilege on tables.

and a new command line option:

Add -g | --grant_object command line option to ora2pg to be able to extract privilege from the given object type. See possible values with GRANT_OBJECT configuration directive.

Here is the complete list of changes:

Remove empty output.sql file in current directory with direct data import. Thanks to kuzmaka for the report.
Fix shell replacement of $$ in function definition in Makefile.PL embedded configuration file. Thanks to kuzmaka for the report.
Fix shell replacement of backslash in Makefile.PL embedded configuration file. Thanks to kuzmaka for the report.
Add warning level to virtual column notice.
Fix comment in where clause breaking the outer join association. Thanks to Pavel Stehule for the report.
Add parsing and support of virtual column from DDL file.
Reapply commit on virtual column export that was accidentally removed in commit d5866c9. Thanks to Alexey for the report.
Fix mix of inner join and outer join not translated correctly. Thanks to Pavel Stehule for the help to solve this issue.
Fix additional comma in column DEFAULT value from DDL input file. Thanks to Cynthia Shang for the report.
Fix comments inside FROM clause breaking translation to ANSI outer joins. Thanks to Pavel Stehule for the report.
Fix replacement of sdo_geometry type into function. Thanks to Saber Chaabane for the report.
Fix subquery in outer join clause. Thanks to Saber Chaabane for the report.
Fix duplicated subqueries placeholder in the from clause. Thanks to Saber Chaabane for the report.
Fix replacement of subquery place older during outer join rewrite. Thanks to Saber Chaabane for the report.
Add DATE_FUNCTION_REWRITE configuration directive. By default Ora2pg rewrite add_month(), add_year() and date_trunc() functions set it to 0 to force Ora2Pg to not translate those functions if translated code is broken. Thanks to Pavel Stehule for the feature request.
Do not report error when -g is used but action is not GRANT. Thanks to Shane Jimmerson for the report.
Oracle function can not modify data, only procedure can do that, so mark them as stable. Thanks to Pavel Stehule for the report.
Missed some obvious combination like upper/lower case or no space after AND/OR on outer join parsing and some other issues.
Add missing call to extract_subqueries() recursively. Thanks to Pavel Stehule for the report.
Add full support of outer join translation in sub queries.
Add translation of mixed inner join and Oracle outer join. Thanks to Pavel Stehule for the report.
Fix missing space between keyword AS and END from the decode() transformation. Thanks to Pavel Stehule for the report.
Fix parsing of outer join with UNION and translation to left join. Thanks to Pavel Stehule for the report.
Remove CHECK constraints for columns converted into boolean using REPLACE_AS_BOOLEAN column. Thanks to Shane Jimmerson for the feature request.
Fix regression on SQL and PLSQL rewrite when a text constant contained a semi-comma.
Add the GRANT_OBJECT configuration directive. When exporting GRANT you can specify a comma separated list of objects for which the privileges will be exported. Default is export for all objects. Here are the possibles values TABLE, VIEW, MATERIALIZED VIEW, SEQUENCE, PROCEDURE, FUNCTION, PACKAGE BODY, TYPE, SYNONYM and DIRECTORY. Only one object type is allowed at a time. For example set it to TABLE if you just want to export privilege on tables. You can use the -g option to overwrite it.
When used this directive prevent the export of users unless it is set to USER. In this case only users definitions are exported.
Add the -g | --grant_object command line option to ora2pg to be able to extract privilege from the given object type. See possible values with GRANT_OBJECT configuration directive.
Improve replacement of ROWNUM by LIMIT+OFFSET clause.
Fix extra semi-colon at end of statement.
Override ora2pg.spec with Devrim's one but with String::Random removing as it is no more used.

Version 18.0

View release

2017 01 29 - v18.0

This new major release adds several new useful features and lot of improvements.

Automatic rewrite of simple form of (+) outer join Oracle's syntax. This major feature makes Ora2Pg become the first free tool that is able to rewrite automatically some (+) outer join in command line mode. This works with simple form of outer join but this is a beginning.
Add export of Oracle's virtual column using a real column and a trigger.
Allow conversion of RAW/CHAR/VARCHAR2 type with precision in DATA_TYPE directive. Useful for example to transform all RAW(32) or VARCHAR2(32) columns into PostgreSQL special type uuid.
Add export NOT VALIDATED state from Oracle foreign keys and check constraints into NOT VALID constraints in PostgreSQL.
Replace call to SYS_GUID() with uuid_generate_v4() by default.
Add "CREATE EXTENSION IF NOT EXISTS dblink;" before an autonomous transaction or "CREATE EXTENSION IF NOT EXISTS pg_background;".
Major rewrite of the way Ora2Pg parse PL/SQL to rewrite function calls and other PL/SQL to plpgsql replacement. There should not be any limitation in rewriting when a function contains a sub query or an other function call inside his parameters.
Refactoring of ora2pg to not requires any dependency other than the Perl DBI module by default. All DBD drivers are now optionals and ora2pg will expect an Oracle DDL file as input by default.
Add export of Oracle's global variables defined in package. They are exported as user defined custom variables and available in a session. If the variable is a constant or have a default value assigned at declaration, ora2pg will create a new file with the declaration (global_variables.conf) to be included in the main configuration file postgresql.conf file.
Create full text search configuration when USE_UNACCENT directive is enabled using the auto detected stemmer or the one defined in FTS_CONFIG. For example: CREATE TEXT SEARCH CONFIGURATION fr (COPY = french); ALTER TEXT SEARCH CONFIGURATION fr ALTER MAPPING FOR hword, hword_part, word WITH unaccent, french_stem; CREATE INDEX place_notes_cidx ON places USING gin(to_tsvector('fr', place_notes));

Changes and incompatibilities from previous release:

FTS_INDEX_ONLY is now enabled by default because the addition of a column is not always possible and not always necessary where a simple function-based index is enough.
Remove use to setweigth() on single column FTS based indexes.
Change default behaviour of Ora2Pg in Full Text Search index export.

A new command line option and some configuration directive have been added:

Option -D | --data_type to allow custom data type replacement at command line like in configuration file with DATA_TYPE.
UUID_FUNCTION to be able to redefined the function called to replace SYS_GUID() Oracle function. Default to uuid_generate_v4.
REWRITE_OUTER_JOIN to be able to disable the rewriting of Oracle native syntax (+) into OUTER JOIN if rewritten code is broken.
USE_UNACCENT and USE_LOWER_UNACCENT configuration directives to use the unaccent extension with pg_trgm with the FTS indexes.
FTS_INDEX_ONLY, by default Ora2Pg creates an extra tsvector column with a dedicated triggers for FTS indexes. Enable this directive if you just want a function-based index like: CREATE INDEX ON t_document USING gin(to_tsvector('pg_catalog.english', title));
FTS_CONFIG, use this directive to force the text search stemmer used with the to_tsvector() function. Default is to auto detect the Oracle FTS stemmer. For example, setting FTS_CONFIG to pg_catalog.english or pg_catalog.french will override the auto detected stemmer.

There's also lot fixes of issues reported by users from the past two months, here is the complete list of changes:

Fix return type in function with a single inout parameter and a returned type.
Prevent wrong rewrite of empty as null when a function is used. Thanks to Pavel Stehule for the report.
Add the UUID_FUNCTION configuration directive. By default Ora2Pg will convert call to SYS_GUID() Oracle function with a call to uuid_generate_v4 from uuid-ossp extension. You can redefined it to use the gen_random_uuid function from pgcrypto extension by changing the function name. Default to uuid_generate_v4. Thanks to sjimmerson for the feature request.
Add rewrite of queries with simple form of left outer join syntax (+) into the ansi form.
Add new command line option -D | --data_type to allow custom data type replacement at command line like in configuration file with DATA_TYPE.
Fix type in ROWNUM replacement expression. Thanks to Pavel Stehule for the report.
Add replacement of SYS_GUID by uuid_generate_v4 and allow custom rewriting of RAW type. Thanks to Nicolas Martin for the report.
Fix missing WHERE clause in ROWNUM replacement with previous patch thanks to Pavel Stehule for the report.
Fix ROWNUM replacement when e sub select is used. Thanks to Pavel Stehule for the report.
Fix wrong syntax in index creation with DROP_INDEXES enabled. Thanks to Pave Stehule for the report.
Remove replacement of substr() by substring() as PostgreSQL have the substr() function too. Thanks to Pavel Stehule for the report.
Move LIMIT replacement for ROWNUM to the end of the query. Thanks to Pavel Stehule for the report.
Fix text default value between parenthesis in table declaration. Thanks to Pavel Stehule for the report.
Fix return type when a function have IN/OUT parameter. Thanks to Pavel Stehule for the report.
Mark uuid type to be exported as text. Thanks to sjimmerson for the report.
Add EXECUTE to open cursor with like "OPEN var1 FOR var2;". Thanks to Pavel Stehule for the report.
Fix replacement of local type ref cursor. Thanks to Pavel Stehule for the report.
Add EXECUTE keyword to OPEN CURSOR ... FOR with dynamic query. Thanks to Pavel Stehule for the report.
Fix case sensitivity issue in FOR .. IN variable declaration replacement. Thanks to Pavel Stehule for the report.
Fix wrong replacement of cast syntax ::. Thanks to Pavel Stehule for the report.
Reactivate numeric cast in call to round(value,n).
Close main output data file at end of export.
Add virtual column state in column info report, first stage to export those columns as columns with associated trigger.
Fix unwanted replacement of REGEXP_INSTR. Thanks to Bernard Bielecki for the report.
Allow rewrite of NUMBER(, 0) into bigint or other type instead numeric(38), just set DATA_TYPE to NUMBER(,0):bigint. Thanks to kuzmaka for the feature request.
Export partitions indexes into PARTITION_INDEXES_....sql separate file named. Thanks to Nicolas Martin for the feature request.
Fix fatal error when schema CTXSYS does not exists. Thanks to Bernard Bielecki for the report.
Fix missing text value replacement. Thanks to Bernard Bielecki for the report.
Fix type replacement in declare section when the keyword END was present into a variable name.
Export NOT VALIDATED Oracle foreign key and check constraint as NOT VALID in PostgreSQL. Thanks to Alexey for the feature request.
Add object matching of regex 'SYS_.*$' to the default exclusion list.
Fix UTF8 output to file as the open pragma "use open ':utf8';" doesn't works in a global context. binmode(':encoding(...)') is used on each file descriptor for data output.
Improve parsing of tables/indexes/constraints/tablespaces DDL from file.
Improve parsing of sequences DDL from file.
Improve parsing of user defined types DDL from file.
Export Oracle's TYPE REF CURSOR with a warning as not supported.
Replace call to plsql_to_plpgsql() in Ora2Pg.pm by a call to new function convert_plsql_code().
Move export of constraints after indexes to be able to use USING index in constraint creation without error complaining that index does not exists.
Add "CREATE EXTENSION IF NOT EXISTS dblink;" before an autonomous transaction or "CREATE EXTENSION IF NOT EXISTS pg_background;".
Improve parsing of packages DDL from file.
When a variable in "FOR varname IN" statement is not found in the DECLARE bloc, Ora2Pg will automatically add the variable to this bloc declared as a RECORD. Thanks to Pavel Stehule for the report.
Major rewrite of the way Ora2Pg parse PL/SQL to rewrite function calls and other PL/SQL to plpgsql replacement. There should not be limitation in rewriting when a function contains a sub query or an other function call inside his parameters.
Fix unwanted SELECT to PERFORM transformation inside literal strings. Thanks to Pavel Stehule for the report.
Fix bug in DEFAULT value rewriting. Thanks to Pavel Stehule for the report.
Fix replacement of DBMS_OUTPUT.put_line with RAISE NOTICE.
Reset global variable storage for each package.
Improve comment parsing in packages and prevent possible infinite loop in global variable replacement.
Add the REWRITE_OUTER_JOIN configuration directive to be able to disable the rewriting of Oracle native syntax (+) into OUTER JOIN if it is broken. Default is to try to rewrite simple form of right outer join for the moment.
Export types and cursors declared as global objects in package spec header into the main output file for package export. Types and cursors declared into the package body are exported into the output file of the first function declared in this package.
Globals variables declared into the package spec header are now identified and replaced into the package code with the call to user defined custom variable. It works just like globals variables declared into the package body.
Add auto detection of Oracle FTS stemmer and disable FTS_CONFIG configuration directive per default. When FTS_CONFIG is set its value will overwrite the auto detected value.
Create full text search configuration when USE_UNACCENT directive is enabled using the auto detected stemmer or the one defined in FTS_CONFIG. For example: CREATE TEXT SEARCH CONFIGURATION fr (COPY = french); ALTER TEXT SEARCH CONFIGURATION fr ALTER MAPPING FOR hword, hword_part, word WITH unaccent, french_stem; CREATE INDEX place_notes_cidx ON places USING gin(to_tsvector('fr', place_notes));
Remove CONTAINS(ABOUT()) from the migration assessment, there no additional difficulty to CONTAINS rewrite.
Add ANYDATA to the migration assessment keyword to detect.
Allow conversion of CHAR/VARCHAR2 type with precision in DATA_TYPE directive. For example it's possible to transform all VARCHAR2(32) columns only into PostgreSQL special type uuid by setting: DATA_TYPE VARCHAR2(32):uuid Thanks to sjimmerson for the feature request.
Update year in copyrights
Fix creation of schema when CREATE_SCHEMA+PG_SCHEMA are defined.
Fix renaming of temporary file when exporting partitions.
Move MODIFY_TYPE to the type section
Update documentation about globals variables.
Add export of Oracle's global variables defined in package. They are exported as user defined custom variables and available in a session. Oracle variables assignment are exported as call to: PERFORM set_config('pkgname.varname', value, false); Use of these variable in the code is replaced by: current_setting('pkgname.varname')::global_variables_type; the variable type is extracted from the pacjkage definition. If the variable is a constant or have a default value assigned at declaration, ora2pg will create file global_variables.conf with the definition to include in postgresql.conf file so that their values will already be set at database connection. Note that the value can always modified by the user so you can not have exactly a constant.
Fix migration assessment of view.
Remove call to FROM SYS.DUAL, only FROM DUAL was replaced.
Replace call to trim into btrim.
Improve rewrite of DECODE when there is function call inside.
Add function replace_right_outer_join() to rewrite Oracle (+) right outer join.
Improve view migration assessment.
Create a FTS section in the configuration file dedicated to FTS control.
Add USE_UNACCENT and USE_LOWER_UNACCENT configuration directives to use the unaccent extension with pg_trgm.
Do not create FTS_INDEXES_* file when there is no Oracle Text indexes.
Update query test score when CONTAINS, SCORE, FUZZY, ABOUT, NEAR keyword are found.
Remove use to setweigth() on single column FTS based indexes. Thanks to Adrien Nayrat for the report.
Update documentation on FTS_INDEX_ONLY with full explanation on the Ora2Pg transformation.
Refactoring ora2pg to not requires any dependency other than the Perl DBI module by default. All DBD drivers are now optionals and ora2pg will expect to received an Oracle DDL file as input by default. This makes easiest packaging or for any distribution that can not build a package because of the DBD::Oracle requirement. DBD::Oracle, DBD::MySQL and DBD::Pg are still required if you want Ora2Pg to migrate your database "on-line" but they are optional because Ora2Pg can also convert input DDL file, this is the default now. Thanks to Gustavo Panizzo for the feature request and the work on Debian packaging.
Remove String::Random dependency in rpm spec file, it is no used even if it was mentioned into a comment.
Exclude internal Oracle Streams AQ JMS types from the export. Thanks to Joanna Xu for the report.
Fix some other spelling issues. Thanks to Gustavo Panizzo for the patch.
Fix some spelling errors. Thanks to Gustavo Panizzo for the patch.
Revert patch 697f09d that was breaking encoding with input file (-i). Thanks to Gary Evans for the report.
Add two new configuration directive to control FTS settings, FTS_INDEX_ONLY and FTS_CONFIG.

Version 17.6

View release

2016 11 17 - v17.6

This release adds several new features:

Adds export of Oracle Text Indexes into FTS or pg_trgm based indexes,
Add export of indexes defined on materialized views
Allow export of materialized views as foreign tables when export type is FDW.
Add replacement of trim() by btrim().

Two new configuration directives have been added:

USE_INDEX_OPCLASS: when value is set to 1, this will force Ora2Pg to export all indexes defined on varchar2() and char() columns using *_pattern_ops operators. If you set it to a value greater than 1 it will only change indexes on columns where the character limit is greater or equal than this value.
CONTEXT_AS_TRGM: when enabled it forces Ora2Pg to translate Oracle Text indexes into PostgreSQL indexes using pg_trgm extension. Default is to translate CONTEXT indexes into FTS indexes and CTXCAT indexes using pg_trgm. Some time using pg_trgm based indexes is enough.

There's also some fix of issues reported by users, here is the complete list of changes:

Fixed non-use of custom temp_dir (-T). Thanks to Sebastian Albert for the patch.
Make export of FTS indexes from materialized view work as for tables.
Fix drop of indexes during export of data when DROP_INDEXES is enabled.
Remove double quote in function and procedure name from an input file to avoid creating a file with double quote in its name.
Fix export of unique index associated to a primary key.
Move OPTION (key "yes") of FDW table before NOT NUL constraint and default clause.
Fix some encoding issue during data export into file.
Rename FTS indexes prefix output file into FTS_INDEXES and export CTXCAT Oracle indexes as GIN pg_trgm indexes instead of FTS indexes.
Add export of indexes of type CTXCAT as FTS indexes.
Export triggers and update order for FTS indexes to separate file prefixed with FTS_INDEXES.
Exclude from export synonyms starting with a slash that correspond to incomplete deleted synonyms. Thanks to Nouredine Mallem for the report.
Add export of indexes defined on materialized views. Thanks to Nouredine Mallem for the report.
Fix export of foreign key and FTS indexes when looking at dba_* tables and multiple different schemas have the same fk or context indexes definition. Thanks to Nouredine Mallemfor the patch.
Fix export of CONTEXT or FULLTEXT Oracle index into PostgreSQL FTS with trigger and initial update statement.
Add configuration directive USE_INDEX_OPCLASS to force Ora2Pg to export all indexes defined on varchar2() and char() columns using those operators. A value greater than 1 will only change indexes on columns where the character limit is greater or equal than this value.
Fix FDW export of mysql tables. Thanks to yafeishi for the report.
Fix decode() rewrite. Thanks to Jean-Yves Julliot for the report.
Fix regression introduced into the export of NUMBER to integer like PG types.
Show partition name in progress bar instead of main table name.

Version 17.5

View release

This is a maintenance release to fix several issues reported by users. There is also some major improvements and new features.

There is a new configuration directive or change default behavior:

Fix export of CLOBs and NCLOB that was truncated to 64 Kb.
PG_BACKGROUND : when enabled autonomous transactions will be built using Robert Haas extension pg_background instead of dblink. Default is to still used dblink as pg_background is available only for PostgreSQL >= 9.5.
All Perl I/O now use the open pragma instead of calling method binmode(). This will force input and output to utf8 using the Perl pragma: use open ':encoding(utf8)'; when configuration directive BINMODE is not set or NLS_LANG is set to UTF8.
Ora2Pg will now export empty lob as empty string instead of NULL when the source column has NOT NULL constraint and that directive EMPTY_LOB_NULL is not activated.
Improve and fix progress bar especially when using JOBS/-J option.
Allow LOAD action to apply all settings defined in the input file on each opened session, this allow to use LOAD with export schema enabled. If settings are not set in the input file encoding and search_path is set from the ora2pg configuration settings.
NUMBER(*,0) is now exported as numeric(38) as well as a NUMBER with DATA_SCALE set to 0, no DATA_PRECISION and a DATA_LENGTH of 22. The last correspond to Oracle type INTEGER or INT.
Allow conversion of type with precision in DATA_TYPE directive. For example it is possible to transform all NUMBER(12,2) only into numeric(12,2) by escaping the comma. Example: DATA_TYPE NUMBER(12\,2):numeric(12\,2);...
Write data exported into temporary files (prefixed by tmp_) and renamed them at end of the export to be able to detect incomplete export and override it at next export.
Add export of type created in package declaration.
Export foreign key when the referenced table is not in the same schema.
Enabled by default PG_SUPPORTS_CHECKOPTION assuming that your Pg destination database is at least a 9.4 version.
Add 12 units to migration assessment report per table/column conflicting with a reserved word in PostgreSQL to reflect the need of code rewriting.
Output a warning when a column has the same name than a system column (xmin,xmax,ctid,etc.)
Replace SYSDATE by a call to clock_timestamp() instead of a call to LOCALTIMESTAMP in plpgsql code.
Add missing documentation about DISABLE_PARTITION directive used to not reproduce partitioning into PostgreSQL and only export partitioned data into the main table.

See changelog for a complete list of changes.

Version 17.4

View release

2016 04 21 - v17.4

Errata in first release attempt.

Fix previous patch that does not handle blob case but just clob
Forgot to change back the query when EMPTY_LOB_NULL is not activated.
Put parenthesis around AT TIME ZONE expression

2016 04 20 - v17.4

This is a maintenance release to fix several issues reported by users. There is also some major data export speed improvement thanks to the work of PostgreSQL Pro and a new RPM spec file provided by Devrim Gündüz to be able to build RPM package for Ora2Pg.

There is a new configuration directive:

EMPTY_LOB_NULL: when enabled force empty_clob() and empty_blob() to be exported as NULL instead as empty string.

See changelog for the complete list of changes.

Version 17.3

View release

2016 03 26 - v17.3

This release fix two regressions introduced in latest release.

Fix major bug in data export. Thanks to Frederic Guiet for the report.
Fix another regression with character data that was not escaped. Thanks to Frederic Guiet for the report.

Please, upgrade.

Version 17.2

View release

This is a maintenance release to fix several issues reported in new LOB extraction method. There is also some feature improvement:

Allow NUMBER with precision to be replaced as boolean.
Allow full relocation of Ora2Pg installation using for example: perl Makefile.PL DESTDIR=/opt/ora2pg

Here is the complete list of other changes:

Allow NUMBER with precision to be replaced as boolean. Thanks to Silvan Auer for the report.
Force empty LOB to be exported as NULL when NO_LOB_LOCATOR is activated to have the same behavior.
Fix case where a LOB is NULL and ora2pg reports error : DBD::Oracle::db::ora_lob_read: locator is not of type OCILobLocatorPtr LOB initialised with EMPTY_CLOB() are also exported as NULL instead of \x
Fix replacement with PERFORM after MINUS. Thanks to Stephane Tachoires for the report.
Comment DBMS_OUTPUT.ENABLE calls. Thanks to Stephane Tachoire for the report.
Fix wrong replacement of SELECT by PERFORM after EXCEPT. Thanks to Stephane Tachoire for the report.
Apply ORACLE_COPIES automatic predicate on custom queries set with REPLACE_QUERY if possible. Thanks to pawelbs for the report.
Fix install of ora2pg.conf file in /etc/ instead of /etc/ora2pg/. Thanks to pawelbs for the report.
Add debug information before searching for custom type.
Attempt to fix error "ORA-01002: fetch out of sequence" when exporting data from a table with user defined types and ORACLE_COPIES. Thanks to pawelbs and Alex Ignatov fir the report.
Fix replacement of path to configuration file in scripts/ora2pg
Remove report sample from documentation about migration assessment report and replace it with a href link. Fix comment about export of domain index.
Always prefix table name with schema in Oracle row count, to prevent failure when the schema is not the connexion default.
Add pattern TOAD_PLAN_.* to the internal table exclusion list.
Fix modification of database owner search_path in import_all.sh auto generated script. Thanks to Stephane Tachoire for the report.

Version 17.1

View release

This is a maintenance release to fix several issues reported in new TEST action. There is also some feature improvement:

Add OPTIONS (key 'true') on table FDW export when a column is detected as a primary key.
Add DELETE configuration directive that allow a similar feature than the WHERE clause to replace TRUNCATE call by a "DELETE FROM table WHERE condition". This feature can be useful with regular "updates". Thanks to Sebastien Albert for the feature request.

Here is the complete list of other changes:

Fix the counter of user defined types and sequences in TEST action
Fix COPY import of data from column with user defined type with NULL value.
Fix DBD::Pg segmentation fault with direct INSERT import from column with user defined type.
Fix TEST action with multiple PG_SCHEMA export. Thanks to Michael Vitale for the report.
Fix documentation about PG_SCHEMA

Version 17.0

View release

This new major release adds a new action type TEST to obtain a count of all objects, primary keys, constraints, etc., at both sides, Oracle and PostgreSQL, to perform a diff between the two database and verify that everything have been well imported. It also fixes several issues reported by users.

A new ora2pg command line option have been added to ora2pg script:

Add --count_rows command line option to perform a real row count on both side, Oracle and PostgreSQL, in TEST report.

See changelog for the complete list of changes and bugfixes:

Version 16.2

View release

This release fixes several issues, is more accurate on migration assessment report and adds some new ora2pg command line options:

Add --pg_dsn, --pg_user and --pg_pwd to be able to set the connection to import directly into PostgreSQL at command line.
Add -f option to script import_all.sh to force to not check user/database existing and skip their creation.

Potential backward compatibility issues:

PG_SUPPORTS_CHECKOPTION is now enabled by default, you may want to migrate to PostgreSQL 9.4 or above.
Remove modification of CLIENT_ENCODING in generic configuration file with --init_project, use the default instead.
Remove modification of directive NLS_LANG to AMERICAN_AMERICA.UTF8 in generic configuration file with --init_project, use the default instead.

See changelog the complete list of changes.

- Fix MODIFY_STRUCT that was not working with MySQL. Thanks to Code-UV for the report. - Fix license string in Makefile.PL. Thanks to RodRaen for the report. - Do not remove non alphanumeric character in index name. Thanks to gwidt for the report. - Reorder trigger event when the update of column is not the last one. Thanks to tayalarun1 for the report. - Fix export of MySQL function containing special characters and white spaces in names. Thanks to Shubham Dabriwala for the report. - Fix grant export for partitions. Thanks to elexus for the report. - Add some other transformation for sqlplus/psql scripts. - Remove comma as possible separator for values in DEFINED_PK, it was preventing the use of a function with multiple parameters. - Fix export of geometry tables when PG_SCHEMA is set. - Add rewriting of some sqlplus settings to psql settings. - Fix TABLESPACE export for partitioned tables. Thanks to elexus for the report. - Fix for Issue #1637. Thanks to Simon Pane for the patch. - Fix typo in --init_project directories tree generation for sequences values. - Fix alias in view target list for function call without alias defined in MySQL export. Thanks to Shubham Dabriwala for the report. - Fix Mysql procedure export when a datatype with precision is used in parameter list. Thanks to Shubham Dabriwala for the report. - Fix collation on string default values. Thanks to Shubham Dabriwalafor the report. - Exclude recycle bin object from ALL_TAB_COLUMNS lookup. Thanks to Dave Betterton for the report. - Fix data types translation (TINYINT|SMALLINT|MEDIUMINT|INTEGER|BIGINT|INT) for MySQL table export. Thanks to Shubham Dabriwala for the report. - Do not export synonym destination table with table_owner when EXPORT_SCHEMA is disabled. Thanks to Priyanshi Gupta for the report. - Fully qualify calls to get_sequence_last_values() when PG_SCHEMA is set. Thanks to Marius Hope for the report. - Fix regression on exporting view as table when VIEW_AS_TABLE contains regexp. Thanks to Neil Bower for the report. - Fix missing execution of initial command statements at start of TEST_DATA action and on both side, those applying to source and destination. Thanks to Petter Jacobsen for the report. - Fix script to get sequence last value with TEST action. Thanks to franxav06 for the patch. - Prepend PERFORM before call to DBMS_OUTPUT.* when USE_ORAFCE is enabled. - Disable USE_ORAFCE when export type is SHOW_REPORT. - Extending the enhancement in Pull Request #1621 to the Oracle_FDW user mapping. Thanks to Simon Pane for the patch. - Changed prefix string to "DIFF:" in test report. Thanks to Simon Pane for the patch. - Fix cases where %ROWCOUNT was not correctly replaced. Thanks to Rui Pereira for the report. - Fix parsing of ORACLE_DSN when creating foreign server in COPY mode. Thanks to Luke Davies for the report. - Fix for Issue #1622, #1627. Thanks to Simon Pane for the patch. - Fix index creation with DESC order in COPY action when DROP_INDEXES is enabled. Thanks to Luke Davies for the report. - Fix for Issue #1610, #1612, #1617 and #1381. Thanks to Simon Pane for the patch. - Fix typo in sqlnet.ora name (was sqlnet.or). Thanks to Martin Nash for the patch. - Fix data export, REPLACE_QUERY was not applied. Thanks to Bachev Constantin for the report. - Fix call to replace_sys_context(). - Fix timestamp(n) data type translation. - Remove use of column GENERATION_EXPRESSION for MySQL version < 5.7.0. Thanks to Hans Choi for the report. - Fix conversion of DATE datatype to timestamp(0) instead of timestamp. Thanks to Akhil Reddy for the report. - Add NVARCHAR/NCHAR defaut convertion data types to DATA_TYPE configuration directive in ora2pg.conf comments. Thanks to Akhil Reddy for the report. - Rename method _get_partitions_type function into _get_partitions_list. - Fix synonym export when no schema information is available. - Fix support of REFERENCING clause in triggers. - Fix partition output file renaming with new RENAME_PARTITION directive. Thanks to Rahul Barigidad for the report. - Fix export of the ROWNUM clause when there is a variable. - Fix sprintf placeholders in geometry queries. - Fix some others issues with row count report. - Fix row count with destination schema and when the PostgreSQL table doesn't exist. Thanks to bizen-ya for the report. - Fix tests comparison with the different settings of EXPORT_SCHEMA, SCHEMA and PG_SCHEMA. Thanks to Marius Hope and bizen-ya for the report. - Fix St_AsText() call for MySQL data extraction. - Add column count comparison for MySQL - Export multi column partition by list as an expression with concat operator. Multi column partition by list is not supported by PostgreSQL. - Fix creation of non existant indexes on partition. Thanks to Shubham Dabriwala for the report. - Fix MySQL function export when there is no BEGIN clause. Thanks to Shubham Dabriwala for the report. - Fix MySQL export of unsigned numeric. Thanks to Shubham Dabriwala for the report. - Fix MySQL output with wrong synthax for JOIN without ON clause. Thanks to Shubham Dabriwala for the report. - Fix virtual column export. Thanks to Rafal Hollins for the report. - Fix index creation on partition with no columns for MySQL export. Thanks to Shubham Dabriwala for the report. - Fix export of MySQL auto_increment when PG_INTEGER_TYPE is disabled. Thanks to Shubham Dabriwala for the report. - Fix MySQL subpartition export. Thanks to Sanyam Singhal for the report. - Move any INTO clause in CONNECT BY query to the final SELECT on the resulting CTE. Thanks to taptarap for the report. - Fix translation of MySQL curtime() function in default values. Thanks to Shubham Dabriwala for the report. - Fix possible "Nested quantifiers in regex" error when exporting package with package name containing regex special characters. Thanks to durandm70 for the report. - Fix documentation about use of unique key for ORACLE_COPY. - Fix extra comma at end of a CHECK contraint. Thanks to Shubham Dabriwala for the report. - Always add DROP TYPE statements with package export even if DROP_IF_EXISTS is not enabled. Thanks to Rui Pereira for the report. - Fix default value of simple dot in MySQL export. Thanks to Shubham Dabriwala for the report. - Fix regression in data type translation after fix on unsigned numeric type. Thanks to Shubham Dabriwala for the report.

- Fixed PostgreSQL "relation not found error" in _dump_fdw_table(), PostgreSQL search_path was not being used. Thanks to James Schriever for the patch. - Fix year and month quoting as reserved words when they are used as aliases. Thanks to duursma for the report. - Fix conversion of to_number(substr(...)) when PG substr() return empty string where Oracle return NULL which make the conversion to numeric fail. The fix using (nullif(substr(...), )::numeric) only concern TABLE export. Thanks to Menelaos Perdikeas for the report. - Add export of MySQL KEY and LINEAR KEY partitioning, now exported as HASH partitioning. Thanks to Sanyam Singhal for the report. - Remove $ sign from characters that require object name quoting. - Fix export of objects with the $ sign in the name. Thanks to yano-rxa and duursma for the report. - Prevent translation of EXEC when used as alias. Thanks to Rui Pereira for the report. - Fix MySQL enum data type export with regression introduced by commit 24a476. Thanks to Shivansh Gahlot for the report. - Rename ORACLE_FDW_TRANSFORM to TRANSFORM_VALUE to apply in all case an expression when retrieving data from Oracle. For example: TRANSFORM_VALUE CLOB_TABLE[CHARDATA:translate("CHARDATA", chr(0), ' ')] to replace all Oracle char(0) in a string by a space character. - Fix add_month() translation failing with some use cases. Thanks to duursma for the report. - Add export of CHECK constraints for MySQL >= 8.0. Thanks to Rahul Barigidad for the report. - Fix MySQL unsigned numeric data type conversion. Thanks to Rahul Barigidad for the report. - Add Functional/Expression indexes fail while exporting from MYSQL. Thanks to Shubham Dabriwala for the report. - Fix export of descending indexes for MySQL. Thanks to Shubham Dabriwala for the report. - Force MySQL auto increment sequence to start at 1 when value is 0. Thanks to Rahul Barigidad for the report. - Fix conversion of MySQL decimal(p,s) to keep the original datatype instead of use of real or double. - Add export of MySQL virtual column. Thanks to Shubham Dabriwala for the report. - Fix export of MySQL function with return clause only. Thanks to Shubham Dabriwala for the report. - Remove scale and precision of a numeric if the scale is higher than the precision. PostgreSQL does not support decimal/numeric attributes where the scale is explicitly higher than the precision. Thanks to Rahul Barigidad for the report. - Fix export of comment for views. Thanks to gh-k-murata for the report. - Add command line option --drop_if_exists to add statement to drop objects before creation if tehy exists. It corresponds to the DROP_IF_EXISTS configuration directive. Thanks to Yoni Sade for the feature request. - Add option --mview_as_table to documentation. - Add configuration directive MVIEW_AS_TABLE and command line option --mview_as_table to set which materialized view to export as table. By default none. Value must be a list of materialized view name or regexp separated by space or comma. If the object name is a materialized view and the export type is TABLE, the view will be exported as a create table statement. If export type is COPY or INSERT, the corresponding data will be exported. - Disable EXPORT_GTT when export type is not TABLE. Thanks to gh-k-murata for the report. - Fix generated external servers wrongly placed in a schema. Thanks to duursma for the report. - Add configuration variable FDW_IMPORT_SCHEMA to rename the schema where foreign tables for data migration will be created. If you use several instances of ora2pg for data migration through the foreign data wrapper, you might need to change the name of the schema for each instance. Default to ora2pg_fdw_import. Thanks to James Schriever for the feature request. - Fix wrong conversion of rownum clause when a subquery is used. Thanks to Rui Pereira for the report. - Escape comma and backslashes in BFILE data export. Thanks to duursma for the patch. - Fix possible infinite loop in Oracle outer join parsing. Thanks a lot to yano-rxa for the report. - Remove privileges default settings on views exported from SYNONYMs - Add support for Rectangle geometry type. Thanks to duursma for the patch. - Fix double replacement of IS NULL/IS NOT NULL when NULL_EQUAL_EMPTY is enabled. - Add CHECK not null only constraints to not null constraint count. - Fix CHECK NOT NULL only constraints that was not exported by generating NOT NULL constraints instead. They are exclude from the count of CHECK constraint as suggested by Florent Jardin but a count difference persist for NOT NULL constraints. - Fix TYPE export when SCHEMA and PRESERVE_CASE have different values. Thanks to Florent Jardin for the report. - Fix custom exception replacement. Thanks to Rui Pereira for the report. - Fix Collection and Polygon geometry INTERNAL export. Thanks to duursma for the patch. - Fix export of efile with parenthesis. Thanks to duursma for the report. - Fix wrong column indices used in spatial index extraction. Thanks to duursma for the report. - Fix call of ST_GeomFromText() with WKT export. Thanks to duursma for the report. - Disable USE_LOB_LOCATOR with WKT geometry export type, ST_GeomFromText and SDO_UTIL.TO_WKTGEOMETRY functions return a CLOB instead of a geometry. Thanks to duursma for the report. - Fix INTERNAL conversion uses the srid from the object instead of the meta-data. Thanks to duursma for the report. - Fix regression in data export when REPLACE_AS_BOOLEAN is set. Thanks to Juri Berlanda for the report. - Fix call to procedure using dblink. Thanks to Rui Pereira for the report. - Keep untouched call to DBMS_OUTPUT functions if USE_ORAFCE is enabled. Thanks to Sanyam Singhal for the report. - Partial fix for MySQL subpartitioning export. - Fix partitions export for MySQL. Thanks to Sanyam Singhal for the report. - Fix generation of export_all.sh following the operating system. - Add information of use of PARALLEL_TABLES with COPY, INSERT and TEST_DATA actions. It is also useful with TEST, TEST_COUNT, and SHOW_TABLE if --count_rows is used for real row count. - Prevent calling real rows count twice with TEST action, and allow it for the SHOW_TABLE action. - Handle count errors when single process. - Move row count wait for all child die to the right place - Fix rewrite of nested replace() functions in CHECK constraint. Thanks to Menelaos Perdikeas for the report. - Fix call of procedures with out parameters when it is not declared in a package. Thanks to taptarap for the report. - Some minor code improvement. Thanks to Markus Elfring for the patch. - Set encoding to read configuration file to utf8. - Remove useless multiple semi-colon after END of a function. - Fix conversion of regexp_replace() by always appending the 'g' modifier. Thanks to Rui Pereira for the report. - Fix synonym detection to avoid listing public synonym when no schema is specified. Thanks to Dilan Salinda for the report. - Fix regexp error with multi-line comment in default value declaration. Thanks to taptarap for the report. - Add missing sub-partition key in partitioned table primary key. Thanks to downvoteit for the report. - Replace all invalid date starting with zero year 0000- to 1970-01-01 when it is a default value and NULL for data. Old behavior was to only replace 0000-00-00 date. Thanks to duursma for the report. - Enclose \i path to data file beween quote to fix import of table with space in their name. - Add PARTITION to the list of reserved work and fix custom keywords list from ORA_RESERVED_WORDS that was not applied. Thanks to markhooper99 for the report. - Add LOAD of pgtt extension before creating global temporary table with TABLE :export. Thanks to duursma for the report. - Fix case where package names should be lower cased. Thanks to Sergey Petrov for the patch. - Cover more case where ALTER ... OWNER TO should not be generated. - Fix case where ALTER ... OWNER TO should not be generated when a view as table definition was not exported. - Fix sub-partition unique and primary keys that lacks columns part of the partition key. Thanks to downvoteit for the report. - Path for function_per_file are mixed case enabled now. Thanks to Sergey Petrov for the patch. - Fix AUTOINCREMENT script to set last value to sequences for serial an identity column for PG version < 12. Thanks to Jaouad Bouras for the report. - Fix detection of ENUM data type for MySQL - Fix issue when exporting table with a geometry column. The search for the SDO_GTYPE need a FROM clause with a FQDN table when the connection user is not the same as the table schema. Thanks to Argo64 for the report. - Rewrite numeric operation with ADD_MONTH(), LAST_DAY() and TRUNC() to use interval. Thanks to duursma for the report. - Fix rewrite of CONNECT BY in cursors and just after a BEGIN. Thanks to taptarap for the report. - Add partition keys to unique index on partitioned table. Thanks to downvoteit for the report. - Fix case where global variable are tested against NULL. Thanks to duursma for the report. - Fix remove of %ROWTYPE in function argument and returned data type. Add regression test. Thanks to Eric Delanoe for the report. - Fix case clause in autoincrement parameters. Thanks to jbouras for the report. - Fix typo in ORACLE_FDW_TRANSFORM example. - Fix progress bar output in quiet mode. Thanks to Sanyam Singhal for the report. - Fix error Can't locate object method "gzclose" via package "IO::File". Thanks to Sanyam Singhal for the report. - Fix cases where translation of function with out parameter was not done correctly. - Fix translation of function with out parameter that returns a value. For example the following Oracle function: CREATE FUNCTION foo(a int, OUT b int) RETURN boolean IS BEGIN b := a; RETURN true; END; is now translated by adding an extra out parameter for the return value: CREATE OR REPLACE FUNCTION foo (a integer, OUT b integer, OUT extra_param boolean) RETURNS record AS $body$ BEGIN b := a; extra_param := true; RETURN; END; $body$ LANGUAGE PLPGSQL STABLE; Thanks to Akhil Reddy for the report. - Fix undefined database connection handle. Thanks to Alexander for the report - Fix case preservation for row count in Oracle side with TEST_COUNT action.. Thanks to Veka for the report. - Only generate the Powershell script when we are running on a Windows operating system. - Fix #1400 and generate PowerShell script "export_schema.ps1". Thanks to moh-hassan for the report. - Fix rewriting assignment of a global variable using SELECT INTO. Thanks to duursma for the report. - Fix partition export for MySQL. Thanks to Sanyam Singhal for the report. - Apply WHERE clause to FDW data export. - Fix useless ST geometry parsing. Thanks to jieguolove for the report. - Replace backslash with slash in BFILE filename when destination data type is text or efile. - Fix RAW(16)/RAW(32) data export when MOFDIFY_TYPE is used on the column. Thanks to Sergey Evseev for the report. - Fix ST_SRID() call. Thanks to jieguolove for the report. - Skip table data export when the table has no column defined. This was generating a fatal error. - Fix untranslated function returned data type when there was a comment just after. The comment is removed. Thanks to taptarap for the report. - Fix other fetching all-column-all-table properties for every table. Thanks to Sergey Petrov for the report. - Fix fetching all-column-all-table properties for every table. Thanks to Sergey Petrov for the report. - Remove any comments between RETURN and returned type to not break parsing. Thanks to taptarap for the report. - Fix global variables in the DECLARE section are not replaced if used with a package name. Thanks to taptarap for the report. - Remove renaming of dist configuration file under Windows OS. Thanks to Julien Monticolo and ohamed Hassan for the report. - Fix remaining data export query failure. Thanks to Sung Woo Chang for the report. - Fix data export, query to retrieve data was broken since change for GTT. - Set function as VOLATILE when there is CALL in the body. - Add support to PG14 procedure out parameters. Thanks to Rui Pereira for the feature request. - Fix missing parenthesis in index column expression with input file. - Fix missing END keyword after embedded CASE clause in a package function. Thanks to taptarap for the report. - Fix conversion of dbms_lob.substr() where second and third parameters must be inverted. Thanks to taptarap for the report. - Fix an other case of wronf NOT NULL detection from input file. - Fix detection of NOT NULL constraint in input file. - Do not quit on error "Undefined subroutine &Ora2Pg::ReadLine", just continue to be able to leverage an Oracle Wallet (SEPS) when no Oracle user and password are provided. If you want to use the interactive mode to type the username and password at command line you must install the Perl package Term::ReadKey before. Thanks to Simon Pane for the report. - Fix partitioning by LIST, only the first value of a list was exported. Thanks to Sergey Grinko for the report. - Fix quoting of DEFAULT NULL. Thanks to Veka for the report. - Fix unwanted multiple CALL keywords. Thanks to taptarap for the report. - Add assessment counter for FND_* packages. - Fix LONG RAW export as bytea in COPY mode. Thanks to Helena Adiduyulmus for the report. - Add new configuration option NO_EXCLUDED_TABLE. By default Ora2Pg exclude from export some Oracle "garbage" tables that should never be part of an export. This behavior generates a lot of REGEXP_LIKE expressions which are slowing down the export when looking at tables. To disable this behavior enable this directive, you will have to exclude or clean up later by yourself the unwanted tables. The regexp used to exclude tables are defined in the array @EXCLUDED_TABLES in lib/Ora2Pg.pm This behavior is independent to the EXCLUDE configuration directive. Thanks to Peter Humaj for the feature request. - Replace all remaining CURSORNAME%NOTFOUND with NOT FOUND - Change translation to SYSDATE from LOCALTIMESTAMP to statement_timestamp() in non PL/SQL code. - Prevent append of SECURITY DEFINER when a procedure execute transaction control statements (ex: COMMIT). When defined with this clause an error is thrown. Thanks to Suman Michael for the report.

- 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

- Fix replacement of TO_CLOB() function, now it is just removed and the parenthesis are kept. Thanks to Rui Pereira for the report. - Fix incorrect detection of cursor on dynamic query. Thanks to Rui Pereira for the report. - Fix quoting column names with spaces and dots. Dots are replaced by underscore. Thanks to Veka for the report. - Fix one case where DEFINED_PKEY with PRESERVE_CASE was not handled correctly. Thanks to Veka for the report. - Fix quoting of reserved keywords in CREATE INDEX columns names. Thanks to Veka for the report. - Fix column name starting with number not quoted in COMMENT. Thanks to Veka for the report. - Fix addition of PERFORM on call to stored procedures not prefixed by the package name. Thanks to Rui Pereira fo the report. - Fix search of ora2pg_conf.dist under Windows instead of ora2pg.conf.dist when --init_project is used. Thanks to Julien Monticolo for the report. - Fix translation from file of check constraint when created on same column, only the last one was exported. Also shortened the prefix for constraint naming, ora2pg_ckey becomes o2pc, ora2pg_ukey is now o2pu and ora2pg_fkey is renamed into o2pf. Thanks to anvithaprabhu8 for the report. - Replace wildcard precision * for numeric by 38. - Fix incomplete listagg() conversion. Thanks to avandras for the report. - Fix potential problem in last_day conversion when USE_ORAFCE is off and a number is added or subtracted to the last day. Thanks to atlterry for the report. - Add DROP_IF_EXISTS configuration directive. Thanks to dherzhau for the feature request. - Do not apply utf8 conversion of comments to input files. - Fix termination of last writer process when parallel and quiet mode are used together. Thanks to David Harper for the patch. - Remove precision in number of digit in timestamp microseconds when setting NLS_TIMESTAMP_FORMAT at session startup. Now use: ALTER SESSION SET NLS_TIMESTAMP_FORMAT='YYYY-MM-DD HH24:MI:SS.FF' instead of '.FF6'. This was possibly the cause of sporadic error: ORA-03106: fatal two-task communication protocol error (DBD ERROR: OCIStmtFetch) Default is precision 6 so it may not change anything. Thanks to Abhijeet Bonde for the report. - Fix call to deprecated MySQL spatial function when version is after 5.7.6. Thanks to naveenjul29 for the report. - Fix false positive detection for nested table with MySQL export. - Fix sequence export read from file. - Add PG_VERSION to the documentation. Thanks to xinjirufen for the report. - Fix error report when preparing query to ALL_IND_COLUMNS IC. Thanks to ganeshakorde for the report. - ora2pg_scanner: Fix detection of service_name in DSN. - Fix error raise_application_error() with named parameters. - Fix replacement of out parameters in triggers. - Change sequence export result storage to hash instead of array. - Fix package export when there is a comment between PACKAGE BODY and the name of the package. - Fix Perl error "malformed utf-8 character in substitution" when there is character not in utf8 in the comment or constants. - Fix detection of MySQL FUNCTION vs PROCEDURE for version >= 5.5. Thanks to naveenjul29 for the report. - Exclude nested tables from the export as it is not supported and it always generate an error. A warning is raised. - Fix column case in check constraints when PRESERVE_CASE is enabled. - Fix search_path in direct PG data export when PG_SCHEMA is set. - Fix -Infinity insert for direct PG data export. - Fix drop indexes when PRESERVE_CASE is enabled. - Remove potential double affectation for function with out parameter. - Create immutable to_char function when used in an index. - Replace dmake by gmake on Windows installation instruction. Thanks to Julien Monticolo for the report. - Fix MySQL version conditions. Thanks to Christoph Berg for the report. - Fix HASH partitioning for duplicate WITH clause. - Fix tests when no schema are set to compare all objects in all schemes. Thanks to gp4git and dlc75 for the report. - Apply ALLOW/EXCLUDE without object to table object by default in TEST action. Thanks to Yony Sade for the feature request. - Add DROP_IF_EXISTS configuration directive. Thanks to dherzhau for the feature request. - Fix regression in removing %ROWTYPE from function parameters. Thanks to Eric Delanoe for the report. - Fix not adding default values to parameters when this is an OUT parameter. Thanks to Eric Delanoe for the report. - Fix ALL_DIRECTORIES call be using table name relative to USER_GRANTS. Thanks to Yoni Sade for the report. - Change all remaining call to static ALL_* tables to a call relative to USER_GRANTS. Thanks to Yoni Sade for the report. - Fix export or partitioned table with unsupported partitioning type like PARTITION BY REFERENCE. The table is created without partition and a warning it fired as well as the following message in the output file as a comment: "-- Unsupported partition type, please check"

- Take Geometry SRID from the data and fallback to SRID defined in metadata when not found. Thanks to Sebastian Albert for the report. - Fix case where Ora2Pg temporary substitution of '' by placeholder was not restored. Thanks to MigOps.com for the patch. - Fix identity column export on unsupported Oracle 18c options. Thanks to MigOps.com for the patch. - Fix export of columns indexes created with single quote. Thanks to MigOps.com for the patch. - Fix replacement of keyword PROCEDURE by FUNCTION in constraints constants definition. Thanks to marie-joechahine for the report. - Replace IS JSON validation clause in CHECK constraints. Thanks to marie-joechahine for the report and MigOps.com for the patch. - Add support to ON OVERFLOW clause in LISTAGG replacement. Thanks to MigOps.com for the patch. - Fix incorrect handling of HAVING+GROUP BY rewriting. Thanks to MigOps.com for the patch. - Add replacement of TO_NCHAR by a cast to varchar. Thanks to MigOps.com for the patch. - Fix replacement of NOTFOUND when there is extra space or new line in the WHEN EXIT clause. Thanks to MigOps.com for the patch. - Fix a regression in NO_VIEW_ORDERING, it was not taken in account anymore. Thanks to RonJojn2 for the report. - Replace DATA_TYPE with DTD_IDENTIFIER in MySQL catalog queries for version prior 5.5.0. Thanks to zejeanmi for the report. - Fix import script to import sequences before tables. Thanks to MigOps.com for the patch. - Fix detail report of custom type in migration assessment. Thanks to MigOps.com for the patch. - Fix duplicate schema prefixed to SYNONYM. Thanks to dlc75 for the reports. - Replace NO_LOB_LOCATOR with USE_LOB_LOCATOR and NO_BLOB_EXPORT with ENABLE_BLOB_EXPORT to avoid confusion with double negative variable. Thanks to Rob Johnson for the report. - Fix some missing replacements of NVL and rewrite !=-1 into != -1. Thanks to MigOps.com for the patch. - Fix ROWNUM followed by + or - operator and when no aliases are provided. Thanks to MigOps.com for the patch. - Add DBSFWUSER to the list of user/schema exclusion. Thanks to MigOps.com for the patch. - Fix regexp to not append subquery aliases on JOIN clause. Thanks to Rui Pereira for the report. - Handle PRESERVE_CASE and EXPORT_SCHEMA in sequence name. Thanks to marie-joechahine for the report. - Add CREATE SCHEMA statement to sequence export when EXPORT_SCHEMA is enabled. Thanks to marie-joechahine for the report. - Fix duplicate index name on subpartition. Thanks to Philippe Beaudoin for the report. - Exclude sequences used for IDENTITY column (ISEQ$$_). Thanks to marie-joechahine for the report. - Fix parsing from file of CREATE SEQUENCE. Thanks to Rui Pereira for the report. - In export_all.sh script use the database owner provided if it is a superuser instead of postgres user. Thanks to jjune235 for the feature request. - Fix parsing of triggers when there is a CASE inside the code. Thanks to Rui Pereira for the report. - Add set application name in connection to Oracle/MySql/PostgreSQL. Thanks to Yoni Sade for the patch. - Fix double column alias when replacing ROWNUM. Thanks to Rui Pereira for the report. - Add translation of the REGEXP_COUNT function and change assessment cost. - Rewrite the way REGEXP_LIKE is translated into regexp_match to support modifiers. This rewrite also fix default behavior between Oracle and PostgreSQL. Thanks to otterrisk for the report. - Add IS JSON to assessment. Thanks to marie-joe Chahine for the report. - Fix multi-columns RANGE partitioning. Thanks to Philippe Beaudoin for the report. - Improve reordering columns. Sort by fieldsize first, if same size then it sorts by original position. Thanks to Sebastien Caunes for the patch. - Append partition's column to the primary key of the table as it must be part of the PK on PostgreSQL. Thanks to xinjirufen for the report. - Fix partition export where PRESERVE_CASE was applied to Oracle side. Thanks to schleb1309 for the report. - Fix trigger export with column restriction. Thanks to Sebastien Caunes for the report. - Update installation information. - Fix table reordering following data type. Thanks to Sebastien Caunes for the patch. - Fix incorrect variable name corresponding to DATA_EXPORT_ORDER making this directive inefficient. Thanks to Ron Johnson for the report. - Fix translation of check constraint when read from file - Fix EMPTY_LOB_NULL, enable/disable as inverted, keep default to enabled. Take care that in old ora2pg.conf it is disabled so it will break backward compatibility with old configuration. - Fix false positive detection of input filename is the same as output file. - Rename variables SCHEMA_ONLY, DATA_ONLY and CONSTRAINTS_ONLY in script import_all.sh to conform to their real use. Thanks to Sebastien Caunes for the report. - Fix comment detection breaking the package header parsing and global variable detection. - Fix ROWNUM detection for replacement by LIMIT - Fix escaping of psql command in configuration file comment and set default value for PG_VERSION to 12. - Replace precision by exactness in documentation. Thanks to Sebastien Caunes for the report. - Prevent reducing DATA_LIMIT when NO_BLOB_EXPORT is enabled. Thanks to Thomas Reiss for the report. - Fix geometry type detection. - Add autodetection of geometry type, srid and dimension for ArcGis geometries. Thanks to changmao01 for the feature request. - Fix call to ST_GeomFromText when no SRID is found. - Fix case where OVERRIDE SYSTEM VALUE clause could be added if PG version is < 10. Thanks to changmao01 for the report. - Fix unwanted call to internal GEOM library for ArcGis geometries. Thanks to changmao01 for the report. - Exclude schema SDE (ArGis) from export. Thanks to changmao01 for the report. - prevent looking twice to same custom data type definition. - Fix previous patch to catch SDO_GEOMETRY on lowercase regexp. - Limit detection of geometry data type to SDO_GEOMETRY. - Fix column name replacement in view definition. Thanks to Amit Sanghvi for the report. - Fix REPLACE_COLS parsing to allow space in column name. Thanks to Amit Sanghvi for the report. - Fix translation from file of triggers with WHEN clause. Thanks to Rui Pereira for the report. - Fix column name kept lowercase in the MOD() clause when -J is used. Thanks to Code-UV for the report. - Keep case of PG_SCHEMA definition when used in TEST action. - Fix data export for columns with custom data type. Thanks to Aymen Zaiter for the report. - Fix missing bracket with || operator in CREATE INDEX. Thanks to traxverlis for the report. - Fix export of single row unique function base index. Example: CREATE UNIQUE INDEX single_row_idx ON single_row ((1)); Thanks to unrandom123 for the report. - Update documentation about schemas used in TEST action. - Disable materialized view export with MySQL export it is not supported. Thanks to naveenjul29 for the report. - Fix table alias detection in Oracle (+) join rewrite. - Fix an infinite loop in Oracle (+) join rewrite when there is no table aliases and the table is prefixed by its schema. Thanks to Olivier Picavet for the report. - Fix MODIFY_STRUCT when column name need to be escaped. Thanks to helmichamsi10 for the report. - Fix empty PARTITION BY () clause. Thanks to Aymen Zaiter. - Fix export of global variable from package description when there is no package body. Thanks to naveenjul29 for the report. - Add package description export when dumping package source, previously only the package body was dump. This will allow to check global variables export. - Whilst working on the Reproducible Builds effort (https//reproducible-builds.org/) it appears that ora2pg could not be built reproducibly. Thanks to Chris Lamb for the patch. - Fix case of NUMBER(*,10) declaration. Oracle has a precision of 1 to 38 for numeric. Even if PostgreSQL allow a precision of 1000 use 38 to replace junk parameter. Thanks to xinjirufen for the report. - Add conversion of default value in function parameters, like syssdate rewriting for example. Thanks to unrandom123 for the report. - Fix a regression in data encoding when exporting data introduced in commit fa8e9de. Thanks to gp4git for the report. - Add debug information about the environment variables used before connecting to Oracle. - Fix case of duplicate between unique index and unique constraint with multiple columns. Thanks to gp4git.

- Fix unwanted references to PK/UK when DROP_INDEXES is enabled. - Fix comparison between function name in TEST report. - Fix duplicates on retrieving partitions information. - Improve SHOW_TABLE report about partitioned tables information. - Drop code about removing DEFAULT NULL in functions parameters. Thanks to chaluvadi286 for the report. - Fix two other case where materialized view can be listed in the table list. - Fix case where materialized view can be listed in the table list. Thanks to Thomas Reiss for the report. - Fix %ROWTYPE removing to be restricted to REF CURSOR. Thanks to jagmohankaintura-tl for the report. - Fix PG functions count when comparing Oracle functions count in TEST action. Remove useless -l option to import_all.sh auto generated script. - Fix PRESERVE_CASE on schema name for functions extracted from a package. - Fix search_path adding public default schema. - Apply PRESERVE_CASE to partition by involved columns. - Add IF EXIXTS to create schema to avoid error when import_all.sh is run several time. - Fix sort order of comment on columns for tables and views. - Fix warning about data export from nonexistent table resulting of index lookup on nested table. - Fix infinite loop in global variables package extraction. Thanks to Thomas Reiss for the report. - Fix global variables and packages export when comments are present in the package description. - Add information about XML_PRETTY size limit to 4000 - Fix column name in indexes when PRESERVE_CASE is enabled. Thanks to Julien traxverlis for the report. - Fix Top 10 of largest tables sort order. Thanks to Tom Vanzieleghem for the patch. - Fix duplicates between indexes and constraints. Thanks to sdpdb and Jon Betts for the report. - Fix SYSDATE replacement and possible infinite loop in SYSDATE parsing. Thanks to pbidault for the report. - Fix export of Oracle TEXT indexes with USE_UNACCENT disabled. Thanks to Eric Delanoe for the report. - Add new configuration directive ALTERNATIVE_QUOTING_REGEXP to support the Alternative Quoting Mechanism ('Q' or 'q') for String Literals. Thanks to just-doit for the report. - Fix OF clause missing in update triggers. Thanks to just-doit for the report. - Fix IS NULL translation in WHERE clause of UPDATE statement. Thanks to Eric Delanoe for the report. - Remove DDL export of LOG indexes on materialized views. - Fix unexpected materialized view listed in table export. Thanks to jagmohankaintura-tl for the report. - Fix default values with single quote in create table DDL. Thanks to justdoit for the report. - Fix double quote in CREATE TRIGGER code and applying of preserve case on column name. - Supply credentials interactively when a password is not defined in configuration file. Thanks to rpeiremans for the patch. - Add supports oracle connections "as sysdba" with username "/" and an empty password to connect to a local oracle instance. Thanks to rpeiremans for the patch. - Fix documentation about materialized view export. - Fix export order of comments on columns. - Fix export of views comments when no schema is used for export and export schema is activated. - Fix cast in replacement with TO_NUMBER and TO_CHAR in indexes. Thanks to Kiran for the report. - Add MGDSYS (Oracle E-Business Suite) to the list of schemas excluded from the export. Thanks to naveenjul29 for the report. - Add more information about PG_DSN use. Thanks to Pepan7 for the report. - Update copyright year. - Fix regression where "SET client_encoding TO ..." was missing data file header. Thanks to Emmanuel Gaultier for the report. - Fix EDITABLE vs EDITIONABLE parsing. Thanks to Naveen Kumar for the report. - Fix typos in documentation. Thanks to swallow-life, ChrisYuan, Edward Betts, Jack Caperon and cavpollo for the patches. - Add OVERRIDING SYSTEM VALUE to INSERT statement when the table has an IDENTITY column. Thanks to Robin Windey for the report - Remove empty parenthesis of identity column options - Limit sequence/identity column value to bigint max - Add an example of DBD::Oracle DSN with 18c. - Fix parsing of identity column from file. Thanks to deepakp555 for the report. - Fix quoting of identifier when PRESERVE_CASE is enable and no particular schema is specified. Thanks to mkgrgis for the report. - Move setting of search_path before truncate table. Thanks to Michael Vitale for the report. - Add explanation about TEST and SIZE migration assessment values. - Mark XMLTYPE as having LOB locator. - Fix XMLTYPE columns that are exported as lob locator. Thanks to Tamas for the report. - Fix a problem of data export throughput that was slowing down all along the export when multiprocess for output was not used. Ora2Pg was forking a process for each chunk of data (see DATA_LIMIT) which is useless when write output is done on a single process (-j 1) and slow down the export. Thanks to markhooper99 and Tamas for reporting, testing and finding the source of the issue. - Fix progress bar in multiprocess mode, update was not displayed at each chunk of data processed. - Add internal debug information for progress bar. - Add debug information for SHOW_REPORT - Fix a long pending issue with custom data type export. Thanks to jhollandsworth for the patch. - Fix LOB data export with value changed to NULL when the CLOB value was 0. Thanks to jhollandsworth for the report. - Fix escape format issue with COPY and bytea. Thanks to Christoph Noel and dwbrock62 for the report. - Add LD_LIBRARY_PATH and PATH prerequisite to run ora2pg. - Fix use of the HIGH_VALUE column in partition listing with Oracle 9i. Thanks to Francisco Puga for the report. - Update the table row count logic to incorporate the PostgreSQL table FQN as established through the set_pg_relation_name routine. Thanks to Jacob Roberts for the patch. - Add the PostgreSQL FQN when printing the results in the TEST function. Thanks to Jacob Roberts for the patch. - Do not look forward function with the SHOW_* action - Fix BLOB export where \x was escaped. Thanks to Christophe Noel for the report. - Update Ora2Pg.pm to fix symbol in column name in create index statement. Thanks to kpoluektov for the patch. - Fix package function extraction when there is a start of comment (/*) in a constant string. Thanks to Tiago Anastacio for the report. - Fix type detection in package declaration. Thanks to Tiago Anastacio for the report. - Avoid displaying error ORA-22831 when exporting LOB. This error can appears when LOB chunk size is different from default 8192. The error has no incidence on the export so we can just ignore it. This patch also use DBD::Oracle ora_lob_chunk_size() method to gather chunk the chunk size of the LOB, fallback to 8192 if not available. Thanks to joedbadmin for the report. - Disable direct report of Oracle errors, all error should be handled at Ora2Pg level. - Fix MySQL data export with allow/exclude objects. Thanks to Manuel Pavy for the report. - Fix exclude/allow object feature in MySQL export that was not working since release 19.0. Thanks to Manuel Pavy for the report. - Add rewrite of MySQL JOIN with WHERE clause instead of ON. Thanks to Marc Rechte for the report. - Fix issue with custom type when multiprocess is used. - Fix progress bar on final total estimated data in multiprocess mode. - Fix ORACLE_HOME path in README.md. Thanks to Lubos Cisar for the patch. - Fix missing replacement with PERFORM in CASE ... WHEN statements. Thanks to Eric Delanoe for the report. - Fix duplicate ora2pg command in iteration. - Improve ora2pg_scanner port on Windows OS. Thanks to Marie Contencin for the report. - Add perl call to all ora2pg commands when the scanner is executed on Windows system as the shebang is not recognized. Thanks to Marie Contencin for the report. - Fix several issue with compressed output. Thanks to Bach Nga for the report. - Fix translation of CURSOR IS SELECT with a comment before the SELECT. Thanks to Izaak van Niekerk for the report. - Fix export of procedures as PostgreSQL procedures with version 11. - Add APEX_040000 to the schemas exclusion list. Thanks to Don Seiler for the report. - Fix possible unquoted default values. Thanks to Marc Rechte for the report. - Fix MySQL SET TRANSACTION clause when TRANSACTION is set to readonly or readwrite this is not supported so fall back in READ COMMITTED isolation level in this case. Thanks to Marc Rechte for the report. - Fix export of functions, column DATA_TYPE does not exists in table INFORMATION_SCHEMA.ROUTINES before MySQL 5.5.0. Replace it with column DTD_IDENTIFIER for prior version. Thanks to Marc Rechte for the report. - Fix double quote in CREATE TRIGGER code and applying of preserve case on column name.

* Add PG_VERSION configuration directive to set the PostgreSQL major version number of the target database. Ex: 9.6 or 10. Default is current major version at time of a new release. This replace the old PG_SUPPORTS_* configuration directives. * Removed all PG_SUPPORTS_* configuration directives minus PG_SUPPORTS_SUBSTR that is related to Redshift engine. * Export of BFILE as bytea is now done through a PL/SQL function to extract the content of a BFILE and generate a bytea data suitable for insert or copy into PostgreSQL. * Foreign keys that reference a partitioned table are no more exported. * Show table name on Oracle side during export using at connection time: DBMS_APPLICATION_INFO.SET_ACTION(table_name); * When the date format is ISO and the value is a constant the call to to_date() is removed and only the constant is preserved. For example: to_date(' 2013-04-01 00:00:00','SYYYY-MM-DD HH24:MI:SS') is replaced by a simple call to: ' 2013-04-01 00:00:00'. This rewrite is limited to PARTITION export type when directive PG_SUPPORTS_PARTITION is enabled. * Add DATA_EXPORT_ORDER configuration directive. By default data export order will be done by sorting on table name. If you have huge tables at end of alphabetic order and are using multiprocess, it can be better to set the sort order on size so that multiple small tables can be processed before the largest tables finish. In this case set this directive to size. Possible values are name and size. Note that export type SHOW_TABLE and SHOW_COLUMN will use this sort order too, not only COPY or INSERT export type. * Add NO_BLOB_EXPORT configuration directive. Exporting BLOB could take time and you may want to export all data except the BLOB columns. In this case enable this directive and the BLOB columns will not be included into data export. The BLOB column must not have a NOT NULL constraint. Thanks to Ilya Vladimirovich for the * Add PREFIX_SUB_PARTITION to enable/disable sub-partitioning table prefixing in case of the partition names are a part of the sub- partition names. * Add special replacement for case of epoch syntax in Oracle: (sysdate - to_date('01-Jan-1970', 'dd-Mon-yyyy'))*24*60*60 is replaced by the PostgreSQL equivalent: (extract(epoch from now()))

- Export indexes and constraints on partitioned table with pg >= 11. - Fix incorrect replacement of NLS_SORT in indexes. - Bring back DISABLE_UNLOGGED feature. Thanks to Jean-Christophe Arnu for the patch - Fix CREATE SCHEMA statement that was not written to dump file. - Fix DBMS_APPLICATION_INFO.set_action() call, old Oracle version do not support named parameters. - Fix duplicate index name on partition. Thanks to buragaddapavan for the report. - Add support to new configuration directive PG_VERSION to control the behavior of Ora2Pg following PostgreSQL version. - Fix error in creation of default partition with PostgreSQL 10. Thanks to buragaddapavan for the report. - Fix missing export of single MAXVALUE partition, this will produce the following range partition: ... FOR VALUES FROM (MINVALUE) TO (MAXVALUE) Previous behavior was to not export partition as it is better to not partition the table at all. However it is declared in Oracle so it is better to export it to see what can be done. Thanks to buragaddapavan for the report. - Do not export foreign keys that reference a partitioned table. Remove NOT VALID on foreign keys defined on a partitioned table if present. Thanks to Denis Oleynikov for the report. - Fix export of BFILE as bytea. Ora2Pg now use a PL/SQL function to extract the content of a BFILE and generate a bytea data suitable for insert or copy into PostgreSQL. Thanks to RickyTR for the report. - Add TIMEZONE_REGION and TIMEZONE_ABBR to migration assessment, no direct equivalent in PostgreSQL. Remove NSLSORT not used in migration assessment. Thanks to buragaddapavan for the report. - Fix output of multiple export type specifed in TYPE directive. - Rewrite and renaming of _get_sql_data() function into _get_sql_statements(). - Limit CURSOR weight in migration assessment to REF CURSOR only, other case are all covered. REF CURSOR might need a review to see if they need to be replaced with a SET OF RECORD. - Fix replacement of EMPTY_CLOB() or EMPTY_BLOB() with empty string when EMPTY_LOB_NULL is disabled and NULL when it is enabled. - Prefix output file with the export type in multiple export type mode, ex: sequence_output.sql or table_output.sql. Thanks to buragaddapavan for the report. - Fix export of data from an Oracle nested table. Thanks to rejo oommen for the report. - Removed cast to timestamp from partition range. Thanks to buragaddapavan and rejo-oommen for the report. - Fix partition default syntax. Thanks to rejo-oommen for the report. - Apply missing SYSUSERS schemas exclusion on columns and partition listing. Thanks to rejo-oommen for the report. - Add warning about parameter order change in output file. - Show table name on Oracle side during export using at connection time: DBMS_APPLICATION_INFO.SET_ACTION(table_name); Thanks to Denis Oleynikov for the feature request. - Report change in ORA_RESERVED_WORDS into documentation. - Add references in the keyword list of ORA_RESERVED_WORDS. - Fix the missing white space in some lines while creating import_all.sh file. Thanks to Fabiano for the patch. - Fix translation of infinity value for float. Thanks to Damien Trecu for the report. - Fix default value in timestamp column definition when a timezone is given. Thanks to buragaddapavan for the report. - Fix missing export of index and constraint in a partitioned table when DISABLE_PARTITION is enabled. Thanks to Denis Oleynikov for the report. - Prevent PARTITION BY when DISABLE_PARTITION is enabled. Thanks to Denis Oleynikov for the report. - Add DATA_EXPORT_ORDER configuration directive. By default data export order will be done by sorting on table name. If you have huge tables at end of alphabetic order and are using multiprocess, it can be better to set the sort order on size so that multiple small tables can be processed before the largest tables finish. In this case set this directive to size. Possible values are name and size. Note that export type SHOW_TABLE and SHOW_COLUMN will use this sort order too, not only COPY or INSERT export type. Thanks to Guy Browne for the feature request. - Fix remove leading ':' on Oracle variable taking care of regex character class. Thanks to jselbach for the report. - Add NO_BLOB_EXPORT configuration directive. Exporting BLOB could take time and you may want to export all data except the BLOB columns. In this case enable this directive and the BLOB columns will not be included into data export. The BLOB column must not have a NOT NULL constraint. Thanks to Ilya Vladimirovich for the feature request. - Fix incorrect rewrote of the first custom type in a row. Thanks to Francesco Loreti for the patch. - Remove double quote in type definition en set type name in lower case when PRESERVE_CASE is disabled. - Add PREFIX_SUB_PARTITION to enable/disable sub-partitioning table prefixing in case of the partition names are a part of the sub- partition names. - Fix epoch replacement case in CREATE TABLE statements. - Apply epoch replacement to default value in table declaration. - Add special replacement for case of epoch syntax in Oracle: (sysdate - to_date('01-Jan-1970', 'dd-Mon-yyyy'))*24*60*60 is replaced by the PostgreSQL equivalent: (extract(epoch from now())) Thanks to rejo-oommen for the feature request. - A few typos in --help sections. Thanks to Christophe Courtois for the report. - Fix export of primary key on partition table. Thanks to chmanu for the patch. - Fix malformed user defined type export. Thanks to Francesco Loreti for the report.

* Add export of default partition and default sub partition. * Add export of HASH partition type. * Add support of stored procedure object. * Add replacement of NLSORT in indexes or queries. For example: CREATE INDEX test_idx ON emp (NLSSORT(emp_name, 'NLS_SORT=GERMAN')); is translated into CREATE INDEX test_idx ON emp ((emp_name collate "german")); The collation still need to be adapted, here probably "de_DE". NLSSORT() in ORDER BY clause are also translated. * Prevent duplicate index with primary key on partition to be exported. * PostgreSQL native partitioning does not allow direct import of data into already attached partitions. We now force direct import into main table but we keep Oracle export of data from individual

--oracle_speed: use to know at which speed Oracle is able to send data. No data will be processed or written written --ora2pg_speed: use to know at which speed Ora2Pg is able to send transformed data. Nothing will be written

* PG_SUPPORTS_PROCEDURE : PostgreSQL v11 adds support to stored procedure objects. Disabled by default. - PARALLEL_MIN_ROWS: set the minimum number of tuples in a table before calling Oracle's parallel mode during data export. Default to 100000 rows.

- Add export of Oracle HASH partitioning when PG_SUPPORTS_PARTITION is enabled. This is a PostgreSQL 11 feature. - Add SUBTYPE translation into DOMAIN with TYPE and PACKAGE export. - Add automatic translation of KEEP (DENSE_RANK FIRST|LAST ORDER BY ...) OVER (PARTITION BY ...) into FIRST|LAST_VALUE(...) OVER (PARTITION BY ... ORDER BY ...). - Add PCTFREE to FILLFACTOR conversion when PCTFREE is upper than the default value: 10. - Replace DELETE clause not followed with FROM (optional in Oracle). - Remove Oracle extra clauses in TRUNCATE command. - Allow use of NUMBER(*) in DATA_TYPE directive to convert all NUMBER(*) into the given type whatever is the length. Ex: DATA_TYPE NUMBER(*):bigint. - Add a PARALLEL hint to all Oracle queries used to migrate data. - Add export of Identity Columns from Oracle Database 12c. - Add translation of UROWID datatype and information in documentation about why default corresponding type OID will fail at data import. - Remove unwanted and unused keywords from CREATE TABLE statements: PARALLEL and COMPRESS. - Remove TEMPORARY in DROP statements. - Improve speed of escape_copy() function used for data export. - Add translation of Oracle functions NUMTOYMINTERVAL() and NUMTODSINTERVAL(). - Add counting of jobs defined in Oracle scheduler in the migration assessment feature. - Add CSMIG in the list of Oracle default system schema - Fully rewrite data export for table with nested user defined types DBD::Oracle fetchall_arrayref() is not able to associate complex custom types to the returned arrays, changed this call to use fetchrow_array() also used to export BLOB. - QUERY export will now output translated queries as well as untranslated ones. This break backward compatibility, previously only translated query was dumped. - Auto detect UTF-8 input files to automatically use utf8 encoding. - Support translation of MySQL global variables. - Add translation of preprocessor in Oracle external table into program in foreign table definition. Allow translation of external table from file. - Add translation to NVL2() Oracle function. - Translate CONVERT() MySQL function. - Translate some form of GROUP_CONCAT() that was not translated. - Remove call to CHARSET in cast() function, replace it by COLLATE every where else. This must cover most of the cases but some specials use might not, so please reports any issue with this behavior. - Add -c | --config command line option to ora2pg_scanner to set custom configuration file to be used instead of ora2pg default: /etc/ora2pg/ora2pg.conf - Improve CONNECT BY and OUTER JOIN translation. - And lot of MySQL to PostgreSQL improvements.

- Add DEFAULT_PARALLELISM_DEGREE to control PARALLEL hint use when exporting data from Oracle. Default is disabled. - Make documentation about KEEP_PKEY_NAMES more explicit about kind of constraints affected by this directive. - Add PG_SUPPORTS_IDENTITY configuration directive to enable export of Oracle identity columns into PostgreSQL 10 feature. If PG_SUPPORTS_IDENTITY is disabled and there is IDENTITY column in the Oracle table, they are exported as serial or bigserial columns. When it is enabled they are exported as IDENTITY columns like: CREATE TABLE identity_test_tab ( id bigint GENERATED ALWAYS AS IDENTITY, description varchar(30) ) ; If there is non default sequence option set in Oracle, they will be appended after the IDENTITY keyword. Additionally in both cases Ora2Pg will create a file AUTOINCREMENT_output.sql with a function to update the associated sequences with the restart value set to "SELECT max(colname)+1 FROM tablename". Of course this file must be imported after data import otherwise sequence will be kept to start value. - Add DISABLE_UNLOGGED configuration directive. By default Ora2Pg export Oracle tables with the NOLOGGING attribute into UNLOGGED tables. You may want to fully disable this feature because you will lost all data from unlogged table in case of PostgreSQL crash. Set it to 1 to export all tables as normal table. When creating a new migration project using --init_project, this directive is activated by default. This is not the case in the default configuration file for backward compatibility. - Add FORCE_SECURITY_INVOKER configuration directive. Ora2Pg use the function's security privileges set in Oracle and it is often defined as SECURITY DEFINER. To override those security privileges for all functions and use SECURITY DEFINER instead, enable this directive. - Add AUTONOMOUS_TRANSACTION in configuration to enable translation of autonomous transactions into a wrapper function using dblink or pg_background extension. If you don't want to use this feature and just want to export the function as a normal one without the pragma call, disable this directive. - Add documentation about COMMENT_SAVEPOINT configuration directive. - Major rewrite in PACKAGE parser to better support global variables detection. Global variable that have no default values are now always initialized to empty string in file global_variables.conf so that we see that they exists. This might not change the global behavior.

- Fix translation of "varname cursor%ROWTYPE;". Thanks to Philippe Beaudoin for the report. - Fix return of autonomous transaction dblink call when function has OUT parameter. Thanks to Pavel Stehule for the report. - Add Oracle to PostgreSQL translation of windows functions KEEP (DENSE_RANK FIRST|LAST ORDER BY ...) OVER (PARTITION BY ...) Thanks to Swapnil bhoot929 for the feature request. - Fix "ORA-03113: end-of-file on communication channel" that what generated by a too long query send to Oracle. The size of queries sent to Oracle to retrieve object information depend of the ALLOW and EXCLUDE directives. If you have lot of objects to filter you can experience this kind of non explicit error. Now Ora2pg use bind parameter to pass the filters values to reduce the size of the prepared query. Thanks to Stephane Tachoire for the report. - Add SUBTYPE translation into DOMAIN with TYPE and PACKAGE export. Thanks to Francesco Loreti for the feature request. - Fix PLS_INTEGER replacement. - Remove precision for RAW|BLOB as type modifier is not allowed for type "bytea". - Fix call of schema.pckg.function() in indexes with a replacement with pckg.function(). Thanks to w0pr for the report. - Fix translation of UPDATE trigger based on columns: "BEFORE UPDATE OF col1,col2 ON table". Thanks to Eric Delanoe for the report. - Remove single / from input file that was causing a double END in some case. Thanks to Philippe Beaudoin for the report. - Limit translation of PCTFREE into FILLFACTOR when PCTFREE is upper than the Oracle default value: 10. With PostgreSQL 100 (complete packing) is the default. - Add PCTFREE to FILLFACTOR conversion. Thanks to Maxim Zakharov for the patch. - Remove TRUNCATE extra clauses. Thanks to e7e6 for the patch. - Fix type conversion when extra \n added after ;. Thanks to Maxim Zakharov for the patch. - Fix DELETE clause not followed with FROM (optional in Oracle). Thanks to Philippe Beaudoin for the patch. - Limit call to ALL_TAB_IDENTITY_COLS to version 12+. Thanks to Andy Garfield for the report. - Fix comment parsing. Thanks to Philippe Beaudoin for the report. - Allow use of NUMBER(*) in DATA_TYPE directive to convert all NUMBER(*) into the given type whatever is the length. Thanks to lingeshpes for the feature request. - Fix bug in function-based index export. Thanks to apol1234 for the report. - Add PARALLEL hint to all data export queries. Thanks to jacks33 for the report. - Make documentation about KEEP_PKEY_NAMES more explicit about kind of constraints affected by this directive. - Fix export of identity columns by enclosing options between parenthesis and replacing CACHE 0 by CACHE 1. Thanks to swmcguffin devtech for the report. - Add parsing of identity columns from file. - Fix unwanted replacement of IF () in MySQL code. Thanks to Krasiyan Andreev for the report. - Fix to_char() translation, thanks to Eric Delanoe for the report. - Fix untranslated PERFORM into exception. Thanks to Pavel Stehule for the report. - Add _get_entities() function to MySQL export. It returns nothing, AUTO_INCREMENT column are translated with corresponding types, smallserial/serial/bigserial. - Fix look at encrypted column on Oracle prior to 10. Thanks to Stephane Tachoires for the patch. - Add export of Identity Columns from Oracle Database 12c. Thanks to swmcguffin-devtech for the feature request. - Prevent Ora2Pg to scan ALL_SCHEDULER_JOBS for version prior to 10 Thanks to Stephane Tachoires for the patch. - Fix pull request #648 to log date only when debug is enabled and use POSIX strftime instead of custom gettime function. - Add system time to debug log info. Thanks to danghb for the patch. - Fix parsing of trigger from file and exception. - Fix very slow export of mysql tablespace when number of table is large. Thanks to yafeishi for the report. - Fix translation of CAST( AS unsigned). Thanks to Krasiyan Andreev. - Fix MySQL character length to use character_maximum_length instead of equal character_octet_length. Thanks to yafeishi for the report. - Fix custom replacement of MySQL data type. Thanks to Krasiyan Andreev for the report. - Fix replacement of call to open cursor with empty parenthesis. Thanks to Philippe Beaudoin for the report. - Fix MySQL data type conversion in function declaration. Thanks to Krasiyan Andreev for the report. - Fix error with -INFINITY as default value for date or timestamp columns. - Fix procedure call rewrite with unwanted comma on begin of parameter list. Thanks to Pavel Stehule for the report. - Fix handling of foreign keys when exporting data and DROP_FKEYS is enabled and ALLOW/EXCLUDE directive is set. Now Ora2Pg will first drop all foreign keys of a table in the export list and all foreign keys of other tables pointing to the table. After data import, it will recreate all of these foreign keys. Thanks to Eric Delanoe for the report. - Fix broken transformation of procedure call with default parameter Thanks to Pavel Stehule for the report. - Translate call to TIMESTAMP in partition range values into a cast. Thanks to markiech for the report. - Fix CONNECT BY translation when the query contain an UNION. Thanks to mohammed-a-wadod for the report. - Fix CONNECT BY with PRIOR on the right side of the predicat. - Fix outer join translation when the (+) was in a function, ex: WHERE UPPER(trim(VW.FRIDAY))= UPPER(trim(FRIDAY.NAME(+))). - Order outer join pending tables in from clause. - Order by object name comments and indexes export. - Fix outer join translation when the table is not in the from clause. Thanks to Cyrille Lintz for the report. - Try to fix potential Oracle schema prefixing PostgreSQL schema name in CREATE SCHEMA. Thanks to Cyrille Lintz for the report. - Fix error in TRIM() translation. Thanks to Cyrille Lintz for the report. - Add translation of UROWID datatype and information in documentation about why default corresponding type OID will fail at data import. Thanks to Cyrille Lintz for the report. - Fix bug in exporting boolean default values in column definition. - Fix bug in column parsing in CREATE TABLE. - Adapt default value for data type changed to boolean. - Fix bad handling of -D (data_type) option. - Change behavior in the attempt to set MySQL global variable type. Now variable type will be timestamp if the variable name contains datetime, time if the name contains only time and date for date. Thanks to Krasiyan Andreev for the report. - Fix function replacement in MySQL declare section. Thanks to Krasiyan Andreev fr the report. - Apply REPLACE_ZERO_DATE to default value in table declaration. Thanks to Krasiyan Andreev for the report. - Add support to embedded comment in table DDL. - Fix replacement of data type for MySQL code. Thanks to Krasiyan Andreev for the report. - Fix MySQL type replacement in function. Thanks to Krasiyan Andreev for the report. - Improve speed of escape_copy() function used for data export. Thanks to pgnickb for the profiling. - Add translation of Oracle functions NUMTOYMINTERVAL() and NUMTODSINTERVAL(). Thanks to Pavel Stehule for the report. - Counting jobs defined in Oracle scheduler. Thanks to slfbovey for the patch. - Fix several issue in create table DDL parser: - remove double quote of object name when a list of column is entered - split of table definition to extract column and constraint parts is now more efficient - remove dot in auto generated constraint name when a schema is given in table name - fix default values with space that was breaking the parser - Remove use of bignum perl module that reports error on some installation. Thanks to Cyrille Lintz for the report. - Fix a typo preventing perldoc to complete. Thanks to slfbovey for the patch. - Fully rewrite data export for table with nested user defined types DBD::Oracle fetchall_arrayref() is not able to associate complex custom types to the returned arrays, changed this call to use fetchrow_array() also used to export BLOB. Thanks to lupynos for the report. - Fix renaming of temporary files during partitions data export. - Fix Oracle use of empty string as default value for integers. Oracle allow such declaration: SOP NUMBER(5) DEFAULT '' which PostgreSQL does not support. Ora2Pg now detect this syntax and replace empty string with NULL. Thanks to ricdba for the report. - Add detection of Oracle version before setting datetime format, needed for Oracle 8i compatibility. - Export of tables from Oracle database are now ordered by name by default. Thanks to Markus Roth for the report. - Fix an other case of missing translation of UNSIGNED into bigint. Thanks to Krasiyan Andreev for the report. - Force replacement of double quote into single quote for MySQL view and function code. - Fix case when SET @varname := ... is used multiple time in the same function. Thanks to Krasiyan Andreev for the report. - Fix case where SET @varname := ... was not translated. Thanks to Krasiyan Andreev for the report. - Adjust the regex pattern of last patch. - Fix unwanted newline after hint replacement that could break comments. Thanks to Pavel Stehule for the report. - Fix if() replacement in query. Thanks to Krasiyan Andreev for the report. - Remove extra parenthesis in some form of JOIN. Thanks to Krasiyan Andreev for the report. - Fix untranslated call to UNSIGNED, now translated as bigint. - Thanks to Krasiyan Andreev for the report. - Fix translation of double(p,s) into decimal(p,s). - Remove use of SET when an assignment is done through a SELECT statement. Thanks to Krasiyan Andreev for the report. - Fix non-quoted reserved keywords in INSERT / COPY statements when exporting data. Thanks to Pavel Stehule for the report. - Fix partition data export to file, temporary files for partition output was not renamed at export end then data was not loaded. - Fix double operator := during function with out param rewrite. - Fix commit f1166e5 to apply changes when FILE_PER_FUNCTION is disable or when an input file is given. - Fix translation of LOCATE(). Thanks to Krasiyan Andreev for the report. - Fix case where MySQL GROUP_CONCAT() function was not translated. Thanks to Krasiyan Andreev for the report. - Fix :new and :old translation in triggers. - Fully rewrite function call qualification process, the second pass now is only use to requalify call to pkg.fct into pkg_ftc when PACKAGE_AS_SCHEMA is disable. The replacement of all function calls using double quote when a non supported character is used or when PRESERVE_CASE is enabled has been completely removed as this takes too much time to process for just very few case. So by default now Ora2Pg will not go through the second pass. This can change in the future especially if this is more performant to process PERFORM replacement. Thanks a lot to Eric Delanoe for his help on this part. - Exclude function and procedure not from package to be used in requalify call. Thanks to Eric Delanoe for the report. - Fix function name qualification in multiprocess mode. - Fix unqualified function call due to unclose file handle. - Prevent try to requalify function call if the function is not found in the file content. - Remove ALGORITHM=.*, DEFINER=.* and SQL SECURITY DEFINER from MySQL DDL code. - An other missing change to previous commit on qualifying function call. - Limit function requalification to export type: VIEW, TRIGGER, QUERY, FUNCTION, PROCEDURE and PACKAGE. - Auto detect UTF-8 input files to automatically use utf8 encoding. - Remove all SHOW ERRORS and other call to SHOW in Oracle package source as they was badly interpreted as global variable. - Fix MySQL CREATE TABLE ... SELECT statement. - Fix pending translation issue on some DATE_FORMAT() case. Thanks to Krasiyan Andreev for the report. - Fix translation of IN (..) in MySQL view. Thanks to Krasiyan Andreev for the report. - Fix MySQL date format with digit. - Fix DATE_FORMAT, WHILE and IFNULL translation issues. - Fix not translated MySQL IF() function. - Fix other MySQL translation issues for @variable. Thanks to Krasiyan Andreev for the report. - Fix issue in MySQL IF translation with IN clause. Thanks to Krasiyan Andreev for the report. - Clarify comment about XML_PRETTY directive. Thanks to TWAC for the report. - Fix remaining MySQL translation issues for @variable reported in issue #590. - Fix no translated := in SET statement. - Fix output order of translated function. - Fix non printable character or special characters that make file encoding to ISO-8859 instead of utf8. Thanks to twac for the report. - Prevent MySQL global variable to be declared twice. Thanks to Krasiyan Andreev for the report. - Support translation of MySQL global variables. Session variable @@varname are translated to PostgreSQL GUC variable and global variable @varname are translated to local variable defined in a DECLARE section. Ora2Pg tries to gather the data type by using integer by default, varchar if there is a constant string ('...') in the value and a timestamp if the variable name have the keyword date or time inside. Thanks to Krasiyan Andreev for the feature request. - Fix DATE_ADD() translation. - Add translation of preprocessor in Oracle external table into program in foreign table definition. Thanks to Thomas Reiss for the report. Allow translation of external table from file. - Fix case where IF EXISTS might not be append when it is not supported by PG. - Translate CONVERT() MySQL function. Thanks to Krasiyan Andreev for the report. - Translate some form of GROUP_CONCAT() that was not translated. Thanks to Krasiyan Andreev for the report. - Apply same principe with COMMIT in MySQL function code than in Oracle code. It is kept untouched to be able to detect a possible change of code logic. It can be automatically commented if COMMENT_COMMIT_ROLLBACK is enabled. Also I have kept the START TRANSACTION call but it is automatically commented. - Add mysql_enable_utf8 => 1 to MySQL connection to avoid issues with encoding. Thanks to Krasiyan Andreev for the report. - Prevent removing of comment on MySQL function and add a "COMMENT ON FUNCTION" statement at end of the function declaration. Thanks to Krasiyan Andreev for the report. - Fix translation of types in MySQL function parameter. Thanks to Krasiyan Andreev for the report. - Remove START TRANSACTION from MySQL function code. Thanks to Krasiyan Andreev for the report. - Fix previous patch, we do not need to look forward for function or procedure definition in VIEW export and there is no package with MySQL. Thanks to Krasiyan Andreev for the report. - Fix call to useless function for MySQL function. - Add rewrite of MySQL function call in function or procedure code translation and some other translation related to MySQL code. - Fix ora2pg_scanner when exporting schema with $ in its name. Thanks to Aurelien Robin for the report. - Disable number of microsecond digit for Oracle version 9. Thanks to Aurelien Robin for the report. - Do not look at encrypted column for DB version < 10. Thanks to Aurelien Robin for the report. - Fix MySQL call to charset in cast function. MySQL charset "utf8" is also set to COLLATE "C.UTF-8". Thanks to Krasiyan Andreev for the report. - Fix two bug in CONNECT BY and OUTER JOIN translation. - Forgot to handle exception to standard call to IF in MySQL IF() translation. Thanks to Krasiyan Andreev for the report. - Forgot to apply previous changes to procedure. - Fix IF() MySQL replacement when nested and when containing an IN (...) clause. Thanks to Krasiyan Andreev for the report. - Fix double BEGIN on MySQL function export. Thanks to Krasiyan Andreev for the report. - Fix enum check constraint name when PRESERVE_CASE is enabled. - Fix case where object with LINESTRING and CIRCULARSTRING was exported as MULTILINESTRING instead of MULTICURVE. - Fix export of MULTICURVE with COMPOUNDCURVE. Thanks to Petr Silhak for the report. - Fix several issue in MySQL table DDL export. Thanks to Krasiyan Andreev for the report. - Fix MySQL auto_increment data type translation and columns export order. - Fix translation of MySQL function CURRENT_TIMESTAMP(). Thanks to Krasiyan Andreev for the report. - Fix export of MySQL alter sequence name when exporting auto increment column. Thanks to Krasiyan Andreev for the report. - Replace IF() call with CASE ... END in VIEW and QUERY export for MySQL. Thanks to Krasiyan Andreev for the feature request. - Replace backquote with double quote on mysql statements when read from file. - Fix bug in REGEXP_SUBSTR replacement. - Prevent replacement with same function name from an other package. Thanks to Eric Delanoe for the report. - Apply same STRICT rule for SELECT INTO to EXECUTE INTO. Thanks to Pavel Stehule for the report. - Fix extra parenthesis removing when a OR clause is present. Thanks to Pavel Stehule for the report. - Keep autonomous pragma commented when conversion is desactivated to be able to identify functions using this pragma. - Fix bug in replacement of package function in string constant. - Fix malformed replacement of array element calls. Thanks to Eric Delanoe for the report. - Fix unwanted replacement of TO_NUMBER function. Thanks to Torquem for the report. - Add an example of DSN for MySQL in ORACLE_DSN documentation. Thanks to François Honore for the report. - Fix typo in default dblink connection string. Thanks to Pavel Stehule for the report. - Add information about Oracle Instant Client installation. Thanks to Jan Birk for the report. - Replace Oracle array syntax arr(i).x into arr[i].x into PL/SQL code. Thanks to Eric Delanoe for the report. - Use a more generic connection string for DBLINK. It will use unix socket by default to connect and the password must be set in .pgpass. This will result in the following connection string: format('port=%s dbname=%s user=%', current_setting('port'), current_database(), current_user) If you want to redefine this connection string use DBLINK_CONN configuration directive. Thanks to Pavel Stehule for the feature request. - Fix missing RETURN NEW in some trigger translation. Thanks to Pavel Stehule for the report. - Fix a missing but non mandatory semi-comma. - Keep PKs/unique constraints which are deferrable in Oracle also deferrable in PostgreSQL. Thank to Sverre Boschman for the patch. - Fix parsing and translation of CONNECT BY. Thanks to bhoot929 for the report. - Fix FDW export when exporting all schema. Thanks to Laurenz Albe for the report. - Add a note about multiple value in export type that can not include COPY or INSERT together with others export type. - Fix duplicate condition. Thanks to Eric Delanoe for the report. - Fix unwanted translation into PERFORM after INTERSECT. - Comment savepoint in code. Thanks to Pavel Stehule for the patch. - Fix "ROLLBACK TO" that was not commented. Thanks to Pavel Stehule for the report. - Fix restore of constant string when additional string constant regex are defined in configuration file. - Fix translation of nextval with sequence name prefixed with their schema. - Cast call to TO_DATE(LOCALTIMESTAMP,...) translated into TO_DATE(LOCALTIMESTAMP::text,...). Thanks to Keshav kumbham for the report. - Remove double quote added automatically by Oracle on view definition when PRESERVE_CASE is not enable. Thanks to JeeIPI for the report. - Fix translation of FROM_TZ with a call to function as first parameter. Thanks to TrungPhan for the report. - Fix package export when FILE_PER_FUNCTION is set. Thanks to Julien Rouhaud for the report. - Add translation of REGEXP_SUBSTR() with the following rules: Translation of REGEX_SUBSTR( string, pattern, [pos], [nth]) converted into SELECT array_to_string(a, '') FROM regexp_matches(substr(string, pos), pattern, 'g') AS foo(a) LIMIT 1 OFFSET (nth - 1); Optional fifth parameter of match_parameter is appended to 'g' when present. Thanks to bhoot929 for the feature request. - Add count of REGEX_SUBSTR to migration assessment cost. - Add translation support of FROM_TZ() Oracle function. Thanks to trPhan for the feature request. - Forces ora2pg to output a message when a custom exception code has less than 5 digit. - Fix errcode when Oracle custom exception number have less than five digit. Thanks to Pavel Stehule for the report. - Fix case where custom errcode are not converted. Thanks to Pavel Stehule for the report. - Fix print of single semicolon with empty line in index export. - Fix problem with TO_TIMESTAMP_TZ conversion. Thanks to Keshav- kumbham for the report. - Fix unwanted double quote in index column with DESC sorting. Thanks to JeeIPI for the report. - Fix non detection case of tables in from clause for outer join translation. Thanks to Keshav for the report. - Fix unwanted replacement of = NULL into IS NULL in update statement. Thanks to Pavel Stehule for the report. - Force schema name used in TEST action to lowercase. Thanks to venkatabn for the report. - Fix export of spatial geometries with CURVEPOLYGON + COMPOUNDCURVE Thanks to kabog for the report.

- Add translation of SUBSTRB into substr. - Allow use of array in MODIFY_TYPE to export Oracle user defined type that are just array of some data type. For example: CREATE OR REPLACE TYPE mem_type IS VARRAY(10) of VARCHAR2(15); can be directly translated into text[] or varchar[]. In this case use the directive as follow: MODIFY_TYPE CLUB:MEMBERS:text[] Ora2Pg will take care to transform all data of this column into the correct format. Only arrays of characters and numerics types are supported. - Add translation of Oracle function LISTAGG() into string_agg(). - Add TEST_VIEW action to perform a simple count of rows returned by views on both database. - Translate SQL%ROWCOUNT into GET DIAGNOSTICS rowcount = ROW_COUNT and add translation of SQL%FOUND. - Add translation of column in trigger event test with IS DISTINCT, for example: IF updating('ID') THEN ... will be translated into: IF TG_OP = 'UPDATE' AND NEW.'ID' IS DISTINCT FROM OLD.'ID' then... - Replace UTL_MATH.EDIT_DISTANCE function by fuzzymatch levenshtein. - Allow use of MODIFY_STRUCT with TABLE export. Table creation DDL will respect the new list of columns and all indexes or foreign key pointing to or from a column removed will not be exported. - Add export of partition and subpartition using PostgreSQL native partitioning. - Auto detect encrypted columns and report them into the assessment. SHOW_COLUMN will also mark columns as encrypted. - Add information to global temporary tables in migration assessment. - Add experimental DATADIFF functionality. - Allow use of multiprocess with -j option or JOBS to FUNCTION and PROCEDURE export. Useful if you have thousands of these objects. - Force RAW(N) type with default value set to sys_guid() as UUID on PostgreSQL. - Replace function with out parameter using select into. For example a call to: get_item_attr( attr_name, p_value ); where p_value is an INOUT parameter, will be rewritten as p_value := get_item_attr( attr_name, p_value ); If there is multiple OUT parameters, Ora2Pg will use syntax: SELECT get_item_attr( attr_name, p_value ) INTO (attr_name, p_value); - Add translation of CONNECT BY using PostgreSQL CTE equivalent. This translation also include a replacement of LEVEL and SYS_CONNECT_BY_PATH native Oracle features. On complex queries there could still be manual editing but all the main work is done. - Add support to user defined exception, errcode affected to each custom exception start from 50001. - Translate call to to_char() with a single parameter into a cast to varchar. Can be disabled using USE_ORAFCE directive. - Improve ora2pg_scanner to automatically generates migration assessment reports for all schema on an Oracle instance. Before the schema name to audit was mandatory, now, when the schema is not set Ora2Pg will scan all schema. The connexion user need to have DBA privilege. Ora2Pg will also add the hostname and SID as prefix in the filename of the report. This last changee forbids ora2pg_scanner to overwrite a report if the same schema name is found in several databases.

- Add USE_ORAFCE configuration directive that can be enabled if you want to use functions defined in the Orafce library and prevent Ora2Pg to translate call to these functions. The Orafce library can be found here: https://github.com/orafce/orafce By default Ora2pg rewrite add_month(), add_year(), date_trunc() and to_char() functions, but you may prefer to use the Orafce functions that do not need any code transformation. Directive DATE_FUNCTION_REWRITE has been removed as it was also used to disable replacement of add_month(), add_year() and date_trunc() when Orafce is used, useless now. - Add FILE_PER_FKEYS configuration directive to allow foreign key declaration to be saved in a separate file during schema export. By default foreign keys are exported into the main output file or in the CONSTRAINT_output.sql file. If enabled foreign keys will be exported into a file named FKEYS_output.sql - Add new COMMENT_COMMIT_ROLLBACK configuration directive. Call to COMMIT/ROLLBACK in PL/SQL code are kept untouched by Ora2Pg to force the user to review the logic of the function. Once it is fixed in Oracle source code or you want to comment this calls enable the directive. - Add CREATE_OR_REPLACE configuration directive. By default Ora2Pg use CREATE OR REPLACE in function DDL, if you need not to override existing functions disable this configuration directive, DDL will not include OR REPLACE. - Add FUNCTION_CHECK configuration directive. Disable this directive if you want to disable check_function_bodies. SET check_function_bodies = false; It disables validation of the function body string during CREATE FUNCTION. Default is to use de postgresql.conf setting that enable it by default. - Add PG_SUPPORTS_PARTITION directive, disabled by default. PostgreSQL version prior to 10.0 do not have native partitioning. Enable this directive if you want to use PostgreSQL declarative partitioning instead of the old style check constraint and trigger. - Add PG_SUPPORTS_SUBSTR configuration directive to replace substr() call with substring() on old PostgreSQL versions or some fork like Redshift. - Add PG_INITIAL_COMMAND to send some statements at session startup. This directive is the equivalent used for Oracle connection, ORA_INITIAL_COMMAND. Both can now be used multiple time now. - Add DBLINK_CONN configuration directive. By default if you have an autonomous transaction translated using dblink extension the connection is defined using the values set with PG_DSN, PG_USER and PG_PWD. If you want to fully override the connection string use this directive to set the connection in the autonomous transaction wrapper function. For example: DBLINK_CONN port=5432 dbname=pgdb host=localhost user=pguser password=pgpass - Add STRING_CONSTANT_REGEXP configuration directive. Ora2Pg replace all string constant during the pl/sql to plpgsql translation, string constant are all text include between single quote. If you have some string placeholder used in dynamic call to queries you can set a list of regexp to be temporary replaced to not break the parser. For example: STRING_CONSTANT_REGEXP <cfqueryparam value=".*"> The list of regexp must use the semi colon as separator. - Add FUNCTION_STABLE configuration directive. By default Oracle functions are marked as STABLE as they can not modify data unless when used in PL/SQL with variable assignment or as conditional expression. You can force Ora2Pg to create these function as VOLATILE by disabling this configuration directive. - Add new TO_NUMBER_CONVERSION configuration directive to control TO_NUMBER translation behavior. By default Oracle call to function TO_NUMBER will be translated as a cast into numeric. For example, TO_NUMBER('10.1234') is converted into PostgreSQL call: to_number('10.1234')::numeric. If you want you can cast the call to integer or bigint by changing the value of the configuration directive. If you need better control of the format, just set it as value, for example: TO_NUMBER_CONVERSION 99999999999999999999D9999999999 will convert the code above as: TO_NUMBER('10.1234', '99999999999999999999D9999999999') Any value of the directive that it is not numeric, integer or bigint will be taken as a mask format. If set to none, then no conversion will be done. - Add LOOK_FORWARD_FUNCTION configuration directive which takes a list of schema to get functions/procedures meta information that are used in the current schema export. When replacing call to function with OUT or INOUT parameters, if a function is declared in an other package then the function call rewriting can not be done because Ora2Pg only knows about functions declared in the current schema. By setting a comma separated list of schema as value of the directive, Ora2Pg will look forward in these packages for all functions, procedures and packages declaration before proceeding to current schema export. - Add PG_SUPPORTS_NAMED_OPERATOR to control the replacement of the PL/SQL operator used in named parameter => with the PostgreSQL proprietary operator := Disable this directive if you are using PG < 9.5 - Add a warning when Ora2Pg reorder the parameters of a function following the PostgreSQL rule that all input parameters following a parameter with a default value must have default values as well. In this case, Ora2Pg extracts all parameters with default values and put them at end of the parameter list. This is to warn you that a manual rewrite is required on calls to this function.

- Add -N | --pg_schema command line option to be able to override the PG_SCHEMA configuration directive. When this option is set at command line, EXPORT_SCHEMA is automatically activated. - Add --no_header option with equivalent NO_HEADER configuration directive to output the Ora2Pg header but just the translated code.

- Remove SysTimestamp() from the list of not translated function, it is replaced with CURRENT_TIMESTAMP for a long time now. - Change migration assessment cost to 84 units (1 day) for type TABLE, INDEX and SYNONYM and to 168 units (2 days) for TABLE PARTITION and GLOBAL TEMPORARY TABLE, this is more realistic. - Set minimum assessment unit to 1 when an object exists. Improve PL/SQL code translation speed. - Change behavior of COMPILE_SCHEMA directive used to force Oracle to compile schema before exporting code. When this directive is enabled and SCHEMA is set to a specific schema name, only invalid objects in this schema will be recompiled. When SCHEMA is not set then all schema will be recompiled. To force recompile invalid object in a specific schema, set COMPILE_SCHEMA to the schema name you want to recompile. This will ask to Oracle to validate the PL/SQL that could have been invalidate after a export/import for example. The 'VALID' or 'INVALID' status applies to functions, procedures, packages and user defined types. - Default transaction isolation level is now set to READ COMMITTED for all action excluding data export. - Oracle doesn't allow the use of lookahead expression but you may want to exclude some objects that match the ALLOW regexp you have defined. For example if you want to export all table starting with E but not those starting with EXP it is not possible to do that in a single expression. Now you can start a regular expression with the ! character to exclude all objects matching the regexp given just after. Our previous example can be written as follow: ALLOW E.* !EXP.* it will be translated into REGEXP_LIKE(..., '^E.*$') AND NOT REGEXP_LIKE(..., '^EXP.*$') in the object search expression. - Fix quoting of PG_SCHEMA with multiple schema in search path. The definition of the search path now follow the following behavior: * when PG_SCHEMA is define, always set search_path to its value. * when EXPORT_SCHEMA is enabled and SCHEMA is set, the search_path is set the name of the schema. - Remove forcing of export_schema when pg_schema is set at command line. This could change the behavior of some previous use of these variables and the resulting value of the search_path but it seems much better understandable. - Rewrite translation of raise_application_error to use RAISE EXCEPTION with a message and the SQLSTATE code. Oracle user defined code -20000 to -20999 are translated to PostgreSQL user define code from 45000 to 45999. Call to raise_application_error(mySQLCODE, myErrmsg); will be translated into RAISE EXCEPTION '%', myErrmsg USING ERRCODE = mySQLCODE; - Remove migration assessment cost for TG_OP and NOT_FOUND they might be fully covered now.