Version 21.0

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,...

Version 20.0

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

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

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 ...

Version 18.2

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 Sys...

Version 18.1

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

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 confi...

Version 17.6

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

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

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.