DECLARE
  v_num NUMBER;
BEGIN
  -- This will trigger a ZERO_DIVIDE error
  v_num := 10 / 0;
  
  -- This line will NEVER execute
  DBMS_OUTPUT.PUT_LINE('Calculation complete'); 
EXCEPTION
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('An error occurred!');
END;
/

DECLARE
  v_name VARCHAR2(100);
BEGIN
  SELECT first_name INTO v_name FROM employees WHERE emp_id = 999;
EXCEPTION
  WHEN NO_DATA_FOUND THEN
    DBMS_OUTPUT.PUT_LINE('Employee 999 does not exist.');
  WHEN TOO_MANY_ROWS THEN
    DBMS_OUTPUT.PUT_LINE('Data corruption: Multiple employees share ID 999.');
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('Unexpected error: ' || SQLERRM);
END;
/

DECLARE
  -- 1. Declare the custom exception
  e_insufficient_funds EXCEPTION;
  v_balance NUMBER := 100;
  v_withdrawal NUMBER := 500;
BEGIN
  IF v_withdrawal > v_balance THEN
    -- 2. Trigger it
    RAISE e_insufficient_funds;
  END IF;
EXCEPTION
  -- 3. Catch it
  WHEN e_insufficient_funds THEN
    DBMS_OUTPUT.PUT_LINE('Transaction rejected: Insufficient funds.');
END;
/

DECLARE
  e_child_exists EXCEPTION;
  -- Bind our name to ORA-02292
  PRAGMA EXCEPTION_INIT(e_child_exists, -2292); 
BEGIN
  DELETE FROM departments WHERE dept_id = 10;
EXCEPTION
  WHEN e_child_exists THEN
    DBMS_OUTPUT.PUT_LINE('Cannot delete department; it still has employees.');
END;
/

CREATE OR REPLACE PROCEDURE log_error(p_msg VARCHAR2) IS
  -- Declare this procedure as independent!
  PRAGMA AUTONOMOUS_TRANSACTION; 
BEGIN
  INSERT INTO error_logs (log_date, message) VALUES (SYSDATE, p_msg);
  
  -- This commit ONLY applies to the INSERT above, not the parent!
  COMMIT; 
END;
/

BEGIN
  -- Attempt a complex update
  UPDATE employees SET salary = salary * 10;
  
  -- Force an error
  RAISE NO_DATA_FOUND;
EXCEPTION
  WHEN OTHERS THEN
    -- Write to the log table (and commit autonomously)
    log_error('Batch failed: ' || SQLERRM);
    
    -- Rollback the massive update
    ROLLBACK; 
END;
/