ora2pgpro consists of a Perl script ora2pgpro and a Perl module Ora2PgPro.pm, the only thing you have to modify is the configuration file ora2pgpro.conf by setting the DSN to the Oracle database and optionally the name of a schema. Once that is done you just have to set the type of export you want: TABLE with constraints, VIEW, MVIEW, TABLESPACE, SEQUENCE, INDEXES, TRIGGER, GRANT, FUNCTION, PROCEDURE, PACKAGE, PARTITION, TYPE, INSERT or COPY, FDW, QUERY, SYNONYM.
By default, ora2pgpro exports to a file that you can load into Postgres Pro with the psql client, but you can also import directly into a Postgres Pro database by setting its DSN into the configuration file. With all configuration options of ora2pgpro.conf, you have full control of what should be exported and how. The following features are 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 range/list partitions and subpartitions.
- Export a table selection (by specifying the table names).
- Export Oracle schema to a Postgres Pro schema.
- Export predefined functions, triggers, procedures, packages, and package bodies.
- Export full data or following a
WHEREclause. - Full support of Oracle BLOB objects as Postgres Pro bytea.
- Export Oracle views as Postgres Pro tables.
- Export Oracle user-defined types.
- Provide some basic automatic conversion of PL/SQL code to PL/pgSQL.
- Works on any platform.
- Export Oracle tables as foreign data wrapper tables.
- Export materialized views.
- Show a report of an Oracle database content.
- Migration cost assessment of an Oracle database.
- Migration difficulty level assessment of an Oracle database.
- Migration cost assessment of PL/SQL code from a file.
- Migration cost assessment of Oracle SQL queries stored in a file.
- Export Oracle locator and spatial geometries into PostGIS.
- Export
DBLINKas Oracle FDW. - Export
SYNONYMs as views. - Export
DIRECTORYas external table or directory forexternal_fileextension. - Dispatch a list of SQL orders over multiple Postgres Pro connections.
- Perform a diff between Oracle and Postgres Pro database for test purposes.
- Full support of Oracle packages as Postgres Pro packages.
- Export VARRAY as Postgres Pro arrays.
- Export associative arrays as collections of pg_variables.
ora2pgpro does its best to convert your Oracle database to Postgres Pro automatically, but there is still manual work to do. The Oracle-specific PL/SQL code generated for functions, procedures, packages, and triggers has to be reviewed to match the Postgres Pro syntax. You can find some useful recommendations on porting Oracle PL/SQL code to Postgres Pro PL/pgSQL in Porting from Oracle PL/SQL and Migration Tools in Postgres Pro.