PL/SQL Interview Questions: Cursors, Bulk Operations, Exceptions, and Transactions

Good PL/SQL interview answers explain the processing pattern, failure behavior, and transaction boundary together. A cursor loop that works for ten rows may be inefficient for a large import. A fast bulk operation may still be wrong if one bad row leaves the application with an unexpected partial result.

Start with three questions: can this be expressed as one SQL statement, what should happen when a row fails, and who decides to commit? This guide builds from cursor fundamentals to BULK COLLECT, FORALL, exception handling, and restartable batch processing. Examples are original practice and should be adapted to your schema and Oracle version.

Key takeaways

• Prefer a clear set-based statement when it expresses the business operation correctly.
• A cursor FOR loop manages its own open, fetch, and close lifecycle, but row-by-row DML still deserves a performance review.
• BULK COLLECT retrieves into collections; FORALL batches a DML statement using collection values.
• LIMIT bounds each fetch, not the size of the transaction.
• SAVE EXCEPTIONS continues eligible bulk DML after row-level failures; it does not commit successes automatically.

When should I use SQL instead of a cursor?

If the requirement is “mark every overdue open invoice for review,” first consider one UPDATE with a clear predicate. Reading the same invoices into PL/SQL and issuing an UPDATE for each row introduces more moving parts without necessarily adding value.

UPDATE invoices
SET review_required = 'Y'
WHERE status = 'OPEN'
  AND due_date < :cutoff_date;

This is an illustrative statement using a caller-supplied cutoff. Its indexes, locking, validation, and transaction policy still matter. Set-based SQL is a starting point, not a claim that every large operation is automatically cheap.

Pattern	Choose when	Explain the trade-off
Single SQL statement	The operation is naturally relational	Plan, locks, constraints, and atomicity
Cursor loop	Per-row procedural work is genuinely needed	Clarity versus repeated SQL calls
BULK COLLECT plus FORALL	Rows need PL/SQL processing before batched DML	Memory, collection indexing, and error policy

What is the difference between implicit and explicit cursors?

Oracle manages implicit cursors for SQL statements executed through PL/SQL. Explicit cursors let you name a query and control its result processing. With manual cursor management, open it, fetch, check for exhaustion, and close it. If a failure can interrupt that sequence, include an appropriate cleanup path.

A cursor FOR loop handles the lifecycle for you:

BEGIN
  FOR r IN (
    SELECT invoice_id, amount
    FROM invoices
    WHERE status = 'OPEN'
    ORDER BY invoice_id
  ) LOOP
    DBMS_OUTPUT.PUT_LINE(r.invoice_id || ': ' || r.amount);
  END LOOP;
END;
/

The loop body runs zero times for an empty result. Do not confuse that with a scalar SELECT INTO, where no row raises NO_DATA_FOUND and more than one row raises TOO_MANY_ROWS. These different contracts explain many basic PL/SQL interview traps.

DBMS_OUTPUT here makes a small learning example easy to inspect; it is not a production batch-monitoring strategy. In a real process, choose structured operational reporting that is appropriate for the volume and data sensitivity.

Which cursor attributes should I understand?

Know what row count and found/not-found mean at the point where you inspect them. With an explicit fetch loop, check exhaustion after fetching and before using the fetched record. Otherwise, you can process stale data or skip a necessary step.

For implicit SQL attributes, capture values promptly if a later SQL statement could replace the information you intended to inspect. “The last statement affected zero rows” is often a meaningful business result, not necessarily an exception.

Compare correctness and explain why procedural processing is or is not needed.

What do BULK COLLECT and FORALL actually improve?

They reduce repeated communication between the PL/SQL and SQL engines when work needs to cross that boundary many times. BULK COLLECT retrieves multiple rows into collections. FORALL submits repeated executions of one DML statement using collection elements. It is not a general-purpose loop that can contain arbitrary procedural statements.

Bulk processing does not eliminate the underlying SQL work. A bad access path, an expensive trigger, or lock contention can still dominate runtime. Measure elapsed time, affected rows, and resource use under representative conditions rather than assuming the word “bulk” proves an optimisation.

How do I use BULK COLLECT LIMIT without losing the last batch?

Use a bounded fetch and process every nonempty collection. The final fetch can contain fewer rows than the limit and still needs processing.

DECLARE
  CURSOR c IS
    SELECT invoice_id FROM invoices
    WHERE status = 'OPEN';
  TYPE id_list IS TABLE OF invoices.invoice_id%TYPE;
  ids id_list;
BEGIN
  OPEN c;
  LOOP
    FETCH c BULK COLLECT INTO ids LIMIT 500;
    EXIT WHEN ids.COUNT = 0;

    FORALL i IN 1 .. ids.COUNT
      UPDATE invoices
      SET review_required = 'Y'
      WHERE invoice_id = ids(i);
  END LOOP;
  CLOSE c;
EXCEPTION
  WHEN OTHERS THEN
    IF c%ISOPEN THEN
      CLOSE c;
    END IF;
    RAISE;
END;
/

This demonstrates batching and cleanup, not the best way to perform this particular update; a single SQL statement would usually be the first design to consider. There is deliberately no COMMIT, because this example leaves transaction ownership to its caller.

With 1,200 selected IDs, the loop processes batches of 500, 500, and 200, then exits on an empty fetch. A limit of 500 is an example, not an optimal setting. Tune against row width, memory, concurrency, and throughput. Fetching fewer rows at once does not release transaction locks or make work durable.

What if a collection has missing indexes?

A dense collection can be processed with a contiguous index range. After deleting individual elements, that assumption may no longer hold. FORALL supports forms such as INDICES OF and VALUES OF for appropriate sparse-collection cases.

In an interview, show a collection with populated indexes 1 and 3 but no element 2. Explain why blindly using 1 through COUNT does not enumerate the actual populated indexes. Keep the mapping from a collection element to the source business key so that failures can be reported accurately.

What happens if one FORALL operation fails?

Be precise about exception handling. Without SAVE EXCEPTIONS, an error stops further iterations. Oracle distinguishes an unhandled error, which rolls back earlier DML changes in that FORALL, from an immediately handled error, where only the failing DML statement is rolled back and earlier successful changes can remain pending. They are not automatically committed.

With SAVE EXCEPTIONS, eligible DML failures are collected while remaining iterations continue; Oracle then raises ORA-24381. Inspect SQL%BULK_EXCEPTIONS for the failed execution indexes and error codes. This still does not decide the final transaction outcome. Oracle's documented bulk error semantics.

Business contract	Handling strategy	What must be explicit
All rows succeed or none do	Roll back the business unit on failure	Scope of rollback and error propagation
Accept valid rows; reject bad rows	Collect errors and report partial success	Which rows are accepted and when they commit
Retry transient failures	Record retryable work with stable identities	Duplicate prevention and retry limits

Do not simply say “FORALL rolls everything back.” Everything could mean the failed iteration, earlier iterations of that statement, other work in the transaction, or the caller's entire operation. State the scope and handler context.

How should bulk errors be reported?

For each failure, preserve a stable source identifier, the relevant collection mapping, and the Oracle error details. ERROR_INDEX needs interpretation against the FORALL indexing form; do not assume it always equals a business key. Capture the diagnostics before further bulk work overwrites the information you need.

Separate data-quality failures from transient infrastructure failures. An invalid field may need correction; a temporary unavailable dependency may merit retry. Do not log sensitive payloads unnecessarily, and do not silently discard rejected rows just because the rest of the batch succeeded.

Who should commit: the procedure or the caller?

Choose and document one owner for the business transaction. A reusable helper that commits unexpectedly can make it impossible for its caller to undo related work. For example, updating an account balance and writing its audit record may be one business operation even if separate procedures perform the two steps.

If a batch is intentionally committed in chunks, define the restart point, duplicate-handling rule, and partial-completion reporting. “Commit every thousand rows” is incomplete without explaining what happens when the process fails immediately after a commit but before recording its progress.

What is wrong with WHEN OTHERS THEN NULL?

It hides failures and can make a caller believe the operation succeeded. Catch specific expected exceptions when recovery is meaningful. For unexpected failures, perform appropriate cleanup, preserve useful diagnostic information, and propagate the error unless the API explicitly reports failure another way.

Logging itself can fail, and its transaction behavior needs thought. Do not introduce an autonomous transaction merely because you want a log entry to survive; that changes transactional relationships and deserves a deliberate design. During an interview, explain the requirement before selecting the mechanism.

A practical batch-import interview scenario

Prompt: import 100,000 records; several have invalid values, and the process may restart. What would you design?

Answer: establish whether partial acceptance is allowed, stage records with stable source keys, validate what can be checked in sets, and choose set-based or bounded bulk DML. Record accepted and rejected outcomes, keep transaction ownership explicit, and make a rerun avoid duplicating previously accepted work. Add reconciliation counts so “finished” means every source record has a known outcome.

Test zero records, one record, a final partial batch, a duplicate source key, a failure early and late in a batch, and a restart after commit. Verify actual persisted rows in a separate transaction, not just a success message printed by the procedure.

Quick answers to common follow-ups

Is FORALL always faster? No. It can reduce engine-call overhead, but workload, SQL plans, triggers, and contention determine the result. Measure.

Does LIMIT commit a batch? No. It limits rows fetched into a collection per fetch.

Does SAVE EXCEPTIONS guarantee partial success is permanent? No. Successful work remains subject to the transaction's final commit or rollback.

How should I prepare? Implement a small import three ways: set-based SQL, a cursor loop, and bounded bulk processing. Introduce a bad row and explain exactly which changes remain pending, which are undone, and who makes them durable.