I Broke Production with a PL/SQL Loop. Here Are the 10 Steps I Skipped

The job was supposed to run for four minutes. At 7:40 the next morning it was still running, half the rows were updated, and I couldn't roll any of it back.

Here is the shape of what I wrote — simplified, but not by much:

BEGIN
  FOR r IN (SELECT item_id, qty FROM stock_lines) LOOP
    UPDATE stock_summary
       SET qty = qty + r.qty
     WHERE item_id = r.item_id;

    COMMIT;                      -- "so it doesn't fill the undo"
  END LOOP;
END;
/

It looks harmless. It is not. Three separate mistakes are stacked in those nine lines, and each one maps to a fundamental I had skipped:

• One UPDATE per row. 400,000 rows meant 400,000 round trips between the PL/SQL engine and the SQL engine. That's the four minutes turning into eight hours.
• COMMIT inside a cursor loop. Oracle needs undo data to keep serving my open cursor a consistent view. I kept committing it away, and somewhere past row 300,000 the job died with ORA-01555: snapshot too old.
• No transaction boundary. Because I had committed 300,000 times, there was nothing to roll back to. The table was in a state that was neither "before" nor "after". We restored from backup.

The honest diagnosis wasn't "he doesn't know PL/SQL". It was that I had learned SQL in the order tutorials teach it syntax first, consequences never and then jumped straight to writing procedural code against a live database.

So I went back and did it properly, in order. Ten steps. The first seven are SQL, the language for asking questions. The last three are PL/SQL, which is what turns a query into a program living inside the database. Each has one diagram, and each is something that would have prevented some version of my bad night.

Where to practice: Oracle Live SQL runs in the browser with nothing to install, or install Oracle Database Free (23ai) with SQL Developer. Both ship with the HR sample schema used below.

---

Step 1 — The model comes before the syntax

A table is a set of rows. Each row is one fact ("employee 100 earns 24,000"). Each column is one attribute of that fact. There is no built-in order if you want rows in an order, you ask for it.

Two kinds of keys hold everything together:

• Primary key — identifies a row uniquely. EMPLOYEE_ID in EMPLOYEES.
• Foreign key — points at another table's primary key. DEPARTMENT_ID in EMPLOYEES points at DEPARTMENTS.

The distinction I got wrong for months: a foreign key is not a join. A join is something you write in a query. A foreign key is a constraint a rule the database enforces whether you ask for it or not. Insert an employee into department 999 when it doesn't exist, and Oracle refuses:

ORA-02291: integrity constraint (HR.EMP_DEPT_FK) violated - parent key not found

That error is the database protecting you. Read the model before writing anything — DESCRIBE employees, then USER_CONSTRAINTS and USER_INDEXES. Ten minutes there saves an hour of debugging, and in my case would have told me that stock_summary.item_id had no index at all.

---

Step 2 — A SELECT doesn't run in the order you write it

You write:

SELECT   department_id, COUNT(*) AS nb
FROM     employees
WHERE    salary > 3000
GROUP BY department_id
HAVING   COUNT(*) > 2
ORDER BY nb DESC;

Oracle logically processes it in a different order: FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY.

This one fact explains most beginner errors:

-- fails: nb does not exist yet when WHERE runs
SELECT COUNT(*) AS nb FROM employees WHERE nb > 2 GROUP BY department_id;

-- works: ORDER BY runs after SELECT, so the alias is visible
SELECT COUNT(*) AS nb FROM employees GROUP BY department_id ORDER BY nb DESC;

WHERE can't see a SELECT alias because SELECT hasn't run yet. ORDER BY can, because it runs last. Learn the six steps and half your syntax errors stop being mysterious.

---

Step 3 — Filtering, and the NULL trap

WHERE keeps a row only when the condition is TRUE. Not FALSE, and here's the trap not UNKNOWN.

NULL is not zero and not an empty string. It means unknown. Any comparison against an unknown produces UNKNOWN, and those rows get dropped:

-- rows with NULL commission are returned by NEITHER query
SELECT * FROM employees WHERE commission_pct  = 0.2;
SELECT * FROM employees WHERE commission_pct != 0.2;

Both silently skip the same rows. In an aggregation feeding a report, that's a number that is quietly wrong rather than loudly broken. Use the dedicated tools:

WHERE commission_pct IS NULL                  -- the only way to test for NULL
SELECT NVL(commission_pct, 0)                 -- substitute a default
SELECT COALESCE(bonus, commission_pct, 0)     -- first non-NULL of the list

Two Oracle specifics worth knowing on day one:

• Oracle treats an empty string '' as NULL. WHERE name = '' matches nothing, ever.
• NOT IN against a subquery that returns even one NULL returns zero rows. Use NOT EXISTS; make it your default.

---

Step 4 — Joins: ON decides what matches, the join type decides who survives

Every join answers two separate questions, and conflating them is the classic bug.

1. What counts as a match? The ON clause.
2. What happens to rows with no match? The join type.

SELECT e.first_name, d.department_name
FROM   employees e
LEFT JOIN departments d ON d.department_id = e.department_id;

• INNER JOIN — matched rows only.
• LEFT JOIN — every employee, even one with no department (department columns come back NULL).
• RIGHT JOIN — every department, even an empty one.
• FULL OUTER JOIN — both sides, matched or not.

Three rules that matter in practice:

Alias every table (e, d) and prefix every column. In a five-table query, unqualified columns are how you join the wrong thing without noticing.

A filter on an outer-joined table in WHERE silently turns it into an inner join. To filter the optional side, the condition goes in ON:

LEFT JOIN departments d
       ON d.department_id = e.department_id
      AND d.location_id   = 1700      -- keeps the LEFT behaviour

Row count exploded after a join? Your join key isn't unique on one side. This is the one that had me "fixing" data that was never wrong — I was double-counting it. Count first, join second.

You'll meet Oracle's legacy (+) syntax in old code. Read it, don't write it.

---

Step 5 — Aggregation: many rows in, one row per group out

GROUP BY slices rows into buckets; aggregate functions collapse each bucket into one row: COUNT, SUM, AVG, MIN, MAX.

SELECT   d.department_name,
         COUNT(*)             AS headcount,
         ROUND(AVG(e.salary)) AS avg_salary
FROM     employees e
JOIN     departments d ON d.department_id = e.department_id
GROUP BY d.department_name
HAVING   AVG(e.salary) > 3000
ORDER BY avg_salary DESC;

Two rules cover nearly everything:

• Every column in SELECT that isn't inside an aggregate must be in GROUP BY, or you get ORA-00979: not a GROUP BY expression.
• WHERE filters rows before grouping; HAVING filters groups after. Filter in WHERE whenever you can — fewer rows to group is also faster.

One subtlety that shows up in every audit: COUNT(*) counts rows, COUNT(column) counts non-NULL values in that column. The gap between the two numbers is usually the data-quality problem you were sent to find.

---

Step 6 — Subqueries, WITH, and window functions

A subquery is a query inside a query, and it can live in several places:

-- scalar: one row, one column
SELECT first_name, (SELECT MAX(salary) FROM employees) AS top_salary FROM employees;

-- inline view: a table you invented on the spot
SELECT * FROM (SELECT department_id, AVG(salary) avg_sal FROM employees GROUP BY department_id)
WHERE avg_sal > 5000;

-- named up front with WITH (a CTE) - far more readable
WITH dept_avg AS (
  SELECT department_id, AVG(salary) AS avg_sal
  FROM   employees
  GROUP  BY department_id
)
SELECT d.department_name, a.avg_sal
FROM   dept_avg a
JOIN   departments d ON d.department_id = a.department_id
WHERE  a.avg_sal > 5000;

Make WITH your default. Three named CTEs beat one query nested four levels deep — for you now, and for whoever inherits it next year.

Then window functions, the step that separates casual SQL from professional SQL. GROUP BY reduces your rows; OVER () keeps them and adds a calculated column beside each one:

SELECT first_name,
       department_id,
       salary,
       RANK()            OVER (PARTITION BY department_id ORDER BY salary DESC) AS rnk,
       ROUND(AVG(salary) OVER (PARTITION BY department_id))                     AS dept_avg
FROM   employees;

Every employee still comes back, now with their rank inside their department and the department average next to their own salary.

This is also where my incident really started. My "running total per item" requirement had a one-line answer — SUM(qty) OVER (PARTITION BY item_id ORDER BY line_date) — and because I didn't know it existed, I reached for a loop. Learn ROW_NUMBER, RANK, DENSE_RANK, LAG, LEAD and running SUM ... OVER, and a whole category of procedural code stops being necessary.

---

Step 7 — DML and transactions: nothing is real until you COMMIT

Reading is half the job. INSERT, UPDATE, DELETE and MERGE change data but your changes are invisible to everyone else until you commit them.

UPDATE employees SET salary = salary * 1.05 WHERE department_id = 60;
SAVEPOINT before_delete;
DELETE FROM employees WHERE hire_date < DATE '1990-01-01';

ROLLBACK TO before_delete;   -- the DELETE never happened, the UPDATE survives
COMMIT;                      -- now the raise is permanent and public

MERGE deserves its own paragraph — "insert if new, update if existing" in a single statement, and it replaces an enormous amount of bad PL/SQL. Including mine:

MERGE INTO stock_summary t
USING (SELECT item_id, SUM(qty) qty FROM stock_lines GROUP BY item_id) s
   ON (t.item_id = s.item_id)
WHEN MATCHED     THEN UPDATE SET t.qty = t.qty + s.qty
WHEN NOT MATCHED THEN INSERT (item_id, qty) VALUES (s.item_id, s.qty);

That is the entire job I had written as a loop. One statement, one transaction, one COMMIT at the end — and fully reversible until then.

Three things that will save you:

• Write the WHERE before the SET. An UPDATE with no WHERE updates every row. Better: run it as a SELECT first, check the count, then convert it.
• DDL commits implicitly. One CREATE TABLE or TRUNCATE mid-work and your open transaction is committed for you, silently. No rollback available.
• A transaction is your undo button. Committing inside a loop is throwing that button away, once per row. If the unit of work is "the whole file", then the transaction is the whole file.

---

Step 8 — Enter PL/SQL: every program is the same shape

SQL asks questions. It has no variables, no loops, no error handling. PL/SQL adds all three and runs inside the database, right next to the data.

Every PL/SQL program — anonymous block, procedure, function, trigger — is this shape:

DECLARE                                    -- optional
  v_name employees.first_name%TYPE;
BEGIN                                      -- required
  SELECT first_name INTO v_name
  FROM   employees
  WHERE  employee_id = 100;

  DBMS_OUTPUT.PUT_LINE('Name: ' || v_name);
EXCEPTION                                  -- optional
  WHEN NO_DATA_FOUND THEN
    DBMS_OUTPUT.PUT_LINE('No employee 100');
  WHEN TOO_MANY_ROWS THEN
    DBMS_OUTPUT.PUT_LINE('More than one row');
END;
/

Notice immediately:

• SELECT ... INTO must return exactly one row. Zero raises NO_DATA_FOUND; two or more raise TOO_MANY_ROWS. Both are exceptions, not warnings.
• %TYPE anchors your variable to the column's datatype. Widen FIRST_NAME to 60 characters next year and your code follows automatically. %ROWTYPE does the same for a whole row: v_emp employees%ROWTYPE;.
• DBMS_OUTPUT.PUT_LINE is your console.log — but enable it first (SET SERVEROUTPUT ON, or the DBMS Output panel in SQL Developer). Silence usually means you forgot.
• The trailing / tells the client "run this block". It's part of the tool, not the language.

Then the control structures, which will look familiar:

IF v_salary > 5000 THEN ... ELSIF ... ELSE ... END IF;
FOR i IN 1..10 LOOP ... END LOOP;
WHILE v_count < 10 LOOP ... END LOOP;
CASE v_grade WHEN 'A' THEN ... ELSE ... END CASE;

---

Step 9 — Cursors, and the mistake that cost me a night

A cursor is a pointer over a query's result set. The readable way to walk one is the implicit cursor FOR loop:

BEGIN
  FOR r IN (SELECT employee_id, salary FROM employees WHERE department_id = 60) LOOP
    DBMS_OUTPUT.PUT_LINE(r.employee_id || ' : ' || r.salary);
  END LOOP;
END;
/

No OPEN, no FETCH, no CLOSE, no %NOTFOUND Oracle handles it. Write the explicit form only when you truly need the control.

Now the lesson I paid for. Oracle runs two engines: the PL/SQL engine and the SQL engine. Every SQL statement inside a loop is a round trip between them — a context switch. Do it 400,000 times and you pay 400,000 times. That was my eight hours.

The fix is to move data in batches:

DECLARE
  TYPE t_line IS TABLE OF stock_lines%ROWTYPE;
  l_lines t_line;
  CURSOR c IS SELECT * FROM stock_lines;
BEGIN
  OPEN c;
  LOOP
    FETCH c BULK COLLECT INTO l_lines LIMIT 1000;   -- LIMIT protects your PGA
    EXIT WHEN l_lines.COUNT = 0;

    FORALL i IN 1 .. l_lines.COUNT
      UPDATE stock_summary
         SET qty = qty + l_lines(i).qty
       WHERE item_id = l_lines(i).item_id;
  END LOOP;
  CLOSE c;

  COMMIT;                         -- once, at the end. One unit of work.
END;
/

BULK COLLECT pulls many rows per switch. FORALL sends many DML statements per switch. Same logic, seconds instead of hours.

Look closely at where the COMMIT moved. My original committed inside the fetch loop, and that is exactly what produced ORA-01555: snapshot too old: Oracle needs undo data to keep serving the open cursor its consistent read, and I kept committing that undo away. If a batch really is too large for one transaction, you don't sprinkle commits — you make the job restartable, with a status column or a watermark table, so a rerun resumes cleanly instead of double-counting.

And the rule above all the others: the fastest PL/SQL is the PL/SQL you deleted. Before writing any loop, ask whether one UPDATE ... WHERE or one MERGE does the same job. Mine did. It took four minutes.

---

Step 10 — Named units: procedures, functions, packages, triggers

Anonymous blocks are for experiments. My eight-hour job was an anonymous block pasted into SQL Developer — no name, no version, no review, and nothing to point at afterwards. Real code gets a name and lives in the database.

CREATE OR REPLACE PROCEDURE raise_salary (
  p_employee_id IN employees.employee_id%TYPE,
  p_pct         IN NUMBER
) IS
BEGIN
  UPDATE employees
     SET salary = salary * (1 + p_pct/100)
   WHERE employee_id = p_employee_id;

  IF SQL%ROWCOUNT = 0 THEN
    RAISE_APPLICATION_ERROR(-20001, 'Unknown employee: ' || p_employee_id);
  END IF;
END raise_salary;
/

A procedure does something. A function returns a value and is callable from SQL. A trigger fires by itself when a table changes — powerful, and easy to regret, because logic nobody can see is logic nobody can debug.

The unit you should actually ship is the package: a specification (the public contract) and a body (the implementation).

CREATE OR REPLACE PACKAGE hr_api AS
  PROCEDURE raise_salary(p_employee_id IN NUMBER, p_pct IN NUMBER);
  FUNCTION  headcount(p_department_id IN NUMBER) RETURN NUMBER;
END hr_api;
/

CREATE OR REPLACE PACKAGE BODY hr_api AS
  -- private helper, invisible outside the package
  FUNCTION employee_exists(p_id IN NUMBER) RETURN BOOLEAN IS ...

  PROCEDURE raise_salary(...) IS ... END;
  FUNCTION  headcount(...)   RETURN NUMBER IS ... END;
END hr_api;
/

Packages give you encapsulation, a stable public interface, session state, and one object to grant privileges on. They also stop you recompiling half the schema every time you change a line.

Finish with exceptions, and take this one seriously:

EXCEPTION
  WHEN NO_DATA_FOUND THEN
    ...
  WHEN OTHERS THEN
    log_error(SQLCODE, SQLERRM, DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);
    RAISE;                      -- re-raise. Always.
END;

WHEN OTHERS THEN NULL; is the most damaging line in PL/SQL. It turns a failure into a success and hides it forever. Log it, then re-raise it.

---

What the post-mortem actually said

Not "he doesn't know PL/SQL". Something more specific, and more fixable: I had learned the syntax without the consequences, in whatever order the tutorials happened to present them.

Skip step 3 and you'll spend a week hunting a NULL. Skip step 7 and you'll commit something you can't take back. Skip step 9 and you'll write a nightly job that outlives the night — which is, precisely, what I did.

The compressed version, for the next person:

Symptom	Actual cause
Query returns nothing	A NULL in the filter, or NOT IN over a NULL
ORA-00979: not a GROUP BY expression	Non-aggregated column missing from GROUP BY
Row count exploded after a join	Join key isn't unique on one side
LEFT JOIN behaves like INNER	Filter on the optional table sits in WHERE, not ON
"My changes disappeared"	Never committed, or a DDL committed something else first
ORA-01555: snapshot too old	Committing inside a loop that is still fetching
NO_DATA_FOUND inside a procedure	SELECT INTO found zero rows
Job runs for hours	Row-by-row loop; needs BULK COLLECT/FORALL, or plain SQL

Open Live SQL, take the HR schema, and work the ten steps in order. One evening each. Ten evenings is the difference between writing queries and being trusted with a database at 3 a.m.

What was your production incident? Mine was a loop but I suspect the WHERE-less UPDATE wins the popular vote.