Oracle 1Z0-183: Five Hundred Orders, Zero Errors, Empty Log

Five Hundred Orders, Zero Errors, Empty Log

A batch package processes 500 orders in a cursor loop. Each iteration body is a nested block whose exception section reads WHEN OTHERS THEN INSERT INTO err_log(run_id, msg) VALUES (v_run, SQLERRM); with no re-raise. Last night 12 orders violated a check constraint (ORA-02290). The job finished normally and reported "500 processed, 0 errors". Ops queried ERR_LOG for that run id and found zero rows, even though the 12 ORA-02290 errors are plainly visible in the alert log for the same window. The batch runs inside a single transaction that the calling job rolls back whenever the job reports any failure. Business rules require the batch to be all-or-nothing: a partial set of shipped orders is not acceptable. A colleague's suggestion is to "just add COMMIT inside the handler so the log rows survive".

1. Why is ERR_LOG empty, and why did the job report zero errors?

Read the worked answer

Two independent defects stack up.

The log insert is in the same transaction. INSERT INTO err_log executed from the handler is ordinary transactional DML. When the caller rolls back the batch, those rows are undone along with everything else. The rows did exist briefly; they were never visible after the rollback.
WHEN OTHERS with no re-raise swallows the failure. The handler terminates the block normally, the cursor loop continues, and the driver counts a handled iteration as success. Nothing sets the error counter, so the job reports "0 errors" and the caller never learns it should roll back - the log loss is caused by the rollback that a different failure triggered.

Note the statement-level rollback: Oracle rolled back only the failing INSERT INTO orders for each bad row. The log row after it committed nothing, because nothing in this code commits.

2. Evaluate the colleague's COMMIT-inside-the-handler proposal against the all-or-nothing requirement.

Read the worked answer

Reject it. COMMIT in a PL/SQL handler is not scoped to the handler - it commits the entire transaction, including the 488 orders that were fine. That directly breaks the stated rule: after the commit, a later failure can no longer roll those orders back.

Secondary damage:

A commit per iteration means up to 500 commits, which is a performance and undo/redo problem by itself.
It makes the batch non-restartable and non-reproducible: a rerun sees partial data.
It can invalidate open cursor fetches in long-running loops, adding ORA-01555/fetch-across-commit hazards.

The requirement "log must survive the rollback" is real; the answer is to move the log out of the transaction, not to commit the transaction.

3. Propose the corrected design and show where the error must be re-raised.

Read the worked answer

Split logging into an autonomous transaction so it commits independently of the caller's fate:

CREATE OR REPLACE PROCEDURE log_err(p_run NUMBER, p_msg VARCHAR2) IS
  PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
  INSERT INTO err_log(run_id, msg, logged_at)
  VALUES (p_run, SUBSTR(p_msg,1,4000), SYSTIMESTAMP);
  COMMIT;              -- mandatory: ends only the autonomous transaction
END;

Then the loop body becomes:

BEGIN
  process_order(rec.order_id);
EXCEPTION
  WHEN OTHERS THEN
    log_err(v_run, SQLERRM || DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);
    v_err_cnt := v_err_cnt + 1;
    RAISE;             -- let the driver see the failure
END;

Reasoning: the autonomous transaction's COMMIT commits only its own work (the log row), leaving the batch transaction open, so the caller's rollback still gives all-or-nothing. RAISE restores the failure to the driver, which now reports 12 errors and triggers the correct rollback. Use FORMAT_ERROR_BACKTRACE so the log identifies the failing line, since WHEN OTHERS has destroyed the original propagation path.