NEWS

Ora2Pg v17.6 released!, November 17 2016

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.

Ora2Pg v17.5 released!, October 21 2016

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.

Here is the complete list of other changes:

  - Fix broken parallel table export (-P option).
  - Fix export of CLOBs and NCLOB that was truncated to 64Kb. Thanks
    to Paul Mzko for the patch.
  - Fix database handle in error report.
  - Fix use of wrong database handle to set search_path. Thanks to
    Paul Mzko for the report.
  - Ora2pg doesn't export schema ForeignKey constraint when connected
    as different DBA user. Thanks to Paul Mzko for the patch.
  - Fix Perl I/O encoding using open pragma instead of calling method
    binmode(). Thanks to Gary Evans for the report.
  - Force input to utf8 using Perl pragma: use open ':encoding(utf8)';
    when BINMODE is not set or NLS_LANG is UTF8.
  - Force ora2pg to export empty lob as empty string instead of NULL
    when the source column has a NOT NULL constraint and directive
    EMPTY_LOB_NULL is not activated. Thanks to Valeriy for the report.
  - Fix missing CASCADE attribute on fkey creation during data export
    when DROP_FKEY was enabled. Thanks to ilya makarov for the report.
  - Fix issue on converting NUMBER(*,0) to numeric, should be ported
    to numeric(38). Thanks to ilya makarov for the report.
  - Correct query for ForeignKey export from oracle. Thanks to ilya
    makarov for the patch.
  - Fix schema change in direct import of data to PostgreSQL.
  - Change query for foreign key extraction to keep the column order.
    Thanks to ilya makarov for the report.
  - 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. Thanks to Paul Mkzo for
    the feature request.
  - Fix infinite loop in blob extraction when error ORA-25408 occurs
    during ora_lob_read() call. Thanks to Paul Mzko for the report.
  - Fix order of columns in foreign keys definition. Thanks to ilya
    makarov for the report.
  - Fix export of partition by range on multicolumn. Thanks to Rupesh
    Admane for the report.
  - Update reserved keywords list. Thanks to Nicolas Gollet for the
    report.
  - Add ON DELETE NO ACTION on foreign key creation (DROP_FKEY) to
    obtain the same output than during constraints export.
  - Fix export of foreign key that was duplicating the columns in both
    part, local and foreign. Thanks to ilya makarov for the report.
  - Remove call to to_char(datecol, format) when exporting date and
    timestamp. This formating is no more needed as we are now forcing
    NLS_DATE_FORMAT and NLS_TIMESTAMP_FORMAT when opening a connection
    to Oracle using:
    	ALTER SESSION SET NLS_DATE_FORMAT='YYYY-MM-DD HH24:MI:SS
    and
    	ALTER SESSION SET NLS_TIMESTAMP_FORMAT='YYYY-MM-DD HH24:MI:SS
    This may result on some speed improvment during data export.
  - Fix parsing of packages from input file.
  - Add export of type created in package declaration. Thanks to
    dezdechado  for the report.
  - Fix converting of procedures with out arguments. Thanks to
    dezdechado for the report.
  - Update documentation about project management.
  - Fix replacement of = NULL by IS NULL in update statement.
    Thanks to dezdechado for the report.
  - Fix parsing of trigger from file that was broken and new line
    removed. Thanks to dezdechado for the report.
  - Fix erasing of quotes from text in triggers. Thanks to dezdechado
    for the report.
  - Fix "return new" on trigger function when there is exception.
    Thanks to dezdechado for the report and solution.
  - Fix conversion of INTEGER and INT into numeric(38) instead of
    numeric without precision. Thanks to dezdechado for the report.
  - Fix export of foreign key when the referenced table is not in the
    same schema. Thanks to Juju for the report.
  - Fix ddl create schema when EXPORT_SCHEMA and CREATE_SCHEMA are
    enabled but no schema is specified.
  - Fix export of NCHAR that was converted as char but was loosing its
    length definition. Thanks to lgerlandsen for the report.
  - Fix parsing of views using WITH statements. Thank to dezdechado
    for the report.
  - Fix case that breaks views/triggers definition when a semicolon
    is encountered in a string value when reading definition from
    file. Thanks to dezdechado for the report.
  - Fix included/excluded of sequences when using ALLOW/EXCLUDE
    directives. Thanks to Roman Sindelar for the report.
  - prepare options modified with some escaping improvements. Thanks
    to ioxgrey for the patch.
  - It seems that for a NUMBER with a DATA_SCALE set to 0, no
    DATA_PRECISION and a DATA_LENGTH of 22 in ALL_TAB_COLUMNS, Oracle
    use a NUMBER(38) instead. This correspond to Oracle type INTEGER
    or INT. I don't really understand the reason of this behavior,
    why not just using a data length of 38? ALL_TAB_COLUMNS and Ora2Pg
    reports a data length of 22, now Ora2Pg will report NUMBER(38) like
    the resulting type of the DESC command.
    
    The following Oracle table:
    
    	CREATE TABLE TEST_TABLE ( FIELD_1 INTEGER, FIELD_2 NUMBER );
    
    will be exported as follow by Ora2Pg:
    
    [1] TABLE TEST_TABLE (owner: HR, 0 rows)
    	FIELD_1 : NUMBER(38) => numeric
    	FIELD_2 : NUMBER(22) => bigint
    
    Oracle data type INTEGER and INT will be exported as numeric by
    default instead of an integer.
  - Fix parsing of function/procedure from file with comments after
    BEGIN statement.
  - Remove DEFERRABLE INITIALLY DEFERRED from CHECK constraints when
    parsed from file. Thanks to Felipe Lavoura for the report.
  - Fix double parenthesis in index definition when parsing index
    creation from file. Thanks to Felipe Lavoura for the report.
  - Fix parsing of COMMENT from file.
  - Fix undetected native Oracle type bug. Thanks to kvnema for the
    report.
  - Fix unwanted text formatting with bind value in INSERT action
    with direct import to PostgreSQL. Thanks to Oleg for the report.
  - Fix inversion of UPDATE_RULE and DELETE_RULE in foreign key
    creation for MySQL export. Thanks to Sebastian Albert for the
    report.
  - Update documentation about DEFER_FKEY and DROP_FKEY to report
    the new behavior.
  - Remove call to SET CONSTRAINTS ALL DEFERRED with direct import.
  - Fix use of NULL value in bind parameter that should be undefined
    (INSERT export mode only). Thanks to Oleg barabaka for the report.
  - Remove replacement of direct call to functions with PERFORM, there
    is too much false positive. Thanks to dezdechado for the reports.
  - Fix a typo in SYSDATE replacement. Thank to dezdechado for report.
  - Remove rewrite of concatenation in RAISE EXCEPTION. Thanks to
    dezdechado for the report.
  - Fix replacement of raise_application_error() when first argument
    is a variable. Thanks to dezdechado for the report.
  - Fix wrong insertion of PERFORM before some function calls. Thanks
    to dezdechado for the report.
  - Replace SYSDATE by a call to clock_timestamp() instead of call to
    LOCALTIMESTAMP in plpgsql code. Thanks to aleksaan for the report.
  - Allow use of comma for object name list separator instead of space
    as workaround on Window OS.
  - Fix documentation about MODIFY_TYPE. Thanks to Nicolas Gollet for
    the report.
  - Add missing documentation about DISABLE_PARTITION directive used
    to not reproduce partitioning into PostgreSQL and only export
    partitioned data into the main table. Thanks to Nicolas Gollet
    for the report.
  - Add information about how to export LONG RAW data type. They need
    to be exported as BLOB before into Oracle to be exported as BYTEA.
  - Fix case where select was wrongly replaced by perform in INSERT
    INTO with SELECT statement. Thanks to dezdechado for the report.
  - Fix links to ora2pg presentation. Thanks to Daniel Lenski for the
    patch.
  - Fix input parameters after one with a default value must also have
    defaults. Thanks to v.agapov fot the patch.
  - Fix debug mode that was interromping the last running table dump.
    Thanks to calbiston for the report.

Ora2Pg v17.4 released!, April 20 2016

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.

Here is the complete list of other changes:

  - Add EMPTY_LOB_NULL directive to force empty_clob() and empty_blob()
    to be exported as NULL instead as empty string. This might improve
    data export speed if you have lot of empty lob. Thanks to Alex
    Ignatov for the report.
  - Fix import_all.sh script to import grant and tablespace separately
    as postgres user and just after indexes and constraints creation.
  - Add parsing of tablespace from "alter table ... add constraint"
    with DDL input file. Thanks to Felipe Lavoura.
  - Remove --single-transaction in import_all.sh script with TABLESPACE
    import. Thanks to Guillaume Lelarge for the report.
  - Fix Makefile.PL to used with latest spec file from Devrim Gündüz
    and following the advice of calbiston.
  - Update spec file to v17.4 and latest change to Makefile.PL
  - Replace ora2pg.spec by postgressql.org spec file by Devrim Gunduz.
  - Generate man page to avoids rpmbuild error.
  - Fix Windows install. Thanks to Lorena Figueredo for the report.
  - Remove "deferrability" call for mysql foreign keys. Thanks to
    Jean-Eric Cuendet for the report.
  - Fix issue in restoring foreign key for mysql data export. Thanks
    to Jean-Eric Cuendet for the report.
  - Remove connection test to PostgreSQL instance as postgres or any
    superuser in import_all.sh
  - Fix creation of configuration directory.
  - Fix Makefile to dissociate CONFDIR and DOCDIR from PREFIX or
    DESTDIR. Thanks to Stephane Schildknecht for the report.
  - Fix date_trunc+add_month replacement issue. Thanks to Lorena
    Figueredo for the report.
  - Do not replace configuration directory in scripts/ora2pg if this
    is a RPM build. Thanks to calbiston for the report.
  - Return empty bytea when a LOB is empty and not NULL.
  - Regular expressions and conditions checks improvement in method
    format_data_type() to make it a bit faster on huge tables. Thanks
    to Svetlana Shorina for the patch.
  - Fix INSERT using on the fly data import with boolean values.
    Thanks to jecuendet for the report.
  - Allow MySQL data type to be converted into boolean. Thanks to
    jecuendet for the report.
  - Fix export of BIT mysql data type into bit bit varying. Thanks
    to jecuendet for the report.
  - Fix call to escape_copy/escape_insert function call.

Ora2Pg v17.3 released!, March 26 2016

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.

Ora2Pg v17.2 released!, March 24 2016

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.

Ora2Pg v17.1 released!, Febuary 29 2016

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

Ora2Pg v17.0 released!, Febuary 22 2016

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 Release note for full list of changes.

Ora2Pg v16.2 released!, January 13 2016

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 Release note for full list of changes.

Ora2Pg v16.1 released!, November 30 2015

This release fixes several issues and adds some very useful features:

    * Generate automatically a new import_all.sh shell script when using option
      --init_project to help automate all import into PostgreSQL.

	See sh import_all.sh -? for more information.

    * Export Oracle bitmap index as PostgreSQL btree_gin index. This require the
      btree_gin extension and PostgreSQL >= 9.4. This is the default.

    * Auto set DEFINED_PK to the first column of a table that have a unique key
      defined that is a NUMBER. This allow data of any table with a numeric
      unique key to be extracted using multiple connexions to Oracle using -J
      option. Tables with no numeric unique key will be exported with a single
      process.

    * Improve BLOB export speed by using hex encoding instead of escape. This
      might speed up be BLOB export by 10.

    * Allow use of LOB locator to retrieve BLOB and CLOB data to prevent having
      to set LONGREADLEN. Now LONGREADLEN is set to 8KB. Old behavior using
      LONGREADLEN can still be enabled by setting NO_LOB_LOCATOR to 0, given
      for backward compatibility. Default is to use LOB locator.

    * Ora2Pg will also auto detect table with BLOB and automatically decrease
      DATA_LIMIT to a value lower or equal to 1000. This is to prevent OOM.

    * Improving indexes and constraints creation speed by using the LOAD action
      and a file containing SQL orders to perform. It is possible to dispatch
      those orders over multiple PostgreSQL connections. To be able to use this
      feature, PG_DSN, PG_USER and PG_PWD must be set. Then:

	ora2pg -t LOAD -c config/ora2pg.conf -i schema/tables/INDEXES_table.sql -j 4
    
      will dispatch indexes creation over 4 simultaneous PostgreSQL connections.
    
      This will considerably accelerate this part of the migration process with
      huge data size.

    * Domain indexes are now exported as b-tree but commented to let you know
      where possible FTS are required.

    * Add number of refresh ON COMMIT materialized view in detailed report.

    * Allow redefinition of numeric type, ex: NUMBER(3)::bigint to fix wrong
      original definition in Oracle.

    * Allow export of all schemas from an Oracle Instance when SCHEMA directive
      is empty and EXPORT_SCHEMA is enabled. All exported objects will be
      prefixed with the name of their original Oracle schema or search_path will
      be set to that schema name. Thanks to Magnus Hagander for the feature
      request.
 
    * Allow use of COPY FREEZE to export data when COPY_FREEZE is enabled. This
      will only works with export to file and when -J or ORACLE_COPIES is not
      set or default to 1. It can be used with direct import into PostgreSQL
      under the same condition but -j or JOBS must be unset or default to 1.
      Thanks to Magnus Hagander for the feature request.

Some new configuration directives:

    * BITMAP_AS_GIN: enable it to use btree_gin extension to create bitmap
      like index with pg >= 9.4. You will need to create the extension by
      yourself: "create extension btree_gin;". Default is to create GIN index,
      when disabled, a btree index will be created.
    * NO_LOB_LOCATOR: to disable use of LOB locator and extract BLOB "inline"
      using a less or more high value in LONGREADLEN.
    * BLOB_LIMIT: to force the value of DATA_LIMIT for tables with BLOB. Default
      is to automatically set this limit using the following code:
	BLOB_LIMIT=DATA_LIMIT; while (BLOB_LIMIT > 1000) BLOB_LIMIT /= 10
    * COPY_FREEZE: use it to use COPY FREEZE instead of simple COPY to speedup
      import into PostgreSQL.

See Release note for full list of changes.

Ora2Pg v16.0 is out!, October 15 2015

This major release improve PL/SQL code replacement, fixes several bugs and adds some major new features:

  * Full migration of MySQL database, it just work like with Oracle database.
  * Full migration assessment report for MySQL database.
  * New script, ora2pg_scanner, to perform a migration assessment of all Oracle and MySQL instances on a network.
  * Add technical difficulty level in migration assessment.
  * Allow migration assessment on client queries extracted from AUDIT_TRAIL (oracle) or general_log table (mysql).
  * Ora2Pg has a "made in one night" brand new Web site (still need some work) http://ora2pg.darold.net/

Example of technical difficulty level assessment output for the sakila database with some more difficulties:

	Total	83.90 cost migration units means approximatively 1 man-day(s).
	Migration level: B-5

Here are the explanation of the migration level code:

    Migration levels:
	A - Migration that might be run automatically
	B - Migration with code rewrite and a human-days cost up to 10 days
	C - Migration with code rewrite and a human-days cost above 10 days
    Technical levels:
	1 = trivial: no stored functions and no triggers
	2 = easy: no stored functions but with triggers, no manual rewriting
	3 = simple: stored functions and/or triggers, no manual rewriting
	4 = manual: no stored functions but with triggers or views with code rewriting
	5 = difficult: stored functions and/or triggers with code rewriting

This is to help you to find the database that can be migrated first with small efforts (A and B) and those who need to conduct a full migration project (C).

This release has also some new useful features:

  * Export type SHOW_TABLE now shows additional information about table type (FOREIGN, EXTERNAL or PARTITIONED with the number of partition).
  * Connection's user and password can be passed through environment variables ORA2PG_USER and ORA2PG_PASSWD to avoid setting them at ora2pg command line.
  * Improve PL/SQL replacement on ADD_MONTH(), ADD_YEAR(), TRUNC(), INSTR() and remove the replacement limitation on DECODE().
  * Add detection of migration difficulties in views, it was previously reserved to functions, procedures, packages and triggers.
  * Replace values in auto generated configuration file from command line options -s, -n, -u and -p when --init_project is used.
  * Adjust lot of scores following new functionalities in Ora2Pg, ex: dblink or synomyms are now easy to migrate.

There is some new command line options to ora2pg script:

  * -m | --mysql : to be used with --init_project and -i option to inform ora2pg that we work with a MySQL format
  * -T | --temp_dir : option to be able to set a distinct temporary directory to run ora2pg in parallel.
  * --audit_user : option to set the user used in audit filter and enable migration assessment report on queries from AUDIT_TRAIL (oracle) or general_log table (mysql).
  * --dump_as_sheet and --print_header options to be able to compute a CSV file with all migration assessment from a list of oracle database.
  * --dump_as_csv option to report assessments into a csv file. It will not include comments or details, just objects names, numbers and cost.

Backward compatibility:

  - Change NULL_EQUAL_EMPTY to be disabled by default to force change in the application instead of transforming the PL/SQL.

This release adds some new configuration directives:

  * MYSQL_PIPES_AS_CONCAT: Enable it if double pipe and double ampersand (|| and &&) should not be taken as equivalent to OR and AND.
  * MYSQL_INTERNAL_EXTRACT_FORMAT: Enable it if you want EXTRACT() replacement to use the internal format returned as an integer.
  * AUDIT_USER: Set the comma separated list of user name that must be used to filter from the DBA_AUDIT_TRAIL or general_log tables.
  * REPLACE_ZERO_DATE: "zero" date: 0000-00-00 00:00:00 it is replaced by a NULL by default, use it to use the date of your choice.
  * INDEXES_RENAMING: force renaming of all indexes using tablename_columnsnames. Very useful for database that have multiple time the same index name or that use the same name than a table.
  * HUMAN_DAYS_LIMIT: default to 5 days, used to set the number of human-days limit for migration of type C.

See Release note for full list of changes.

Paris, September 24 2015

Video of a presentation at PgSessions #7, Conference "Oracle : comment s'en passer?" in french.

Version 15.3 released, Monday June 01 2015

This is a maintenance release only that fixes several minor bugs and typos. The configuration file have been entirely rewritten to classify configuration directives in section for better understanding.

See Release note for full list of changes.

Version 15.2 released, Monday April 13 2015

This new minor release fixes some issues and adds two new configuration directives:

  • ORA_INITIAL_COMMAND to be able to execute a custom command just after the connection to Oracle,
    for example to unlock a security policy.
  • INTERNAL_DATE_MAX to change the behavior of Ora2Pg with internal date found in user defined types.

This version will also automatically re-order exported views taking into account interdependencies.

See Release note for full list of changes.

Moscow, Saturday Febuary 7 2015

Slides of a presentation at PgConf.RU, Conference "Oracle to PostgreSQL migration: a hard way?" in english.

Version 15.1 released, Friday Febuary 6 2015

New minor release just to fix two annoying bugs in previous release.

    - Fix replacement of function name which include SELECT in their name by
      PERFORM. Thanks to Frederic Bamiere for the report.
    - Fix creation of sources subdirectories when initializing a new migration project.

Version 15.0 released, Wednesday Febuary 4 2015

This major release improve PL/SQL code replacement, fixes several bugs and adds some new useful features:

    - Add support to the PostgreSQL external_file extension to mimic BFILE
      type from Oracle. See https://github.com/darold/external_file for
      more information.
    - Allow export of Oracle's DIRECTORY as external_file extension objects
      This will also try to export read/write privilege on those directories.
    - Allow export of Oracle's DATABASE LINK as Oracle foreign data wrapper
      server using oracle_fdw.
    - Allow function with PRAGMA AUTONOMOUS_TRANSACTION to be exported through
      a dblink wrapper to achieve the autonomous transaction.
    - Allow export of Oracle's SYNONYMS as views. Views can use foreign table
      to create "synonym" on object of a remote database.
    - Add trimming of data when DATA_TYPE is used to convert CHAR(n) Oracle
      column into varchar(n) or text. Default is to trim both side any space
      character. This behavior can be controlled using two new configuration
      directives TRIM_TYPE and TRIM_CHAR.
    - Add auto detection of geometry constraint type and dimensions through
      spatial index parameters. This avoid the overhead of sequential scan
      of the geometric column.
    - Add support to export Oracle sub partition and create sub partition
      for PostgreSQL with the corresponding trigger.
    - ALLOW and EXCLUDE directives are now able to apply filter on the object
      type. Backward compatibility can not be fully preserved, older definition
      will apply to current export type only, this could change your export in
      some conditions. See documentation update for more explanation.
    - Add PACKAGE_AS_SCHEMA directive to change default behavior that use a
      schema to emulate Oracle package function call. When disable, all calls
      to package_name.function_name() will be turn into package_name_function_name()
      just like a function call in current schema.
    - Add FKEY_OPTIONS to force foreign keys options. List of supported options
      are: ON DELETE|UPDATE CASCADE|RESTRICT|NO ACTION.
    - Add rewriting of internal functions in package body, those functions will
      be prefixed by the package name. Thanks to Dominique Legendre for the
      feature request.

Some change can break backward compatibility and make configuration directives obsolete:

    - The ALLOW_PARTITION configuration directive has been removed. With new
      extended filters in ALLOW/EXCLUDE directive, this one is obsolete.
      Backward compatibility is preserved but may be removed in the future.
    - ALLOW and EXCLUDE directives do not works as previously. Backward
      compatibility may be preserved with some export type but may be broken
      in most of them. See documentation.
    - It is recommended now to leave the NLS_LANG and CLIENT_ENCODING commented
      to let Ora2Pg handle automatically the encoding. Those directives may be
      removed in the future.

See Release note for full list of changes.