# Appendix — Commands: Syntax + Example

Each entry: syntax → example on the [course schema](course-plan.md#shared-example-schema) → one note → the chapter that uses it.
Entries follow chapter order.

---

## MERGE

**Syntax:**

```sql
MERGE INTO <target> t
USING <source | subquery> s
ON    (<join condition>)
WHEN MATCHED THEN UPDATE SET t.col = s.col [WHERE ...] [DELETE WHERE ...]
WHEN NOT MATCHED THEN INSERT (cols) VALUES (s.cols) [WHERE ...];
```

**Example:**

```sql
MERGE INTO customers c
USING (SELECT customer_id, MAX(order_date) AS last_dt
       FROM   orders GROUP BY customer_id) o
ON    (c.customer_id = o.customer_id)
WHEN MATCHED THEN
  UPDATE SET c.last_order_date = o.last_dt
WHEN NOT MATCHED THEN
  INSERT (customer_id, status, last_order_date)
  VALUES (o.customer_id, 'NEW', o.last_dt);
```

**Note:** Upsert in one statement; the source can be any query. A column used in `ON` cannot be updated.

**Deep dive:** [deep-dives/merge.md](deep-dives/merge.md) — full syntax, worked examples on the course schema, what breaks at scale, pitfalls.

↩ [Chapter 1](course-plan.md)

---

## INSERT SELECT

**Syntax:**

```sql
INSERT INTO <target> [(cols)]
SELECT ... FROM ... [WHERE ...];
```

**Example:**

```sql
INSERT INTO orders_archive (order_id, customer_id, order_date, amount, status)
SELECT order_id, customer_id, order_date, amount, status
FROM   orders
WHERE  order_date < DATE '2025-01-01';
```

**Note:** Set-based, one operation. For large volumes — `/*+ APPEND */` (see [APPEND HINT](#append-hint)).

**Deep dive:** [deep-dives/insert-select.md](deep-dives/insert-select.md) — full syntax, worked examples on the course schema, what breaks at scale, pitfalls.

↩ [Chapter 1](course-plan.md), [Chapter 9](course-plan.md)

---

## UPDATE WHERE EXISTS

**Syntax:**

```sql
UPDATE <target> t
SET    col = <value | (SELECT ... FROM <src> s WHERE s.key = t.key)>
WHERE  EXISTS (SELECT 1 FROM <src> s WHERE s.key = t.key [AND ...]);
```

**Example:**

```sql
UPDATE customers c
SET    c.status = 'ACTIVE'
WHERE  EXISTS (SELECT 1 FROM orders o
               WHERE  o.customer_id = c.customer_id
               AND    o.order_date >= ADD_MONTHS(SYSDATE, -12));
```

**Note:** `WHERE EXISTS` prevents updating rows with no match (which would get NULL from a subquery in SET). When values from the source are needed too — `MERGE` reads better.

**Deep dive:** [deep-dives/update-where-exists.md](deep-dives/update-where-exists.md) — full syntax, worked examples on the course schema, what breaks at scale, pitfalls.

↩ [Chapter 1](course-plan.md)

---

## EXISTS

**Syntax:**

```sql
EXISTS (<subquery>)        -- TRUE as soon as the first row is found
NOT EXISTS (<subquery>)
```

**Example:**

```sql
SELECT CASE WHEN EXISTS (SELECT 1 FROM orders WHERE customer_id = :id)
            THEN 1 ELSE 0 END
INTO   v_has_orders
FROM   dual;
```

**Note:** `COUNT(*) > 0` scans every match; `EXISTS` stops at the first. For anti-joins (`NOT EXISTS` vs `NOT IN`) — `NOT IN` returns nothing if the source contains a NULL; `NOT EXISTS` is safe.

**Deep dive:** [deep-dives/exists.md](deep-dives/exists.md) — full syntax, worked examples on the course schema, what breaks at scale, pitfalls.

↩ [Chapter 1](course-plan.md)

---

## FETCH FIRST

**Syntax:**

```sql
SELECT ... FROM ... [WHERE ...] [ORDER BY ...]
[OFFSET <n> ROWS]
FETCH FIRST <n> ROWS ONLY | FETCH FIRST <n> PERCENT ROWS WITH TIES;
```

**Example:**

```sql
SELECT order_id, amount
FROM   orders
WHERE  customer_id = :id
ORDER  BY order_date DESC
FETCH FIRST 10 ROWS ONLY;
```

**Note:** 12c+. Cannot be combined with `FOR UPDATE` (ORA-02014) — use `ROWNUM <= n` there.

**Deep dive:** [deep-dives/fetch-first.md](deep-dives/fetch-first.md) — full syntax, worked examples on the course schema, what breaks at scale, pitfalls.

↩ [Chapter 1](course-plan.md), [Chapter 8](course-plan.md)

---

## BULK COLLECT

**Syntax:**

```sql
SELECT ... BULK COLLECT INTO <collection(s)> FROM ...;
FETCH <cursor> BULK COLLECT INTO <collection(s)> [LIMIT <n>];
EXECUTE IMMEDIATE '<sql>' BULK COLLECT INTO <collection>;
<DML> RETURNING <col> BULK COLLECT INTO <collection>;
```

**Example:**

```sql
DECLARE
  TYPE t_ids IS TABLE OF orders.order_id%TYPE;
  l_ids t_ids;
BEGIN
  SELECT order_id BULK COLLECT INTO l_ids
  FROM   orders WHERE customer_id = :id;      -- small, known result — no LIMIT needed
  DBMS_OUTPUT.PUT_LINE(l_ids.COUNT);
END;
```

**Note:** Brings the whole result into PGA at once. For large or unknown result sizes — always a cursor + [LIMIT](#limit).

**Deep dive:** [deep-dives/bulk-collect.md](deep-dives/bulk-collect.md) — full syntax, worked examples on the course schema, what breaks at scale, pitfalls.

↩ [Chapter 2](course-plan.md)

---

## LIMIT

**Syntax:**

```sql
FETCH <cursor> BULK COLLECT INTO <collection> LIMIT <n>;
```

**Example:**

```sql
OPEN c;
LOOP
  FETCH c BULK COLLECT INTO l_rows LIMIT 10000;
  EXIT WHEN l_rows.COUNT = 0;   -- not c%NOTFOUND here — that drops the last partial batch
  -- ... process l_rows ...
END LOOP;
CLOSE c;
```

**Note:** 1,000–10,000 is the sensible range; beyond that the gain is marginal and PGA grows. If you do use `EXIT WHEN c%NOTFOUND` — only at the end of the loop, after processing.

**Deep dive:** [deep-dives/limit.md](deep-dives/limit.md) — full syntax, worked examples on the course schema, what breaks at scale, pitfalls.

↩ [Chapter 2](course-plan.md)

---

## FORALL

**Syntax:**

```sql
FORALL i IN <low> .. <high> [SAVE EXCEPTIONS]
  <single DML statement referencing collection(i)>;
FORALL i IN INDICES OF <collection> ...       -- sparse collection
FORALL i IN VALUES OF <index_collection> ...  -- subset chosen by an index collection
```

**Example:**

```sql
FORALL i IN 1 .. l_rows.COUNT
  UPDATE orders
  SET    amount = l_rows(i).amount
  WHERE  order_id = l_rows(i).order_id;
DBMS_OUTPUT.PUT_LINE('total: ' || SQL%ROWCOUNT);           -- total rows
DBMS_OUTPUT.PUT_LINE('row 1: ' || SQL%BULK_ROWCOUNT(1));   -- per collection element
```

**Note:** `FORALL` is not a loop — it is one DML statement sent to the SQL engine with the whole collection. The body is exactly one statement.

**Deep dive:** [deep-dives/forall.md](deep-dives/forall.md) — full syntax, worked examples on the course schema, what breaks at scale, pitfalls.

↩ [Chapter 2](course-plan.md)

---

## SAVE EXCEPTIONS

**Syntax:**

```sql
FORALL i IN 1 .. <n> SAVE EXCEPTIONS <dml>;
-- If any element fails: ORA-24381 at the end, not in the middle.
-- SQL%BULK_EXCEPTIONS.COUNT, SQL%BULK_EXCEPTIONS(j).ERROR_INDEX, .ERROR_CODE
```

**Example:**

```sql
DECLARE
  e_bulk_errors EXCEPTION;
  PRAGMA EXCEPTION_INIT(e_bulk_errors, -24381);
BEGIN
  FORALL i IN 1 .. l_rows.COUNT SAVE EXCEPTIONS
    INSERT INTO orders_archive VALUES l_rows(i);
EXCEPTION
  WHEN e_bulk_errors THEN
    FOR j IN 1 .. SQL%BULK_EXCEPTIONS.COUNT LOOP
      log_error('archive idx ' || SQL%BULK_EXCEPTIONS(j).ERROR_INDEX,
                -SQL%BULK_EXCEPTIONS(j).ERROR_CODE,
                SQLERRM(-SQL%BULK_EXCEPTIONS(j).ERROR_CODE));
    END LOOP;
END;
```

**Note:** Without `SAVE EXCEPTIONS` — one failing row stops the whole batch and rolls it back. `ERROR_CODE` is positive — negate it for `SQLERRM`.

**Deep dive:** [deep-dives/save-exceptions.md](deep-dives/save-exceptions.md) — full syntax, worked examples on the course schema, what breaks at scale, pitfalls.

↩ [Chapter 2](course-plan.md), [Chapter 7](course-plan.md)

---

## CURSOR FOR LOOP

**Syntax:**

```sql
FOR <rec> IN (<select>) LOOP ... END LOOP;
FOR <rec> IN <cursor_name>[(<params>)] LOOP ... END LOOP;
```

**Example:**

```sql
FOR r IN (SELECT customer_id, tier FROM customers WHERE tier = 'VIP') LOOP
  l_tier(r.customer_id) := r.tier;
END LOOP;
```

**Note:** Since 10g the compiler fetches 100 rows per fetch automatically (`PLSQL_OPTIMIZE_LEVEL >= 2`, the default) — fine for reading. DML inside the loop is still row by row — that is what [FORALL](#forall) is for.

**Deep dive:** [deep-dives/cursor-for-loop.md](deep-dives/cursor-for-loop.md) — full syntax, worked examples on the course schema, what breaks at scale, pitfalls.

↩ [Chapter 2](course-plan.md), [Chapter 4](course-plan.md)

---

## DETERMINISTIC

**Syntax:**

```sql
CREATE [OR REPLACE] FUNCTION <name> (<params>) RETURN <type>
  DETERMINISTIC
IS ... END;
```

**Example:**

```sql
CREATE OR REPLACE FUNCTION vat_amount (p_amount NUMBER) RETURN NUMBER
  DETERMINISTIC
IS
BEGIN
  RETURN ROUND(p_amount * 0.17, 2);
END;
/
```

**Note:** A promise to Oracle: same input → same output. Lets it skip repeated calls within the same fetch and build a function-based index. Never mark a function that reads a table, `SYSDATE` or package state.

**Deep dive:** [deep-dives/deterministic.md](deep-dives/deterministic.md) — full syntax, worked examples on the course schema, what breaks at scale, pitfalls.

↩ [Chapter 3](course-plan.md)

---

## RESULT_CACHE

**Syntax:**

```sql
CREATE [OR REPLACE] FUNCTION <name> (<params>) RETURN <type>
  RESULT_CACHE
IS ... END;
```

**Example:**

```sql
CREATE OR REPLACE FUNCTION customer_tier (p_id NUMBER) RETURN VARCHAR2
  RESULT_CACHE
IS
  v_tier customers.tier%TYPE;
BEGIN
  SELECT tier INTO v_tier FROM customers WHERE customer_id = p_id;
  RETURN v_tier;
END;
/
```

**Note:** The result is stored in the SGA keyed by parameter values and shared by all sessions; invalidated automatically when a table it read changes. Good for small, stable lookup tables — not for `orders`.

**Deep dive:** [deep-dives/result_cache.md](deep-dives/result_cache.md) — full syntax, worked examples on the course schema, what breaks at scale, pitfalls.

↩ [Chapter 3](course-plan.md)

---

## PRAGMA UDF

**Syntax:**

```sql
CREATE [OR REPLACE] FUNCTION <name> (<params>) RETURN <type> IS
  PRAGMA UDF;
BEGIN ... END;
```

**Example:**

```sql
CREATE OR REPLACE FUNCTION vat_amount (p_amount NUMBER) RETURN NUMBER
  DETERMINISTIC
IS
  PRAGMA UDF;
BEGIN
  RETURN ROUND(p_amount * 0.17, 2);
END;
/
```

**Note:** 12c+. Compiles the function for calls from SQL — makes the context switch cheaper (typically 2–4x). It does not eliminate it: JOIN/CASE are still better.

**Deep dive:** [deep-dives/pragma-udf.md](deep-dives/pragma-udf.md) — full syntax, worked examples on the course schema, what breaks at scale, pitfalls.

↩ [Chapter 3](course-plan.md)

---

## WITH FUNCTION

**Syntax:**

```sql
WITH FUNCTION <name> (<params>) RETURN <type> IS
  BEGIN ... END;
SELECT ... FROM ...;
```

**Example:**

```sql
WITH FUNCTION vat (p NUMBER) RETURN NUMBER IS
  BEGIN RETURN ROUND(p * 0.17, 2); END;
SELECT order_id, amount, vat(amount) AS vat_amount
FROM   orders
WHERE  order_date >= DATE '2026-01-01';
/
```

**Note:** 12c+. An inline function for a single query — no schema object, and the context switch is as cheap as `PRAGMA UDF`. In SQL*Plus/SQLcl terminate with `/`. Inside a PL/SQL block — only via dynamic SQL (`OPEN FOR` / `EXECUTE IMMEDIATE`).

**Deep dive:** [deep-dives/with-function.md](deep-dives/with-function.md) — full syntax, worked examples on the course schema, what breaks at scale, pitfalls.

↩ [Chapter 3](course-plan.md)

---

## ASSOCIATIVE ARRAY

**Syntax:**

```sql
TYPE <t> IS TABLE OF <element_type> INDEX BY PLS_INTEGER | VARCHAR2(<n>);
<var> <t>;
<var>(<key>) := <value>;
```

**Example:**

```sql
DECLARE
  TYPE t_tier_map IS TABLE OF customers.tier%TYPE INDEX BY PLS_INTEGER;
  l_tier t_tier_map;
BEGIN
  FOR r IN (SELECT customer_id, tier FROM customers WHERE tier = 'VIP') LOOP
    l_tier(r.customer_id) := r.tier;
  END LOOP;
  IF l_tier.EXISTS(:some_id) THEN
    DBMS_OUTPUT.PUT_LINE(l_tier(:some_id));
  END IF;
END;
```

**Note:** A hash in session memory (PGA), keyed access in O(1). Every session holds its own copy — in a package global this multiplies memory by the number of sessions. Use in SQL (`TABLE()`) — only if the type is declared in a package spec (12c+).

**Deep dive:** [deep-dives/associative-array.md](deep-dives/associative-array.md) — full syntax, worked examples on the course schema, what breaks at scale, pitfalls.

↩ [Chapter 4](course-plan.md)

---

## COLLECTION METHODS

**Syntax:**

```sql
c.COUNT   c.EXISTS(i)   c.FIRST   c.LAST   c.NEXT(i)   c.PRIOR(i)
c.DELETE   c.DELETE(i)   c.DELETE(i, j)   c.TRIM[(n)]   c.EXTEND[(n)]   c.LIMIT
```

**Example:**

```sql
-- Iterate a sparse collection (non-contiguous keys):
DECLARE
  i PLS_INTEGER := l_tier.FIRST;
BEGIN
  WHILE i IS NOT NULL LOOP
    DBMS_OUTPUT.PUT_LINE(i || ' => ' || l_tier(i));
    i := l_tier.NEXT(i);
  END LOOP;
  l_tier.DELETE;                          -- frees the memory
END;
```

**Note:** `FOR i IN 1 .. c.COUNT` is correct only for a dense collection; for sparse — `FIRST`/`NEXT`. A package-level collection — `DELETE` after use, otherwise the memory stays for the life of the session.

**Deep dive:** [deep-dives/collection-methods.md](deep-dives/collection-methods.md) — full syntax, worked examples on the course schema, what breaks at scale, pitfalls.

↩ [Chapter 4](course-plan.md)

---

## NOCOPY

**Syntax:**

```sql
PROCEDURE <name> (<param> [OUT | IN OUT] NOCOPY <type>) ...
```

**Example:**

```sql
PROCEDURE apply_discounts (p_rows IN OUT NOCOPY t_order_rows) IS
BEGIN
  FOR i IN 1 .. p_rows.COUNT LOOP
    p_rows(i).amount := p_rows(i).amount * 0.9;
  END LOOP;
END;
```

**Note:** `OUT`/`IN OUT` are passed by copy by default (copy in, copy out) — on a 100K-row collection that hurts. `NOCOPY` requests pass-by-reference; a hint, not a guarantee. Side effect: if the procedure fails midway, partial changes remain. `IN` is always by reference — no need.

**Deep dive:** [deep-dives/nocopy.md](deep-dives/nocopy.md) — full syntax, worked examples on the course schema, what breaks at scale, pitfalls.

↩ [Chapter 4](course-plan.md)

---

## COMMIT

**Syntax:**

```sql
COMMIT [WORK] [WRITE [WAIT | NOWAIT] [IMMEDIATE | BATCH]];
```

**Example:**

```sql
LOOP
  FETCH c BULK COLLECT INTO l_ids LIMIT 50000;
  EXIT WHEN l_ids.COUNT = 0;
  FORALL i IN 1 .. l_ids.COUNT
    UPDATE orders SET status = 'PROCESSED' WHERE order_id = l_ids(i);
  COMMIT;                                 -- once per batch
END LOOP;
```

**Note:** Every COMMIT = a wait on the log writer (`log file sync`). COMMIT inside a loop over an open cursor risks `ORA-01555` and prevents a full rollback. `COMMIT WRITE NOWAIT BATCH` — only for jobs allowed to lose the last transaction on a crash.

**Deep dive:** [deep-dives/commit.md](deep-dives/commit.md) — full syntax, worked examples on the course schema, what breaks at scale, pitfalls.

↩ [Chapter 5](course-plan.md)

---

## SAVEPOINT

**Syntax:**

```sql
SAVEPOINT <name>;
ROLLBACK TO [SAVEPOINT] <name>;
```

**Example:**

```sql
BEGIN
  SAVEPOINT before_batch;
  FORALL i IN 1 .. l_ids.COUNT
    UPDATE orders SET status = 'PROCESSED' WHERE order_id = l_ids(i);
  post_batch_checks(l_ids);
EXCEPTION
  WHEN OTHERS THEN
    ROLLBACK TO before_batch;             -- undoes this batch only, not the whole job
    log_error('batch', SQLCODE, SQLERRM);
    RAISE;
END;
```

**Note:** Undoes part of a transaction without ending it; locks of the undone part are released. Oracle rolls back only the *failing statement* by itself — DML a procedure completed before it raised stays in the transaction (unless the exception escapes the top-level call). `SAVEPOINT` + `ROLLBACK TO` in the handler is what makes a procedure atomic.

**Deep dive:** [deep-dives/savepoint.md](deep-dives/savepoint.md) — full syntax, worked examples on the course schema, what breaks at scale, pitfalls.

↩ [Chapter 5](course-plan.md)

---

## EXECUTE IMMEDIATE

**Syntax:**

```sql
EXECUTE IMMEDIATE <sql_string>
  [INTO <var(s)> | BULK COLLECT INTO <collection>]
  [USING [IN | OUT | IN OUT] <bind>, ...]
  [RETURNING INTO <var(s)>];
```

**Example:**

```sql
EXECUTE IMMEDIATE
  'UPDATE orders SET status = ''CLOSED'' WHERE status = :1 AND order_date < :2'
  USING p_status, p_before;
DBMS_OUTPUT.PUT_LINE(SQL%ROWCOUNT);

-- Dynamic PL/SQL block:
EXECUTE IMMEDIATE 'BEGIN pricing_pkg.reprice_all(:yr); END;' USING p_year;
```

**Note:** Binds are positional, not by name (`:1` twice = two binds in `USING`). Object/column names cannot be bound — that is what [DBMS_ASSERT](#dbms_assert) is for. DDL via `EXECUTE IMMEDIATE` performs an implicit COMMIT.

**Deep dive:** [deep-dives/execute-immediate.md](deep-dives/execute-immediate.md) — full syntax, worked examples on the course schema, what breaks at scale, pitfalls.

↩ [Chapter 6](course-plan.md)

---

## OPEN FOR

**Syntax:**

```sql
OPEN <ref_cursor> FOR <sql_string> [USING <bind>, ...];
FETCH <ref_cursor> [BULK COLLECT] INTO ... [LIMIT <n>];
CLOSE <ref_cursor>;
```

**Example:**

```sql
DECLARE
  c     SYS_REFCURSOR;
  TYPE t_ids IS TABLE OF orders.order_id%TYPE;
  l_ids t_ids;
BEGIN
  OPEN c FOR 'SELECT order_id FROM ' || DBMS_ASSERT.SQL_OBJECT_NAME(p_table) ||
             ' WHERE status = :1' USING p_status;
  LOOP
    FETCH c BULK COLLECT INTO l_ids LIMIT 10000;
    EXIT WHEN l_ids.COUNT = 0;
    -- ... process ...
  END LOOP;
  CLOSE c;
END;
```

**Note:** A dynamic SELECT with a large result — `OPEN FOR` + `BULK COLLECT LIMIT`, not `EXECUTE IMMEDIATE ... BULK COLLECT` (which loads everything into memory).

**Deep dive:** [deep-dives/open-for.md](deep-dives/open-for.md) — full syntax, worked examples on the course schema, what breaks at scale, pitfalls.

↩ [Chapter 6](course-plan.md)

---

## DBMS_ASSERT

**Syntax:**

```sql
DBMS_ASSERT.SQL_OBJECT_NAME(<str>)   -- object exists, otherwise ORA-44002
DBMS_ASSERT.SIMPLE_SQL_NAME(<str>)   -- syntactically valid SQL name, otherwise ORA-44003
DBMS_ASSERT.ENQUOTE_LITERAL(<str>)   -- wraps in '' and doubles inner quotes
DBMS_ASSERT.ENQUOTE_NAME(<str>)      -- wraps in ""
```

**Example:**

```sql
EXECUTE IMMEDIATE 'TRUNCATE TABLE ' || DBMS_ASSERT.SQL_OBJECT_NAME(p_table);
```

**Note:** For identifiers (table/column/schema) that cannot be bound. For values — always bind, never `ENQUOTE_LITERAL`.

**Deep dive:** [deep-dives/dbms_assert.md](deep-dives/dbms_assert.md) — full syntax, worked examples on the course schema, what breaks at scale, pitfalls.

↩ [Chapter 6](course-plan.md)

---

## DBMS_SQL

**Syntax:**

```sql
c := DBMS_SQL.OPEN_CURSOR;
DBMS_SQL.PARSE(c, <sql>, DBMS_SQL.NATIVE);
DBMS_SQL.BIND_VARIABLE(c, ':name', <value>);
DBMS_SQL.DEFINE_COLUMN(c, <pos>, <var>[, <size>]);
n := DBMS_SQL.EXECUTE(c);
WHILE DBMS_SQL.FETCH_ROWS(c) > 0 LOOP DBMS_SQL.COLUMN_VALUE(c, <pos>, <var>); ... END LOOP;
DBMS_SQL.CLOSE_CURSOR(c);
-- Conversion: DBMS_SQL.TO_REFCURSOR(c)  /  DBMS_SQL.TO_CURSOR_NUMBER(rc)
```

**Example:**

```sql
-- Column count unknown at write time: describe at runtime
DECLARE
  c      INTEGER := DBMS_SQL.OPEN_CURSOR;
  l_cnt  INTEGER;
  l_desc DBMS_SQL.DESC_TAB;
BEGIN
  DBMS_SQL.PARSE(c, 'SELECT * FROM ' || DBMS_ASSERT.SQL_OBJECT_NAME(p_table), DBMS_SQL.NATIVE);
  DBMS_SQL.DESCRIBE_COLUMNS(c, l_cnt, l_desc);
  FOR i IN 1 .. l_cnt LOOP
    DBMS_OUTPUT.PUT_LINE(l_desc(i).col_name || ' type=' || l_desc(i).col_type);
  END LOOP;
  DBMS_SQL.CLOSE_CURSOR(c);
END;
```

**Note:** The heavy tool. Only when the number/type of columns or binds is unknown in advance. For everything else — [EXECUTE IMMEDIATE](#execute-immediate) / [OPEN FOR](#open-for): faster and more readable.

**Deep dive:** [deep-dives/dbms_sql.md](deep-dives/dbms_sql.md) — full syntax, worked examples on the course schema, what breaks at scale, pitfalls.

↩ [Chapter 6](course-plan.md)

---

## EXCEPTION

**Syntax:**

```sql
BEGIN
  ...
EXCEPTION
  WHEN <exception> [OR <exception>] THEN ...
  WHEN OTHERS THEN ... RAISE;
END;
RAISE;              -- re-raises the current error with the original stack
RAISE <exception>;  -- raises a named exception
```

**Example:**

```sql
BEGIN
  SELECT tier INTO v_tier FROM customers WHERE customer_id = p_id;
EXCEPTION
  WHEN NO_DATA_FOUND THEN
    v_tier := 'NONE';                             -- expected case — a business decision
  WHEN TOO_MANY_ROWS THEN
    RAISE_APPLICATION_ERROR(-20001, 'duplicate customer ' || p_id);
  WHEN OTHERS THEN
    log_error('get_tier ' || p_id, SQLCODE, SQLERRM, DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);
    RAISE;
END;
```

**Note:** `WHEN OTHERS` must end with `RAISE` or `RAISE_APPLICATION_ERROR` (otherwise warning `PLW-06009` — rightly so). A handler in an inner block catches only what happened in that block.

**Deep dive:** [deep-dives/exception.md](deep-dives/exception.md) — full syntax, worked examples on the course schema, what breaks at scale, pitfalls.

↩ [Chapter 7](course-plan.md)

---

## RAISE_APPLICATION_ERROR

**Syntax:**

```sql
RAISE_APPLICATION_ERROR(<error_number -20000..-20999>, <message> [, <keep_errors BOOLEAN>]);
```

**Example:**

```sql
IF p_amount < 0 THEN
  RAISE_APPLICATION_ERROR(-20010, 'amount must be >= 0, got ' || p_amount);
END IF;
```

**Note:** Turns a business error into `ORA-20010` that the caller (PL/SQL, JDBC, .NET) sees with the message. `TRUE` as the third parameter keeps the previous error's stack.

**Deep dive:** [deep-dives/raise_application_error.md](deep-dives/raise_application_error.md) — full syntax, worked examples on the course schema, what breaks at scale, pitfalls.

↩ [Chapter 7](course-plan.md)

---

## PRAGMA EXCEPTION_INIT

**Syntax:**

```sql
<exc_name> EXCEPTION;
PRAGMA EXCEPTION_INIT(<exc_name>, <ora_error_number>);
```

**Example:**

```sql
DECLARE
  e_row_locked EXCEPTION;
  PRAGMA EXCEPTION_INIT(e_row_locked, -54);     -- ORA-00054: resource busy (NOWAIT)
BEGIN
  SELECT order_id INTO v_id FROM orders WHERE order_id = :id FOR UPDATE NOWAIT;
EXCEPTION
  WHEN e_row_locked THEN
    DBMS_OUTPUT.PUT_LINE('busy, retry later');
END;
```

**Note:** Gives a name to an ORA error that has no predefined exception (`-54`, `-24381`, `-1555`, your own `-20xxx`). `WHEN e_row_locked` is more readable and safer than `WHEN OTHERS THEN IF SQLCODE = -54`.

**Deep dive:** [deep-dives/pragma-exception_init.md](deep-dives/pragma-exception_init.md) — full syntax, worked examples on the course schema, what breaks at scale, pitfalls.

↩ [Chapter 7](course-plan.md), [Chapter 8](course-plan.md)

---

## FORMAT_ERROR_BACKTRACE

**Syntax:**

```sql
DBMS_UTILITY.FORMAT_ERROR_BACKTRACE  RETURN VARCHAR2   -- "ORA-06512: at "PKG", line 42 ..."
DBMS_UTILITY.FORMAT_ERROR_STACK      RETURN VARCHAR2   -- full message (2000 chars, more than SQLERRM)
DBMS_UTILITY.FORMAT_CALL_STACK       RETURN VARCHAR2   -- who called whom (works without an error too)
```

**Example:**

```sql
EXCEPTION
  WHEN OTHERS THEN
    log_error('nightly', SQLCODE, DBMS_UTILITY.FORMAT_ERROR_STACK,
              DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);
    RAISE;
```

**Note:** The backtrace points to the line where the error was raised — only if called in the first handler that catches it. `RAISE` preserves it; `RAISE_APPLICATION_ERROR` without `TRUE` resets it. 12c+ also has `UTL_CALL_STACK` for structured access.

**Deep dive:** [deep-dives/format_error_backtrace.md](deep-dives/format_error_backtrace.md) — full syntax, worked examples on the course schema, what breaks at scale, pitfalls.

↩ [Chapter 7](course-plan.md)

---

## PRAGMA AUTONOMOUS_TRANSACTION

**Syntax:**

```sql
PROCEDURE <name> IS
  PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
  ...
  COMMIT;   -- COMMIT/ROLLBACK required before exit, otherwise ORA-06519
END;
```

**Example:**

```sql
CREATE OR REPLACE PROCEDURE log_error (
  p_ctx VARCHAR2, p_code NUMBER, p_msg VARCHAR2, p_backtrace VARCHAR2 DEFAULT NULL) IS
  PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
  INSERT INTO error_log (logged_at, ctx, err_code, err_msg, backtrace)
  VALUES (SYSTIMESTAMP, p_ctx, p_code, p_msg, p_backtrace);
  COMMIT;
END;
/
```

**Note:** The log survives even when the main transaction rolls back. Only for logging/audit — not for business logic: the autonomous transaction does not see the caller's uncommitted changes and can block against it.

**Deep dive:** [deep-dives/pragma-autonomous_transaction.md](deep-dives/pragma-autonomous_transaction.md) — full syntax, worked examples on the course schema, what breaks at scale, pitfalls.

↩ [Chapter 7](course-plan.md)

---

## FOR UPDATE

**Syntax:**

```sql
SELECT ... FROM ... WHERE ...
FOR UPDATE [OF <col>] [NOWAIT | WAIT <seconds> | SKIP LOCKED];
-- In a cursor:  UPDATE ... WHERE CURRENT OF <cursor>;
```

**Example:**

```sql
-- Work queue: several workers in parallel, each takes 1000 free rows
SELECT order_id BULK COLLECT INTO l_ids
FROM   orders
WHERE  status = 'NEW' AND ROWNUM <= 1000
FOR UPDATE SKIP LOCKED;
-- ... process ...
FORALL i IN 1 .. l_ids.COUNT
  UPDATE orders SET status = 'DONE' WHERE order_id = l_ids(i);
COMMIT;                                     -- releases the locks
```

**Note:** The default (no `NOWAIT`/`SKIP LOCKED`) = wait forever. COMMIT closes a `FOR UPDATE` cursor (`ORA-01002` on the next fetch) — so each batch = a fresh SELECT. `FETCH FIRST` is not supported with `FOR UPDATE` — use `ROWNUM`.

**Deep dive:** [deep-dives/for-update.md](deep-dives/for-update.md) — full syntax, worked examples on the course schema, what breaks at scale, pitfalls.

↩ [Chapter 8](course-plan.md)

---

## APPEND HINT

**Syntax:**

```sql
INSERT /*+ APPEND */ INTO <table> SELECT ...;            -- direct-path insert
INSERT /*+ APPEND_VALUES */ INTO <table> VALUES (...);   -- for FORALL (11.2+)
```

**Example:**

```sql
INSERT /*+ APPEND */ INTO orders_archive
SELECT * FROM orders WHERE order_date < DATE '2025-01-01';
COMMIT;      -- required before reading the table in the same session (ORA-12838)
```

**Note:** Writes above the HWM straight to the datafiles, bypassing the buffer cache, minimal undo. With `NOLOGGING` on the table — minimal redo too (then back up right after!). Locks the table against other DML until COMMIT; does not fill holes in existing blocks.

**Deep dive:** [deep-dives/append-hint.md](deep-dives/append-hint.md) — full syntax, worked examples on the course schema, what breaks at scale, pitfalls.

↩ [Chapter 9](course-plan.md)

---

## PARALLEL DML

**Syntax:**

```sql
ALTER SESSION ENABLE PARALLEL DML;              -- or hint: /*+ ENABLE_PARALLEL_DML */ (12c+)
INSERT /*+ APPEND PARALLEL(t, n) */ INTO t SELECT /*+ PARALLEL(s, n) */ ... FROM s;
UPDATE /*+ PARALLEL(t, n) */ t SET ...;
DELETE /*+ PARALLEL(t, n) */ FROM t WHERE ...;
COMMIT;
```

**Example:**

```sql
ALTER SESSION ENABLE PARALLEL DML;
INSERT /*+ APPEND PARALLEL(a, 8) */ INTO orders_archive a
SELECT /*+ PARALLEL(o, 8) */ * FROM orders o WHERE o.order_date < DATE '2025-01-01';
COMMIT;
```

**Note:** Without `ENABLE PARALLEL DML` — only the SELECT is parallel, the INSERT is not. Too high a degree starves a shared server — coordinate with the DBA. COMMIT is required before further DML/SELECT in the same session.

**Deep dive:** [deep-dives/parallel-dml.md](deep-dives/parallel-dml.md) — full syntax, worked examples on the course schema, what breaks at scale, pitfalls.

↩ [Chapter 9](course-plan.md)

---

## DBMS_PARALLEL_EXECUTE

**Syntax:**

```sql
DBMS_PARALLEL_EXECUTE.CREATE_TASK(<task_name>);
DBMS_PARALLEL_EXECUTE.CREATE_CHUNKS_BY_ROWID(<task>, <owner>, <table>, <by_row>, <chunk_size>);
  -- or CREATE_CHUNKS_BY_NUMBER_COL / CREATE_CHUNKS_BY_SQL
DBMS_PARALLEL_EXECUTE.RUN_TASK(<task>, <sql_stmt>, DBMS_SQL.NATIVE, parallel_level => <n>);
  -- sql_stmt must contain :start_id and :end_id
DBMS_PARALLEL_EXECUTE.TASK_STATUS(<task>);   RESUME_TASK(<task>);   DROP_TASK(<task>);
```

**Example:**

```sql
BEGIN
  DBMS_PARALLEL_EXECUTE.CREATE_TASK('reprice');
  DBMS_PARALLEL_EXECUTE.CREATE_CHUNKS_BY_ROWID('reprice', USER, 'ORDERS', TRUE, 100000);
  DBMS_PARALLEL_EXECUTE.RUN_TASK('reprice',
    'UPDATE orders SET amount = pricing_pkg.reprice(order_id, amount)
      WHERE rowid BETWEEN :start_id AND :end_id',
    DBMS_SQL.NATIVE, parallel_level => 8);
  -- Monitor: SELECT status, COUNT(*) FROM user_parallel_execute_chunks
  --          WHERE task_name = 'reprice' GROUP BY status;
  DBMS_PARALLEL_EXECUTE.DROP_TASK('reprice');
END;
```

**Note:** Each chunk = a separate transaction with its own COMMIT, run as a scheduler job. A failed chunk does not stop the others — `RESUME_TASK` reruns only those. Requires the `CREATE JOB` privilege.

**Deep dive:** [deep-dives/dbms_parallel_execute.md](deep-dives/dbms_parallel_execute.md) — full syntax, worked examples on the course schema, what breaks at scale, pitfalls.

↩ [Chapter 9](course-plan.md)

---

## TRUNCATE

**Syntax:**

```sql
TRUNCATE TABLE <table> [DROP | REUSE STORAGE];
ALTER TABLE <table> TRUNCATE PARTITION <p> [UPDATE INDEXES];
ALTER TABLE <table> DROP PARTITION <p> [UPDATE INDEXES];
```

**Example:**

```sql
ALTER TABLE orders TRUNCATE PARTITION p_2024_12 UPDATE INDEXES;
```

**Note:** DDL — immediate, no per-row undo/redo, implicit COMMIT, no ROLLBACK, no triggers. Without `UPDATE INDEXES` — global indexes become `UNUSABLE`. `DELETE` of millions of rows = hours + huge undo; `TRUNCATE` = seconds.

**Deep dive:** [deep-dives/truncate.md](deep-dives/truncate.md) — full syntax, worked examples on the course schema, what breaks at scale, pitfalls.

↩ [Chapter 9](course-plan.md)

---

## EXCHANGE PARTITION

**Syntax:**

```sql
ALTER TABLE <partitioned> EXCHANGE PARTITION <p> WITH TABLE <plain_table>
  [INCLUDING | EXCLUDING INDEXES] [WITH | WITHOUT VALIDATION] [UPDATE INDEXES];
```

**Example:**

```sql
-- Archive: a whole partition becomes a standalone table, the partition is left empty. Zero copying.
CREATE TABLE orders_archive_2024_12 FOR EXCHANGE WITH TABLE orders;   -- 12.2+: identical structure guaranteed
ALTER TABLE orders EXCHANGE PARTITION p_2024_12
  WITH TABLE orders_archive_2024_12 INCLUDING INDEXES WITHOUT VALIDATION;
```

**Note:** Metadata swap only — 40M rows in a millisecond. Works the other way too: loading a staging table into a partition. The table must match in structure and local indexes.

**Deep dive:** [deep-dives/exchange-partition.md](deep-dives/exchange-partition.md) — full syntax, worked examples on the course schema, what breaks at scale, pitfalls.

↩ [Chapter 9](course-plan.md)

---

## DBMS_APPLICATION_INFO

**Syntax:**

```sql
DBMS_APPLICATION_INFO.SET_MODULE(<module_name>, <action_name>);
DBMS_APPLICATION_INFO.SET_ACTION(<action_name>);
DBMS_APPLICATION_INFO.SET_CLIENT_INFO(<client_info>);
DBMS_APPLICATION_INFO.SET_SESSION_LONGOPS(<rindex>, <slno>, <op_name>, ..., <sofar>, <totalwork>, ...);
```

**Example:**

```sql
DBMS_APPLICATION_INFO.SET_MODULE('nightly_batch', 'load');
load_orders;
DBMS_APPLICATION_INFO.SET_ACTION('reprice');
reprice_orders;
DBMS_APPLICATION_INFO.SET_MODULE(NULL, NULL);
-- What is running now:
SELECT sid, module, action, sql_id FROM v$session WHERE module = 'nightly_batch';
```

**Note:** This is what lets the DBA (and AWR/ASH) attribute SQL to a job and a step. Zero cost. `SET_SESSION_LONGOPS` — a progress bar in `V$SESSION_LONGOPS` for long loops.

**Deep dive:** [deep-dives/dbms_application_info.md](deep-dives/dbms_application_info.md) — full syntax, worked examples on the course schema, what breaks at scale, pitfalls.

↩ [Chapter 10](course-plan.md)

---

## DBMS_UTILITY GET_TIME

**Syntax:**

```sql
DBMS_UTILITY.GET_TIME      RETURN NUMBER   -- hundredths of a second, elapsed (wall clock)
DBMS_UTILITY.GET_CPU_TIME  RETURN NUMBER   -- hundredths of a second, CPU only
```

**Example:**

```sql
DECLARE
  t0 PLS_INTEGER := DBMS_UTILITY.GET_TIME;
  c0 PLS_INTEGER := DBMS_UTILITY.GET_CPU_TIME;
BEGIN
  reprice_orders;
  DBMS_OUTPUT.PUT_LINE('elapsed ' || (DBMS_UTILITY.GET_TIME - t0) / 100 || 's, cpu ' ||
                       (DBMS_UTILITY.GET_CPU_TIME - c0) / 100 || 's');
END;
```

**Note:** The absolute value is meaningless (wraps / overflows) — only differences. elapsed ≫ cpu = waiting (I/O, locks, log file sync); elapsed ≈ cpu = the code itself. For human-readable timestamps — `SYSTIMESTAMP` and subtraction.

**Deep dive:** [deep-dives/dbms_utility-get_time.md](deep-dives/dbms_utility-get_time.md) — full syntax, worked examples on the course schema, what breaks at scale, pitfalls.

↩ [Chapter 10](course-plan.md)

---

## DBMS_HPROF

**Syntax:**

```sql
-- One-time (DBA): CREATE DIRECTORY prof_dir AS '...';  @?/rdbms/admin/dbmshptab.sql
DBMS_HPROF.START_PROFILING(location => '<dir>', filename => '<file>');
... the code being measured ...
DBMS_HPROF.STOP_PROFILING;
runid := DBMS_HPROF.ANALYZE('<dir>', '<file>');   -- loads into DBMSHP_FUNCTION_INFO / DBMSHP_PARENT_CHILD_INFO
```

**Example:**

```sql
BEGIN
  DBMS_HPROF.START_PROFILING('PROF_DIR', 'nightly.trc');
  nightly_batch;
  DBMS_HPROF.STOP_PROFILING;
END;
/
SELECT function, calls, function_elapsed_time/1e6 AS sec
FROM   dbmshp_function_info
WHERE  runid = :runid
ORDER  BY function_elapsed_time DESC FETCH FIRST 10 ROWS ONLY;
```

**Note:** Hierarchical profiler — per function: who called it, how many times, how long (including SQL as `__static_sql_exec_line42`). Answers "where do the 4 hours go" without guessing. Requires READ/WRITE on the DIRECTORY.

**Deep dive:** [deep-dives/dbms_hprof.md](deep-dives/dbms_hprof.md) — setup, full example with output, call-tree query, plshprof, 12.2+ table mode, pitfalls.

↩ [Chapter 10](course-plan.md)

---

## DBMS_XPLAN

**Syntax:**

```sql
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(<sql_id>, <child_no>, '<format>'));
  -- format 'ALLSTATS LAST': actual statistics — needs /*+ GATHER_PLAN_STATISTICS */ or statistics_level=ALL
EXPLAIN PLAN FOR <sql>;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);      -- estimated plan only
```

**Example:**

```sql
-- 1. The job's heaviest SQL (see DBMS_APPLICATION_INFO)
SELECT sql_id, executions, elapsed_time/1e6 AS sec, SUBSTR(sql_text, 1, 60) AS txt
FROM   v$sql WHERE module = 'nightly_batch'
ORDER  BY elapsed_time DESC FETCH FIRST 5 ROWS ONLY;
-- 2. The plan that actually ran
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('7h35uxf5uhmm1', NULL, 'ALLSTATS LAST'));
```

**Note:** `DISPLAY_CURSOR` shows what actually ran (from the shared pool); `EXPLAIN PLAN` — what the optimizer guesses, which can differ (binds, statistics). E-Rows vs A-Rows: a big gap = wrong statistics. Requires SELECT on `V$SQL`/`V$SQL_PLAN`.

**Deep dive:** [deep-dives/dbms_xplan.md](deep-dives/dbms_xplan.md) — the functions, format string, three worked plans (pruning, misestimate, bind peeking), reading checklist, automatic plan capture from PL/SQL, pitfalls.

↩ [Chapter 10](course-plan.md)