Oracle Packages in PostgreSQL: How We Solve the Biggest Challenge

The Hardest Construct in Any Oracle Migration

Of the entire PL/SQL surface, packages are the one piece that doesn't map directly. Oracle packages bundle related functions, procedures, types, constants, cursors, and exceptions into a single named namespace — with package-level state that persists for the lifetime of a session, an optional initialisation block, and a clear public-vs-private boundary.

PostgreSQL has none of this directly. There's no CREATE PACKAGE. Functions live in schemas. State doesn't persist between calls unless you explicitly stash it somewhere. There's no public-vs-private distinction beyond schema-level GRANT.

So how do real migrations handle this? Not by faking packages. By understanding what each part of a package was actually doing in your codebase — and translating each pattern into the PostgreSQL idiom that does the same job. This article walks through the seven patterns that cover 99% of enterprise Oracle packages, with concrete before/after code for each.

The Feature-Mapping Overview

Pattern 1 — Package as Schema

The simplest part. Each Oracle package becomes a PostgreSQL schema with the same name. Functions that lived inside the package become functions inside the schema. Callers use schema.function()instead of package.function():

-- Oracle
CREATE OR REPLACE PACKAGE pkg_sales AS
  FUNCTION top_n_customers(p_year NUMBER, p_n NUMBER) RETURN SYS_REFCURSOR;
  PROCEDURE monthly_close(p_year NUMBER, p_month NUMBER);
END pkg_sales;
/

-- Caller side
v_cur := pkg_sales.top_n_customers(2024, 10);
pkg_sales.monthly_close(2024, 12);

-- PostgreSQL — equivalent shape, function calls match closely
CREATE SCHEMA pkg_sales;

CREATE FUNCTION pkg_sales.top_n_customers(p_year INTEGER, p_n INTEGER)
  RETURNS refcursor AS $$ ... $$ LANGUAGE plpgsql;

CREATE PROCEDURE pkg_sales.monthly_close(p_year INTEGER, p_month INTEGER)
  LANGUAGE plpgsql AS $$ ... $$;

-- Caller side
v_cur := pkg_sales.top_n_customers(2024, 10);
CALL pkg_sales.monthly_close(2024, 12);

Most transpilers handle this entirely automatically. The transformation is mechanical: rename the wrapper, keep the function bodies. The hard part is what comes next.

Pattern 2 — Package-Level State

Oracle packages can declare variables at the package level that persist for the entire session. This is the single biggest behavioural difference vs PostgreSQL:

-- Oracle — g_user_context lives for the whole session
CREATE OR REPLACE PACKAGE pkg_audit AS
  g_user_context VARCHAR2(100);
  PROCEDURE set_user(p_user VARCHAR2);
  FUNCTION get_user RETURN VARCHAR2;
END pkg_audit;
/

CREATE OR REPLACE PACKAGE BODY pkg_audit AS
  PROCEDURE set_user(p_user VARCHAR2) IS
  BEGIN
    g_user_context := p_user;
  END;

  FUNCTION get_user RETURN VARCHAR2 IS
  BEGIN
    RETURN g_user_context;
  END;
END pkg_audit;
/

Three PostgreSQL idioms for the same effect, in order of preference:

• GUC variables (best for simple session-state) — SET pkg_audit.user_context = 'alice'; then read with current_setting('pkg_audit.user_context'). Persists for the session. Survives reconnect inside a connection pool only if you re-set on checkout.
• Session-local temp table (best for structured / multi-row state) — CREATE TEMP TABLE pkg_audit_state(...) ON COMMIT PRESERVE ROWS;. Auto-drops at end of session.
• Application-level (best for stateless functions) — pass the context as a function parameter every time. Removes the implicit-state dependency entirely. This is the most PG-native answer when refactoring is on the table.

-- PostgreSQL — GUC variable approach
CREATE SCHEMA pkg_audit;

CREATE FUNCTION pkg_audit.set_user(p_user TEXT) RETURNS VOID
  LANGUAGE plpgsql AS $$
BEGIN
  PERFORM set_config('pkg_audit.user_context', p_user, false);
END $$;

CREATE FUNCTION pkg_audit.get_user() RETURNS TEXT
  LANGUAGE sql STABLE AS $$
  SELECT current_setting('pkg_audit.user_context', true);
$$;

-- First time you reference a custom GUC namespace per session, register it:
-- ALTER DATABASE mydb SET pkg_audit.user_context = '';

The transpiler can choose any of the three, but the default is GUC variables when the package state is scalar (1-2 variables) and a temp table when it's structured. A migration tool should also flag every package-state variable in its risk report so the team can review whether refactoring to stateless parameters is the right call.

Pattern 3 — Initialization Block

Oracle packages can have an unnamed BEGIN ... END block at the end of the body that runs once per session, on first reference. Common use: priming a cache, opening a connection, validating config:

-- Oracle — runs once per session on first call
CREATE OR REPLACE PACKAGE BODY pkg_config AS
  g_max_retries NUMBER;

  PROCEDURE refresh_config IS
  BEGIN
    SELECT max_retries INTO g_max_retries FROM config_table WHERE id = 1;
  END;

  -- Initialization block — runs once per session
BEGIN
  refresh_config;
END pkg_config;
/

PostgreSQL has no equivalent. The replacement pattern is an idempotent init function that callers invoke on first need — typically wrapped inside the first "public" function call so the caller doesn't have to think about it:

-- PostgreSQL — explicit, idempotent
CREATE FUNCTION pkg_config._ensure_initialized() RETURNS VOID
  LANGUAGE plpgsql AS $$
BEGIN
  -- Cheap check: skip if already cached in this session
  IF current_setting('pkg_config.initialized', true) = 'true' THEN
    RETURN;
  END IF;
  PERFORM pkg_config.refresh_config();
  PERFORM set_config('pkg_config.initialized', 'true', false);
END $$;

CREATE FUNCTION pkg_config.get_max_retries() RETURNS INTEGER
  LANGUAGE plpgsql STABLE AS $$
BEGIN
  PERFORM pkg_config._ensure_initialized();
  RETURN current_setting('pkg_config.max_retries')::INTEGER;
END $$;

Pattern 4 — Private vs Public Functions

Oracle distinguishes public (declared in spec) and private (defined only in body) functions. PostgreSQL has no such distinction at the language level — every function in a schema is callable if you have USAGE on the schema and EXECUTE on the function.

The convention that works in production: prefix private functions with an underscore (_helper_fn) and revoke EXECUTE from PUBLIC on them. This gives you both the visual signal ("don't call this directly") and the actual access control:

-- PostgreSQL — private helper convention
CREATE FUNCTION pkg_sales._compute_tax(p_amount NUMERIC) RETURNS NUMERIC
  LANGUAGE sql IMMUTABLE AS $$
  SELECT p_amount * 0.08;
$$;

REVOKE EXECUTE ON FUNCTION pkg_sales._compute_tax(NUMERIC) FROM PUBLIC;
GRANT EXECUTE ON FUNCTION pkg_sales._compute_tax(NUMERIC) TO pkg_sales_owner;

-- Callers within the schema can use it; external callers get permission denied.

Pattern 5 — Autonomous Transactions

Oracle's PRAGMA AUTONOMOUS_TRANSACTION lets a procedure commit or rollback independently of the caller's transaction. Common use: writing audit log rows that must persist even when the outer transaction rolls back.

PostgreSQL has no direct equivalent. The proven pattern is a dblink loopback — open a new connection to the same database, run the audit insert there, close. Slightly more boilerplate, same semantics:

-- Oracle — autonomous audit logger
CREATE OR REPLACE PROCEDURE pkg_audit.log_action(p_action VARCHAR2) IS
  PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
  INSERT INTO audit_log(ts, action) VALUES (SYSTIMESTAMP, p_action);
  COMMIT;
END;
/

-- PostgreSQL — dblink loopback (CREATE EXTENSION dblink first)
CREATE FUNCTION pkg_audit.log_action(p_action TEXT) RETURNS VOID
  LANGUAGE plpgsql AS $$
BEGIN
  PERFORM dblink_exec(
    'host=localhost dbname=' || current_database() || ' user=audit_writer',
    format('INSERT INTO audit_log(ts, action) VALUES (now(), %L)', p_action)
  );
END $$;

The dblink loopback is genuinely a new connection — auth needs to work, and you may want a dedicated low-privilege user (audit_writer) to limit what an autonomous block can do. Worth designing upfront, not on the day of migration.

Pattern 6 — Cursor Variables Across the Boundary

Oracle packages often expose SYS_REFCURSOR return types so callers can iterate over result sets. PostgreSQL's equivalent is the refcursor type — same concept, different declaration syntax:

-- Oracle
CREATE OR REPLACE PACKAGE pkg_reports AS
  FUNCTION top_customers(p_year NUMBER) RETURN SYS_REFCURSOR;
END pkg_reports;
/

-- PostgreSQL — refcursor with explicit OPEN ... FOR
CREATE FUNCTION pkg_reports.top_customers(p_year INTEGER)
  RETURNS refcursor LANGUAGE plpgsql AS $$
DECLARE
  ref refcursor;
BEGIN
  OPEN ref FOR
    SELECT customer_id, SUM(amount) AS total
    FROM sales WHERE EXTRACT(YEAR FROM sale_date) = p_year
    GROUP BY customer_id ORDER BY total DESC LIMIT 10;
  RETURN ref;
END $$;

-- Caller — same pattern as Oracle: get the cursor, FETCH from it
BEGIN;
  SELECT pkg_reports.top_customers(2024);
  FETCH ALL FROM "<unnamed portal 1>";
COMMIT;

Pattern 7 — Package Constants

Oracle packages can declare CONSTANT values in the spec. PostgreSQL has no language-level constants inside schemas. The clean replacement is an IMMUTABLE function that returns the value — same semantics for the caller, optimiser inlines it where possible:

-- Oracle
CREATE OR REPLACE PACKAGE pkg_tax AS
  c_default_rate CONSTANT NUMBER := 0.08;
END pkg_tax;
/

-- PostgreSQL — IMMUTABLE function
CREATE FUNCTION pkg_tax.default_rate() RETURNS NUMERIC
  LANGUAGE sql IMMUTABLE AS $$ SELECT 0.08::NUMERIC $$;

What Not To Do — Don't Fake Packages

Several Oracle-to-PostgreSQL projects have attempted to simulate packages — wrapping every function call in a thin emulation layer that mimics package semantics. This sounds appealing (zero caller-side code changes) but creates a long tail of subtle bugs:

• Performance overhead from the emulation layer compounds on hot paths
• Debugging is hard — stack traces point at the emulation, not your code
• The emulation layer becomes another thing to maintain forever
• The minor caller-side change (pkg.fn() → pkg.fn() — same syntax!) is rarely the real obstacle anyway

The proven pattern is the one in this article: translate each Oracle package construct to its idiomatic PostgreSQL equivalent. The result reads cleanly, performs predictably, and doesn't leave a layer of magic for future engineers to debug.

The Honest Verdict

Packages are the hardest construct to migrate, but they're not impossible — they're seven well-understood patterns dressed up in a single keyword. The mental model that works: a package is a namespace plus optional session-state. PostgreSQL has both pieces; you just spell them differently.

A modern migration tool catches every package in your schema, classifies each construct by the seven patterns above, and produces the PostgreSQL equivalent with the right state-management choice flagged for human review. The 95% that's mechanical is mechanical. The 5% that needs a design decision is surfaced clearly, not hidden behind emulation.