Using PRAGMA EXCEPTION_INIT to Map Oracle Errors

-- ===================================================
-- CORRECT PATTERNS
-- ===================================================

-- Pattern 1: Basic anonymous block usage
DECLARE
  e_no_customer    EXCEPTION;
  PRAGMA EXCEPTION_INIT(e_no_customer, -1403);  -- ORA-01403: no data found
  v_customer_name  VARCHAR2(200);
BEGIN
  SELECT customer_name INTO v_customer_name
  FROM io.thecodeforge.customers
  WHERE customer_id = p_customer_id;
  
  RETURN v_customer_name;
EXCEPTION
  WHEN e_no_customer THEN
    -- Instantly readable: customer not found, return NULL
    io.thecodeforge.logging.log_warning(
      'CUSTOMER_LOOKUP',
      'Customer ID ' || p_customer_id || ' not found — returning NULL'
    );
    RETURN NULL;
END;
/

-- Pattern 2: Multiple exceptions in the same declarative section
DECLARE
  e_dup_order      EXCEPTION;
  PRAGMA EXCEPTION_INIT(e_dup_order, -1);       -- ORA-00001: unique violation
  
  e_parent_missing EXCEPTION;
  PRAGMA EXCEPTION_INIT(e_parent_missing, -2291); -- ORA-02291: FK violation
  
  e_deadlock       EXCEPTION;
  PRAGMA EXCEPTION_INIT(e_deadlock, -60);        -- ORA-00060: deadlock
BEGIN
  INSERT INTO io.thecodeforge.orders (
    order_id, customer_id, order_date
  ) VALUES (
    p_order_id, p_customer_id, SYSDATE
  );
EXCEPTION
  WHEN e_dup_order THEN
    io.thecodeforge.logging.log_warning('ORDER_INSERT', 'Duplicate order: ' || p_order_id);
  WHEN e_parent_missing THEN
    io.thecodeforge.logging.log_error('ORDER_INSERT', 'Customer ' || p_customer_id || ' not found');
    RAISE_APPLICATION_ERROR(-20001, 'Invalid customer ID: ' || p_customer_id);
  WHEN e_deadlock THEN
    -- Deadlock: roll back and signal caller to retry
    ROLLBACK;
    RAISE_APPLICATION_ERROR(-20099, 'Deadlock on order insert — retry eligible');
END;
/

-- ===================================================
-- WRONG PATTERNS — DO NOT SHIP THESE
-- ===================================================

-- WRONG 1: Pragma after BEGIN — causes PLS-00702 at compile time
DECLARE
  e_my_exception EXCEPTION;
BEGIN
  PRAGMA EXCEPTION_INIT(e_my_exception, -1403);  -- COMPILATION ERROR
  SELECT customer_name INTO v_name FROM customers WHERE customer_id = p_id;
END;
/

-- WRONG 2: Positive error number — silent failure, exception never fires
DECLARE
  e_my_exception EXCEPTION;
  PRAGMA EXCEPTION_INIT(e_my_exception, 1403);   -- WRONG: must be -1403
BEGIN
  NULL;
EXCEPTION
  WHEN e_my_exception THEN
    NULL;  -- This handler will NEVER execute
END;
/

-- WRONG 3: Reversed argument order — compiles but binding is incorrect
DECLARE
  e_my_exception EXCEPTION;
  PRAGMA EXCEPTION_INIT(-1403, e_my_exception);  -- WRONG argument order
BEGIN
  NULL;
END;
/

-- WRONG 4: Forward reference — exception not yet declared
DECLARE
  PRAGMA EXCEPTION_INIT(e_my_exception, -1403);  -- WRONG: declared before exception
  e_my_exception EXCEPTION;
BEGIN
  NULL;
END;
/

-- ===================================================
-- io.thecodeforge.error_pkg — Package Specification
-- Single source of truth for all named exceptions.
-- Reference from other packages as:
--   io.thecodeforge.error_pkg.e_unique_violation
-- ===================================================
CREATE OR REPLACE PACKAGE io.thecodeforge.error_pkg AS

  -- --------------------------------------------------
  -- DATA INTEGRITY EXCEPTIONS
  -- --------------------------------------------------
  e_unique_violation      EXCEPTION;           -- ORA-00001
  PRAGMA EXCEPTION_INIT(e_unique_violation, -1);
  
  e_fk_parent_not_found   EXCEPTION;           -- ORA-02291
  PRAGMA EXCEPTION_INIT(e_fk_parent_not_found, -2291);
  
  e_fk_child_exists       EXCEPTION;           -- ORA-02292
  PRAGMA EXCEPTION_INIT(e_fk_child_exists, -2292);
  
  e_not_null_violation    EXCEPTION;           -- ORA-01400
  PRAGMA EXCEPTION_INIT(e_not_null_violation, -1400);
  
  e_value_too_large       EXCEPTION;           -- ORA-12899
  PRAGMA EXCEPTION_INIT(e_value_too_large, -12899);
  
  e_check_constraint      EXCEPTION;           -- ORA-02290
  PRAGMA EXCEPTION_INIT(e_check_constraint, -2290);

  -- --------------------------------------------------
  -- QUERY AND CURSOR EXCEPTIONS
  -- --------------------------------------------------
  e_no_data_found         EXCEPTION;           -- ORA-01403
  PRAGMA EXCEPTION_INIT(e_no_data_found, -1403);
  
  e_too_many_rows         EXCEPTION;           -- ORA-01422
  PRAGMA EXCEPTION_INIT(e_too_many_rows, -1422);
  
  e_cursor_already_open   EXCEPTION;           -- ORA-06511
  PRAGMA EXCEPTION_INIT(e_cursor_already_open, -6511);
  
  e_max_open_cursors      EXCEPTION;           -- ORA-01000
  PRAGMA EXCEPTION_INIT(e_max_open_cursors, -1000);

  -- --------------------------------------------------
  -- TYPE CONVERSION EXCEPTIONS
  -- --------------------------------------------------
  e_invalid_number        EXCEPTION;           -- ORA-01722
  PRAGMA EXCEPTION_INIT(e_invalid_number, -1722);
  
  e_value_error           EXCEPTION;           -- ORA-06502
  PRAGMA EXCEPTION_INIT(e_value_error, -6502);
  
  e_date_format_mismatch  EXCEPTION;           -- ORA-01830
  PRAGMA EXCEPTION_INIT(e_date_format_mismatch, -1830);
  
  e_invalid_month         EXCEPTION;           -- ORA-01843
  PRAGMA EXCEPTION_INIT(e_invalid_month, -1843);

  -- --------------------------------------------------
  -- CONCURRENCY AND LOCKING EXCEPTIONS
  -- --------------------------------------------------
  e_deadlock              EXCEPTION;           -- ORA-00060
  PRAGMA EXCEPTION_INIT(e_deadlock, -60);
  
  e_resource_busy         EXCEPTION;           -- ORA-00054
  PRAGMA EXCEPTION_INIT(e_resource_busy, -54);
  
  e_snapshot_too_old      EXCEPTION;           -- ORA-01555
  PRAGMA EXCEPTION_INIT(e_snapshot_too_old, -1555);

  -- --------------------------------------------------
  -- MEMORY AND RESOURCE EXCEPTIONS
  -- --------------------------------------------------
  e_out_of_process_memory EXCEPTION;           -- ORA-04030
  PRAGMA EXCEPTION_INIT(e_out_of_process_memory, -4030);
  
  e_shared_pool_exhausted EXCEPTION;           -- ORA-04031
  PRAGMA EXCEPTION_INIT(e_shared_pool_exhausted, -4031);
  
  e_package_state_discarded EXCEPTION;         -- ORA-04068
  PRAGMA EXCEPTION_INIT(e_package_state_discarded, -4068);

  -- --------------------------------------------------
  -- COLLECTION EXCEPTIONS
  -- --------------------------------------------------
  e_collection_uninit     EXCEPTION;           -- ORA-06531
  PRAGMA EXCEPTION_INIT(e_collection_uninit, -6531);
  
  e_subscript_beyond_count EXCEPTION;          -- ORA-06533
  PRAGMA EXCEPTION_INIT(e_subscript_beyond_count, -6533);

  -- --------------------------------------------------
  -- CUSTOM APPLICATION EXCEPTIONS (-20000 to -20999)
  -- Reserve ranges for specific subsystems:
  --   -20001 to -20099: business rule violations
  --   -20100 to -20199: data quality errors
  --   -20200 to -20299: configuration errors
  --   -20300 to -20399: integration errors
  -- --------------------------------------------------
  e_business_rule_violation EXCEPTION;         -- ORA-20001
  PRAGMA EXCEPTION_INIT(e_business_rule_violation, -20001);
  
  e_data_quality_error    EXCEPTION;           -- ORA-20101
  PRAGMA EXCEPTION_INIT(e_data_quality_error, -20101);
  
  e_configuration_error   EXCEPTION;           -- ORA-20201
  PRAGMA EXCEPTION_INIT(e_configuration_error, -20201);
  
  e_integration_error     EXCEPTION;           -- ORA-20301
  PRAGMA EXCEPTION_INIT(e_integration_error, -20301);

END io.thecodeforge.error_pkg;
/

-- ===================================================
-- Usage example: referencing error_pkg from another package
-- ===================================================
CREATE OR REPLACE PROCEDURE io.thecodeforge.order.create_order(
  p_customer_id IN NUMBER,
  p_order_id    OUT NUMBER
) IS
BEGIN
  INSERT INTO io.thecodeforge.orders (
    customer_id, order_date, status, created_at
  ) VALUES (
    p_customer_id, SYSDATE, 'PENDING', SYSTIMESTAMP
  ) RETURNING order_id INTO p_order_id;
  
  COMMIT;
EXCEPTION
  WHEN io.thecodeforge.error_pkg.e_fk_parent_not_found THEN
    -- Instantly readable: the customer_id FK reference is invalid
    ROLLBACK;
    io.thecodeforge.logging.log_error(
      'ORDER_CREATE',
      'Customer ' || p_customer_id || ' does not exist in customers table'
    );
    RAISE_APPLICATION_ERROR(
      -20001,
      'Cannot create order: customer ID ' || p_customer_id || ' not found'
    );
    
  WHEN io.thecodeforge.error_pkg.e_unique_violation THEN
    -- Unique constraint: which one? Extract from SQLERRM
    ROLLBACK;
    io.thecodeforge.logging.log_error(
      'ORDER_CREATE',
      'Duplicate order detected. Customer: ' || p_customer_id ||
      ' Stack: ' || DBMS_UTILITY.FORMAT_ERROR_STACK
    );
    RAISE;  -- Let the caller decide how to handle duplicates
    
  WHEN io.thecodeforge.error_pkg.e_deadlock THEN
    -- Deadlock: always roll back and signal retry-eligible
    ROLLBACK;
    RAISE_APPLICATION_ERROR(
      -20099,
      'Deadlock on order create for customer ' || p_customer_id ||
      ' — retry eligible'
    );
END create_order;
/

-- ===================================================
-- Step 1: Generate exception declarations from data dictionary
-- Run this to produce the code for your error_pkg
-- ===================================================
SELECT
  '  -- Table: ' || table_name || ' | Type: ' ||
  CASE constraint_type
    WHEN 'U' THEN 'UNIQUE'
    WHEN 'P' THEN 'PRIMARY KEY'
    WHEN 'R' THEN 'FOREIGN KEY'
  END AS comment_line,
  '  e_' || LOWER(REGEXP_REPLACE(constraint_name, '[^A-Z0-9]', '_')) ||
  ' EXCEPTION;  -- ' || table_name AS exception_decl,
  '  PRAGMA EXCEPTION_INIT(e_' ||
  LOWER(REGEXP_REPLACE(constraint_name, '[^A-Z0-9]', '_')) ||
  ', ' ||
  CASE constraint_type
    WHEN 'U' THEN '-1'
    WHEN 'P' THEN '-1'
    WHEN 'R' THEN '-2291'
  END || ');' AS pragma_decl
FROM user_constraints
WHERE constraint_type IN ('U', 'P', 'R')
  AND generated = 'USER NAME'   -- exclude system-generated names
ORDER BY table_name, constraint_type, constraint_name;

-- ===================================================
-- Step 2: Constraint-specific exceptions in error_pkg
-- Example: payment system schema
-- ===================================================
CREATE OR REPLACE PACKAGE io.thecodeforge.error_pkg AS

  -- Generic unique violation (catch-all for unspecified constraints)
  e_unique_violation         EXCEPTION;
  PRAGMA EXCEPTION_INIT(e_unique_violation, -1);

  -- io.thecodeforge.payment.transaction_log
  -- Unique index on (batch_id, sequence_number)
  e_txn_batch_seq_unique     EXCEPTION;
  PRAGMA EXCEPTION_INIT(e_txn_batch_seq_unique, -1);
  
  -- io.thecodeforge.customers
  -- Unique index on (email_address)
  e_customer_email_unique    EXCEPTION;
  PRAGMA EXCEPTION_INIT(e_customer_email_unique, -1);
  
  -- Unique index on (tax_id_number)
  e_customer_tax_id_unique   EXCEPTION;
  PRAGMA EXCEPTION_INIT(e_customer_tax_id_unique, -1);
  
  -- io.thecodeforge.orders
  -- Unique index on (external_reference_id)
  e_order_ext_ref_unique     EXCEPTION;
  PRAGMA EXCEPTION_INIT(e_order_ext_ref_unique, -1);

  -- FK exceptions (distinct error numbers — can be caught selectively)
  e_txn_order_fk             EXCEPTION;        -- transaction -> order FK
  PRAGMA EXCEPTION_INIT(e_txn_order_fk, -2291);
  
  e_order_customer_fk        EXCEPTION;        -- order -> customer FK
  PRAGMA EXCEPTION_INIT(e_order_customer_fk, -2291);

END io.thecodeforge.error_pkg;
/

-- ===================================================
-- Step 3: Procedure with constraint-specific handlers
-- Each handler communicates the exact business condition
-- ===================================================
CREATE OR REPLACE PROCEDURE io.thecodeforge.payment.insert_transaction(
  p_batch_id    IN NUMBER,
  p_seq_number  IN NUMBER,
  p_order_id    IN NUMBER,
  p_amount      IN NUMBER
) IS
  v_constraint_name VARCHAR2(128);
BEGIN
  INSERT INTO io.thecodeforge.payment.transaction_log (
    batch_id, sequence_number, order_id, amount, created_at
  ) VALUES (
    p_batch_id, p_seq_number, p_order_id, p_amount, SYSTIMESTAMP
  );
EXCEPTION
  WHEN io.thecodeforge.error_pkg.e_txn_order_fk THEN
    -- FK: order does not exist — this is always a data quality issue
    io.thecodeforge.logging.log_error(
      'TXN_INSERT',
      'Order ' || p_order_id || ' not found. Batch: ' || p_batch_id ||
      ' Seq: ' || p_seq_number
    );
    RAISE_APPLICATION_ERROR(-20301,
      'Integration error: order ID ' || p_order_id ||
      ' does not exist in orders table');
    
  WHEN io.thecodeforge.error_pkg.e_unique_violation THEN
    -- Unique violation: extract constraint name for diagnosis
    -- (since all unique exceptions share -1, we use SQLERRM here)
    v_constraint_name := REGEXP_SUBSTR(
      SQLERRM,
      '\(([^.]+)\.([^)]+)\)',
      1, 1, NULL, 2   -- capture group 2: constraint name after schema
    );
    
    IF v_constraint_name = 'TXN_BATCH_SEQ_UK' THEN
      -- Known duplicate: batch/sequence already processed
      io.thecodeforge.logging.log_warning(
        'TXN_DUPLICATE',
        'Batch ' || p_batch_id || ' seq ' || p_seq_number ||
        ' already exists — idempotent skip'
      );
      -- Do not raise: idempotent skip is correct behavior for known duplicates
    ELSE
      -- Unknown constraint: log fully and raise for investigation
      io.thecodeforge.logging.log_error(
        'TXN_UNEXPECTED_DUP',
        'Constraint: ' || NVL(v_constraint_name, 'UNKNOWN') ||
        ' Batch: ' || p_batch_id || ' Seq: ' || p_seq_number ||
        ' Stack: ' || DBMS_UTILITY.FORMAT_ERROR_STACK
      );
      RAISE;
    END IF;
END insert_transaction;
/

-- ===================================================
-- Step 4: Audit query — find all constraint violations
-- that hit the catch-all e_unique_violation handler
-- (these are candidates for constraint-specific mapping)
-- ===================================================
SELECT constraint_name_extracted,
       COUNT(*) AS occurrences,
       MAX(created_at) AS last_seen
FROM (
  SELECT
    REGEXP_SUBSTR(
      full_stack,
      '\(IO\.THECODEFORGE\.([^)]+)\)',
      1, 1, NULL, 1
    ) AS constraint_name_extracted,
    created_at
  FROM io.thecodeforge.logging.error_log
  WHERE primary_error_code = 'ORA-00001'
    AND created_at > SYSDATE - 30
)
GROUP BY constraint_name_extracted
ORDER BY occurrences DESC;

-- ===================================================
-- Hybrid pattern: PRAGMA EXCEPTION_INIT for known errors
-- + SQLERRM/FORMAT_ERROR_STACK parsing for dynamic cases
-- ===================================================
CREATE OR REPLACE PROCEDURE io.thecodeforge.etl.load_staging_data(
  p_source_table IN VARCHAR2,   -- dynamic: table name varies by feed
  p_target_table IN VARCHAR2
) IS
  v_sql             VARCHAR2(4000);
  v_constraint_name VARCHAR2(128);
  v_error_stack     VARCHAR2(4000);
  v_row_count       NUMBER := 0;
  v_error_count     NUMBER := 0;
  
  -- Known errors: use named exceptions
  e_source_not_found EXCEPTION;
  PRAGMA EXCEPTION_INIT(e_source_not_found, -942);  -- ORA-00942: table not found
  
  e_target_not_found EXCEPTION;
  PRAGMA EXCEPTION_INIT(e_target_not_found, -942);  -- same error, different semantic
BEGIN
  -- Validate that both tables exist before building dynamic SQL
  DECLARE
    v_exists NUMBER;
  BEGIN
    SELECT COUNT(*) INTO v_exists
    FROM user_tables
    WHERE table_name = UPPER(p_source_table);
    
    IF v_exists = 0 THEN
      RAISE_APPLICATION_ERROR(-20301,
        'Source table does not exist: ' || p_source_table);
    END IF;
  END;
  
  -- Build and execute dynamic SQL for the load
  v_sql := 'INSERT INTO io.thecodeforge.' || p_target_table ||
            ' SELECT * FROM io.thecodeforge.staging.' || p_source_table ||
            ' WHERE status = ''PENDING''';
  
  BEGIN
    EXECUTE IMMEDIATE v_sql;
    v_row_count := SQL%ROWCOUNT;
  EXCEPTION
    WHEN io.thecodeforge.error_pkg.e_unique_violation THEN
      -- Known error type, unknown which constraint
      -- Extract constraint name from SQLERRM at runtime
      v_error_stack := DBMS_UTILITY.FORMAT_ERROR_STACK;
      v_constraint_name := REGEXP_SUBSTR(
        v_error_stack,
        'unique constraint \(([^.]+)\.([^)]+)\)',
        1, 1, 'i', 2  -- capture group 2: constraint name
      );
      
      io.thecodeforge.logging.log_error(
        'ETL_UNIQUE_VIOLATION',
        'Table: ' || p_target_table ||
        ' Constraint: ' || NVL(v_constraint_name, 'UNKNOWN') ||
        ' SQL: ' || SUBSTR(v_sql, 1, 500)
      );
      
      v_error_count := v_error_count + 1;
      -- Do not re-raise for batch loads — log and continue
      
    WHEN io.thecodeforge.error_pkg.e_snapshot_too_old THEN
      -- ORA-01555: long-running insert conflicted with undo
      io.thecodeforge.logging.log_error(
        'ETL_SNAPSHOT_TOO_OLD',
        'Table: ' || p_target_table ||
        ' SQL: ' || SUBSTR(v_sql, 1, 500) ||
        ' Action: Increase UNDO_RETENTION or reduce batch window'
      );
      RAISE;  -- This one we DO re-raise — cannot continue
      
    WHEN OTHERS THEN
      -- Unknown error: capture everything for diagnosis
      v_error_stack := DBMS_UTILITY.FORMAT_ERROR_STACK;
      io.thecodeforge.logging.log_error(
        'ETL_UNKNOWN_ERROR',
        'SQLCODE: ' || SQLCODE || CHR(10) ||
        'Table: ' || p_target_table || CHR(10) ||
        'SQL: ' || SUBSTR(v_sql, 1, 500) || CHR(10) ||
        'Stack: ' || v_error_stack || CHR(10) ||
        'Backtrace: ' || DBMS_UTILITY.FORMAT_ERROR_BACKTRACE
      );
      RAISE;
  END;
  
  COMMIT;
  
  io.thecodeforge.logging.log_info(
    'ETL_LOAD_COMPLETE',
    'Table: ' || p_target_table ||
    ' Rows loaded: ' || v_row_count ||
    ' Errors: ' || v_error_count
  );
END load_staging_data;
/

-- ===================================================
-- Pattern: parse SQLERRM to route dynamically
-- Use when the same procedure handles multiple
-- constraint types and named exceptions cannot differentiate
-- ===================================================
CREATE OR REPLACE PROCEDURE io.thecodeforge.etl.smart_error_router IS
  v_error_code   NUMBER;
  v_error_msg    VARCHAR2(4000);
  v_constraint   VARCHAR2(128);
BEGIN
  -- ... DML operations ...
  NULL;
EXCEPTION
  WHEN OTHERS THEN
    v_error_code := SQLCODE;
    v_error_msg  := DBMS_UTILITY.FORMAT_ERROR_STACK;
    
    CASE v_error_code
      WHEN -1 THEN  -- ORA-00001: unique violation
        v_constraint := REGEXP_SUBSTR(
          v_error_msg, 'unique constraint \([^.]+\.([^)]+)\)', 1, 1, 'i', 1
        );
        io.thecodeforge.logging.log_error(
          'UNIQUE_VIOLATION',
          'Constraint: ' || NVL(v_constraint, 'UNKNOWN') ||
          ' Full stack: ' || v_error_msg
        );
      WHEN -2291 THEN  -- ORA-02291: FK parent not found
        io.thecodeforge.logging.log_error(
          'FK_VIOLATION',
          'Parent key not found: ' || v_error_msg
        );
      ELSE
        io.thecodeforge.logging.log_error(
          'UNEXPECTED_ERROR',
          'Code: ' || v_error_code ||
          ' Stack: ' || v_error_msg ||
          ' Backtrace: ' || DBMS_UTILITY.FORMAT_ERROR_BACKTRACE
        );
    END CASE;
    
    RAISE;  -- Always re-raise after logging
END smart_error_router;
/

-- ===================================================
-- io.thecodeforge.test.error_pkg_test
-- Complete test suite for all PRAGMA EXCEPTION_INIT
-- Run after: Oracle upgrades, schema DDL, error_pkg deploys
-- ===================================================
CREATE OR REPLACE PACKAGE io.thecodeforge.test.error_pkg_test AS
  PROCEDURE test_unique_violation_mapping;
  PROCEDURE test_fk_parent_not_found_mapping;
  PROCEDURE test_fk_child_exists_mapping;
  PROCEDURE test_no_data_found_mapping;
  PROCEDURE test_snapshot_too_old_is_declared;  -- cannot artificially raise, just verify declaration
  PROCEDURE test_deadlock_is_declared;
  PROCEDURE test_custom_error_range_mapping;
  PROCEDURE run_all_tests;
END io.thecodeforge.test.error_pkg_test;
/

CREATE OR REPLACE PACKAGE BODY io.thecodeforge.test.error_pkg_test AS

  -- Internal helper: fail with a clear message
  PROCEDURE fail(p_test_name IN VARCHAR2, p_message IN VARCHAR2) IS
  BEGIN
    RAISE_APPLICATION_ERROR(-20999,
      'PRAGMA TEST FAIL: ' || p_test_name || ' — ' || p_message);
  END fail;
  
  PROCEDURE pass(p_test_name IN VARCHAR2) IS
  BEGIN
    DBMS_OUTPUT.PUT_LINE('PASS: ' || p_test_name);
  END pass;

  -- --------------------------------------------------
  -- Test: e_unique_violation catches ORA-00001
  -- --------------------------------------------------
  PROCEDURE test_unique_violation_mapping IS
    v_caught BOOLEAN := FALSE;
  BEGIN
    -- Ensure test customer exists
    BEGIN
      INSERT INTO io.thecodeforge.test.test_customers (
        customer_id, email
      ) VALUES (999999, 'pragma_test@thecodeforge.io');
    EXCEPTION
      WHEN io.thecodeforge.error_pkg.e_unique_violation THEN
        NULL;  -- Already exists from a previous run — that is fine
    END;
    
    -- Now deliberately trigger ORA-00001
    BEGIN
      INSERT INTO io.thecodeforge.test.test_customers (
        customer_id, email
      ) VALUES (999999, 'pragma_test_dup@thecodeforge.io');  -- duplicate PK
    EXCEPTION
      WHEN io.thecodeforge.error_pkg.e_unique_violation THEN
        v_caught := TRUE;
      WHEN OTHERS THEN
        fail('test_unique_violation_mapping',
             'WHEN OTHERS caught instead of e_unique_violation. SQLCODE: ' || SQLCODE);
    END;
    
    ROLLBACK;
    
    IF NOT v_caught THEN
      fail('test_unique_violation_mapping',
           'e_unique_violation did not fire for ORA-00001 — check pragma binding');
    END IF;
    
    pass('test_unique_violation_mapping');
  END test_unique_violation_mapping;

  -- --------------------------------------------------
  -- Test: e_fk_parent_not_found catches ORA-02291
  -- --------------------------------------------------
  PROCEDURE test_fk_parent_not_found_mapping IS
    v_caught BOOLEAN := FALSE;
  BEGIN
    BEGIN
      INSERT INTO io.thecodeforge.test.test_orders (
        order_id, customer_id, order_date
      ) VALUES (
        999999,
        -999999,  -- customer_id that definitely does not exist
        SYSDATE
      );
    EXCEPTION
      WHEN io.thecodeforge.error_pkg.e_fk_parent_not_found THEN
        v_caught := TRUE;
      WHEN OTHERS THEN
        fail('test_fk_parent_not_found_mapping',
             'WHEN OTHERS caught instead of e_fk_parent_not_found. SQLCODE: ' || SQLCODE);
    END;
    
    ROLLBACK;
    
    IF NOT v_caught THEN
      fail('test_fk_parent_not_found_mapping',
           'e_fk_parent_not_found did not fire for ORA-02291');
    END IF;
    
    pass('test_fk_parent_not_found_mapping');
  END test_fk_parent_not_found_mapping;

  -- --------------------------------------------------
  -- Test: e_no_data_found catches ORA-01403
  -- --------------------------------------------------
  PROCEDURE test_no_data_found_mapping IS
    v_caught BOOLEAN := FALSE;
    v_dummy  VARCHAR2(100);
  BEGIN
    BEGIN
      SELECT customer_name INTO v_dummy
      FROM io.thecodeforge.customers
      WHERE customer_id = -999999;  -- guaranteed to not exist
    EXCEPTION
      WHEN io.thecodeforge.error_pkg.e_no_data_found THEN
        v_caught := TRUE;
      WHEN OTHERS THEN
        fail('test_no_data_found_mapping',
             'WHEN OTHERS caught instead of e_no_data_found. SQLCODE: ' || SQLCODE);
    END;
    
    IF NOT v_caught THEN
      fail('test_no_data_found_mapping',
           'e_no_data_found did not fire for ORA-01403');
    END IF;
    
    pass('test_no_data_found_mapping');
  END test_no_data_found_mapping;

  -- --------------------------------------------------
  -- Test: custom error range mapping (-20001)
  -- --------------------------------------------------
  PROCEDURE test_custom_error_range_mapping IS
    v_caught BOOLEAN := FALSE;
  BEGIN
    BEGIN
      RAISE_APPLICATION_ERROR(-20001, 'Test business rule violation');
    EXCEPTION
      WHEN io.thecodeforge.error_pkg.e_business_rule_violation THEN
        v_caught := TRUE;
      WHEN OTHERS THEN
        fail('test_custom_error_range_mapping',
             'WHEN OTHERS caught instead of e_business_rule_violation. SQLCODE: ' || SQLCODE);
    END;
    
    IF NOT v_caught THEN
      fail('test_custom_error_range_mapping',
           'e_business_rule_violation did not fire for ORA-20001');
    END IF;
    
    pass('test_custom_error_range_mapping');
  END test_custom_error_range_mapping;

  -- --------------------------------------------------
  -- Test: verify declaration exists (for errors we cannot
  -- safely raise artificially in production-adjacent schemas)
  -- --------------------------------------------------
  PROCEDURE test_snapshot_too_old_is_declared IS
    v_exists NUMBER;
  BEGIN
    SELECT COUNT(*) INTO v_exists
    FROM dba_source
    WHERE owner = 'IO'
      AND name = 'ERROR_PKG'
      AND UPPER(text) LIKE '%E_SNAPSHOT_TOO_OLD%'
      AND UPPER(text) LIKE '%PRAGMA%';
    
    IF v_exists = 0 THEN
      fail('test_snapshot_too_old_is_declared',
           'e_snapshot_too_old not found in error_pkg — add PRAGMA EXCEPTION_INIT(e_snapshot_too_old, -1555)');
    END IF;
    
    pass('test_snapshot_too_old_is_declared');
  END test_snapshot_too_old_is_declared;

  -- --------------------------------------------------
  -- Run all tests and report results
  -- --------------------------------------------------
  PROCEDURE run_all_tests IS
    v_start TIMESTAMP := SYSTIMESTAMP;
  BEGIN
    DBMS_OUTPUT.PUT_LINE(
      '=== io.thecodeforge.error_pkg test suite ===' || CHR(10) ||
      'Started: ' || TO_CHAR(v_start, 'YYYY-MM-DD HH24:MI:SS')
    );
    
    test_unique_violation_mapping;
    test_fk_parent_not_found_mapping;
    test_no_data_found_mapping;
    test_custom_error_range_mapping;
    test_snapshot_too_old_is_declared;
    
    DBMS_OUTPUT.PUT_LINE(
      CHR(10) || 'All tests passed. Duration: ' ||
      EXTRACT(SECOND FROM (SYSTIMESTAMP - v_start)) || 's'
    );
  EXCEPTION
    WHEN OTHERS THEN
      DBMS_OUTPUT.PUT_LINE('TEST SUITE FAILED: ' || SQLERRM);
      RAISE;  -- Fail the CI/CD step
  END run_all_tests;

END io.thecodeforge.test.error_pkg_test;
/

-- Execute tests (add this to your CI/CD pipeline step)
BEGIN
  io.thecodeforge.test.error_pkg_test.run_all_tests;
END;
/

-- ===================================================
-- Custom error range allocation
-- Document this in your architecture wiki
-- ===================================================
-- -20001 to -20099: Business rule violations
--   -20001: Generic business rule
--   -20002: Order state machine violation
--   -20003: Payment limit exceeded
--   -20004: Customer account suspended
--
-- -20100 to -20199: Data quality errors
--   -20101: Invalid data format
--   -20102: Missing required field
--   -20103: Reference data not found
--
-- -20200 to -20299: Configuration errors
--   -20201: Missing configuration key
--   -20202: Invalid configuration value
--
-- -20300 to -20399: Integration errors
--   -20301: Upstream system unavailable
--   -20302: Response format unexpected
--   -20303: Idempotency key conflict
-- ===================================================

-- Package specification: full custom error range declarations
CREATE OR REPLACE PACKAGE io.thecodeforge.error_pkg AS

  -- Business rule violations
  e_business_rule          EXCEPTION;
  PRAGMA EXCEPTION_INIT(e_business_rule, -20001);
  
  e_order_state_violation  EXCEPTION;
  PRAGMA EXCEPTION_INIT(e_order_state_violation, -20002);
  
  e_payment_limit_exceeded EXCEPTION;
  PRAGMA EXCEPTION_INIT(e_payment_limit_exceeded, -20003);
  
  e_account_suspended      EXCEPTION;
  PRAGMA EXCEPTION_INIT(e_account_suspended, -20004);

  -- Data quality errors
  e_invalid_data_format    EXCEPTION;
  PRAGMA EXCEPTION_INIT(e_invalid_data_format, -20101);
  
  e_missing_required_field EXCEPTION;
  PRAGMA EXCEPTION_INIT(e_missing_required_field, -20102);
  
  e_reference_not_found    EXCEPTION;
  PRAGMA EXCEPTION_INIT(e_reference_not_found, -20103);

  -- Integration errors
  e_integration_unavailable EXCEPTION;
  PRAGMA EXCEPTION_INIT(e_integration_unavailable, -20301);
  
  e_idempotency_conflict   EXCEPTION;
  PRAGMA EXCEPTION_INIT(e_idempotency_conflict, -20303);

END io.thecodeforge.error_pkg;
/

-- ===================================================
-- Usage: raising and catching custom errors
-- The caller catches by name, not by error number
-- ===================================================

-- Payment validation procedure: raises named custom errors
CREATE OR REPLACE PROCEDURE io.thecodeforge.payment.validate_payment(
  p_customer_id IN NUMBER,
  p_amount      IN NUMBER
) IS
  v_limit   NUMBER;
  v_status  VARCHAR2(20);
BEGIN
  SELECT credit_limit, account_status
  INTO v_limit, v_status
  FROM io.thecodeforge.customers
  WHERE customer_id = p_customer_id;
  
  IF v_status = 'SUSPENDED' THEN
    RAISE_APPLICATION_ERROR(-20004,
      'Account ' || p_customer_id || ' is suspended — payment rejected');
  END IF;
  
  IF p_amount > v_limit THEN
    RAISE_APPLICATION_ERROR(-20003,
      'Payment amount ' || p_amount ||
      ' exceeds credit limit ' || v_limit ||
      ' for account ' || p_customer_id);
  END IF;
EXCEPTION
  WHEN io.thecodeforge.error_pkg.e_no_data_found THEN
    RAISE_APPLICATION_ERROR(-20103,
      'Customer ' || p_customer_id || ' not found in reference data');
END validate_payment;
/

-- Order creation procedure: catches named custom errors from validate_payment
CREATE OR REPLACE PROCEDURE io.thecodeforge.order.create_order_with_payment(
  p_customer_id IN NUMBER,
  p_amount      IN NUMBER
) IS
BEGIN
  io.thecodeforge.payment.validate_payment(p_customer_id, p_amount);
  
  INSERT INTO io.thecodeforge.orders (
    customer_id, order_amount, status, created_at
  ) VALUES (
    p_customer_id, p_amount, 'PENDING', SYSTIMESTAMP
  );
  
  COMMIT;
EXCEPTION
  WHEN io.thecodeforge.error_pkg.e_account_suspended THEN
    -- Readable: account suspended — specific business response
    io.thecodeforge.logging.log_warning(
      'ORDER_REJECTED',
      'Account suspended: ' || p_customer_id
    );
    -- Return a specific response code to the caller instead of raising
    ROLLBACK;
    
  WHEN io.thecodeforge.error_pkg.e_payment_limit_exceeded THEN
    -- Readable: payment limit — offer partial order option
    io.thecodeforge.logging.log_info(
      'ORDER_LIMIT',
      'Payment limit exceeded for customer: ' || p_customer_id ||
      ' Amount: ' || p_amount
    );
    ROLLBACK;
    RAISE_APPLICATION_ERROR(-20003,
      'Order amount exceeds your credit limit. Contact support to increase limit.');
    
  WHEN io.thecodeforge.error_pkg.e_reference_not_found THEN
    -- Customer not found in validate_payment — data quality issue
    io.thecodeforge.logging.log_error(
      'ORDER_DATA_QUALITY',
      'Customer ' || p_customer_id || ' referenced in order but not in customers table'
    );
    ROLLBACK;
    RAISE;
END create_order_with_payment;
/