ora2pg Output Is a Starting Point, Not a Finish Line

What ora2pg Actually Does

ora2pg is an open-source Perl tool that connects to an Oracle database, reads the schema objects, and generates PostgreSQL-compatible DDL and, where possible, translated PL/SQL. It handles the mechanical conversions automatically: table definitions, data type mapping (VARCHAR2 to VARCHAR, NUMBER to NUMERIC, DATE to TIMESTAMP), index definitions, sequence creation, and basic stored procedure syntax translation.

The ora2pg assessment report is legitimately useful. Run it before any conversion work:

# Generate a migration complexity assessment
ora2pg -t SHOW_REPORT \
  --estimate_cost \
  -c ora2pg.conf \
  > migration_assessment.txt

# The report grades each object by migration complexity:
# A = trivial  (tables, basic indexes)
# B = easy     (simple views, sequences)
# C = medium   (stored procedures with basic PL/SQL)
# D = difficult (packages, complex PL/SQL, Oracle-specific functions)
# E = very difficult (DBMS_* package calls, advanced features)

The assessment score gives you a defensible estimate for planning — if you have 400 objects graded A and B, 40 graded C, and 8 graded D or E, your conversion effort is concentrated in those 8 difficult objects, not spread uniformly. The mistake most teams make is treating the ora2pg output for grade C-E objects as done once it compiles without errors. It compiles. It probably doesn't behave correctly.

Hidden Cost #1: PL/SQL Package State

Oracle packages have package-level variables — state that persists for the duration of a database session. A PL/SQL package can declare a variable at the package level, set it in one procedure call, and read it in a subsequent call within the same session. This is a common pattern for passing context between procedures without parameter threading.

PostgreSQL has no equivalent construct. PL/pgSQL functions are stateless between calls. ora2pg will translate the package structure and move the package-level variables into the function bodies, but the state management logic doesn't survive the conversion.

-- Oracle package with session-level state (common pattern)
CREATE OR REPLACE PACKAGE order_context AS
  g_user_id    NUMBER;
  g_org_id     NUMBER;
  g_audit_flag BOOLEAN := FALSE;

  PROCEDURE set_context(p_user NUMBER, p_org NUMBER);
  FUNCTION  get_user RETURN NUMBER;
END order_context;

-- ora2pg output approximation (does NOT preserve session state):
CREATE OR REPLACE FUNCTION order_context_set_context(
  p_user NUMERIC, p_org NUMERIC
) RETURNS VOID AS $$
BEGIN
  -- g_user_id and g_org_id have nowhere to live between calls
  -- ora2pg may emit these as local variables or omit them entirely
  NULL;
END;
$$ LANGUAGE plpgsql;

The PostgreSQL equivalent requires either application-layer session context (SET LOCAL app.user_id = X and current_setting('app.user_id')), a dedicated context table with session-scoped rows, or a connection-level parameter group. Each approach has trade-offs in complexity and connection pool compatibility. ora2pg cannot choose for you — a human needs to design the replacement pattern for every package that relies on session-level state.

Hidden Cost #2: Oracle Implicit Type Conversion

Oracle converts between data types implicitly in many contexts where PostgreSQL raises an error. The most common: comparing a VARCHAR2 column to a NUMBER literal, or passing a VARCHAR2 to a function that expects a DATE. Oracle silently coerces. PostgreSQL refuses.

-- Oracle: works silently
SELECT * FROM orders WHERE order_status = 1;
-- order_status is VARCHAR2, 1 is a NUMBER
-- Oracle coerces 1 to '1' and compares

-- PostgreSQL: fails with type mismatch
-- ERROR: operator does not exist: character varying = integer

-- Fix: explicit cast in the query or index definition
SELECT * FROM orders WHERE order_status = '1';
-- or
SELECT * FROM orders WHERE order_status = 1::text;

This shows up in hundreds of places across a medium-sized Oracle schema. Application queries that were written against Oracle's permissive type system need to be audited and fixed. ora2pg converts the schema — it cannot touch application code. You need to run your full application test suite against the converted PostgreSQL schema and fix every type mismatch error. Budget this work as a separate stream from schema conversion.

DATE type behavior is another common surprise. Oracle's DATE type includes time components (hours, minutes, seconds). PostgreSQL's DATE type is date-only; you must use TIMESTAMP for date-plus-time. ora2pg maps Oracle DATE to PostgreSQL TIMESTAMP by default, which is usually correct — but any application code that relies on Oracle's DATE arithmetic (adding integers to dates, SYSDATE comparisons) needs review for correctness after the type change.

Hidden Cost #3: Oracle Hints Have No PostgreSQL Equivalent

Oracle SQL hints (/*+ INDEX(t idx_name) */, /*+ USE_NL(a b) */, /*+ PARALLEL(4) */) are embedded in application SQL to override the optimizer's plan choices. On Oracle databases that have accumulated years of optimizer tuning, hints are common — often critical for keeping specific queries on fast plans.

PostgreSQL ignores Oracle hints. They're parsed as comments and have no effect. ora2pg's schema conversion leaves hints in the converted SQL as dead code. The query runs, but the plan the hint was enforcing is gone. If that hint was placed there because the Oracle optimizer was choosing a bad plan without it, the PostgreSQL query will choose its own plan — which may or may not be good.

-- Oracle query with performance-critical hint
SELECT /*+ INDEX(o IDX_ORDERS_CUSTID) NL_JOIN(o i) */
  o.order_id, o.order_date, i.item_desc
FROM orders o
JOIN order_items i ON i.order_id = o.order_id
WHERE o.customer_id = :cust_id
  AND o.status = 'OPEN';

-- After ora2pg conversion to PostgreSQL:
-- The hint is a comment -- PostgreSQL ignores it entirely
-- The plan is now 100% at PostgreSQL's discretion

-- If plan is suboptimal, PostgreSQL alternatives:
-- 1. Verify statistics are current: ANALYZE orders; ANALYZE order_items;
-- 2. Check index exists and is being used: EXPLAIN (ANALYZE, BUFFERS) ...
-- 3. If PostgreSQL chooses a bad plan, use pg_hint_plan extension
--    (not default, must be installed separately)
-- 4. Or rewrite the query to be more explicit about join order

The practical work: extract every Oracle hint from your application's SQL, understand why it was placed there, verify whether PostgreSQL's optimizer chooses a correct plan without guidance, and add pg_hint_plan hints where needed. On a large application, this is weeks of work, not days.

Hidden Cost #4: ROWNUM and Pagination Rewrites

Oracle uses ROWNUM for row limiting and pagination. This is so deeply embedded in Oracle application code that it deserves its own section.

-- Oracle pagination pattern (extremely common)
SELECT *
FROM (
  SELECT t.*, ROWNUM rn
  FROM (
    SELECT order_id, order_date, customer_id
    FROM orders
    WHERE status = 'OPEN'
    ORDER BY order_date DESC
  ) t
  WHERE ROWNUM <= 20
)
WHERE rn > 10;

-- PostgreSQL equivalent
SELECT order_id, order_date, customer_id
FROM orders
WHERE status = 'OPEN'
ORDER BY order_date DESC
LIMIT 10 OFFSET 10;

ora2pg attempts to convert ROWNUM patterns to LIMIT/OFFSET, and it handles the simple cases. It does not reliably handle nested ROWNUM subqueries, ROWNUM in UPDATE statements, or ROWNUM combined with window functions. Every ROWNUM occurrence in application code needs manual review post-conversion.

Hidden Cost #5: Sequences and Identity Gaps

Oracle sequences and PostgreSQL sequences behave differently in one critical way: Oracle sequences have a NOCACHE option that guarantees no gaps on restart, while PostgreSQL's SEQUENCE CACHE parameter pre-allocates values in memory per-session. If your application has any logic that assumes sequence values are gapless or monotonically increasing without gaps (never a safe assumption, but common in legacy code), PostgreSQL's caching behavior will produce gaps on server restart or connection reset.

-- Oracle sequence (common production configuration)
CREATE SEQUENCE order_seq
  START WITH 1
  INCREMENT BY 1
  NOCACHE
  NOORDER;

-- ora2pg output
CREATE SEQUENCE order_seq
  START 1
  INCREMENT 1
  CACHE 20;  -- ora2pg adds a cache value by default

-- If your application checks for sequence gaps as a data integrity measure,
-- change to CACHE 1 (no caching) or document that gaps are expected.
-- For PostgreSQL 10+, IDENTITY columns are the preferred approach:
-- order_id BIGINT GENERATED ALWAYS AS IDENTITY

The Conversion Work That Actually Takes Time

To summarize where the real effort goes in an ora2pg-based migration:

Package state rewrites: Every Oracle package with package-level variables needs a designed replacement pattern. Plan 4-8 hours per complex package.
Application type mismatch remediation: Run the full application test suite against the converted schema; every type mismatch error is a fix. Budget based on test suite coverage — you won't find them all in testing, only in production.
Query plan validation: For every query with Oracle hints, verify PostgreSQL chooses an acceptable plan. EXPLAIN ANALYZE on the top 100 queries by frequency and by execution time from your AWR baseline.
ROWNUM and pagination audits: grep for ROWNUM across all application code. Every occurrence needs manual review.
DBMS_* package replacements: DBMS_SCHEDULER → pg_cron, DBMS_CRYPTO → pgcrypto, DBMS_OUTPUT → RAISE NOTICE, DBMS_LOB → bytea/text operations. Each is a separate effort.

A reasonable rule of thumb from production migrations: ora2pg automated conversion handles 60-70% of the mechanical work. The remaining 30-40% is manual remediation. For a database with 500+ schema objects and a complex application layer, that manual work is measured in months, not weeks. Build that into your project plan before you commit a go-live date.

Planning an Oracle-to-PostgreSQL migration?

We scope Oracle migrations with an honest assessment of what automated tooling handles and what requires manual engineering. Free assessment, no obligation.

Get a Free Assessment → More Articles