Migrating Oracle PL/SQL to PostgreSQL PL/pgSQL: a working engineer's guide

If you have run an Oracle-to-Postgres migration, you already know the hard part is not the schema. It is the PL/SQL. Tens of thousands of lines of business logic, locked into Oracle-specific constructs, that need to compile and behave identically against a PostgreSQL target.

This post walks through the constructs that break naive converters and the patterns that work. It is the playbook we use inside Trekport, written so a senior DBA can audit every conversion choice.

TL;DR

PL/SQL and PL/pgSQL share a syntactic family but diverge in nearly every non-trivial construct. The five categories that cause 90 percent of pain:

Packages (PostgreSQL has no native equivalent)
Compound triggers (PostgreSQL fires statement and row triggers separately)
FORALL and BULK COLLECT (different bulk semantics)
Autonomous transactions (PostgreSQL added them only via pg_background)
Oracle built-in packages (DBMS_*, UTL_*) that have no Postgres analog

Everything else is workable with disciplined translation. Below, we go through each in detail.

1. Packages

Oracle packages bundle related functions and procedures, with a separately declared header (spec) and body. They support package-level state via package variables that persist for the duration of a session.

PostgreSQL has no package construct. The widely accepted convention is to emulate packages with schemas: each Oracle package becomes a Postgres schema, and each package function becomes a function in that schema.

Naive translation:

-- Oracle
CREATE OR REPLACE PACKAGE BODY hr_pkg AS
  PROCEDURE raise_salary(emp_id NUMBER, pct NUMBER) IS
  BEGIN
    UPDATE employees SET salary = salary * (1 + pct/100)
     WHERE id = emp_id;
  END;
END hr_pkg;

becomes

-- PostgreSQL
CREATE SCHEMA IF NOT EXISTS hr_pkg;

CREATE OR REPLACE PROCEDURE hr_pkg.raise_salary(
  emp_id INTEGER, pct NUMERIC
) LANGUAGE plpgsql AS $$
BEGIN
  UPDATE employees SET salary = salary * (1 + pct/100)
   WHERE id = emp_id;
END;
$$;

The simple cases work. The hard cases are package-level state, cross-package calls with circular references, and overloaded signatures.

Package-level state

Oracle:

CREATE OR REPLACE PACKAGE BODY config AS
  current_user_id NUMBER;
  PROCEDURE set_user(u NUMBER) IS BEGIN current_user_id := u; END;
  FUNCTION get_user RETURN NUMBER IS BEGIN RETURN current_user_id; END;
END config;

PostgreSQL has no session-scoped variables in PL/pgSQL. Three idiomatic options:

SET configuration parameters (recommended). Use a custom GUC namespace.

PERFORM set_config('config.current_user_id', '42', false);
SELECT current_setting('config.current_user_id', true)::int;

A session-scoped temp table for richer state.
pg_advisory_lock-bound state if you need per-transaction semantics.

Trekport defaults to GUCs because they are session-scoped, transparent in pg_settings, and cheap to read.

Circular package references

If hr_pkg calls audit_pkg and audit_pkg calls back into hr_pkg, you cannot deploy either in isolation. Naive deployers fail at the first unresolved reference.

Trekport solves this with a two-phase deployment: (a) deploy all package signatures first (header-only, with stub bodies), (b) replace bodies once all signatures exist. This is phase 7 and 8 of our 14-phase deployment, and it is documented in the docs.

Overloaded signatures

Oracle allows overloads on parameter type. PostgreSQL does too, with a caveat: overload resolution can become ambiguous in PostgreSQL where it was unambiguous in Oracle, particularly for NUMBER overloads (since Postgres splits this into int, bigint, numeric).

Trekport generates explicit INTEGER and BIGINT overloads alongside any NUMBER-typed function, so callers do not need explicit casts.

2. Triggers

PostgreSQL supports BEFORE and AFTER triggers at row and statement level. Oracle adds compound triggers, which let one trigger declaration handle all four phases (BEFORE STATEMENT, BEFORE EACH ROW, AFTER EACH ROW, AFTER STATEMENT) with shared local state across phases.

Compound trigger emulation

Oracle:

CREATE OR REPLACE TRIGGER salary_audit
FOR UPDATE OF salary ON employees
COMPOUND TRIGGER
  TYPE change_t IS TABLE OF NUMBER;
  changes change_t;

  BEFORE STATEMENT IS
  BEGIN
    changes := change_t();
  END BEFORE STATEMENT;

  AFTER EACH ROW IS
  BEGIN
    changes.EXTEND;
    changes(changes.LAST) := :NEW.id;
  END AFTER EACH ROW;

  AFTER STATEMENT IS
  BEGIN
    INSERT INTO audit_log (changed_ids) VALUES (changes);
  END AFTER STATEMENT;
END;

PostgreSQL needs three separate triggers backed by a shared session-scoped temporary table or array variable in a GUC. Trekport generates this mechanically and emits a comment block linking back to the original Oracle compound trigger so future readers can audit the translation.

3. FORALL and BULK COLLECT

Oracle FORALL and BULK COLLECT are bulk DML and bulk fetch primitives that bypass the per-row context switch between SQL and PL/SQL.

-- Oracle
DECLARE
  TYPE id_list IS TABLE OF NUMBER;
  ids id_list;
BEGIN
  SELECT id BULK COLLECT INTO ids FROM employees WHERE active = 'Y';
  FORALL i IN 1..ids.COUNT
    UPDATE employees SET reviewed_at = SYSDATE WHERE id = ids(i);
END;

PostgreSQL PL/pgSQL has no FORALL. The idiomatic translation is a single set-based UPDATE using ANY (array):

-- PostgreSQL
DO $$
DECLARE
  ids INTEGER[];
BEGIN
  SELECT ARRAY_AGG(id) INTO ids FROM employees WHERE active = 'Y';
  UPDATE employees SET reviewed_at = NOW() WHERE id = ANY(ids);
END $$;

This is faster than the Oracle original because it eliminates the round-trip entirely. Trekport detects FORALL-over-BULK-COLLECT patterns and collapses them into set-based UPDATEs automatically. The performance win is typically 5x to 20x.

4. Exception handling

PostgreSQL has full exception-handling support. Most Oracle exception code ports cleanly. Three quirks to know:

RAISE_APPLICATION_ERROR has no PG equivalent. Translate to RAISE EXCEPTION USING ERRCODE = '...'. Trekport assigns Oracle error codes (-20000 to -20999) to a Postgres ERRCODE namespace consistently across the codebase.
Named exceptions (PRAGMA EXCEPTION_INIT) translate to PG named exceptions in a per-schema namespace. Trekport generates the namespace declarations.
WHEN OTHERS THEN NULL is the most common anti-pattern. Trekport warns when it appears, because silent swallowing of errors usually masks migration bugs.

5. Built-in packages (DBMS_, UTL_)

Oracle ships hundreds of built-in packages. Most have no Postgres analog out-of-the-box. You have three options:

Option A: orafce (recommended baseline)

The orafce extension provides PostgreSQL implementations of the most-used Oracle built-ins:

NVL, NVL2, DECODE, INSTR, MONTHS_BETWEEN, ADD_MONTHS, LAST_DAY
DBMS_OUTPUT, DBMS_PIPE, DBMS_RANDOM, DBMS_LOB, UTL_FILE
SYSDATE semantics (returns timestamp not timestamptz)
Oracle-style date arithmetic

Install once, and converted code that uses these calls runs unmodified.

Option B: Trekport-bundled compatibility extension

For Oracle constructs orafce omits (SYS_GUID, USERENV, certain REGEXP_* flags, CONNECT_BY_ROOT path semantics), Trekport ships a companion extension installed alongside orafce.

Option C: Replace with Postgres idioms (preferred where practical)

For some packages, rewriting is better than emulating. For example:

DBMS_SCHEDULER jobs become pg_cron jobs with declarative scheduling.
DBMS_AQ queues become pg_message_queue or table-backed queues.
UTL_HTTP calls become pg_net or application-layer HTTP.

Trekport flags Oracle constructs in this category in its conversion report so the migration team can choose explicitly per-call.

6. Autonomous transactions

PRAGMA AUTONOMOUS_TRANSACTION is one of the few Oracle constructs that genuinely has no clean Postgres equivalent. The closest options:

pg_background (extension): runs SQL in a separate backend.
dblink to the same database: opens a new connection.
Refactoring the autonomous transaction out (often the cleanest path): most autonomous transactions are used for audit logging or error logging, both of which can be moved to a deferred trigger or queued via LISTEN/NOTIFY.

Trekport detects PRAGMA AUTONOMOUS_TRANSACTION, emits a translation that uses pg_background if it is available at the target, and flags the construct in the report so the team can choose to refactor.

7. The long tail

Beyond the five major categories, the Oracle PL/SQL ecosystem has a long tail of constructs that need attention. A non-exhaustive list:

MERGE translates to INSERT ... ON CONFLICT ... DO UPDATE.
Pipelined functions translate to set-returning functions with RETURN NEXT or RETURN QUERY.
Hierarchical queries (CONNECT BY) translate to recursive CTEs.
ROWID has no native Postgres equivalent. Trekport replaces ROWID with surrogate primary keys plus a sequence-backed identity, preserving application semantics.
SDO_GEOMETRY translates to PostGIS geometry (PostGIS install required at the target).
XMLTYPE translates to native PG xml type with xpath() for path navigation.

What Trekport does differently

Most of the patterns above are not unique to us. Anyone with enough Oracle and Postgres experience knows them. What is hard is doing them all correctly, every time, on a 30,000-object estate.

Trekport handles this through:

AST-based conversion instead of regex pattern matching, so we never produce structurally invalid SQL.
A 14-phase dependency-ordered deployment that handles circular package refs, materialized view chains, and trigger-on-trigger orderings that one-shot deployers cannot.
A 100 percent deployment mandate: every object is either fully equivalent PostgreSQL or backed by a verified extension. We do not ship stubs.
Validation built in: row counts, type checks, constraint checks, and function smoke tests run automatically. Sign-off is HTML or PDF, not a verbal handoff.

Try it

If you have an Oracle estate to migrate, download Trekport Studio. The free trial covers up to 10 tables, end-to-end. The full docs cover every type mapping and PL/SQL construct in the table above.

If your estate has unusual constructs (custom Oracle types, bespoke extensions, decade-old PL/SQL with PRO*C callouts), talk to us. We have probably seen it.