PL/SQL to PL/pgSQL

PL/SQL is the part of an Oracle schema that most migration plans underestimate. The schema converter handles tables, types, and sequences. The data pipeline handles rows. But PL/SQL — packages, procedures, functions, triggers, and types — encodes business logic that does not appear in the data model and cannot be discovered by reading CREATE TABLE statements.

This chapter is not a PL/SQL-to-PL/pgSQL translation manual. It is a guide to the structural differences that require architectural decisions, the patterns that translate mechanically, the patterns that do not translate at all, and how a product-grade migration handles the boundary between what can be automated and what cannot.

8.1 What PL/SQL Contains

An Oracle schema can contain:

• Packages: collections of related procedures, functions, constants, and type definitions. The most common PL/SQL structure.
• Standalone procedures and functions: callable by applications directly.
• Triggers: event-driven code that fires before/after INSERT, UPDATE, DELETE on specific tables.
• Types: user-defined object types and collection types.
• Anonymous blocks: PL/SQL executed directly (in scripts, jobs, or dynamically). Usually not stored in the schema.
• Database jobs (DBMS_SCHEDULER / DBMS_JOB): scheduled PL/SQL execution.

Each category has a different migration path and different risk profile.

8.2 Oracle Packages → PostgreSQL Schemas and Functions

Oracle packages have two parts: the package specification (the public interface) and the package body (the implementation). The package provides a namespace: pkg_accounts.get_balance(p_account_id) is distinct from pkg_payments.get_balance(p_payment_id).

PostgreSQL has no package concept. The nearest equivalents are:

Schema as namespace: Create a PostgreSQL schema named after the package and put functions in it. pkg_accounts.get_balance becomes the PostgreSQL function pkg_accounts.get_balance(p_account_id BIGINT) in schema pkg_accounts.

-- Oracle package spec
CREATE OR REPLACE PACKAGE pkg_accounts AS
  FUNCTION get_balance(p_account_id NUMBER) RETURN NUMBER;
  PROCEDURE update_status(p_account_id NUMBER, p_status VARCHAR2);
END pkg_accounts;

-- PostgreSQL equivalent (schema as namespace)
CREATE SCHEMA IF NOT EXISTS pkg_accounts;

CREATE OR REPLACE FUNCTION pkg_accounts.get_balance(p_account_id BIGINT)
RETURNS NUMERIC
LANGUAGE plpgsql
AS $$
DECLARE
  v_balance NUMERIC;
BEGIN
  SELECT balance INTO v_balance
  FROM account
  WHERE account_id = p_account_id;
  RETURN v_balance;
END;
$$;

CREATE OR REPLACE PROCEDURE pkg_accounts.update_status(
  p_account_id BIGINT,
  p_status TEXT
)
LANGUAGE plpgsql
AS $$
BEGIN
  UPDATE account SET status = p_status WHERE account_id = p_account_id;
END;
$$;

Package state (package variables): Oracle packages can declare variables in the package specification or body that persist for the lifetime of a session. These are package-level global variables:

-- Oracle package with session state
CREATE OR REPLACE PACKAGE pkg_session AS
  g_current_user_id NUMBER;
  PROCEDURE set_user(p_user_id NUMBER);
  FUNCTION  get_user RETURN NUMBER;
END pkg_session;

PostgreSQL has no equivalent session-level state within a function namespace. Options:

1. Use PostgreSQL SET LOCAL / current_setting() to store session state in configuration parameters
2. Use a session-local temporary table
3. Pass the state as explicit function parameters (the cleanest approach)

-- PostgreSQL: using SET/current_setting for session state
CREATE OR REPLACE PROCEDURE pkg_session.set_user(p_user_id BIGINT)
LANGUAGE plpgsql
AS $$
BEGIN
  PERFORM set_config('pkg_session.current_user_id', p_user_id::TEXT, false);
  -- false = not transaction-local (persists for session)
END;
$$;

CREATE OR REPLACE FUNCTION pkg_session.get_user()
RETURNS BIGINT
LANGUAGE plpgsql
AS $$
BEGIN
  RETURN current_setting('pkg_session.current_user_id', true)::BIGINT;
END;
$$;

Product note: Pulsaride Transform inventories all packages using DBA_PROCEDURES and DBA_SOURCE during assessment. Package-level variable declarations are flagged as PACKAGE_STATE findings with a note on the three resolution options. Package procedures and functions are catalogued with their signatures, call counts (from V$SQL sampling if accessible), and dependency relationships. The migration planner uses this inventory to prioritize which packages must be migrated before cutover.

8.3 Exception Handling

Oracle PL/SQL has a structured exception handling model with named exceptions, SQLCODE, SQLERRM, and user-defined exceptions:

-- Oracle exception handling
BEGIN
  INSERT INTO account (account_id, email) VALUES (v_id, v_email);
EXCEPTION
  WHEN DUP_VAL_ON_INDEX THEN
    -- duplicate key
    UPDATE account SET email = v_email WHERE account_id = v_id;
  WHEN OTHERS THEN
    v_error_code := SQLCODE;
    v_error_msg  := SQLERRM;
    INSERT INTO error_log (error_code, error_msg, created_at)
    VALUES (v_error_code, v_error_msg, SYSDATE);
    RAISE;
END;

PostgreSQL PL/pgSQL uses similar syntax with different exception names:

-- PostgreSQL exception handling
BEGIN
  INSERT INTO account (account_id, email) VALUES (v_id, v_email);
EXCEPTION
  WHEN unique_violation THEN
    -- duplicate key (PostgreSQL equivalent of DUP_VAL_ON_INDEX)
    UPDATE account SET email = v_email WHERE account_id = v_id;
  WHEN OTHERS THEN
    INSERT INTO error_log (error_code, error_msg, created_at)
    VALUES (SQLSTATE, SQLERRM, NOW());
    RAISE;
END;

Oracle exception name → PostgreSQL exception name mappings:

Oracle Exception	PostgreSQL Exception
DUP_VAL_ON_INDEX	unique_violation
NO_DATA_FOUND	no_data_found
TOO_MANY_ROWS	too_many_rows
VALUE_ERROR	invalid_text_representation or numeric_value_out_of_range
ZERO_DIVIDE	division_by_zero
CURSOR_ALREADY_OPEN	(not applicable — PostgreSQL cursors work differently)
INVALID_CURSOR	(not applicable)
LOGIN_DENIED	invalid_password

SQLCODE in Oracle returns a negative ORA- number or +100 for NO_DATA_FOUND. In PostgreSQL, use SQLSTATE which returns a 5-character code per the SQL standard. Oracle PL/SQL code that branches on specific SQLCODE values (e.g., WHEN SQLCODE = -2291) must be rewritten to use PostgreSQL's SQLSTATE or named exception constants.

8.4 Cursor Patterns

Oracle PL/SQL uses explicit cursors frequently. The three common patterns and their PostgreSQL equivalents:

Pattern 1: Implicit Cursor (SELECT INTO)

-- Oracle
SELECT balance INTO v_balance
FROM account
WHERE account_id = p_id;
-- Raises NO_DATA_FOUND if no row, TOO_MANY_ROWS if more than one

-- PostgreSQL PL/pgSQL — identical syntax
SELECT balance INTO v_balance
FROM account
WHERE account_id = p_id;
-- FOUND is set to false if no row; does not automatically raise
-- To match Oracle behavior:
IF NOT FOUND THEN
  RAISE EXCEPTION 'No data found for account_id %', p_id;
END IF;

The behavior difference: Oracle raises NO_DATA_FOUND automatically on a failed SELECT INTO. PostgreSQL sets the FOUND variable to false and continues execution. Code that relies on Oracle's automatic exception raise must be updated to check FOUND explicitly.

Pattern 2: Explicit Cursor FOR Loop

-- Oracle: cursor FOR loop
FOR rec IN (SELECT account_id, balance FROM account WHERE status = 'ACTIVE') LOOP
  -- process rec.account_id, rec.balance
  UPDATE ledger SET current_balance = rec.balance WHERE account_id = rec.account_id;
END LOOP;

-- PostgreSQL PL/pgSQL: identical syntax
FOR rec IN (SELECT account_id, balance FROM account WHERE status = 'ACTIVE') LOOP
  UPDATE ledger SET current_balance = rec.balance WHERE account_id = rec.account_id;
END LOOP;

Cursor FOR loops translate directly. This is one of the areas where Oracle-to-PostgreSQL PL/SQL migration is genuinely easy.

Pattern 3: REF CURSOR

Oracle REF CURSOR allows returning a result set from a function or procedure. PostgreSQL uses RETURNS TABLE(...) or RETURNS REFCURSOR for similar purposes.

-- Oracle procedure returning REF CURSOR
CREATE OR REPLACE PROCEDURE get_active_accounts(
  p_cursor OUT SYS_REFCURSOR
) AS
BEGIN
  OPEN p_cursor FOR
    SELECT account_id, user_name, balance FROM account WHERE status = 'ACTIVE';
END;

-- PostgreSQL equivalent using RETURNS TABLE
CREATE OR REPLACE FUNCTION get_active_accounts()
RETURNS TABLE (account_id BIGINT, user_name TEXT, balance NUMERIC)
LANGUAGE plpgsql
AS $$
BEGIN
  RETURN QUERY
  SELECT a.account_id, a.user_name, a.balance
  FROM account a
  WHERE a.status = 'ACTIVE';
END;
$$;

-- Called as:
SELECT * FROM get_active_accounts();

Application code calling the Oracle procedure via JDBC using CallableStatement with registerOutParameter(Types.REF_CURSOR) must be updated to call the PostgreSQL function using a regular SELECT.

8.5 BULK COLLECT and FORALL

BULK COLLECT and FORALL are Oracle PL/SQL extensions for high-performance bulk DML. They operate on collections (arrays) to reduce round-trips between PL/SQL and the SQL engine.

-- Oracle: BULK COLLECT + FORALL
DECLARE
  TYPE account_id_list IS TABLE OF NUMBER;
  TYPE balance_list    IS TABLE OF NUMBER;
  v_ids      account_id_list;
  v_balances balance_list;
BEGIN
  SELECT account_id, balance
  BULK COLLECT INTO v_ids, v_balances
  FROM account
  WHERE status = 'ACTIVE';

  FORALL i IN 1..v_ids.COUNT
    UPDATE ledger
    SET current_balance = v_balances(i)
    WHERE account_id = v_ids(i);
END;

PostgreSQL PL/pgSQL has no BULK COLLECT or FORALL. The PostgreSQL equivalent depends on what the code is doing:

Option 1: Use a single SQL statement

Most BULK COLLECT + FORALL patterns are doing something that can be expressed as a single SQL DML statement. The above Oracle example is equivalent to:

-- PostgreSQL: single UPDATE...FROM (eliminates PL/pgSQL entirely)
UPDATE ledger l
SET current_balance = a.balance
FROM account a
WHERE l.account_id = a.account_id
AND a.status = 'ACTIVE';

This is the preferred resolution: eliminate the procedural bulk pattern in favor of set-oriented SQL.

Option 2: Use arrays in PL/pgSQL

When the FORALL logic is more complex (conditional processing, error logging per row), use PostgreSQL arrays:

-- PostgreSQL: array-based bulk processing
DECLARE
  v_ids      BIGINT[];
  v_balances NUMERIC[];
BEGIN
  SELECT ARRAY_AGG(account_id), ARRAY_AGG(balance)
  INTO v_ids, v_balances
  FROM account
  WHERE status = 'ACTIVE';

  -- Process using unnest
  UPDATE ledger l
  SET current_balance = vals.balance
  FROM (SELECT UNNEST(v_ids) AS account_id, UNNEST(v_balances) AS balance) vals
  WHERE l.account_id = vals.account_id;
END;

Product note: Pulsaride Transform flags all BULK COLLECT and FORALL occurrences in DBA_SOURCE with a BULK_COLLECT_FORALL finding. For each occurrence, the assessment report includes:

• The procedure/function name and line numbers
• A classification: SET_OPERATION (can be rewritten as SQL) or CONDITIONAL_BULK (requires array or loop pattern)
• A suggested PostgreSQL equivalent for SET_OPERATION cases

8.6 Autonomous Transactions

Oracle autonomous transactions (PRAGMA AUTONOMOUS_TRANSACTION) allow a procedure or function to commit independently of the calling transaction. They are commonly used for audit logging: log the attempt even if the main transaction rolls back.

-- Oracle: autonomous transaction for audit
CREATE OR REPLACE PROCEDURE log_audit_event(
  p_table_name  VARCHAR2,
  p_action      VARCHAR2,
  p_user_id     NUMBER
) AS
  PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
  INSERT INTO audit_log (table_name, action, user_id, logged_at)
  VALUES (p_table_name, p_action, p_user_id, SYSDATE);
  COMMIT;  -- commits only this autonomous transaction
END;

PostgreSQL has no PRAGMA AUTONOMOUS_TRANSACTION. This is a structural difference with no direct equivalent.

The PostgreSQL alternatives:

Option 1: dblink to a separate connection

The dblink extension allows opening a second connection to the same (or another) database and executing SQL in a separate transaction:

-- PostgreSQL: autonomous transaction via dblink
CREATE OR REPLACE PROCEDURE log_audit_event(
  p_table_name TEXT,
  p_action     TEXT,
  p_user_id    BIGINT
)
LANGUAGE plpgsql
AS $$
BEGIN
  PERFORM dblink_exec(
    'dbname=' || current_database(),
    format(
      'INSERT INTO audit_log (table_name, action, user_id, logged_at)
       VALUES (%L, %L, %L, NOW())',
      p_table_name, p_action, p_user_id
    )
  );
END;
$$;

This is transactionally independent but has overhead: it opens a new connection for each call. Acceptable for low-frequency audit logging; not acceptable for high-frequency operations.

Option 2: Redesign to use a separate commit path

The cleanest long-term approach: move audit logging out of the transaction boundary entirely. Use an application-level audit log that writes to a separate table (or message queue) outside the main transaction, or use PostgreSQL's LISTEN/NOTIFY to asynchronously process audit events.

Product note: PRAGMA AUTONOMOUS_TRANSACTION is classified as a HIGH_RISK_MIGRATION finding. It requires a design decision, not a mechanical translation. Pulsaride Transform surfaces all occurrences with context (which operation they log, how frequently they are called) and provides both the dblink option and the redesign option with trade-offs. Autonomous transaction procedures must be reviewed by an architect before migration proceeds.

8.7 Triggers

Oracle triggers are event-driven PL/SQL that fires before or after DML on a table. PostgreSQL supports triggers with very similar semantics.

-- Oracle: BEFORE INSERT trigger
CREATE OR REPLACE TRIGGER account_bi
  BEFORE INSERT ON account
  FOR EACH ROW
BEGIN
  :NEW.created_at := SYSDATE;
  :NEW.created_by := SYS_CONTEXT('USERENV', 'SESSION_USER');
  IF :NEW.account_id IS NULL THEN
    SELECT account_seq.NEXTVAL INTO :NEW.account_id FROM DUAL;
  END IF;
END;

-- PostgreSQL equivalent
CREATE OR REPLACE FUNCTION account_bi_fn()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
BEGIN
  NEW.created_at := NOW();
  NEW.created_by := current_user;
  IF NEW.account_id IS NULL THEN
    NEW.account_id := nextval('account_seq');
  END IF;
  RETURN NEW;
END;
$$;

CREATE TRIGGER account_bi
  BEFORE INSERT ON account
  FOR EACH ROW
  EXECUTE FUNCTION account_bi_fn();

The structural difference: PostgreSQL separates the trigger function (a standalone function returning TRIGGER) from the trigger binding (which attaches the function to a table event). Oracle combines them.

Trigger Ordering

Oracle fires triggers in an undefined order when multiple triggers exist on the same table/event. PostgreSQL fires triggers in alphabetical order by trigger name. If Oracle trigger behavior depends on execution order (sharing state through package variables), this is a problem.

Product note: Pulsaride Transform scans for multiple triggers on the same table/event combination and emits a TRIGGER_ORDER_DEPENDENCY warning if they exist. Application behavior that depends on Oracle's undefined (but practically stable) trigger ordering must be explicitly reviewed.

Statement-Level vs Row-Level Triggers

Oracle supports both FOR EACH ROW (row-level) and statement-level triggers. PostgreSQL supports the same. The Oracle WHEN clause (trigger firing condition) translates to PostgreSQL's WHEN clause in the trigger definition:

-- Oracle
CREATE OR REPLACE TRIGGER account_status_au
  AFTER UPDATE OF status ON account
  FOR EACH ROW
  WHEN (OLD.status <> NEW.status)
BEGIN ...

-- PostgreSQL
CREATE TRIGGER account_status_au
  AFTER UPDATE OF status ON account
  FOR EACH ROW
  WHEN (OLD.status <> NEW.status)
  EXECUTE FUNCTION account_status_au_fn();

8.8 Database Jobs (Scheduled Procedures)

Oracle's DBMS_SCHEDULER and the older DBMS_JOB run PL/SQL procedures on a schedule. PostgreSQL has no built-in scheduler equivalent. Options:

1. pg_cron extension: A PostgreSQL extension that provides cron-like scheduling within the database. Requires installation and superuser setup.

2. External scheduler: Move the scheduling to the operating system (cron), Kubernetes CronJob, or an application scheduler (Quartz, Spring Batch schedules). Call the PostgreSQL function via a shell script or application process.

3. pgAgent: A PostgreSQL job scheduler that runs as a separate agent process.

Product note: Pulsaride Transform inventories all DBMS_SCHEDULER and DBMS_JOB entries during assessment. Each job is documented with: schedule expression, procedure called, last run time, enabled/disabled state, and failure history (from DBA_SCHEDULER_JOB_RUN_DETAILS). The migration team decides on a scheduling infrastructure before cutover. The assessment report includes the pg_cron equivalent expression for each DBMS_SCHEDULER schedule.

8.9 Example: The pkg_billing Migration

A billing system has a package pkg_billing with four procedures: calculate_invoice, apply_payment, generate_statement, and reverse_payment. The package body is 1,200 lines.

Assessment findings:

Finding	Location	Priority
PACKAGE_STATE	g_current_period DATE (package variable)	High
BULK_COLLECT_FORALL	calculate_invoice, lines 88–112	Medium (SET_OPERATION)
AUTONOMOUS_TRANSACTION	apply_payment, line 201	High
DUP_VAL_ON_INDEX	apply_payment, line 208	Low (rename)
BULK_COLLECT_FORALL	generate_statement, lines 445–478	Medium (CONDITIONAL_BULK)

Migration plan for pkg_billing:

1. g_current_period: Refactor to pass billing period as an explicit parameter to all four procedures. Eliminates package state.

2. BULK COLLECT in calculate_invoice (lines 88–112): Rewrite as a single UPDATE ... FROM (SELECT ...) statement. This is the SET_OPERATION pattern — the entire loop is a join update.

3. Autonomous transaction in apply_payment: Route payment application events to a Kafka topic consumed by an audit service. The audit service commits independently. This is the redesign option, chosen because apply_payment is called 15,000 times per day — too frequent for dblink overhead.

4. DUP_VAL_ON_INDEX: Rename to unique_violation in PostgreSQL.

5. BULK COLLECT in generate_statement (lines 445–478): Rewrite using PostgreSQL arrays and UNNEST. The loop has conditional logic (different statement line formats based on transaction type) that prevents a pure SQL rewrite.

After migration, the pkg_billing schema in PostgreSQL contains four functions with an average of 180 lines each (down from 300 lines each in Oracle, because the BULK COLLECT pattern expands to many lines in Oracle but collapses to SQL in PostgreSQL). Total PL/pgSQL for this package: ~720 lines, migrated from 1,200 Oracle lines.