ORA-01422: Exact Fetch Returned More Than Requested Number of Rows

-- ============================================================
-- Setup: demonstrate the ORA-01422 failure condition
-- Runnable on Oracle 19c, 21c, 23ai
-- ============================================================

CREATE TABLE demo_employees (
  employee_id   NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  employee_code VARCHAR2(20) NOT NULL,
  full_name     VARCHAR2(100) NOT NULL,
  department    VARCHAR2(50),
  created_date  DATE DEFAULT SYSDATE
);

-- Insert one clean record and one duplicate
INSERT INTO demo_employees (employee_code, full_name, department)
VALUES ('EMP-001', 'Alice Chen', 'Engineering');

INSERT INTO demo_employees (employee_code, full_name, department)
VALUES ('EMP-001', 'Alice Chen (duplicate)', 'Engineering');  -- Intentional duplicate

INSERT INTO demo_employees (employee_code, full_name, department)
VALUES ('EMP-002', 'Bob Ortiz', 'Finance');  -- Unique

COMMIT;

-- ============================================================
-- Demo 1: ORA-01422 fires on the duplicate employee_code
-- ============================================================

DECLARE
  v_name VARCHAR2(100);
BEGIN
  SELECT full_name INTO v_name
  FROM demo_employees
  WHERE employee_code = 'EMP-001';  -- Two rows match
  DBMS_OUTPUT.PUT_LINE('Name: ' || v_name);
EXCEPTION
  WHEN TOO_MANY_ROWS THEN
    DBMS_OUTPUT.PUT_LINE('ORA-01422: EMP-001 has duplicates');
    DBMS_OUTPUT.PUT_LINE('This is a data model bug, not a code bug');
END;
/

-- ============================================================
-- Demo 2: Same table, unique code — SELECT INTO works
-- ============================================================

DECLARE
  v_name VARCHAR2(100);
BEGIN
  SELECT full_name INTO v_name
  FROM demo_employees
  WHERE employee_code = 'EMP-002';  -- Exactly one row
  DBMS_OUTPUT.PUT_LINE('Name: ' || v_name);
END;
/

-- ============================================================
-- Demo 3: Find duplicates before they cause ORA-01422
-- Run this during data quality checks
-- ============================================================

SELECT
  employee_code,
  COUNT(*)                                                    AS row_count,
  LISTAGG(employee_id, ', ') WITHIN GROUP (ORDER BY employee_id) AS employee_ids
FROM demo_employees
GROUP BY employee_code
HAVING COUNT(*) > 1
ORDER BY COUNT(*) DESC;

-- Expected output:
-- EMPLOYEE_CODE  ROW_COUNT  EMPLOYEE_IDS
-- EMP-001        2          1, 2

-- Cleanup
-- DROP TABLE demo_employees PURGE;

-- ============================================================
-- Complete ORA-01422 diagnostic sequence
-- Run these queries in order after an ORA-01422 incident
-- ============================================================

-- Step 1: Map the ORA-06512 line number to source code
-- Replace PKG_NAME and the line range with values from your error stack
SELECT line, text
FROM all_source
WHERE name = 'EMPLOYEE_PKG'
  AND type = 'PACKAGE BODY'
  AND line BETWEEN 44 AND 52
ORDER BY line;

-- Step 2: Run the failing query without INTO to see all matching rows
-- Replace employee_code value with the input that triggered the error
SELECT employee_id, employee_code, full_name, created_date
FROM employees
WHERE employee_code = 'EMP-0042'
ORDER BY created_date DESC;

-- Step 3: Check constraint status — both columns matter
SELECT
  c.constraint_name,
  c.constraint_type,
  c.status,
  c.validated,
  c.rely,
  c.last_change,
  col.column_name
FROM user_constraints c
JOIN user_cons_columns col
  ON col.constraint_name = c.constraint_name
WHERE c.table_name = 'EMPLOYEES'
  AND c.constraint_type IN ('P', 'U')
ORDER BY c.constraint_type, col.position;

-- If validated = NOT VALIDATED:
-- The constraint is marked ENABLED but is not enforcing existing data
-- Existing duplicates are possible even though status shows ENABLED

-- Step 4: Count all duplicates across the table
SELECT
  employee_code,
  COUNT(*)                                                      AS row_count,
  MIN(created_date)                                             AS oldest_row,
  MAX(created_date)                                             AS newest_row,
  LISTAGG(employee_id, ', ') WITHIN GROUP (ORDER BY created_date DESC) AS ids_newest_first
FROM employees
GROUP BY employee_code
HAVING COUNT(*) > 1
ORDER BY COUNT(*) DESC
FETCH FIRST 50 ROWS ONLY;

-- Step 5: Identify when duplicates were inserted
-- Group by date to identify the event that caused accumulation
SELECT
  TRUNC(created_date) AS insert_date,
  COUNT(*)            AS rows_inserted
FROM (
  -- Only rows that are duplicates
  SELECT e.employee_id, e.created_date
  FROM employees e
  WHERE e.employee_code IN (
    SELECT employee_code
    FROM employees
    GROUP BY employee_code
    HAVING COUNT(*) > 1
  )
)
GROUP BY TRUNC(created_date)
ORDER BY insert_date DESC;

-- If a single date shows a spike in inserts:
-- That date is your event — check deployment logs and bulk load history

-- Step 6: Verify the object compilation timestamp
-- If the package was recompiled after the error, line numbers in the stack may be stale
SELECT object_name, object_type, status, last_ddl_time
FROM all_objects
WHERE object_name = 'EMPLOYEE_PKG'
  AND object_type = 'PACKAGE BODY';

-- ============================================================
-- WHEN TOO_MANY_ROWS: correct handler pattern
-- ============================================================

CREATE OR REPLACE FUNCTION get_employee_by_code(
  p_employee_code IN VARCHAR2
) RETURN employees%ROWTYPE IS
  v_result employees%ROWTYPE;
BEGIN
  SELECT * INTO v_result
  FROM employees
  WHERE employee_code = p_employee_code;

  RETURN v_result;

EXCEPTION
  WHEN NO_DATA_FOUND THEN
    -- Zero rows: employee does not exist
    -- Decide here based on business semantics
    RAISE_APPLICATION_ERROR(
      -20002,
      'Employee not found: ' || p_employee_code
    );

  WHEN TOO_MANY_ROWS THEN
    -- Multiple rows: data model bug — unique constraint missing or disabled
    -- Include the duplicate key in the message — required for immediate diagnosis
    RAISE_APPLICATION_ERROR(
      -20003,
      'ORA-01422: duplicate employee_code detected: ' || p_employee_code
      || ' | Unique constraint on employees.employee_code is missing or disabled.'
      || ' | Query: SELECT COUNT(*) FROM employees WHERE employee_code = '''
      || p_employee_code || ''' to confirm.'
    );
    -- DO NOT return a default value here
    -- The caller would receive incorrect data with no indication of the problem
END get_employee_by_code;
/

-- ============================================================
-- Deduplication pattern: remove duplicates before adding constraint
-- Keeps the most recent row per employee_code
-- ============================================================

-- Step 1: Preview what will be deleted — review before executing
SELECT employee_id, employee_code, full_name, created_date,
       ROW_NUMBER() OVER (
         PARTITION BY employee_code
         ORDER BY created_date DESC, employee_id DESC
       ) AS rn
FROM employees
WHERE employee_code IN (
  SELECT employee_code FROM employees
  GROUP BY employee_code HAVING COUNT(*) > 1
)
ORDER BY employee_code, rn;

-- Step 2: Delete all but the most recent row per duplicate code
-- rn = 1 is the keeper; rn > 1 are the duplicates
DELETE FROM employees
WHERE employee_id IN (
  SELECT employee_id
  FROM (
    SELECT
      employee_id,
      ROW_NUMBER() OVER (
        PARTITION BY employee_code
        ORDER BY created_date DESC, employee_id DESC
      ) AS rn
    FROM employees
  )
  WHERE rn > 1
);

-- Step 3: Verify no duplicates remain before adding the constraint
SELECT COUNT(*) AS remaining_duplicates
FROM (
  SELECT employee_code FROM employees
  GROUP BY employee_code HAVING COUNT(*) > 1
);
-- Must return 0 before proceeding

-- Step 4: Add and validate the unique constraint
ALTER TABLE employees
ADD CONSTRAINT uk_employees_employee_code
UNIQUE (employee_code)
ENABLE VALIDATE;  -- VALIDATE explicitly — never NOVALIDATE for new constraints

-- Step 5: Confirm constraint is active and enforcing
SELECT constraint_name, status, validated
FROM user_constraints
WHERE constraint_name = 'UK_EMPLOYEES_EMPLOYEE_CODE';
-- Expected: status = ENABLED, validated = VALIDATED

COMMIT;

-- ============================================================
-- BULK COLLECT patterns for multi-row queries
-- Runnable on Oracle 19c, 21c, 23ai
-- ============================================================

-- ============================================================
-- Pattern 1: BULK COLLECT for a genuinely multi-row result
-- Use when the caller needs all matching rows
-- ============================================================

CREATE OR REPLACE FUNCTION get_active_addresses(
  p_customer_id IN NUMBER
) RETURN SYS_REFCURSOR IS
  v_cursor SYS_REFCURSOR;
BEGIN
  OPEN v_cursor FOR
    SELECT address_id, address_line1, city, postal_code, address_type
    FROM customer_addresses
    WHERE customer_id = p_customer_id
      AND active_flag = 'Y'
    ORDER BY address_type, created_date DESC;

  RETURN v_cursor;
  -- REF CURSOR: caller iterates all rows
  -- No ORA-01422 possible
END get_active_addresses;
/

-- ============================================================
-- Pattern 2: BULK COLLECT with ORDER BY — deterministic single row
-- Use when you want the most recent or highest-priority match
-- Replaces the SELECT INTO + ROWNUM = 1 anti-pattern
-- ============================================================

CREATE OR REPLACE FUNCTION get_current_price(
  p_product_id IN NUMBER,
  p_price_date IN DATE DEFAULT SYSDATE
) RETURN NUMBER IS
  TYPE price_tab IS TABLE OF NUMBER;
  v_prices price_tab;
BEGIN
  SELECT price
  BULK COLLECT INTO v_prices
  FROM product_prices
  WHERE product_id    = p_product_id
    AND effective_date <= p_price_date
    AND (expiry_date IS NULL OR expiry_date > p_price_date)
  ORDER BY effective_date DESC;  -- Most recent first
  -- No FETCH FIRST needed — we take element 1 below

  IF v_prices.COUNT = 0 THEN
    -- No price configured — this is a data error, not a default case
    RAISE_APPLICATION_ERROR(
      -20010,
      'No price configured for product_id = ' || p_product_id
      || ' as of ' || TO_CHAR(p_price_date, 'YYYY-MM-DD')
    );
  END IF;

  -- v_prices(1) is the most recent effective price — deterministic
  RETURN v_prices(1);
END get_current_price;
/

-- ============================================================
-- Pattern 3: Oracle 12c+ FETCH FIRST for simple deterministic lookups
-- Cleaner syntax for single-column fetches
-- ============================================================

DECLARE
  v_price NUMBER;
BEGIN
  SELECT price INTO v_price
  FROM product_prices
  WHERE product_id    = 42
    AND effective_date <= SYSDATE
    AND (expiry_date IS NULL OR expiry_date > SYSDATE)
  ORDER BY effective_date DESC
  FETCH FIRST 1 ROWS ONLY;
  -- FETCH FIRST 1 ROWS ONLY: returns at most one row
  -- Zero rows still raises ORA-01403 — handle WHEN NO_DATA_FOUND separately
  DBMS_OUTPUT.PUT_LINE('Current price: ' || v_price);
EXCEPTION
  WHEN NO_DATA_FOUND THEN
    DBMS_OUTPUT.PUT_LINE('No price configured for this date');
END;
/

-- ============================================================
-- Pattern 4: NVL + aggregate for scalar results
-- Never raises ORA-01403 or ORA-01422
-- Use when you need one computed value from many rows
-- ============================================================

CREATE OR REPLACE FUNCTION get_customer_total_balance(
  p_customer_id IN NUMBER
) RETURN NUMBER IS
  v_total NUMBER;
BEGIN
  SELECT NVL(SUM(balance), 0)
  INTO v_total
  FROM accounts
  WHERE customer_id = p_customer_id
    AND status = 'ACTIVE';
  -- SUM on zero rows returns NULL; NVL converts to 0
  -- SUM on multiple rows returns the aggregate — exactly one row always
  RETURN v_total;
END get_customer_total_balance;
/

-- ============================================================
-- The ROWNUM = 1 anti-pattern: non-deterministic results
-- ============================================================

-- Setup: two rows for the same employee_code
INSERT INTO demo_employees (employee_code, full_name, department, created_date)
VALUES ('EMP-003', 'Carol Davis', 'Operations', DATE '2024-01-15');

INSERT INTO demo_employees (employee_code, full_name, department, created_date)
VALUES ('EMP-003', 'Carol Davis (transferred)', 'Finance', DATE '2025-06-01');

COMMIT;

-- ============================================================
-- Anti-pattern: ROWNUM = 1 without ORDER BY
-- Which row comes back? Depends on the execution plan.
-- The answer changes with statistics updates, parallel query,
-- buffer cache state, and insert order.
-- ============================================================

DECLARE
  v_name VARCHAR2(100);
BEGIN
  SELECT full_name INTO v_name
  FROM demo_employees
  WHERE employee_code = 'EMP-003'
    AND ROWNUM = 1;  -- Non-deterministic: returns whichever row the plan finds first
  DBMS_OUTPUT.PUT_LINE('Got: ' || v_name);
  -- In dev: probably 'Carol Davis' (older, inserted first)
  -- In prod: could be either, depending on concurrent activity
END;
/

-- ============================================================
-- Correct alternative 1: FETCH FIRST with ORDER BY (Oracle 12c+)
-- Deterministic — always returns the most recent record
-- ============================================================

DECLARE
  v_name VARCHAR2(100);
BEGIN
  SELECT full_name INTO v_name
  FROM demo_employees
  WHERE employee_code = 'EMP-003'
  ORDER BY created_date DESC
  FETCH FIRST 1 ROWS ONLY;  -- Deterministic: most recent row
  DBMS_OUTPUT.PUT_LINE('Most recent: ' || v_name);
  -- Always returns 'Carol Davis (transferred)' — the June 2025 record
EXCEPTION
  WHEN NO_DATA_FOUND THEN
    DBMS_OUTPUT.PUT_LINE('Employee not found');
    -- FETCH FIRST 1 ROWS ONLY still raises ORA-01403 on zero rows
    -- Handle WHEN NO_DATA_FOUND as you would for any SELECT INTO
END;
/

-- ============================================================
-- Correct alternative 2: ROWNUM applied to an ordered subquery
-- Use when FETCH FIRST is not available (pre-12c)
-- ============================================================

DECLARE
  v_name VARCHAR2(100);
BEGIN
  SELECT full_name INTO v_name
  FROM (
    SELECT full_name
    FROM demo_employees
    WHERE employee_code = 'EMP-003'
    ORDER BY created_date DESC  -- ORDER BY inside the subquery
  )
  WHERE ROWNUM = 1;  -- Applied after ordering — deterministic
  DBMS_OUTPUT.PUT_LINE('Most recent: ' || v_name);
EXCEPTION
  WHEN NO_DATA_FOUND THEN
    DBMS_OUTPUT.PUT_LINE('Employee not found');
END;
/

-- ============================================================
-- The only real fix: add the unique constraint
-- ROWNUM = 1 is a workaround. The constraint is the fix.
-- ============================================================

-- First: remove the duplicate we inserted
DELETE FROM demo_employees
WHERE employee_id IN (
  SELECT employee_id FROM (
    SELECT employee_id,
           ROW_NUMBER() OVER (
             PARTITION BY employee_code
             ORDER BY created_date DESC, employee_id DESC
           ) AS rn
    FROM demo_employees
    WHERE employee_code = 'EMP-003'
  ) WHERE rn > 1
);
COMMIT;

-- Then: add the constraint so the duplicate cannot return
ALTER TABLE demo_employees
ADD CONSTRAINT uk_demo_employees_code
UNIQUE (employee_code)
ENABLE VALIDATE;

-- ============================================================
-- Complete unique constraint management workflow
-- ============================================================

-- Step 1: Audit existing duplicates
-- Always run this before attempting to add a unique constraint
SELECT
  employee_code,
  COUNT(*)                                                        AS duplicate_count,
  LISTAGG(employee_id, ', ') WITHIN GROUP (ORDER BY created_date DESC) AS ids_newest_first,
  MAX(created_date)                                               AS most_recent_insert
FROM employees
GROUP BY employee_code
HAVING COUNT(*) > 1
ORDER BY COUNT(*) DESC;

-- If this returns any rows, proceed to Step 2
-- If no rows, jump to Step 3

-- Step 2: Deduplicate — keep the most recent row per code
-- Preview first: verify the rows being deleted before executing
SELECT employee_id, employee_code, full_name, created_date,
       ROW_NUMBER() OVER (
         PARTITION BY employee_code
         ORDER BY created_date DESC, employee_id DESC
       ) AS rn  -- rn = 1 is the keeper; rn > 1 are duplicates
FROM employees
WHERE employee_code IN (
  SELECT employee_code FROM employees
  GROUP BY employee_code HAVING COUNT(*) > 1
)
ORDER BY employee_code, rn;

-- Execute deduplication:
DELETE FROM employees
WHERE employee_id IN (
  SELECT employee_id
  FROM (
    SELECT employee_id,
           ROW_NUMBER() OVER (
             PARTITION BY employee_code
             ORDER BY created_date DESC, employee_id DESC
           ) AS rn
    FROM employees
  )
  WHERE rn > 1
);

-- Verify: should return 0 before adding the constraint
SELECT COUNT(*) AS remaining_duplicates
FROM (SELECT employee_code FROM employees GROUP BY employee_code HAVING COUNT(*) > 1);

COMMIT;

-- Step 3: Add the unique constraint with ENABLE VALIDATE
-- ENABLE VALIDATE (default): verifies all existing rows
-- ENABLE NOVALIDATE: skips existing rows — never use for new constraints
ALTER TABLE employees
ADD CONSTRAINT uk_employees_employee_code
UNIQUE (employee_code)
ENABLE VALIDATE;  -- Explicit for clarity — do not rely on the default

-- Step 4: Verify constraint is ENABLED and VALIDATED
SELECT constraint_name, status, validated, last_change
FROM user_constraints
WHERE constraint_name = 'UK_EMPLOYEES_EMPLOYEE_CODE';
-- Required output: status = ENABLED, validated = VALIDATED

-- Step 5: Re-enable a disabled constraint after a bulk load
-- (The correct way — not ENABLE NOVALIDATE)

-- If duplicates were introduced during the bulk load:
DELETE FROM employees   -- Deduplicate first (Step 2 above)
WHERE employee_id IN (...);
COMMIT;

-- Then re-enable with full validation:
ALTER TABLE employees
ENABLE VALIDATE CONSTRAINT uk_employees_employee_code;

-- Step 6: Monitor constraint status — run daily via scheduled job
-- Alert if any unique constraint is not ENABLED + VALIDATED
SELECT
  table_name,
  constraint_name,
  status,
  validated,
  last_change
FROM user_constraints
WHERE constraint_type = 'U'
  AND (status != 'ENABLED' OR validated != 'VALIDATED')
ORDER BY table_name, constraint_name;
-- Zero rows expected — any result is an alert condition

-- ============================================================
-- Oracle 23ai: SQL Domains for reusable column constraints
-- Provides an additional enforcement layer above the table constraint
-- ============================================================

-- Create a domain that enforces uniqueness semantics at the type level
-- (Oracle 23ai feature — requires 23ai or later)
CREATE DOMAIN employee_code_domain AS VARCHAR2(20)
  NOT NULL
  CONSTRAINT chk_employee_code_format CHECK (REGEXP_LIKE(VALUE, '^EMP-[0-9]{3,}$'));

-- Apply the domain to the column
ALTER TABLE employees
MODIFY employee_code employee_code_domain;
-- The CHECK constraint from the domain is now enforced at insert time
-- The unique constraint (Step 3 above) is still required for uniqueness
-- Domains add format validation; constraints add uniqueness enforcement

-- ============================================================
-- Layer 1: Constraint status monitoring
-- Schedule every 5 minutes via DBMS_SCHEDULER
-- ============================================================

CREATE OR REPLACE PROCEDURE monitor_constraint_status IS
  v_disabled_count NUMBER;
  v_detail         VARCHAR2(4000);
BEGIN
  -- Count disabled or unvalidated unique constraints
  SELECT COUNT(*)
  INTO v_disabled_count
  FROM user_constraints
  WHERE constraint_type = 'U'
    AND (status != 'ENABLED' OR validated != 'VALIDATED');

  IF v_disabled_count > 0 THEN
    -- Build detail list for the alert
    SELECT LISTAGG(
      table_name || '.' || constraint_name
      || ' [' || status || '/' || validated || ']', ', '
    ) WITHIN GROUP (ORDER BY table_name)
    INTO v_detail
    FROM user_constraints
    WHERE constraint_type = 'U'
      AND (status != 'ENABLED' OR validated != 'VALIDATED');

    -- Log and raise — will trigger your alerting integration
    RAISE_APPLICATION_ERROR(
      -20050,
      v_disabled_count || ' unique constraint(s) are not fully active: ' || v_detail
    );
  END IF;
END monitor_constraint_status;
/

-- Schedule via DBMS_SCHEDULER
BEGIN
  DBMS_SCHEDULER.CREATE_JOB(
    job_name        => 'JOB_MONITOR_CONSTRAINTS',
    job_type        => 'STORED_PROCEDURE',
    job_action      => 'MONITOR_CONSTRAINT_STATUS',
    start_date      => SYSTIMESTAMP,
    repeat_interval => 'FREQ=MINUTELY; INTERVAL=5',
    enabled         => TRUE,
    comments        => 'Alerts when unique constraints are disabled or unvalidated'
  );
END;
/

-- ============================================================
-- Layer 2: Daily duplicate detection
-- Catches duplicates that bypassed constraint enforcement
-- Schedule once daily via DBMS_SCHEDULER
-- ============================================================

CREATE OR REPLACE PROCEDURE detect_critical_duplicates IS
  v_sql       VARCHAR2(4000);
  v_dup_count NUMBER;

  -- Define critical tables and columns to check
  -- Update this list when new business-key columns are added
  TYPE check_rec IS RECORD (
    tbl_name VARCHAR2(128),
    col_name VARCHAR2(128)
  );
  TYPE check_tab IS TABLE OF check_rec;
  v_checks check_tab;
BEGIN
  v_checks := check_tab(
    check_rec('EMPLOYEES',  'EMPLOYEE_CODE'),
    check_rec('ACCOUNTS',   'ACCOUNT_NUMBER'),
    check_rec('PRODUCTS',   'PRODUCT_SKU'),
    check_rec('CUSTOMERS',  'EMAIL'),
    check_rec('ORDERS',     'ORDER_REFERENCE')
  );

  FOR i IN 1 .. v_checks.COUNT LOOP
    -- Validate names against the data dictionary before concatenation
    v_sql :=
      'SELECT COUNT(*) FROM (' ||
      '  SELECT ' || DBMS_ASSERT.SIMPLE_SQL_NAME(v_checks(i).col_name) ||
      '  FROM '  || DBMS_ASSERT.SQL_OBJECT_NAME(v_checks(i).tbl_name) ||
      '  GROUP BY ' || DBMS_ASSERT.SIMPLE_SQL_NAME(v_checks(i).col_name) ||
      '  HAVING COUNT(*) > 1' ||
      ')';

    EXECUTE IMMEDIATE v_sql INTO v_dup_count;

    IF v_dup_count > 0 THEN
      RAISE_APPLICATION_ERROR(
        -20051,
        'Duplicate values detected: ' || v_dup_count ||
        ' duplicate value(s) in ' ||
        v_checks(i).tbl_name || '.' || v_checks(i).col_name ||
        ' | These will cause ORA-01422 on SELECT INTO queries against this column.'
      );
    END IF;
  END LOOP;

  DBMS_OUTPUT.PUT_LINE('Duplicate detection: all checks passed');
END detect_critical_duplicates;
/

-- ============================================================
-- Layer 3: Error log analysis
-- Run weekly to identify recurring ORA-01422 patterns
-- ============================================================

-- Recurring ORA-01422 by module — identifies affected procedures
SELECT
  module,
  COUNT(*)        AS error_count,
  MIN(created_at) AS first_occurrence,
  MAX(created_at) AS last_occurrence,
  ROUND(
    COUNT(*) / NULLIF(
      EXTRACT(DAY FROM (MAX(created_at) - MIN(created_at))), 0
    ), 1
  )               AS errors_per_day
FROM app_error_log
WHERE error_stack LIKE '%ORA-01422%'
  AND created_at > SYSTIMESTAMP - INTERVAL '30' DAY
GROUP BY module
ORDER BY COUNT(*) DESC;

-- ORA-01422 spike detection — correlates with deployment events
SELECT
  TRUNC(created_at, 'HH') AS hour_bucket,
  COUNT(*)                AS error_count
FROM app_error_log
WHERE error_stack LIKE '%ORA-01422%'
  AND created_at > SYSTIMESTAMP - INTERVAL '7' DAY
GROUP BY TRUNC(created_at, 'HH')
ORDER BY hour_bucket DESC;

============================================================
JAVA / JDBC — ORA-01422 handling
============================================================

try {
    String name = employeeRepo.getByCode("EMP-0042");
} catch (SQLException e) {
    switch (e.getErrorCode()) {
        case 1422:
            // ORA-01422: TOO_MANY_ROWS
            // Data model bug — unique constraint missing or disabled
            // This is a 500, not a 400 or 404
            logger.error("Data integrity violation: ORA-01422", Map.of(
                "oraCode",   e.getErrorCode(),
                "message",   e.getMessage(),  // Includes employee_code if handler adds it
                "procedure", "getByCode"
            ));
            // Alert the on-call team
            alertingService.critical("ORA-01422: duplicate data in employees table");
            throw new DataIntegrityException("Duplicate records found", e);

        case 1403:
            // ORA-01403: NO_DATA_FOUND — separate case, different handling
            throw new NotFoundException("Employee not found", e);

        default:
            logger.error("Unexpected Oracle error",
                Map.of("oraCode", e.getErrorCode(), "message", e.getMessage()));
            throw e;
    }
}


============================================================
PYTHON / python-oracledb — ORA-01422 handling
============================================================

import oracledb

try:
    employee = employee_repo.get_by_code('EMP-0042')
except oracledb.DatabaseError as e:
    error = e.args[0]
    if error.code == 1422:
        # ORA-01422: TOO_MANY_ROWS
        logger.error('Data integrity violation: ORA-01422', extra={
            'ora_code':  error.code,
            'message':   error.message,
            'procedure': 'get_by_code'
        })
        alert_team('critical', f'ORA-01422: duplicates in employees')
        raise DataIntegrityError('Duplicate records found') from e
    elif error.code == 1403:
        raise RecordNotFoundError('Employee not found') from e
    else:
        raise


============================================================
NODE.JS / node-oracledb — ORA-01422 handling
============================================================

const oracledb = require('oracledb');

try {
    const employee = await employeeRepo.getByCode('EMP-0042');
} catch (err) {
    if (err.errorNum === 1422) {
        // ORA-01422: TOO_MANY_ROWS
        console.error(JSON.stringify({
            level:     'critical',
            oraCode:   err.errorNum,
            message:   err.message,
            procedure: 'getByCode'
        }));
        await alertingService.critical('ORA-01422: duplicate employee records');
        throw new DataIntegrityError('Duplicate records found', { cause: err });
    } else if (err.errorNum === 1403) {
        throw new NotFoundError('Employee not found', { cause: err });
    } else {
        throw err;
    }
}


============================================================
Key points across all drivers:
============================================================
1. Error code 1422 = ORA-01422 (TOO_MANY_ROWS)
2. Error code 1403 = ORA-01403 (NO_DATA_FOUND)
3. Handle each specifically — they require different responses
4. ORA-01422 is always HTTP 500 — it is a server-side data integrity failure
5. ORA-01403 may be HTTP 404 or HTTP 500 depending on business semantics
6. Preserve the Oracle error code through every abstraction layer
7. Log the full Oracle error message — it contains the diagnostic context