Ora2Pg 14.1 released

Ora2Pg 14 released

Dalibo is proud to announce that version 14.1 of Ora2Pg, a free and reliable
tool used to migrate an Oracle database to PostgreSQL, has been officially
released and is publicly available for download.

This major release adds full export of Oracle Locator or Spatial
geometries into
PostGis, SDO_GEOM functions and SDO_OPERATOR are also translated. This
export
adds the following features:

1. Basic and complex geometry types support
2. Geometry data conversion from Oracle to PostGIS
3. Spatial Index conversion
4. Geometry metadata / constraints support
5. Spatial functions conversion

For spatial data export, you have three choice, WKT to export data using
SDO_UTIL.TO_WKTGEOMETRY(), WKB to export data using
SDO_UTIL.TO_WKBGEOMETRY()
and INTERNAL to export geometry using a Pure Perl library. Unlike the first
two methods, INTERNAL is fast and do not raise Out Of Memory. The export is
done in WKT format so that you can verify your geometry before importing to
PostgreSQL.

Other additional major features are:

- Parallel table processing.
- Auto generation of migration template with a complete project tree.
- Allow user defined queries to extract data from Oracle.

Parallel table processing is controlled by the -P or --parallel command line
options or the PARALLEL_TABLE configuration directive to set the number of
tables that will be processed in parallel for data extraction. The limit is
the number of cores on your machine. Ora2Pg will the open one connection to
Oracle database for each parallel table extraction. This directive, when
upper
than 1, will invalidate ORACLE_COPIES but not JOBS, so the real number of
process that will be used is (PARALLEL_TABLES * JOBS).

The two options --project_base and --init_project when used indicate to
Ora2Pg
to create a project template with a work tree, a generic configuration file
and a shell script to export all objects from the Oracle database. So
that you
just have to define the Oracle database connection into the
configuration file
and then execute the shell script called export_schema.sh to export your
Oracle database into files. Here a sample of the command and the
project's tree.

ora2pg --project_base /tmp --init_project test_project

/tmp/test_project/
config/
ora2pg.conf
data/
export_schema.sh
reports/
schema/
fdws/ functions/ grants/ kettles/
mviews/ packages/ partitions/
procedures/ sequences/ tables/
tablespaces/ triggers/ types/ views/
sources/
functions/ mviews/ packages/
partitions/ procedures/ triggers/
types/ views/

It create a generic config file where you just have to define the Oracle
database connection and a shell script called export_schema.sh. The
sources/ directory will contains the Oracle code, the schema/ will
contains the code ported to PostgreSQL. The reports/ directory will
contains the html reports with the migration cost assessment.

Sometime you may want to extract data from an Oracle table but you need a
custom query for that. Not just a "SELECT * FROM table" like Ora2Pg do but
a more complex query. The new directive REPLACE_QUERY allow you to overwrite
the query used by Ora2Pg to extract data. The format is
TABLENAME[SQL_QUERY].
If you have multiple table to extract by replacing the Ora2Pg query, you can
define multiple REPLACE_QUERY lines. For example:

REPLACE_QUERY EMPLOYEES[SELECT e.id,e.fisrtname,lastname FROM
EMPLOYEES e
JOIN EMP_UPDT u ON (e.id=u.id AND u.cdate>'2014-08-01 00:00:00')]

Other new features are:

- Export of declaration of language C function. Previous version was
not exporting function with no code body like external C function.
- Export of COMMENT from views.
- Function to replace some call to SYS_CONTECT(USERENV, ...) by the
PostgreSQL equivalent.
- Add POSTGIS_SCHEMA configuration directive to add the dedicated
PostGis schema into the search_path.
- Add PG_SUPPORTS_IFEXISTS configuration directive to be able to
suppress
IF EXISTS call in DDL statement generated by Ora2Pg.
- Triggers are now all excluded/allowed following the table names
specified
in the ALLOW and EXCLUDED directives
- Allow automatic export of nested tables (TYPE+TABLE+COPY).

As usual these also some bug fixes and Oracle to PostgreSQl conversion
adjustments, see https://github.com/darold/ora2pg/blob/master/changelog
for a complete list.

Thanks to all contributors, they are all cited in the changelog file.
With very
specials thanks to Dominique Legendre and the French Geological Survey -
BRGM
(http://www.brgm.eu/) for their great help.

Useful Links:

- Website: http://ora2pg.darold.net/
- Download: http://sourceforge.net/projects/ora2pg/
- Development: https://github.com/darold/ora2pg
- Changelog: https://github.com/darold/ora2pg/blob/master/changelog
- Documentation: https://github.com/darold/ora2pg/blob/master/README

About Ora2Pg :

Ora2Pg is an easy and reliable tool to migrate from Oracle to PostgreSQL.
It is developed since 2001 and can export most of the Oracle objects
(table, view, materialized view, tablespace, sequence, indexes, trigger,
grant, function, procedure, package, partition, data, blob and external
table).

Ora2Pg works on any platform and is available under the GPL v3 licence.

Docs, Download & Support at http://ora2pg.darold.net/

--
Gilles Darold
Consultant PostgreSQL
http://dalibo.com - http://dalibo.org