How it works

Ora2Pg consist of a Perl script (ora2pg) and a Perl module (Ora2Pg.pm), the only thing you have to modify is the configuration file ora2pg.conf by setting the DSN to the Oracle database and optionaly the name of a schema. Once that's done you just have to set the type of export you want, here are the capabilities: TABLE with constraints, VIEW, TABLESPACE, SEQUENCE, INDEXES, TRIGGER, GRANT, FUNCTION, PROCEDURE, PACKAGE, PARTITON and DATA.

By default Ora2Pg exports to a file that you can load into PostgreSQL with the psql client, but you can also import directly into a PostgreSQL database by setting its DSN into the configuration file. With all configuration options of ora2pg.conf you have full control of what should be exported and how.

Features included: 

        - Export full database schema (tables, views, sequences, indexes), with unique, primary, foreign key and check constraints.
        - Export grants/privileges for users and groups.
        - Export a table selection (by specifying the table names).
        - Export Oracle schema to a PostgreSQL 7.3+ schema.
        - Export predefined functions, triggers, procedures, packages and package bodies.
	- Export range and list Oracle partition.
        - Export full datas or following a WHERE clause.
	- Full support of Oracle BLOB object as PG BYTEA.
        - Export Oracle views as PG tables.
	- Export Oracle user defined types.
        - Provide basic help for converting PLSQL code to PLPGSQL (still needs manual work).
	- Works on any plateform.

Ora2Pg do its best to automatically convert your Oracle database to PostgreSQL but there's still manual works to do. The Oracle specific PL/SQL code generated for functions, procedures, packages and triggers has to be reviewed to match the PostgreSQL syntax. You will find some useful recommandations on porting Oracle PL/SQL code to PostgreSQL PL/PGSQL at Converting from other Databases to PostgreSQL, section: Oracle.

Some links

Slides of the talk I've given at PgConf.Eu 2011 in Amsterdam, conference "Ora2Pg best practices" in french and a translation in english

An english presentation talking about Ora2Pg at PgCon 2010 in Ottawa: Oracle to PostgreSQL migration, great thanks to Gurjeet Singh from EnterpriseDB

A french presentation of Ora2Pg at the Linux Solution 2005 in Paris: LinuxSolution2005-Ora2pg.pdf, great thanks to Jean-Paul Argudo from Dalibo.

Ora2Pg Developement

Source Lines of Code: 4,080
Estimated Cost to Develop: $ 118,259

(Generated using David A. Wheeler's 'SLOCCount'.)