Home / Guides
Migration guide

Oracle to PostgreSQL with ora2pg: the workflow and the traps

By · November 2, 2026 · 13 min read

The short version

An Oracle to PostgreSQL migration using ora2pg goes in a fixed order: install ora2pg with the Oracle client and the DBD::Oracle Perl module, point ora2pg.conf at the source schema, run SHOW_REPORT --estimate_cost to size the job, export the tables and sequences, load them, load the data with COPY, then add indexes and constraints, then port the PL/SQL by hand from what ora2pg gives you, and finish with its TEST actions. ora2pg converts the schema and moves the data well. What it cannot decide for you is behaviour: Oracle DATE carries a time, NUMBER has no fixed type, an empty string is NULL, DECODE matches NULL to NULL, and unquoted names are uppercase. Each of those changes query results silently on PostgreSQL, and each is shown below with real output from PostgreSQL 16.

What I ran, and what I did not

I did not run ora2pg for this guide. It is a Perl program that reads Oracle through DBD::Oracle, and the installation page says that module needs the Oracle client libraries and ORACLE_HOME set. On this laptop:

$ which ora2pg
ora2pg not found
$ perl -MDBD::Oracle -e 1
Can't locate DBD/Oracle.pm in @INC (you may need to install the DBD::Oracle module) ...

The Oracle Free container I keep for tests was also down (the Python driver got Connection refused on port 11521), so nothing on the Oracle side was executed either. The Oracle behaviour below is quoted from Oracle's SQL Language Reference. The PostgreSQL side was run for real: the converted table, the rewritten function and every trap, on PostgreSQL 16.14 (Debian 16.14-1.pgdg13+1), through psycopg2. The output blocks are what that script printed.

Install: Perl, Oracle client, DBD::Oracle

From the ora2pg installation docs:

ora2pg itself installs like any Perl module, per Installing Ora2Pg:

tar xjf ora2pg-x.x.tar.bz2
cd ora2pg-x.x/
perl Makefile.PL
make && make install

That puts ora2pg in /usr/local/bin/ and a template ora2pg.conf in /etc/ora2pg/. On Windows the docs point to Strawberry Perl. The DBD::Oracle build is where most installs stall, so test the connection before anything else: ora2pg -t SHOW_VERSION -c config/ora2pg.conf prints the Oracle version (Oracle connection).

Start a project tree instead of one big output.sql

ora2pg --project_base /app/migration/ --init_project test_project

Per the configuration page, this creates schema/ (converted DDL), sources/ (the original Oracle code), data/, config/, reports/, plus two scripts: export_schema.sh, which exports every object type, and import_all.sh, which loads the files interactively. The same page says the export script ends by printing the command for the data export, to be run once the schema import is done and checked. For a first migration the docs recommend using these scripts, and I agree: a directory per object type is much easier to review than one file.

ora2pg.conf: the directives that matter

The file is one directive per line, uppercase name, a tab, the value; a later line wins over an earlier one (configuration). The ones I set first:

ORACLE_HOME /opt/oracle/instantclient_21
ORACLE_DSN  dbi:Oracle:host=oradb;service_name=FREEPDB1;port=1521
ORACLE_USER system
ORACLE_PWD  secret
SCHEMA  APP
EXPORT_SCHEMA   1
PG_VERSION  16
PG_INTEGER_TYPE 1
DEFAULT_NUMERIC numeric
OUTPUT  output.sql

What each one does, from the docs:

Directive Effect
ORACLE_DSN, ORACLE_USER, ORACLE_PWD DBI connection; the docs suggest a DBA login so nothing is skipped for lack of grants (Oracle connection)
SCHEMA, EXPORT_SCHEMA which Oracle schema to read, and whether to create it as a PostgreSQL schema instead of using the default one (schema export)
PG_VERSION target major version (export control)
PG_INTEGER_TYPE, DEFAULT_NUMERIC NUMBER(p) becomes smallint, integer or bigint; NUMBER with no precision becomes DEFAULT_NUMERIC, which is bigint unless you change it (type control)
PRESERVE_CASE off means names are lowercased; the author does not recommend turning it on (export control)
NULL_EQUAL_EMPTY rewrites NULL tests in PL/SQL to treat '' as NULL (PL/SQL conversion)

DEFAULT_NUMERIC numeric is my choice, and the first trap below shows why.

SHOW_REPORT: the migration assessment

ora2pg -t SHOW_REPORT --estimate_cost --dump_as_html -c config/ora2pg.conf > reports/report.html

SHOW_REPORT lists every object type with counts, invalid objects and a comment on what will not be exported; --estimate_cost adds a cost column in units of five minutes by default (migration cost assessment). The report also gives a grade. A means the migration might run automatically, B means code rewriting up to a person-day limit, C means above it; a number from 1 (no stored code, no triggers) to 5 (stored code that needs rewriting) gives the technical difficulty. The sample report in the docs already shows what to expect: bitmap indexes become b-tree, jobs are not exported, synonyms become views. Read this report before you promise anyone a date. If the choice of tool is still open, I compare the options in ora2pg vs AWS SCT vs Azure DMS.

The order of exports and imports

TYPE takes one export type, or a comma-separated list, but COPY and INSERT cannot be chained with other types (export type). The order I use:

  1. TABLE with FILE_PER_CONSTRAINT, FILE_PER_INDEX and FILE_PER_FKEYS set, so constraints, indexes and foreign keys land in separate files (PostgreSQL import). Load only the table file.
  2. SEQUENCE, TYPE, PARTITION if you have them.
  3. COPY for the data. PostgreSQL's own advice is to create the table, bulk load with COPY, then build indexes and foreign keys afterwards (Populating a Database).
  4. The index, constraint and foreign key files.
  5. SEQUENCE_VALUES, which exports DDL to set each sequence's last value (export type). See the sequence trap below for why this runs after the data.
  6. FUNCTION, PROCEDURE, PACKAGE, TRIGGER, then VIEW. The docs say these types "still require little manual editing" before loading, especially with PL/SQL or Oracle-specific SQL in them (export type), and that PL/SQL conversion "is a work in progress" and "you will likely have manual work" (PL/SQL conversion).
  7. TEST, TEST_COUNT and TEST_DATA to diff objects, row counts and rows between the two sides (test the migration).

Oracle to PostgreSQL migration challenges, run on PostgreSQL

The Oracle source I had in mind is a small one, written in the uppercase style of most legacy schemas:

CREATE TABLE CUSTOMERS (
  CUSTOMER_ID NUMBER PRIMARY KEY,
  NAME        VARCHAR2(100) NOT NULL,
  MIDDLE_NAME VARCHAR2(50),
  CREDIT      NUMBER,
  SIGNED_UP   DATE,
  STATUS      CHAR(1)
);
CREATE SEQUENCE CUSTOMERS_SEQ START WITH 1001;

CREATE OR REPLACE FUNCTION CUSTOMER_LABEL(P_ID NUMBER) RETURN VARCHAR2 IS
  V_LABEL VARCHAR2(200);
BEGIN
  SELECT NAME || ' ' || NVL(MIDDLE_NAME, '-') || ' (' ||
         DECODE(STATUS, 'A', 'active', 'S', 'suspended', NULL, 'unknown', 'other') || ')'
    INTO V_LABEL FROM CUSTOMERS WHERE CUSTOMER_ID = P_ID;
  RETURN V_LABEL;
END;
/

On PostgreSQL I built the table the way ora2pg's settings above describe it, and added a second column for each trap so the two choices sit side by side:

CREATE TABLE customers (
    customer_id  bigint PRIMARY KEY,
    name         varchar(100) NOT NULL,
    middle_name  varchar(50),
    credit       bigint,
    credit_exact numeric,
    signed_up    timestamp(0),
    signed_up_d  date,
    status       char(1)
);
INSERT INTO customers VALUES
 (1001, 'Ann Lee',   '',     1250.75, 1250.75, '2024-03-15 14:30:00', '2024-03-15 14:30:00', 'A'),
 (1002, 'Bo Chen',   NULL,   80.5,    80.5,    '2024-03-16 09:05:59', '2024-03-16 09:05:59', 'S'),
 (1003, 'Cy Novak',  'J',    0.4,     0.4,     '2024-03-17 23:59:59', '2024-03-17 23:59:59', NULL);
INSERT 0 3

NUMBER without precision

In Oracle, NUMBER with no precision or scale "specifies the maximum range and precision for an Oracle number" (Data Types), so a column declared that way can hold 1250.75. Mapped to bigint, PostgreSQL rounds and does not complain:

SELECT customer_id, credit, credit_exact FROM customers ORDER BY 1;
customer_id | credit | credit_exact
1001 | 1251 | 1250.75
1002 | 81 | 80.5
1003 | 0 | 0.4

Before you accept bigint, check the source with SELECT COUNT(*) FROM t WHERE col != TRUNC(col) per column. If you cannot check, map to numeric: unconstrained numeric stores values "of any length" without coercing them to a scale (numeric types).

DATE carries a time

Oracle stores year, month, day, hour, minute and second in every DATE (Data Types). PostgreSQL date has no time of day (date/time types). ora2pg's default DATA_TYPE list maps DATE to timestamp(0) (type control), which is right. The danger is someone "fixing" it back to date:

SELECT customer_id, signed_up, signed_up_d FROM customers ORDER BY 1;
customer_id | signed_up | signed_up_d
1001 | 2024-03-15 14:30:00 | 2024-03-15
1002 | 2024-03-16 09:05:59 | 2024-03-16
1003 | 2024-03-17 23:59:59 | 2024-03-17

More on this in Oracle DATE is not PostgreSQL date.

The empty string and NVL

Oracle "currently treats a character value with a length of zero as null" (Nulls), so Ann's empty middle name would be NULL there. PostgreSQL keeps the two apart:

SELECT count(*) FILTER (WHERE middle_name IS NULL) AS is_null, count(*) FILTER (WHERE middle_name = '') AS empty FROM customers;
is_null | empty
1 | 1

That breaks every NVL translated straight to COALESCE. Oracle's NVL returns expr2 when expr1 is null (NVL); PostgreSQL's COALESCE returns the first argument that is not null, and its docs say it provides capabilities similar to NVL (conditional expressions). An empty string is not null on PostgreSQL, so it passes straight through:

SELECT customer_id, COALESCE(middle_name, '-') AS coalesce_only, COALESCE(NULLIF(middle_name, ''), '-') AS nullif_first FROM customers ORDER BY 1;
customer_id | coalesce_only | nullif_first
1001 |  | -
1002 | - | -
1003 | J | J

Either wrap with NULLIF(x, ''), or turn empty strings into NULL in the data once, which is what the ora2pg docs call the better way if you control the application (PL/SQL conversion). Rows loaded from Oracle will not contain '', but rows your new application writes will. The full story is in Oracle treats an empty string as NULL, PostgreSQL does not.

DECODE matches NULL, CASE does not

"In a DECODE function, Oracle considers two nulls to be equivalent" (DECODE). The obvious rewrite, a simple CASE status WHEN ..., compares with =, and NULL = NULL is not true:

SELECT customer_id,
       CASE status WHEN 'A' THEN 'active' WHEN 'S' THEN 'suspended' WHEN NULL THEN 'unknown' ELSE 'other' END AS simple_case,
       CASE WHEN status = 'A' THEN 'active' WHEN status = 'S' THEN 'suspended' WHEN status IS NULL THEN 'unknown' ELSE 'other' END AS searched_case
  FROM customers ORDER BY 1;
customer_id | simple_case | searched_case
1001 | active | active
1002 | suspended | suspended
1003 | other | unknown

Any DECODE with NULL among its search values has to become a searched CASE with IS NULL.

The function, converted and run

With those three fixes the function becomes:

CREATE FUNCTION customer_label(p_id bigint) RETURNS varchar
LANGUAGE plpgsql STABLE AS $$
DECLARE
  v_label varchar(200);
BEGIN
  SELECT name || ' ' || COALESCE(NULLIF(middle_name, ''), '-') || ' (' ||
         CASE WHEN status = 'A' THEN 'active'
              WHEN status = 'S' THEN 'suspended'
              WHEN status IS NULL THEN 'unknown'
              ELSE 'other' END || ')'
    INTO v_label
    FROM customers WHERE customer_id = p_id;
  RETURN v_label;
END;
$$;
CREATE FUNCTION

SELECT customer_id, customer_label(customer_id) FROM customers ORDER BY 1;
customer_id | customer_label
1001 | Ann Lee - (active)
1002 | Bo Chen - (suspended)
1003 | Cy Novak J (unknown)

STABLE matches what ora2pg does by default with FUNCTION_STABLE (PL/SQL conversion).

ROWNUM becomes LIMIT, after the ORDER BY

WHERE ROWNUM <= 2 is applied before ORDER BY in the same query; Oracle's docs warn that the rows are numbered first and reordered after (ROWNUM), which is why Oracle code wraps the ordered query in a subquery. On PostgreSQL the subquery goes away, and LIMIT needs an ORDER BY or you get "an unpredictable subset" of rows (LIMIT and OFFSET):

SELECT customer_id, credit_exact FROM customers ORDER BY credit_exact DESC LIMIT 2;
customer_id | credit_exact
1001 | 1250.75
1002 | 80.5

Sequences that start behind the data

SEQUENCE exports each sequence with its last position at export time (export type). If the data export runs later, the table holds ids the sequence has not reached yet. Here the sequence was created at 1001 and the data was already in:

CREATE SEQUENCE customers_seq START WITH 1001;
CREATE SEQUENCE

INSERT INTO customers (customer_id, name) VALUES (nextval('customers_seq'), 'Dee Park');
ERROR:  duplicate key value violates unique constraint "customers_pkey"
DETAIL:  Key (customer_id)=(1001) already exists.

SELECT setval('customers_seq', (SELECT max(customer_id) FROM customers));
setval
1003

INSERT INTO customers (customer_id, name) VALUES (nextval('customers_seq'), 'Dee Park') RETURNING customer_id;
customer_id
1004

Run SEQUENCE_VALUES (or setval per sequence, see sequence functions) after the data load and before the application connects. Oracle's CUSTOMERS_SEQ.NEXTVAL becomes nextval('customers_seq'); the report notes ora2pg rewrites those calls (migration cost assessment).

Uppercase identifiers

Oracle reads unquoted names as uppercase (object names); PostgreSQL folds them to lowercase (lexical structure). With PRESERVE_CASE off, ora2pg lowercases everything and unquoted SQL from the application keeps working. Turn it on and this is what every query meets:

CREATE TABLE "CUSTOMERS_QUOTED" (id integer);
CREATE TABLE

SELECT * FROM customers_quoted;
ERROR:  relation "customers_quoted" does not exist
LINE 1: SELECT * FROM customers_quoted
                      ^

SELECT count(*) FROM "CUSTOMERS_QUOTED";
count
0

The case rules for the other engines are in upper, lower and quoted identifier case across databases.

Sources