Migrating a Legacy Oracle Schema to PostgreSQL Without Losing PL/SQL Logic

Moving tables from Oracle to PostgreSQL is a solved problem. The risk lives in the PL/SQL that accumulated over the database's life: packages, triggers, and procedures that encode rules no one has written down. Porting the schema without auditing that surface area produces a database that holds the right rows and applies the wrong logic.

Start with an audit, not a migration

Before any DDL is translated, inventory the executable surface: how many packages, how many procedures and functions, how many triggers, and how many lines of PL/SQL depend on Oracle-specific constructs (sequences, hierarchical queries, autonomous transactions, package-level state) that have no direct PostgreSQL equivalent.

Oracle construct	PostgreSQL path	Effort
Sequences + triggers for IDs	`GENERATED ... AS IDENTITY`	Low
`CONNECT BY` hierarchies	Recursive CTE	Medium
Package-level state	Refactor — no equivalent	High
Autonomous transactions	Redesign (dblink / rethink)	High

Implementation note

An automated converter will translate perhaps 70% of straightforward PL/SQL. The remaining 30% — the package state and the autonomous transactions — is exactly the logic your core operations depend on. Porting it under test is the fixed-scope bridge we deliver before a full cutover is scheduled.

Estimate your PL/SQL risk →

Port under test, not under pressure

Each ported procedure needs a behavioural test built from the Oracle original's actual outputs, so equivalence is proven rather than assumed. This is slower up front and far cheaper than discovering a logic divergence in production after the source system is gone.

Free reference snippet

Yours to use

A working piece from this integration — no sign-up. The full build handles the edge cases, safeguards, and cutover.

Audit your Oracle PL/SQL surface area before estimating migration effort:

-- Oracle: inventory all PL/SQL objects with line counts
SELECT
    OBJECT_TYPE,
    OBJECT_NAME,
    COUNT(*)            AS    statements,
    MAX(LINE)              AS    total_lines
FROM USER_SOURCE
WHERE OBJECT_TYPE IN (
    'PACKAGE', 'PACKAGE BODY',
    'PROCEDURE', 'FUNCTION', 'TRIGGER'
)
GROUP BY OBJECT_TYPE, OBJECT_NAME
ORDER BY MAX(LINE) DESC;

-- Equivalent PostgreSQL query to check pgsql function inventory
SELECT
    routine_type,
    routine_name,
    pg_get_functiondef(oid) AS definition
FROM pg_proc
WHERE pronamespace = 'public'::regnamespace
  AND routine_type IN ('FUNCTION', 'PROCEDURE');

-- Syntax side-by-side for a simple row-update procedure
/* Oracle PL/SQL */
CREATE OR REPLACE PROCEDURE raise_salary(
    p_emp_id   IN  employees.employee_id%TYPE,
    p_increase IN  NUMBER
)
IS
BEGIN
    UPDATE employees
       SET salary = salary + p_increase,
           modified_dt = SYSDATE
     WHERE employee_id = p_emp_id;

    IF SQL%ROWCOUNT = 0 THEN
        RAISE_APPLICATION_ERROR(-20001, 'Not found');
    END IF;
END raise_salary;
/

/* PostgreSQL PL/pgSQL */
CREATE OR REPLACE PROCEDURE raise_salary(
    p_emp_id   INTEGER,
    p_increase NUMERIC
)
LANGUAGE plpgsql
AS $$
BEGIN
    UPDATE employees
       SET salary      = salary + p_increase,
           modified_dt = CURRENT_TIMESTAMP
     WHERE employee_id = p_emp_id;

    IF NOT FOUND THEN
        RAISE EXCEPTION 'Not found';
    END IF;
END;
$$;

Implementation pathway

Step by step

How we'd take this from discovery to a production-safe cutover — the phases, the canonical mapping, and the edge cases that bite.

Data migration is the easy part — mechanical, well-tooled, reversible. The genuine engineering challenge is porting PL/SQL business logic: packages, triggers, stored procedures, and the implicit contracts they encode. This pathway structures that port in phases that surface risk early, validate translation accuracy incrementally, and preserve the ability to roll back to Oracle until cutover is proven.

Implementation Phases

Schema and dependency audit. Use USER_DEPENDENCIES and ALL_OBJECTS in Oracle to map every package, procedure, function, trigger, sequence, and synonym. Classify each by complexity: stateless query wrappers (low risk), multi-statement logic with cursors (medium), packages with global variables or session state (high). Build a dependency graph so you migrate leaf objects before their consumers. Output this as a plsql_inventory.csv with columns: object_name, object_type, line_count, complexity_tier, depends_on.
Tool-assisted surface translation. Run ora2pg (or aws_dms with PL/SQL transformation rules, or the EnterpriseDB migration toolkit) against the inventory from phase 1. Set TYPE = PACKAGE, TYPE = PROCEDURE, TYPE = FUNCTION individually — do not bulk-export everything at once. Each run produces a .sql output and a .report file indicating lines that require manual review. Target: an automated translation pass for every tier-low object, reviewed and committed to a feature branch in your migration schema.
Package decomposition. Oracle packages have no direct equivalent in PostgreSQL. Split each package into a PostgreSQL schema of the same name, then create standalone FUNCTIONs (or PROCEDUREs in PG 11+). Preserve original namespacing: PKG_ORDERS.CalculateTotals() becomes orders.calculate_totals(). Handle package-level global variables by converting to SET_config() session parameters or by passing state explicitly — document the decision in the migration log.
Manual logic rewrite for unsupported constructs. Every translated file will contain markers where automated translation fails. Prioritize: BULK COLLECT/FORALL → FOREACH ... IN ARRAY with UNNEST(); AUTONOMOUS_TRANSACTION → dblink or NOTIFY via pg_notify(); CONNECT BY → recursive WITH RECURSIVE CTEs; SYS_REFCURSOR → REFCURSOR with explicit RETURN clause. Exception blocks shift from WHEN OTHERS THEN to WHEN OTHERS THEN RAISE or explicit GET STACKED DIAGNOSTICS. Each rewritten function gets a new row in plsql_inventory with translated = TRUE and translator = 'manual'.
Trigger conversion. Oracle row-level triggers with :NEW/:OLD must be rewritten as PostgreSQL CREATE TRIGGER statements using NEW.* and OLD.* in transition tables (REFERENCING NEW TABLE AS new_rows). BEFORE triggers that modify :NEW values become INSTEAD OF triggers on views or BEFORE row triggers using NEW.* := assignments. Validate that firing order (Oracle's FOLLOWS/PRECEDES) maps correctly — PostgreSQL supports FOLLOWS from version 9.0 onward.
Sequence and identity alignment. Oracle sequences embedded in BEFORE INSERT triggers become PostgreSQL GENERATED ALWAYS AS IDENTITY columns or explicit nextval('seq_name') calls. If sequences drive surrogate keys consumed by application code, validate that CURRVAL semantics (Oracle) map to lastval() (session-scoped in PG) or that you pass the new key explicitly in returning clauses using RETURNING id INTO.
Shadow-run unit testing. Deploy the migrated pg schema alongside the existing Oracle schema. Replay a curated set of production transaction traces (captured via Oracle AWR or flashback query) against both systems. Compare outputs row-by-row using MINUS on both sides. Every discrepancy generates a bug ticket. Target: zero critical discrepancies in tier-high objects before proceeding.
Load testing with production volume. Spin up a PostgreSQL read replica fed by logical replication from Oracle (ora2pg --type COPY for initial load, then BDR or pglogical for delta sync). Run your full regression suite against the replica. Profile with pg_stat_statements and compare query plans — sequential scans introduced by different cost models often indicate missing indexes that Oracle's optimizer avoided.
Cutover freeze and dual-write gate. Agree on a cutover window with the application team. Enable dual-write: Oracle is system of record, PostgreSQL receives writes via an intermediate API layer or trigger-based propagation. Run in this state for 48–72 hours. Monitor pg_stat_replication lag, exception logs in both systems, and application error rates. Only after stability metrics are met does the team flip the primary connection string.

Field Mapping: Oracle to PostgreSQL Equivalents

Oracle Construct	PostgreSQL Equivalent	Key Identifier / Note
`CREATE PACKAGE` / `PACKAGE BODY`	`CREATE SCHEMA` + standalone `FUNCTION`s	Package state lost; use `SET_config()` for session globals
`CREATE SEQUENCE` + `NEXTVAL`	`GENERATED ALWAYS AS IDENTITY` or `nextval('seq')`	Sequence caching behavior differs; test with `currval()` scope
`:NEW` / `:OLD` in row triggers	`NEW.` / `OLD.` with transition tables	`REFERENCING NEW TABLE AS nt` for statement-level access
`NVL(expr, default)`	`COALESCE(expr, default)`	Handles more than two arguments; ANSI-compliant
`SYSDATE`	`CURRENT_TIMESTAMP`	Oracle `SYSDATE` is session timezone; PG uses database timezone
`BULK COLLECT INTO`	`INTO ARRAY[... ]` or `FOREACH` over cursor	No native `FORALL`; simulate with `INSERT ... SELECT UNNEST()`
`CONNECT BY` / `PRIOR`	`WITH RECURSIVE` CTE	Rewrite hierarchical queries; Oracle's `LEVEL` → explicit counter
`DUAL` table	`DUAL` (PG 9.1+ supports it) or `SELECT ...;` directly	PostgreSQL does not require `FROM DUAL` for expressions

Edge Cases

Autonomous transactions — PostgreSQL has no native support. If your Oracle code uses AUTONOMOUS_TRANSACTION for audit logging inside committed transactions, the pglogical layer or a notification queue (pg_notify) must replace it; failing to do so silently changes audit semantics.
Synonyms — Oracle public and private synonyms have no PostgreSQL equivalent. Resolve all synonym references before migration or map them to schemas and SEARCH_PATH adjustments; unresolved synonyms cause runtime undefined_table errors.
Object types and UDTs — Oracle's CREATE TYPE objects with methods cannot be directly migrated. Map to PostgreSQL CREATE TYPE (composite) and separate functions, or handle in application layer.
Session state in package globals — Any package variable that holds state across calls (not reset per statement) will be lost or behave unexpectedly in PostgreSQL. Applications relying on this as a transaction-scoped cache will produce incorrect results.
Dynamic SQL with bind variables — EXECUTE IMMEDIATE with bind variable syntax may translate incorrectly if the Oracle code uses positional USING clauses with mixed types; validate generated PREPARE/EXECUTE statements for type coercion correctness.
TIMESTAMP vs DATE precision — Oracle DATE has second precision; PostgreSQL TIMESTAMP has microsecond. Applications casting DATE to string with an implicit format may mis-parse dates after migration.
Case-sensitive identifiers — Oracle object names are case-insensitive unless quoted. PostgreSQL unquoted identifiers are lowercased; quoted identifiers are case-sensitive. Package/function names in pgSQL must match the case of the application call or be normalized in the migration script.

Cutover

Make the cutover reversible by retaining the Oracle schema in read-only mode for a minimum of two full business cycles after the flip. Use a shadow comparison: run identical transaction sets against both databases for 48 hours post-cutover, diffing results via an automated MINUS query suite against a reconciliation table. If the delta exceeds zero on any critical object, the rollback procedure is: update the connection string in the application's secrets manager (e.g., Vault, AWS Parameter Store) to point back to Oracle, re-enable Oracle writes, and discard the PostgreSQL replica — the application layer never writes to both simultaneously. Only after the reconciliation period passes without discrepancies does the team decommission the Oracle objects and delete the migration schema from PostgreSQL. Schedule the final Oracle drop during a low-traffic window and retain a final logical export (pg_dump of the migration schema) as a point-in-time artifact for 90 days.

What a full implementation includes

Canonical mapping between Oracle · PL/SQL and PostgreSQL · PL/pgSQL, to the field level.
The edge cases that corrupt data at cutover — identified, handled, and tested.
Production-safe rollout: reversible, phased, with reconciliation checks.
Handover documentation your team can operate from.