Engineering

PL/SQL to PL/pgSQL: What Changes and What Stays the Same

Rakesh Mamidala·Founder & Lead Engineer·April 10, 2026·14 min read

Why the Conversion Is Easier Than You Think

PL/SQL and PL/pgSQL share the same roots — both descend from Ada-influenced block-structured languages. The control flow (IF/THEN/ELSIF, LOOP, WHILE, FOR) is nearly identical. The main friction points are function names, package semantics, and a handful of Oracle-specific constructs. This guide covers all of them.

The Package Problem

PostgreSQL has no concept of packages. DBMigrateAIPro handles this by flattening each package into individual functions using a pkg__procname() naming convention. Package constants become IMMUTABLE functions. Package-level variables (state that persists across calls) are moved to a dedicated configuration table.

sql

-- Oracle package spec
CREATE OR REPLACE PACKAGE hr_utils AS
  g_company_id NUMBER := 1;
  FUNCTION get_salary(p_emp_id NUMBER) RETURN NUMBER;
END;

-- PostgreSQL equivalent (flattened)
CREATE OR REPLACE FUNCTION hr_utils__get_salary(p_emp_id INTEGER)
RETURNS NUMERIC AS $$
  SELECT salary FROM employees WHERE id = p_emp_id;
$$ LANGUAGE sql STABLE;

Cursors and BULK COLLECT

Explicit cursors translate almost directly. BULK COLLECT INTO becomes a SELECT INTO array or a FOR loop. FORALL bulk DML becomes UNNEST-based batch operations.

sql

-- Oracle BULK COLLECT
DECLARE
  TYPE id_tab IS TABLE OF NUMBER;
  l_ids id_tab;
BEGIN
  SELECT id BULK COLLECT INTO l_ids FROM employees WHERE dept_id = 10;
  FORALL i IN l_ids.FIRST..l_ids.LAST
    UPDATE salaries SET amount = amount * 1.1 WHERE emp_id = l_ids(i);
END;

-- PostgreSQL equivalent
DO $$
DECLARE
  l_ids INTEGER[];
BEGIN
  SELECT ARRAY_AGG(id) INTO l_ids FROM employees WHERE dept_id = 10;
  UPDATE salaries SET amount = amount * 1.1
  WHERE emp_id = ANY(l_ids);
END;
$$;

Exception Handlers

Oracle exception names map to PostgreSQL SQLSTATE codes. NO_DATA_FOUND becomes WHEN NO_DATA_FOUND (same syntax, same semantics). TOO_MANY_ROWS maps to SQLSTATE 'P0003'. User-defined exceptions use RAISE EXCEPTION in PostgreSQL.

Function & Operator Quick Reference

Oracle	PostgreSQL	Notes
`NVL(x, y)`	`COALESCE(x, y)`	ANSI standard
`NVL2(x, y, z)`	`CASE WHEN x IS NOT NULL THEN y ELSE z END`
`DECODE(x, a, b, c)`	`CASE WHEN x=a THEN b ELSE c END`
`SYSDATE`	`NOW() or CURRENT_TIMESTAMP`	No time zone in Oracle DATE
`SYSTIMESTAMP`	`CLOCK_TIMESTAMP()`
`TRUNC(date)`	`DATE_TRUNC('day', date)`
`ROWNUM`	`ROW_NUMBER() OVER () or LIMIT`	Context-dependent
`CONNECT BY PRIOR`	`WITH RECURSIVE cte AS (...)`	Requires rewrite
`DBMS_OUTPUT.PUT_LINE`	`RAISE NOTICE`
`EXECUTE IMMEDIATE`	`EXECUTE (dynamic SQL)`
`SYS_GUID()`	`gen_random_uuid()`	Requires pgcrypto or pg 13+
`TO_DATE('...','YYYY-MM-DD')`	`'...'::DATE or TO_DATE()`	Mostly compatible

AUTONOMOUS_TRANSACTION

Oracle's PRAGMA AUTONOMOUS_TRANSACTION has no direct equivalent in PostgreSQL. The common use case — writing an audit log entry that persists even if the outer transaction rolls back — can be replicated using dblink to open a separate connection, or by moving audit writes to a background worker. DBMigrateAIPro flags all autonomous transaction procedures for manual review with guidance.

← Back to Blog Try DBMigrateAIPro →