Ora2Pg, a Perl converter that turns an Oracle schema into PostgreSQL SQL

Two files and one configuration edit

Ora2Pg is a Perl script and a Perl module, and the operational surface is small. The only thing you have to modify is the configuration file, where you set the DSN pointing at the Oracle database and optionally a schema name. Once that is done, you choose the type of export you want and the tool does the rest. The export types are the real interface: TABLE with constraints, VIEW, MVIEW, TABLESPACE, SEQUENCE, INDEXES, TRIGGER, GRANT, FUNCTION, PROCEDURE, PACKAGE, PARTITION, TYPE, INSERT or COPY, FDW, QUERY, KETTLE and SYNONYM. The design goal is stated in plain terms: it should not require any Oracle knowledge beyond the connection parameters. That framing suits reverse engineering a database you inherited as much as it suits a planned enterprise move, and the same code path handles both.

File output by default, direct import when you want it

By default the tool writes to a file that you load with the psql client, which is the safer arrangement for a production database, since an operator reviews the SQL before it runs. The alternative is to point the configuration at a PostgreSQL DSN as well and let the tool import directly. The documentation is explicit that with all the options in the configuration file you have full control over what is exported and how. A report of the Oracle database content can be produced, and separately there are assessments for migration cost, for migration difficulty level, for PL/SQL code held in a file, and for Oracle SQL queries held in a file. The project points to an HTML sample of a database migration report so you can see the shape of that output before running it against anything large.

What the converter actually produces

The feature list is long but specific about the mapping. A full schema export covers tables, views, sequences and indexes, with unique, primary, foreign key and check constraints. Grants and privileges for users and groups are exported. Range and list partitions, and subpartitions, are handled, along with materialized views. Oracle BLOB objects become PostgreSQL BYTEA, Oracle views can be exported as PostgreSQL tables, and user defined types come across. Oracle locator and spatial geometries can be exported into PostGIS, and DBLINK is exported as an Oracle foreign data wrapper. Oracle DIRECTORY objects can become an external table or a directory for the external_file extension, and SYNONYMS are exported as views. A table selection can be limited by name, data can be exported in full or filtered by a WHERE clause, and a diff between the Oracle and PostgreSQL databases is available for test purposes. Dispatching a list of SQL commands over multiple PostgreSQL connections is also supported.

The install path, and the Oracle client it depends on

Installing the Perl modules is the easy half. The hard half is Oracle. The Oracle Instant Client or a full Oracle installation must be present on the system, and the client libraries have to be in place with `ORACLE_HOME` defined, otherwise the DBD::Oracle driver will not work. The documentation also gives the RPM route for the instant client packages, and mentions the ZIP archives as an alternative to install wherever you prefer, for example under `/opt/oracle/instantclient_12_2/`. On Windows the guidance is Strawberry Perl with the matching Oracle clients, and the note that since version 5.32 the Perl distribution bundles pre-compiled drivers for DBD::Oracle and DBD::Pg. The rest of the requirements are a Perl 5.10 or later distribution and DBI above 1.614 for connecting to a database at all. The module installs go through CPAN:

perl -MCPAN -e 'install DBD::Oracle'
perl -MCPAN -e 'install DBD::MySQL'
perl -MCPAN -e 'install DBD::ODBC'
perl -MCPAN -e 'install Time::HiRes'

If your distribution already ships these modules you can skip the CPAN step and use the packaged versions.

Beyond Oracle, and the plugins that make it work

The scope is wider than the name suggests. Exporting a MySQL database needs the DBD::MySQL module, which in turn requires the MySQL client libraries, and exporting a SQL Server database needs DBD::ODBC, which requires the unixODBC package. The Compress::Zlib module lets the whole output be dumped as a compressed gzip file, and the bzip2 program does the same for that format. Loading exports on the fly into PostgreSQL, rather than through the psql client, requires the DBD::Pg module. The documentation also notes that on some distributions you may need Time::HiRes, and that if your distribution already packages these modules you can simply use those. Releases of the tool are published on SourceForge, and all Perl modules can be found on CPAN by searching the full name of the module, for example DBD::Oracle.

What still needs a human after the script runs

The project is upfront that the tool does its best but that manual work remains. Oracle specific PL/SQL generated for functions, procedures, packages and triggers has to be reviewed to match PostgreSQL syntax. The conversion offered is described as some basic automatic conversion of PLSQL code to PLPGSQL, and the word basic is doing real work in that sentence. A migration plan that treats the generated SQL as finished is a migration plan that stalls at the first stored procedure. The assessment features exist for this reason, since the cost and difficulty reports are meant to be read before you commit to a timeline rather than discovered halfway through it. The project also offers Kettle integration, generating XML ktr files for Pentaho Data Integrator, which is the path for teams that want the data movement in a dedicated data integration tool rather than in shell scripts.

A classic layout, and a recent push against old releases

The repository is laid out the way a mature Perl distribution usually is: an INSTALL file, a LICENSE, a MANIFEST, a Makefile.PL for the CPAN build, a README, a changelog, and directories for documentation, the library code, packaging scripts and the scripts themselves. The licence is GPL-3.0. The release history is where the one oddity sits. The tagged releases are old: version 25.0 from 2025-04-20, then 24.3 from 2024-03-28 and 24.2 from 2024-03-07, in a project that numbers releases by year and version rather than by semantic version. The last push to the repository was on 2026-09-28 and the repository is not archived, so work continues on the branch even though the last published release is well over a year old. In practice that means the master branch, not the release tag, is what you get when you clone, and the difference between the two is the thing to check before you plan around a specific behaviour.

Editorial conclusion

Adopt Ora2Pg if you are moving an Oracle schema to PostgreSQL and want a generated starting point with cost and difficulty reports attached, and skip it if you expect a one-command finished migration, because the project states the PL/SQL generated for functions, procedures, packages and triggers has to be reviewed by hand. Verify three things first: that the Oracle client libraries and `ORACLE_HOME` are in place before the Perl driver will work at all; which export type you actually need, since the list of them is long and the default writes to a file rather than into your target database; and whether your version of PostgreSQL is at least 8.4, which is the floor the schema output targets.

Frequently asked questions

Official sources

1. darold/ora2pg on GitHub
2. License: GPL-3.0
3. Project website
4. README
5. Releases