PL/SQL anti-pattern

PL/SQL row-by-row cursor

Large cursor FOR loops that apply business rules one row at a time, with per-row lookups, commits inside the loop and WHEN OTHERS handlers that hide failures.

Request a codebase scan Explore demo

FAMILY: PL/SQL
CUTOVER RISK: High; Hides business logic or coupling that a rewrite will drop unless it is extracted explicitly.
MODERN MAPPING: Set-based query plus explicit rule table
GENERATED ARTIFACT: REBATE_RATE.dmn.json

Before and after

legacy source → modern mapping

Legacy: exhibits the pattern

pkg_rebate.pkbSQL

1PROCEDURE apply_rebates(p_period IN VARCHAR2) IS
2  CURSOR c_cust IS
3    SELECT cust_id, segment FROM customers
4    WHERE rebate_done = 'N';
5  v_spend  NUMBER(12,2);
6  v_rate   NUMBER(5,4);
7  v_count  PLS_INTEGER := 0;
8BEGIN
9  FOR r IN c_cust LOOP
10    BEGIN
11      SELECT NVL(SUM(amount), 0) INTO v_spend
12        FROM invoices
13       WHERE cust_id = r.cust_id AND period = p_period;
14      IF r.segment = 'GOLD' AND v_spend >= 50000 THEN
15        v_rate := 0.0300;
16      ELSIF v_spend >= 20000 THEN
17        v_rate := 0.0150;
18      ELSE
19        v_rate := 0;
20      END IF;
21      UPDATE customers
22         SET rebate = ROUND(v_spend * v_rate, 2),
23             rebate_done = 'Y'
24       WHERE cust_id = r.cust_id;
25    EXCEPTION
26      WHEN OTHERS THEN NULL;
27    END;
28    v_count := v_count + 1;
29    IF MOD(v_count, 1000) = 0 THEN COMMIT; END IF;
30  END LOOP;
31  COMMIT;
32END apply_rebates;

Modern: Set-based query plus explicit rule table

apply_rebates.sql (set-based)SQL

1-- rule table REBATE_RATE (DMN, reviewed)
2WITH spend AS (
3  SELECT c.cust_id, c.segment,
4         COALESCE(SUM(i.amount), 0) AS total
5  FROM customers c
6  LEFT JOIN invoices i
7    ON i.cust_id = c.cust_id AND i.period = :period
8  WHERE c.rebate_done = 'N'
9  GROUP BY c.cust_id, c.segment
10)
11UPDATE customers c
12SET rebate = ROUND(s.total * CASE
13      WHEN s.segment = 'GOLD' AND s.total >= 50000 THEN 0.0300
14      WHEN s.total >= 20000 THEN 0.0150
15      ELSE 0 END, 2),
16    rebate_done = 'Y'
17FROM spend s
18WHERE s.cust_id = c.cust_id;

Why it is dangerous in modernization

Batch logic in Oracle shops often lives in a stored procedure that opens a cursor over millions of rows and, for each one, runs lookups, applies tiered rules and issues an UPDATE. Over time the loop body becomes the only specification of the business process. The shape causes three migration problems. First, rules are interleaved with row handling, so extracting them means separating the calculation from the cursor plumbing. Second, commits every N rows inside the loop make the procedure restartable only through flag columns and bookkeeping tables, which are business-critical and undocumented. Third, EXCEPTION WHEN OTHERS THEN NULL inside the loop skips bad rows silently, so the legacy job's output depends on which rows failed. A rewrite as a set-based query or a streaming job changes all three unless they are made explicit.

How PYD ENTERPRISES detects it

The PL/SQL front end parses packages, procedures, cursors, and embedded SQL into one AST, with SQL statements analysed for the tables and columns they read and write. For each cursor loop the engine separates the cursor query (the input set), per-row lookups (joins in disguise), the calculation (candidate rules), and the writes. COMMIT statements inside loops are flagged with the restart logic that surrounds them, and exception handlers are classified by what they catch and whether they re-raise, log or swallow. Column-level lineage records which input columns flow into which updated columns through which rule.

Maps to: Set-based query plus explicit rule table

The cursor query and per-row lookups become one set-based query or a chunked batch reader. The calculation becomes a DMN decision table or a pure function with its own tests, applied in SQL with a CASE expression or in a batch service. Restart bookkeeping is replaced by the batch framework's checkpointing, and swallowed exceptions become rejected-row output with reasons.

Generated specification fragment

REBATE_RATE.dmn.jsonJSON

1{
2  "decision": "Rebate rate",
3  "hitPolicy": "FIRST",
4  "inputs": ["segment", "periodSpend"],
5  "outputs": ["rate"],
6  "rules": [
7    { "id": "R1", "segment": "\"GOLD\"",
8      "periodSpend": ">= 50000", "rate": 0.03 },
9    { "id": "R2", "segment": "-",
10      "periodSpend": ">= 20000", "rate": 0.015 },
11    { "id": "R3", "segment": "-", "periodSpend": "-",
12      "rate": 0 }
13  ],
14  "source": "pkg_rebate.pkb:14-20",
15  "note": "GOLD below 50000 falls to R2 or R3"
16}

Every generated element links back to the source file and line range shown in the legacy pane. Review and sign off before building against it.

The rule hidden in the IF chain

Read quickly, the procedure gives Gold customers 3% and everyone else 1.5% above 20,000. Read precisely, a Gold customer with 30,000 of spend gets 1.5%, because the first branch fails and the second applies to any segment. That is what the FIRST hit policy in the generated DMN table makes visible: rule order matters, and a reviewer can see that R2 applies to Gold customers too. Whether that is intended is a business question, and it is now asked before the rewrite, not after.

Commits inside the loop

Committing every 1,000 rows keeps undo small, but it means a failure halfway through leaves some customers with rebates and others without. The rebate_done flag is the restart mechanism: rerun the procedure and it picks up where it left off. Committing while a cursor is still open on the same table also risks ORA-01555 (snapshot too old) on long runs. PYD ENTERPRISES reports the flag as restart state, not business data, and the target design replaces it with checkpointing in the batch framework or with a single set-based statement inside one transaction, where data volumes allow.

WHEN OTHERS THEN NULL

Any error for a customer, whether a numeric overflow or a lock timeout, is swallowed, the row keeps rebate_done = 'N', and the next run tries again. That is accidental retry logic. The engine flags every handler that catches OTHERS without re-raising or logging, and the generated equivalence tests include a row that fails so the new job's rejected-row output can be compared with the legacy behavior of leaving the row untouched.

Related patterns

Usually found in the same codebase.

Back to the full catalog

Find every PL/SQL row-by-row cursor in your codebase.

Run PYD ENTERPRISES in your private VPC and get each occurrence with file, line range, modern mapping and equivalence fixtures, ready for your architects to review.

Start 14-day trial Book technical walkthrough