Appendix — Commands: Syntax + Example

Each entry: syntax → example on the course schema → one note → the chapter that uses it. Entries follow chapter order.

Chapter 1 — SQL First

Chapter 1

Syntax

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

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 — full syntax, worked examples on the course schema, what breaks at scale, pitfalls.

Chapter 1 Chapter 9

Syntax

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

Example

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).

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

Chapter 1

Syntax

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

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 — full syntax, worked examples on the course schema, what breaks at scale, pitfalls.

Chapter 1

Syntax

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

Example

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 — full syntax, worked examples on the course schema, what breaks at scale, pitfalls.

Chapter 1 Chapter 8

Syntax

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

Example

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 — full syntax, worked examples on the course schema, what breaks at scale, pitfalls.

Chapter 2 — Bulk

Chapter 2

Syntax

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

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.

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

Chapter 2

Syntax

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

Example

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 — full syntax, worked examples on the course schema, what breaks at scale, pitfalls.

Chapter 2

Syntax

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

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 — full syntax, worked examples on the course schema, what breaks at scale, pitfalls.

Chapter 2 Chapter 7

Syntax

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

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 — full syntax, worked examples on the course schema, what breaks at scale, pitfalls.

Chapter 2 Chapter 4

Syntax

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

Example

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 is for.

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

Chapter 3 — Functions Inside SQL

Chapter 3

Syntax

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

Example

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 — full syntax, worked examples on the course schema, what breaks at scale, pitfalls.

Chapter 3

Syntax

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

Example

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 — full syntax, worked examples on the course schema, what breaks at scale, pitfalls.

Chapter 3

Syntax

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

Example

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 — full syntax, worked examples on the course schema, what breaks at scale, pitfalls.

Chapter 3

Syntax

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

Example

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 — full syntax, worked examples on the course schema, what breaks at scale, pitfalls.

Chapter 4 — In-Memory Lookup

Chapter 4

Syntax

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

Example

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 — full syntax, worked examples on the course schema, what breaks at scale, pitfalls.

Chapter 4

Syntax

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

-- 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 — full syntax, worked examples on the course schema, what breaks at scale, pitfalls.

Chapter 4

Syntax

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

Example

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 — full syntax, worked examples on the course schema, what breaks at scale, pitfalls.

Chapter 5 — COMMIT Strategy

Chapter 5

Syntax

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

Example

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 — full syntax, worked examples on the course schema, what breaks at scale, pitfalls.

Chapter 5

Syntax

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

Example

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 — full syntax, worked examples on the course schema, what breaks at scale, pitfalls.

Chapter 6 — Dynamic SQL

Chapter 6

Syntax

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

Example

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 is for. DDL via EXECUTE IMMEDIATE performs an implicit COMMIT.

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

Chapter 6

Syntax

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

Example

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 — full syntax, worked examples on the course schema, what breaks at scale, pitfalls.

Chapter 6

Syntax

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

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 — full syntax, worked examples on the course schema, what breaks at scale, pitfalls.

Chapter 6

Syntax

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

-- 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 / OPEN FOR: faster and more readable.

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

Chapter 7 — Exceptions

Chapter 7

Syntax

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

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 — full syntax, worked examples on the course schema, what breaks at scale, pitfalls.

Chapter 7

Syntax

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

Example

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 — full syntax, worked examples on the course schema, what breaks at scale, pitfalls.

Chapter 7 Chapter 8

Syntax

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

Example

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 — full syntax, worked examples on the course schema, what breaks at scale, pitfalls.

Chapter 7

Syntax

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

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 — full syntax, worked examples on the course schema, what breaks at scale, pitfalls.

Chapter 7

Syntax

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

Example

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 — full syntax, worked examples on the course schema, what breaks at scale, pitfalls.

Chapter 8 — Locking

Chapter 8

Syntax

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

Example

-- 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 — full syntax, worked examples on the course schema, what breaks at scale, pitfalls.

Chapter 9 — Huge Tables

Chapter 9

Syntax

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

Example

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 — full syntax, worked examples on the course schema, what breaks at scale, pitfalls.

Chapter 9

Syntax

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

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 — full syntax, worked examples on the course schema, what breaks at scale, pitfalls.

Chapter 9

Syntax

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

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 — full syntax, worked examples on the course schema, what breaks at scale, pitfalls.

Chapter 9

Syntax

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

Example

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 — full syntax, worked examples on the course schema, what breaks at scale, pitfalls.

Chapter 9

Syntax

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

Example

-- 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 — full syntax, worked examples on the course schema, what breaks at scale, pitfalls.

Chapter 10 — Measure

Chapter 10

Syntax

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

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 — full syntax, worked examples on the course schema, what breaks at scale, pitfalls.

Chapter 10

Syntax

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

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 — full syntax, worked examples on the course schema, what breaks at scale, pitfalls.

Chapter 10

Syntax

-- 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

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 — setup, full example with output, call-tree query, plshprof, 12.2+ table mode, pitfalls.

Chapter 10

Syntax

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

-- 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 — the functions, format string, three worked plans (pruning, misestimate, bind peeking), reading checklist, automatic plan capture from PL/SQL, pitfalls.