ORA-06512 Is Not an Error — It's a Stack Trace Indicator

-- Example error stack (read bottom-up for call order):
-- ORA-01403: no data found
-- ORA-06512: at "IO.THECODEFORGE.ORDER_PROCESSOR", line 142
-- ORA-06512: at "IO.THECODEFORGE.BATCH_RUNNER", line 87
-- ORA-06512: at line 1
--
-- Translation: anonymous block (line 1) called BATCH_RUNNER (line 87)
-- which called ORDER_PROCESSOR (line 142), which raised ORA-01403.

-- Robust primary error extractor
CREATE OR REPLACE FUNCTION io.thecodeforge.util.parse_primary_error(
  p_error_stack IN VARCHAR2
) RETURN VARCHAR2 DETERMINISTIC IS
  v_match  VARCHAR2(20);
  v_pos    PLS_INTEGER := 1;
  v_idx    PLS_INTEGER := 0;
BEGIN
  -- Walk through all ORA- codes in order and return the first
  -- one that is not ORA-06512
  LOOP
    v_idx := v_idx + 1;
    v_match := REGEXP_SUBSTR(
      p_error_stack,
      'ORA-[0-9]{5}',
      1,
      v_idx
    );
    
    EXIT WHEN v_match IS NULL;            -- no more codes
    EXIT WHEN v_match != 'ORA-06512';     -- found the real error
  END LOOP;
  
  RETURN NVL(v_match, 'UNKNOWN');         -- defensive fallback
END parse_primary_error;
/

-- Comprehensive exception handler pattern for production procedures
CREATE OR REPLACE PROCEDURE io.thecodeforge.example.guarded_proc IS
  v_primary VARCHAR2(20);
BEGIN
  -- ... business logic ...
  NULL;
EXCEPTION WHEN OTHERS THEN
  v_primary := io.thecodeforge.util.parse_primary_error(SQLERRM);
  INSERT INTO io.thecodeforge.logging.error_log (
    session_id,
    primary_error,
    full_stack,
    backtrace,
    object_name,
    error_line,
    call_stack,
    created_at
  ) VALUES (
    SYS_CONTEXT('USERENV','SESSIONID'),
    v_primary,
    DBMS_UTILITY.FORMAT_ERROR_STACK,       -- up to 2000 bytes
    DBMS_UTILITY.FORMAT_ERROR_BACKTRACE,   -- exact line trace
    $$PLSQL_UNIT,
    $$PLSQL_LINE,
    DBMS_UTILITY.FORMAT_CALL_STACK,        -- who called whom
    SYSTIMESTAMP
  );
  RAISE;  -- ALWAYS re-raise after logging
END guarded_proc;
/

-- ORA-00001: unique constraint violation
-- Pattern: individual row handling with constraint name extraction
CREATE OR REPLACE PROCEDURE io.thecodeforge.etl.insert_order(
  p_order_id   IN NUMBER,
  p_customer_id IN NUMBER,
  p_amount      IN NUMBER
) IS
  v_constraint_name VARCHAR2(128);
BEGIN
  INSERT INTO io.thecodeforge.orders (
    order_id, customer_id, order_amount, created_at
  ) VALUES (
    p_order_id, p_customer_id, p_amount, SYSTIMESTAMP
  );
EXCEPTION
  WHEN DUP_VAL_ON_INDEX THEN
    -- Extract constraint name from SQLERRM for precise diagnosis
    v_constraint_name := REGEXP_SUBSTR(
      SQLERRM, '\(([^)]+)\)', 1, 1, NULL, 1
    );
    io.thecodeforge.logging.log_error(
      p_error_type  => 'UNIQUE_VIOLATION',
      p_message     => 'Constraint: ' || v_constraint_name ||
                       ' OrderID: ' || p_order_id,
      p_stack       => DBMS_UTILITY.FORMAT_ERROR_BACKTRACE
    );
    -- Decide: skip duplicate or raise to caller
    RAISE;
END insert_order;
/

-- ORA-02291: integrity constraint violated - parent key not found
-- Pattern: pre-validation for FK relationships in batch loads
CREATE OR REPLACE PROCEDURE io.thecodeforge.etl.load_order_items IS
  v_orphan_count NUMBER;
BEGIN
  -- Identify orphaned staging records BEFORE attempting inserts
  INSERT INTO io.thecodeforge.staging.rejected_items (
    order_id, product_id, reject_reason, rejected_at
  )
  SELECT s.order_id, s.product_id,
         'Parent order not found', SYSTIMESTAMP
  FROM io.thecodeforge.staging.raw_items s
  WHERE NOT EXISTS (
    SELECT 1 FROM io.thecodeforge.orders o
    WHERE o.order_id = s.order_id
  );
  
  v_orphan_count := SQL%ROWCOUNT;
  
  IF v_orphan_count > 0 THEN
    io.thecodeforge.logging.log_warning(
      'FK_PRECHECK',
      v_orphan_count || ' orphaned items moved to rejected_items'
    );
  END IF;
  
  -- Now insert only valid items
  INSERT INTO io.thecodeforge.order_items (
    order_id, product_id, quantity, unit_price
  )
  SELECT s.order_id, s.product_id, s.quantity, s.unit_price
  FROM io.thecodeforge.staging.raw_items s
  WHERE EXISTS (
    SELECT 1 FROM io.thecodeforge.orders o
    WHERE o.order_id = s.order_id
  );
  
  COMMIT;
END load_order_items;
/

-- ORA-02292: integrity constraint violated - child record found
-- Pattern: safe delete with dependency check
CREATE OR REPLACE PROCEDURE io.thecodeforge.service.delete_customer(
  p_customer_id IN NUMBER
) IS
  v_child_count NUMBER;
BEGIN
  SELECT COUNT(*) INTO v_child_count
  FROM io.thecodeforge.orders
  WHERE customer_id = p_customer_id;
  
  IF v_child_count > 0 THEN
    RAISE_APPLICATION_ERROR(-20010,
      'Cannot delete customer ' || p_customer_id ||
      ': ' || v_child_count || ' orders exist. '
      || 'Archive orders first.');
  END IF;
  
  DELETE FROM io.thecodeforge.customers
  WHERE customer_id = p_customer_id;
END delete_customer;
/

-- ORA-01000: maximum open cursors exceeded
-- Root cause analysis query
SELECT s.sid, s.serial#, s.username, s.program,
       COUNT(*) AS open_cursor_count
FROM v$open_cursor oc
JOIN v$session s ON oc.sid = s.sid
GROUP BY s.sid, s.serial#, s.username, s.program
HAVING COUNT(*) > 200  -- typical OPEN_CURSORS default is 300
ORDER BY open_cursor_count DESC;

-- Leak-proof cursor pattern
CREATE OR REPLACE PROCEDURE io.thecodeforge.etl.process_large_dataset IS
  CURSOR c_data IS
    SELECT /*+ PARALLEL(4) */
           record_id, payload, created_at
    FROM io.thecodeforge.staging.raw_data
    WHERE status = 'PENDING'
    ORDER BY created_at;
  
  TYPE t_batch IS TABLE OF c_data%ROWTYPE;
  v_batch  t_batch;
  v_limit  PLS_INTEGER := 5000;
  v_total  PLS_INTEGER := 0;
BEGIN
  OPEN c_data;
  LOOP
    FETCH c_data BULK COLLECT INTO v_batch LIMIT v_limit;
    EXIT WHEN v_batch.COUNT = 0;
    
    FORALL i IN 1..v_batch.COUNT
      INSERT INTO io.thecodeforge.processed_data (
        record_id, payload, processed_at
      ) VALUES (
        v_batch(i).record_id,
        v_batch(i).payload,
        SYSTIMESTAMP
      );
    
    -- Commit in batches to prevent ORA-01555
    COMMIT;
    v_total := v_total + v_batch.COUNT;
    
    io.thecodeforge.logging.log_progress(
      'PROCESS_LARGE_DATASET',
      v_total || ' rows processed'
    );
  END LOOP;
  CLOSE c_data;
  
  io.thecodeforge.logging.log_info(
    'PROCESS_LARGE_DATASET',
    'Completed. Total rows: ' || v_total
  );
EXCEPTION
  WHEN OTHERS THEN
    IF c_data%ISOPEN THEN
      CLOSE c_data;
    END IF;
    io.thecodeforge.logging.log_error(
      'PROCESS_LARGE_DATASET',
      DBMS_UTILITY.FORMAT_ERROR_STACK
    );
    RAISE;
END process_large_dataset;
/

-- ORA-01555 prevention: check undo retention and usage
SELECT tablespace_name,
       ROUND(SUM(bytes) / 1024 / 1024) AS undo_mb,
       COUNT(*) AS extent_count
FROM dba_undo_extents
GROUP BY tablespace_name;

SELECT name, value
FROM v$parameter
WHERE name IN ('undo_retention', 'undo_tablespace', 'undo_management');

-- ORA-01403: NO_DATA_FOUND — robust lookup pattern
CREATE OR REPLACE FUNCTION io.thecodeforge.service.get_customer_name(
  p_customer_id IN NUMBER
) RETURN VARCHAR2 IS
  v_name VARCHAR2(200);
BEGIN
  SELECT customer_name INTO v_name
  FROM io.thecodeforge.customers
  WHERE customer_id = p_customer_id;
  
  RETURN v_name;
EXCEPTION
  WHEN NO_DATA_FOUND THEN
    -- Decision point: is a missing customer a business error or expected?
    -- Option A: return NULL and let the caller decide
    RETURN NULL;
    -- Option B: raise with context (uncomment if missing customer is an error)
    -- RAISE_APPLICATION_ERROR(-20001,
    --   'Customer not found: ' || p_customer_id);
  WHEN TOO_MANY_ROWS THEN
    -- This should never happen if customer_id is PK — indicates corruption
    io.thecodeforge.logging.log_error(
      'DATA_INTEGRITY',
      'Multiple rows for customer_id: ' || p_customer_id
    );
    RAISE_APPLICATION_ERROR(-20002,
      'Data integrity violation: multiple customers for ID ' ||
      p_customer_id || '. Check unique constraint on CUSTOMERS.CUSTOMER_ID.');
END get_customer_name;
/

-- ORA-06502: VALUE_ERROR prevention with defensive sizing
CREATE OR REPLACE PROCEDURE io.thecodeforge.etl.transform_address(
  p_raw_address IN VARCHAR2,
  p_city        OUT VARCHAR2,   -- target column is VARCHAR2(100)
  p_state       OUT VARCHAR2    -- target column is VARCHAR2(50)
) IS
  v_delimiter_pos PLS_INTEGER;
BEGIN
  v_delimiter_pos := INSTR(p_raw_address, ',');
  
  IF v_delimiter_pos = 0 THEN
    -- No delimiter — entire string is city, state is unknown
    p_city := SUBSTR(p_raw_address, 1, 100);   -- defensive SUBSTR
    p_state := NULL;
  ELSE
    p_city  := SUBSTR(TRIM(SUBSTR(p_raw_address, 1, v_delimiter_pos - 1)), 1, 100);
    p_state := SUBSTR(TRIM(SUBSTR(p_raw_address, v_delimiter_pos + 1)), 1, 50);
  END IF;
  
  IF LENGTH(p_raw_address) > 100 THEN
    io.thecodeforge.logging.log_warning(
      'TRUNCATION',
      'Address truncated from ' || LENGTH(p_raw_address) ||
      ' to 100 chars. Original: ' || SUBSTR(p_raw_address, 1, 200)
    );
  END IF;
END transform_address;
/

-- ORA-06550: compilation error in dynamic SQL — log the SQL text
CREATE OR REPLACE PROCEDURE io.thecodeforge.util.exec_dynamic(
  p_sql IN VARCHAR2
) IS
BEGIN
  EXECUTE IMMEDIATE p_sql;
EXCEPTION
  WHEN OTHERS THEN
    io.thecodeforge.logging.log_error(
      'DYNAMIC_SQL_FAILURE',
      'SQLCODE: ' || SQLCODE || CHR(10) ||
      'SQL: ' || SUBSTR(p_sql, 1, 4000) || CHR(10) ||
      'Stack: ' || DBMS_UTILITY.FORMAT_ERROR_BACKTRACE
    );
    RAISE;
END exec_dynamic;
/

-- ORA-01476: ZERO_DIVIDE prevention
CREATE OR REPLACE FUNCTION io.thecodeforge.util.safe_divide(
  p_numerator   IN NUMBER,
  p_denominator IN NUMBER,
  p_default     IN NUMBER DEFAULT 0
) RETURN NUMBER DETERMINISTIC IS
BEGIN
  IF p_denominator = 0 OR p_denominator IS NULL THEN
    RETURN p_default;
  END IF;
  RETURN p_numerator / p_denominator;
END safe_divide;
/

-- ORA-04068 detection and automatic retry pattern
CREATE OR REPLACE PROCEDURE io.thecodeforge.service.resilient_call IS
  v_retry_count PLS_INTEGER := 0;
  c_max_retries CONSTANT PLS_INTEGER := 3;
  e_package_state_discarded EXCEPTION;
  PRAGMA EXCEPTION_INIT(e_package_state_discarded, -4068);
BEGIN
  LOOP
    BEGIN
      io.thecodeforge.core.process_batch;  -- the real work
      EXIT;  -- success — leave the retry loop
    EXCEPTION
      WHEN e_package_state_discarded THEN
        v_retry_count := v_retry_count + 1;
        IF v_retry_count <= c_max_retries THEN
          io.thecodeforge.logging.log_warning(
            'ORA-04068',
            'Package state discarded. Retry ' || v_retry_count ||
            ' of ' || c_max_retries ||
            '. Object: ' || $$PLSQL_UNIT
          );
          -- Re-execution reinitializes the package state automatically
        ELSE
          io.thecodeforge.logging.log_error(
            'ORA-04068',
            'Max retries exhausted after ' || c_max_retries ||
            ' attempts. Raising to caller.'
          );
          RAISE;
        END IF;
    END;
  END LOOP;
END resilient_call;
/

-- ORA-04030 prevention: monitor PGA memory usage
SELECT s.sid, s.serial#, s.username, s.module,
       ROUND(p.pga_used_mem / 1024 / 1024, 1)  AS pga_used_mb,
       ROUND(p.pga_alloc_mem / 1024 / 1024, 1) AS pga_alloc_mb,
       ROUND(p.pga_max_mem / 1024 / 1024, 1)   AS pga_max_mb
FROM v$session s
JOIN v$process p ON s.paddr = p.addr
WHERE p.pga_alloc_mem > 100 * 1024 * 1024  -- > 100 MB
ORDER BY p.pga_alloc_mem DESC;

-- ORA-04031 diagnosis: shared pool free memory and fragmentation
SELECT pool, name,
       ROUND(bytes / 1024 / 1024, 1) AS mb
FROM v$sgastat
WHERE pool = 'shared pool'
  AND name IN ('free memory', 'library cache', 'sql area')
ORDER BY bytes DESC;

-- Check for hard parsing (non-bind-variable SQL)
SELECT force_matching_signature,
       COUNT(*) AS versions,
       MIN(sql_text) AS sample_sql
FROM v$sql
GROUP BY force_matching_signature
HAVING COUNT(*) > 50
ORDER BY versions DESC
FETCH FIRST 10 ROWS ONLY;

-- ORA-00060: deadlock retry pattern
CREATE OR REPLACE PROCEDURE io.thecodeforge.service.update_inventory(
  p_product_id IN NUMBER,
  p_quantity   IN NUMBER
) IS
  v_retry_count PLS_INTEGER := 0;
  c_max_retries CONSTANT PLS_INTEGER := 3;
  c_retry_delay CONSTANT NUMBER := 0.5;  -- seconds
  e_deadlock EXCEPTION;
  PRAGMA EXCEPTION_INIT(e_deadlock, -60);
BEGIN
  LOOP
    BEGIN
      UPDATE io.thecodeforge.inventory
      SET quantity_on_hand = quantity_on_hand - p_quantity,
          last_updated = SYSTIMESTAMP
      WHERE product_id = p_product_id
        AND quantity_on_hand >= p_quantity;
      
      IF SQL%ROWCOUNT = 0 THEN
        RAISE_APPLICATION_ERROR(-20003,
          'Insufficient inventory for product ' || p_product_id ||
          '. Requested: ' || p_quantity);
      END IF;
      
      EXIT;  -- success
    EXCEPTION
      WHEN e_deadlock THEN
        v_retry_count := v_retry_count + 1;
        IF v_retry_count <= c_max_retries THEN
          io.thecodeforge.logging.log_warning(
            'DEADLOCK',
            'Deadlock on product ' || p_product_id ||
            '. Retry ' || v_retry_count || ' of ' || c_max_retries
          );
          DBMS_SESSION.SLEEP(c_retry_delay * v_retry_count);
        ELSE
          RAISE;
        END IF;
    END;
  END LOOP;
END update_inventory;
/

-- ORA-00054: lock detection before DDL
-- Check for active locks before attempting partition maintenance
SELECT l.session_id, l.oracle_username, l.os_user_name,
       o.object_name, o.object_type,
       l.locked_mode,
       s.sql_id, s.status, s.last_call_et AS seconds_in_wait
FROM dba_locks l
JOIN dba_objects o ON l.object_id = o.object_id
JOIN v$session s ON l.session_id = s.sid
WHERE o.object_name = 'ORDERS'
ORDER BY l.locked_mode DESC;

-- Deadlock trace file location
SELECT value AS trace_dir
FROM v$diag_info
WHERE name = 'Default Trace File';

-- Distinguish missing object from missing privilege
-- (Must be run by a DBA or user with SELECT on DBA_ views)
CREATE OR REPLACE PROCEDURE io.thecodeforge.util.diagnose_942(
  p_object_name IN VARCHAR2,
  p_schema      IN VARCHAR2 DEFAULT USER
) IS
  v_exists      NUMBER;
  v_grant_count NUMBER;
BEGIN
  -- Step 1: does the object exist at all?
  SELECT COUNT(*) INTO v_exists
  FROM dba_objects
  WHERE object_name = UPPER(p_object_name)
    AND owner = UPPER(p_schema)
    AND object_type IN ('TABLE','VIEW','SYNONYM','MATERIALIZED VIEW');
  
  IF v_exists = 0 THEN
    DBMS_OUTPUT.PUT_LINE(
      'DIAGNOSIS: Object ' || UPPER(p_schema) || '.' ||
      UPPER(p_object_name) || ' does not exist.'
    );
    -- Check for synonyms that might point to it
    SELECT COUNT(*) INTO v_exists
    FROM dba_synonyms
    WHERE synonym_name = UPPER(p_object_name);
    IF v_exists > 0 THEN
      DBMS_OUTPUT.PUT_LINE(
        'NOTE: A synonym named ' || UPPER(p_object_name) ||
        ' exists. Check if it points to the correct schema.'
      );
    END IF;
    RETURN;
  END IF;
  
  -- Step 2: object exists — check direct grants
  SELECT COUNT(*) INTO v_grant_count
  FROM dba_tab_privs
  WHERE table_name = UPPER(p_object_name)
    AND owner = UPPER(p_schema)
    AND grantee = USER;
  
  DBMS_OUTPUT.PUT_LINE(
    'Object exists. Direct grants to ' || USER || ': ' || v_grant_count
  );
  
  -- Step 3: check role grants (won't work in definer's rights)
  FOR rec IN (
    SELECT granted_role FROM dba_role_privs WHERE grantee = USER
  ) LOOP
    SELECT COUNT(*) INTO v_exists
    FROM dba_tab_privs
    WHERE table_name = UPPER(p_object_name)
      AND owner = UPPER(p_schema)
      AND grantee = rec.granted_role;
    IF v_exists > 0 THEN
      DBMS_OUTPUT.PUT_LINE(
        'WARNING: Grant exists via role ' || rec.granted_role ||
        ' — this will NOT work inside definer''s rights procedures'
      );
    END IF;
  END LOOP;
END diagnose_942;
/

-- Deployment grant verification script
-- Run after every deployment to catch missing grants
SELECT o.owner, o.object_name, o.object_type,
       'MISSING GRANT' AS issue
FROM dba_objects o
WHERE o.owner = 'IO'
  AND o.object_type IN ('TABLE','VIEW')
  AND o.created > SYSDATE - 1  -- objects created in last 24 hours
  AND NOT EXISTS (
    SELECT 1 FROM dba_tab_privs p
    WHERE p.owner = o.owner
      AND p.table_name = o.object_name
      AND p.grantee = 'APP_USER'  -- your application schema
  )
ORDER BY o.created DESC;

-- ORA-01722: invalid number — safe conversion pattern
CREATE OR REPLACE FUNCTION io.thecodeforge.util.safe_to_number(
  p_value   IN VARCHAR2,
  p_default IN NUMBER DEFAULT NULL
) RETURN NUMBER DETERMINISTIC IS
BEGIN
  RETURN TO_NUMBER(
    REGEXP_REPLACE(p_value, '[^0-9.\-]', '')  -- strip non-numeric chars
  );
EXCEPTION
  WHEN VALUE_ERROR THEN
    RETURN p_default;
END safe_to_number;
/

-- ORA-01830/01858/01861: date conversion — defensive parsing
CREATE OR REPLACE FUNCTION io.thecodeforge.util.safe_to_date(
  p_value  IN VARCHAR2,
  p_format IN VARCHAR2 DEFAULT 'YYYY-MM-DD'
) RETURN DATE DETERMINISTIC IS
  v_date DATE;
BEGIN
  -- Try the expected format first
  v_date := TO_DATE(p_value, p_format);
  RETURN v_date;
EXCEPTION
  WHEN OTHERS THEN
    -- Try ISO 8601 fallback
    BEGIN
      v_date := TO_DATE(p_value, 'YYYY-MM-DD"T"HH24:MI:SS');
      RETURN v_date;
    EXCEPTION
      WHEN OTHERS THEN
        -- Try US format fallback
        BEGIN
          v_date := TO_DATE(p_value, 'MM/DD/YYYY');
          RETURN v_date;
        EXCEPTION
          WHEN OTHERS THEN
            io.thecodeforge.logging.log_warning(
              'DATE_PARSE_FAILURE',
              'Cannot parse: ' || SUBSTR(p_value, 1, 100) ||
              ' with format ' || p_format
            );
            RETURN NULL;
        END;
    END;
END safe_to_date;
/

-- Pre-validation: find bad data before it hits your DML
SELECT record_id, amount_text,
       CASE WHEN REGEXP_LIKE(amount_text, '^-?[0-9]+(\.[0-9]+)?$')
            THEN 'VALID' ELSE 'INVALID' END AS validation_status
FROM io.thecodeforge.staging.raw_transactions
WHERE NOT REGEXP_LIKE(NVL(amount_text,'X'), '^-?[0-9]+(\.[0-9]+)?$');

-- ORA-30926: fix by deduplicating the source
-- BAD: source has duplicate customer_ids
MERGE INTO io.thecodeforge.customers tgt
USING io.thecodeforge.staging.customer_updates src
  ON (tgt.customer_id = src.customer_id)
WHEN MATCHED THEN UPDATE SET tgt.email = src.email
WHEN NOT MATCHED THEN INSERT (customer_id, email)
  VALUES (src.customer_id, src.email);
-- Fails with ORA-30926 if staging has duplicate customer_ids

-- GOOD: deduplicated source
MERGE INTO io.thecodeforge.customers tgt
USING (
  SELECT customer_id, email
  FROM (
    SELECT customer_id, email,
           ROW_NUMBER() OVER (
             PARTITION BY customer_id
             ORDER BY updated_at DESC   -- keep most recent
           ) AS rn
    FROM io.thecodeforge.staging.customer_updates
  )
  WHERE rn = 1
) src
  ON (tgt.customer_id = src.customer_id)
WHEN MATCHED THEN UPDATE SET tgt.email = src.email
WHEN NOT MATCHED THEN INSERT (customer_id, email)
  VALUES (src.customer_id, src.email);

-- Pre-check for duplicates before running MERGE
SELECT customer_id, COUNT(*) AS dup_count
FROM io.thecodeforge.staging.customer_updates
GROUP BY customer_id
HAVING COUNT(*) > 1
ORDER BY dup_count DESC;