PL/SQL to PL/pgSQL: The Complete Conversion Guide (Part 1)

The Big Picture — Most of PL/SQL Maps Cleanly

After running 500+ migrations, the pattern is consistent. PL/SQL on a real enterprise codebase splits roughly 80 / 15 / 5: 80% is plain ANSI SQL that moves with no changes, 15% is Oracle-specific but mechanically convertible — type names, function names, syntax variants — and 5% is genuinely vendor-specific and needs human attention (packages with persistent state, autonomous transactions, deep DBMS_* dependencies).

This guide walks through the 15% — the constructs every migration project will hit. Part 1 covers the constructs you encounter on every block of PL/SQL: type mappings, variables, built-in functions, cursors, conditionals, loops, exceptions, and DBMS_OUTPUT. Part 2 will cover the harder layer — packages, autonomous transactions, advanced bulk operations, triggers, and object types.

Every example is paste-able into a transpiler test. The point isn't memorisation — it's building the mental model that PL/SQL and PL/pgSQL are the same language with different spellings for the constructs we cover here.

Type Mappings — The Foundation

Every PL/SQL block starts with type declarations. Most of them map directly; a few have nuances worth knowing.

The single biggest gotcha here is DATE. Oracle DATE holds date and time; PostgreSQL DATE holds only date. The right mapping is to TIMESTAMP, not DATE. Every modern transpiler does this automatically, but hand-written conversions miss it constantly and produce silent precision loss.

Variables, %TYPE, and Defaults

Variable declarations are nearly identical. The few differences are syntactic: PL/pgSQL uses DEFAULT or := interchangeably (PL/SQL accepts both too), and %TYPE / %ROWTYPEwork the same way.

-- Oracle PL/SQL
DECLARE
  v_emp_id    employees.emp_id%TYPE;
  v_salary    NUMBER(10, 2) := 0;
  v_hire_date DATE          DEFAULT SYSDATE;
  v_record    employees%ROWTYPE;
BEGIN
  -- ...
END;

-- PostgreSQL PL/pgSQL — only SYSDATE → NOW() differs
DECLARE
  v_emp_id    employees.emp_id%TYPE;
  v_salary    NUMERIC(10, 2) := 0;
  v_hire_date TIMESTAMP      DEFAULT NOW();
  v_record    employees%ROWTYPE;
BEGIN
  -- ...
END;

Built-in Function Mappings

The functions you call inside every block. Most are 1:1; a few need rewriting.

Cursors — Three Forms, All Supported

PL/pgSQL supports every cursor pattern PL/SQL uses. The syntax is nearly identical.

-- Oracle: explicit cursor + FETCH loop
DECLARE
  CURSOR c_emp IS SELECT emp_id, last_name FROM employees WHERE dept_id = 10;
  v_id   employees.emp_id%TYPE;
  v_name employees.last_name%TYPE;
BEGIN
  OPEN c_emp;
  LOOP
    FETCH c_emp INTO v_id, v_name;
    EXIT WHEN c_emp%NOTFOUND;
    DBMS_OUTPUT.PUT_LINE(v_id || ' ' || v_name);
  END LOOP;
  CLOSE c_emp;
END;

-- PostgreSQL: same structure, %NOTFOUND becomes NOT FOUND
DO $$
DECLARE
  c_emp  CURSOR FOR SELECT emp_id, last_name FROM employees WHERE dept_id = 10;
  v_id   employees.emp_id%TYPE;
  v_name employees.last_name%TYPE;
BEGIN
  OPEN c_emp;
  LOOP
    FETCH c_emp INTO v_id, v_name;
    EXIT WHEN NOT FOUND;
    RAISE NOTICE '% %', v_id, v_name;
  END LOOP;
  CLOSE c_emp;
END $$;

The cursor FOR loop is also identical: FOR rec IN c_emp LOOP ... END LOOP; works on both sides. The FOR-IN-SELECT shortcut (no explicit cursor declaration) is identical too. There's almost nothing to learn here.

Conditionals — Almost Identical

IF / ELSIF / ELSE / END IF is identical. CASE has two forms — the statement form (executes a branch) and the expression form (returns a value). Both DBs have both; just watch the closing keyword.

-- Identical on both sides:
IF v_score > 90 THEN
  v_grade := 'A';
ELSIF v_score > 80 THEN
  v_grade := 'B';
ELSE
  v_grade := 'C';
END IF;

-- Oracle CASE statement closes with END CASE
CASE v_status
  WHEN 'NEW'    THEN v_priority := 1;
  WHEN 'ACTIVE' THEN v_priority := 2;
  ELSE                v_priority := 99;
END CASE;

-- PostgreSQL CASE statement: identical, also ends with END CASE

Loops and FORALL

LOOP / EXIT WHEN / FOR / WHILE are all identical on both sides. The interesting one is FORALL: in Oracle it's a bulk-DML optimisation; in PostgreSQL the same effect is achieved by a single SQL statement (PG's optimiser already batches under the hood) so the transpiler often collapses FORALL i IN 1..n DO UPDATE ... into one UPDATE ... WHERE id IN (...).

-- Oracle: FORALL for bulk-update of an array
DECLARE
  TYPE id_array IS TABLE OF NUMBER INDEX BY PLS_INTEGER;
  v_ids id_array;
BEGIN
  -- populate v_ids ...
  FORALL i IN 1 .. v_ids.COUNT
    UPDATE orders SET status = 'PROCESSED' WHERE order_id = v_ids(i);
END;

-- PostgreSQL: collapse to a single set-based UPDATE
DO $$
DECLARE
  v_ids INT[];
BEGIN
  -- populate v_ids ...
  UPDATE orders SET status = 'PROCESSED' WHERE order_id = ANY(v_ids);
END $$;

Exceptions — The Named-Exception Mapping

The EXCEPTION block syntax is identical on both sides. The named exceptions need translation, and RAISE_APPLICATION_ERROR becomes RAISE EXCEPTION:

-- Oracle
BEGIN
  SELECT salary INTO v_salary FROM employees WHERE emp_id = p_id;
EXCEPTION
  WHEN NO_DATA_FOUND THEN
    RAISE_APPLICATION_ERROR(-20001, 'Employee not found: ' || p_id);
  WHEN OTHERS THEN
    RAISE_APPLICATION_ERROR(-20999, 'Unexpected: ' || SQLERRM);
END;

-- PostgreSQL — almost identical, just the RAISE syntax differs
BEGIN
  SELECT salary INTO STRICT v_salary FROM employees WHERE emp_id = p_id;
EXCEPTION
  WHEN NO_DATA_FOUND THEN
    RAISE EXCEPTION 'Employee not found: %', p_id;
  WHEN OTHERS THEN
    RAISE EXCEPTION 'Unexpected: %', SQLERRM;
END;

Note the SELECT INTO STRICT — without STRICT, PostgreSQL silently returns NULL on no-rows, instead of raising NO_DATA_FOUND. This is the single most common subtle bug in hand-converted code. Always use STRICT when porting from Oracle SELECT INTO.

DBMS_OUTPUT.PUT_LINE → RAISE NOTICE

Both are debug-style logging. Oracle's DBMS_OUTPUT buffers output until the block ends and then flushes; PostgreSQL's RAISE NOTICE writes immediately to the client. For most use cases this difference doesn't matter, but it does affect ordering inside long-running blocks.

-- Oracle
DBMS_OUTPUT.PUT_LINE('Processing employee ' || v_id || ', salary $' || v_salary);

-- PostgreSQL
RAISE NOTICE 'Processing employee %, salary $%', v_id, v_salary;

What's Coming in Part 2

Part 2 covers the harder 5% — the constructs that need design decisions, not just spelling changes:

• Packages — no native PostgreSQL equivalent; the standard pattern is one schema per package, with related functions inside
• Autonomous transactions — PostgreSQL doesn't have PRAGMA AUTONOMOUS_TRANSACTION directly; the dblink workaround pattern
• BULK COLLECT semantics — PL/pgSQL supports the syntax but performance characteristics differ; when to keep it, when to rewrite
• Triggers — Oracle BEFORE / AFTER ROW triggers vs PG's trigger-functions model; the boilerplate that surrounds your business logic
• Object types and methods — Oracle's OO surface and how to translate to PG composite types + functions

The Honest Verdict

Most PL/SQL is more portable than the team thinks. The constructs in this article — type declarations, variables, cursors, conditionals, loops, exceptions, output — cover the vast majority of every enterprise codebase, and the conversion rules are mechanical enough that modern transpilers clear them with no human input.

The five percent that still needs human attention isn't hidden — a good transpiler finds every instance, classifies it, and tells you upfront whether each one is a one-line fix or a redesign. That visibility is what makes the migration timeline honest.