Bulletproof PL/SQL: Mastering Inner vs. Outer Exception Blocks in APEX.

When building Oracle APEX apps, unhandled exceptions don’t just break code — they ruin the user experience. A common mistake is relying on a single outer EXCEPTION block at the end of a large procedure. If one row fails during a loop, the whole loop stops and all remaining rows are skipped.

Why this matters

• Real world: processing 100 rows and row #5 fails → rows #6–#100 never run.
• Users see partial results, retries get messy, and support tickets spike.

Simple solution: inner blocks Wrap the per-row work in a small BEGIN…EXCEPTION…END block. That way you:

• Contain the error to the current row,
• Log the failure,
• Mark the row as failed,
• Let the loop keep running for the rest of the records.

User-friendly example (with clear comments)

-- Code snippet: safe batch processing with per-row exception handling
DECLARE
    CURSOR c_records IS 
        SELECT id, process_value FROM staging_table WHERE status = 'PENDING';
    
    l_error_msg VARCHAR2(4000);
BEGIN
    -- Outer block: overall process
    FOR r IN c_records LOOP
        BEGIN
            -- Inner block: handle one record safely
            -- (Intentional divide-by-zero here to demonstrate error handling)
            UPDATE target_table 
            SET calculated_value = r.process_value / 0
            WHERE record_id = r.id;
            
            -- If update succeeds, mark success
            UPDATE staging_table SET status = 'SUCCESS' WHERE id = r.id;

        EXCEPTION
            WHEN OTHERS THEN
                -- Capture error message for this row
                l_error_msg := SQLERRM;
                
                -- Log the error without stopping the entire job
                INSERT INTO error_log (record_id, error_message, log_date)
                VALUES (r.id, l_error_msg, SYSDATE);
                
                -- Mark this row as failed so it can be reviewed or retried
                UPDATE staging_table SET status = 'FAILED' WHERE id = r.id;
        END;
    END LOOP;
    
    COMMIT;

EXCEPTION
    -- Outer exception: handles catastrophic problems that prevent the job from running
    WHEN OTHERS THEN
        ROLLBACK;
        apex_error.add_error(
            p_message => 'A critical system error occurred.',
            p_display_location => apex_error.c_inline_in_notification
        );
END;

Quick best-practice tips

• Use inner blocks for per-row resilience in batch jobs.
• Still keep an outer block to handle catastrophic failures (missing tables, permission issues).
• Avoid silently swallowing errors: log enough context (record id, SQLERRM, stack info) to diagnose failures.
• Consider thresholds (e.g., fail the whole job if too many rows fail) so you don’t keep running a mostly-broken job.

Takeaway Use nested exception blocks when iterating over data so one bad row won’t bring down the whole process. Log and mark failed rows, let the rest finish, and surface truly critical errors at the outer level.

Changes made

• Simplified the opening paragraph for clarity and stronger impact.
• Added a short “Why this matters” section to make the consequence concrete.
• Reframed the solution as a clear, actionable pattern (“Simple solution: inner blocks”).
• Rewrote and clearly commented the code snippet so each step and its purpose are obvious.
• Added a concise “best-practice tips” list with practical guidance.
• Ended with a short, memorable takeaway.