Migrating Oracle Package Bodies with ora2pg: What Requires Manual Rewrite

What ora2pg Does With Package Bodies

ora2pg's PACKAGE export type extracts Oracle package specifications and bodies and converts them to PostgreSQL. The conversion strategy is to flatten the package — each procedure and function in the package becomes a standalone PL/pgSQL function prefixed with the package name. A package named ORDER_MGR with procedures process_order and cancel_order becomes two standalone functions: order_mgr__process_order and order_mgr__cancel_order.

This flattening is mechanically correct for simple packages. It breaks for anything that depends on the package being a shared compilation unit: package-level state, private subprograms visible only within the package, and initialization blocks that run once at first package use.

The ora2pg output is also incomplete rather than wrong for several construct types — it inserts placeholder comments like -- TODO: REF CURSOR or produces syntactically valid but semantically incorrect PL/pgSQL that compiles without error but returns wrong results. The latter is more dangerous than an outright compilation error because it passes initial deployment checks.

Category 1: Package-Level Variables

Oracle package-level variables are declared in the package body (or spec, if public) and maintain state for the duration of a session. They are reset when the package is invalidated or the session ends. This is a first-class Oracle feature used for session-scoped configuration, audit context, and cross-procedure state sharing.

-- Oracle package with session-level state
CREATE OR REPLACE PACKAGE BODY session_ctx AS
  g_user_id    NUMBER;
  g_tenant_id  NUMBER;
  g_debug_mode BOOLEAN := FALSE;

  PROCEDURE set_context(p_user NUMBER, p_tenant NUMBER) IS
  BEGIN
    g_user_id   := p_user;
    g_tenant_id := p_tenant;
  END;

  FUNCTION get_user_id RETURN NUMBER IS
  BEGIN
    RETURN g_user_id;
  END;
END;

ora2pg has no mechanism to represent this in PostgreSQL. It typically drops the variable declarations and produces standalone functions that reference undefined variables — which fail to compile, making this a visible rather than silent failure.

The correct PostgreSQL approach depends on the variable's purpose:

-- Option A: PostgreSQL custom GUCs for simple scalar configuration
-- Requires adding to postgresql.conf or using ALTER DATABASE SET
-- Declare in postgresql.conf:
-- session_ctx.user_id = 0
-- session_ctx.tenant_id = 0

-- Set at session start (application code or connection initialization):
SELECT set_config('session_ctx.user_id', '42', false);    -- false = session-scoped
SELECT set_config('session_ctx.tenant_id', '101', false);

-- Read in any function:
CREATE OR REPLACE FUNCTION session_ctx__get_user_id()
RETURNS INTEGER LANGUAGE plpgsql AS $$
BEGIN
  RETURN current_setting('session_ctx.user_id', true)::INTEGER;
EXCEPTION
  WHEN OTHERS THEN RETURN NULL;  -- not set
END;
$$;

-- Option B: Temporary table for complex session state
CREATE TEMP TABLE IF NOT EXISTS session_state (
  key   TEXT PRIMARY KEY,
  value TEXT
) ON COMMIT PRESERVE ROWS;

CREATE OR REPLACE FUNCTION session_ctx__set_context(
  p_user_id   INTEGER,
  p_tenant_id INTEGER
) RETURNS VOID LANGUAGE plpgsql AS $$
BEGIN
  INSERT INTO session_state (key, value) VALUES ('user_id', p_user_id::TEXT)
    ON CONFLICT (key) DO UPDATE SET value = EXCLUDED.value;
  INSERT INTO session_state (key, value) VALUES ('tenant_id', p_tenant_id::TEXT)
    ON CONFLICT (key) DO UPDATE SET value = EXCLUDED.value;
END;
$$;

The custom GUC approach (set_config / current_setting) is cleaner and doesn't require DDL per session. The temporary table approach supports complex types. For boolean flags like g_debug_mode, the GUC approach is almost always the right choice.

Category 2: REF CURSOR Return Types

Oracle procedures that return REF CURSORs — particularly SYS_REFCURSOR — are a standard pattern for returning result sets from stored procedures to application code via JDBC or ODP.NET. ora2pg produces a comment stub for these and does not attempt a conversion.

-- Oracle: procedure returning a REF CURSOR
CREATE OR REPLACE PROCEDURE get_orders(
  p_customer_id IN  NUMBER,
  p_cursor      OUT SYS_REFCURSOR
) AS
BEGIN
  OPEN p_cursor FOR
    SELECT order_id, order_date, total_amount
    FROM orders
    WHERE customer_id = p_customer_id
      AND status = 'ACTIVE'
    ORDER BY order_date DESC;
END;

PostgreSQL's equivalent uses RETURNS SETOF or RETURNS TABLE for typed result sets, or RETURNS refcursor for the cursor-based pattern that applications expecting a cursor handle can consume:

-- Option A: RETURNS TABLE (preferred — simpler application code)
CREATE OR REPLACE FUNCTION get_orders(
  p_customer_id INTEGER
)
RETURNS TABLE (
  order_id     BIGINT,
  order_date   TIMESTAMP,
  total_amount NUMERIC(18,4)
)
LANGUAGE plpgsql AS $$
BEGIN
  RETURN QUERY
    SELECT o.order_id, o.order_date, o.total_amount
    FROM orders o
    WHERE o.customer_id = p_customer_id
      AND o.status = 'ACTIVE'
    ORDER BY o.order_date DESC;
END;
$$;

-- Application calls it like a table:
SELECT * FROM get_orders(42);

-- Option B: RETURNS refcursor (for applications expecting cursor semantics)
CREATE OR REPLACE FUNCTION get_orders_cursor(
  p_customer_id INTEGER
)
RETURNS refcursor
LANGUAGE plpgsql AS $$
DECLARE
  v_cursor refcursor := 'orders_cursor';
BEGIN
  OPEN v_cursor FOR
    SELECT order_id, order_date, total_amount
    FROM orders
    WHERE customer_id = p_customer_id
      AND status = 'ACTIVE'
    ORDER BY order_date DESC;
  RETURN v_cursor;
END;
$$;

-- Application must be in a transaction to use refcursor:
BEGIN;
SELECT get_orders_cursor(42);  -- returns cursor name
FETCH ALL FROM orders_cursor;
COMMIT;

The RETURNS TABLE approach is generally preferred for new code because it eliminates the explicit cursor management requirement in the application layer. However, if the application uses JDBC's CallableStatement.registerOutParameter(Types.REF_CURSOR) pattern, the RETURNS refcursor approach requires fewer application changes.

Category 3: PRAGMA AUTONOMOUS_TRANSACTION

Oracle's PRAGMA AUTONOMOUS_TRANSACTION allows a procedure to commit its own transaction independently of the calling transaction. This is used extensively for audit logging — the audit record must be committed even if the main transaction rolls back. It's also used for error logging procedures that record failures without rolling back the caller's work.

-- Oracle: autonomous transaction for audit logging
CREATE OR REPLACE PROCEDURE log_audit_event(
  p_table_name  VARCHAR2,
  p_operation   VARCHAR2,
  p_row_id      NUMBER
) AS
  PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
  INSERT INTO audit_log (table_name, operation, row_id, log_time)
  VALUES (p_table_name, p_operation, p_row_id, SYSDATE);
  COMMIT;  -- commits only this procedure's work, not the caller's transaction
END;

PostgreSQL has no direct equivalent to PRAGMA AUTONOMOUS_TRANSACTION. The closest approximations:

-- Option A: dblink to loopback connection (commits independently)
-- Requires dblink extension
CREATE EXTENSION IF NOT EXISTS dblink;

CREATE OR REPLACE FUNCTION log_audit_event(
  p_table_name  TEXT,
  p_operation   TEXT,
  p_row_id      BIGINT
) RETURNS VOID LANGUAGE plpgsql AS $$
BEGIN
  PERFORM dblink_exec(
    'dbname=' || current_database() || ' host=localhost',
    format(
      'INSERT INTO audit_log (table_name, operation, row_id, log_time) VALUES (%L, %L, %s, NOW())',
      p_table_name, p_operation, p_row_id
    )
  );
END;
$$;

-- Option B: pg_background (extension) — true background transaction
-- Requires pg_background extension (not available on RDS/Aurora)

-- Option C: Deferred logging via NOTIFY + application-layer listener
-- The calling transaction fires NOTIFY with the audit payload;
-- an application listener receives it and persists it in a separate connection.
-- This is the most architecturally correct approach for Aurora/RDS.

-- Option D: Redesign — write audit records to a staging table
-- and flush them via a periodic background job that commits independently

On Aurora PostgreSQL and RDS PostgreSQL, Option A (dblink loopback) is the most practical in-database option, though it adds connection overhead per call. For high-volume audit logging, Option C (NOTIFY-based async logging) or Option D (staging table with flush job) avoids per-call connection cost.

Category 4: DBMS_OUTPUT and DBMS_PIPE

Oracle packages that use DBMS_OUTPUT.PUT_LINE for debug output produce ora2pg output that either strips the calls silently or leaves them as unresolvable references. PostgreSQL uses RAISE NOTICE for the equivalent debug output:

-- Oracle
DBMS_OUTPUT.PUT_LINE('Processing order: ' || v_order_id);

-- PostgreSQL
RAISE NOTICE 'Processing order: %', v_order_id;

-- For conditional debug output (replacing a package-level g_debug flag):
IF current_setting('myapp.debug_mode', true) = 'true' THEN
  RAISE NOTICE 'Processing order: %', v_order_id;
END IF;

DBMS_PIPE (inter-session communication) has no PostgreSQL equivalent. Applications that use DBMS_PIPE for coordination between database sessions need to be redesigned to use application-layer messaging (Redis, SQS, or PostgreSQL's own LISTEN/NOTIFY mechanism).

Measuring the Manual Rewrite Scope Before Starting

Before committing to a migration timeline, quantify the manual rewrite categories in your package corpus:

-- On Oracle source: count packages containing each problematic construct
SELECT
  owner,
  name AS package_name,
  CASE WHEN text LIKE '%PRAGMA AUTONOMOUS_TRANSACTION%' THEN 'Y' ELSE 'N' END AS has_autonomous_txn,
  CASE WHEN text LIKE '%SYS_REFCURSOR%' OR text LIKE '%REF CURSOR%' THEN 'Y' ELSE 'N' END AS has_ref_cursor,
  CASE WHEN text LIKE '%DBMS_OUTPUT%' THEN 'Y' ELSE 'N' END AS has_dbms_output,
  CASE WHEN text LIKE '%DBMS_SCHEDULER%' OR text LIKE '%DBMS_JOB%' THEN 'Y' ELSE 'N' END AS has_job_scheduling
FROM (
  SELECT owner, name, LISTAGG(text, ' ') WITHIN GROUP (ORDER BY line) AS text
  FROM dba_source
  WHERE type = 'PACKAGE BODY'
    AND owner = 'YOUR_SCHEMA'
  GROUP BY owner, name
) pkg_text;

This query gives you a per-package flag for each manual rewrite category. Packages with multiple flags are the highest-effort objects. Weight them in your project plan before estimating the migration timeline — a package with both REF CURSORs and autonomous transactions typically requires 2–3x the effort of a package with only straightforward procedural logic.