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

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 shell script when using option
      --init_project to help automate all import into PostgreSQL.

	See 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

    * 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
    * 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:
    * 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)

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