Introduction Oracle ORA-06502 `PL/SQL: numeric or value error` occurs when a PL/SQL variable cannot hold the assigned value due to size mismatch, conversion failure, or NULL assignment to a NOT NULL variable. These errors commonly surface in production when input data exceeds expected sizes or formats.

Symptoms - `ORA-06502: PL/SQL: numeric or value error: character string buffer too small` - `ORA-06502: PL/SQL: numeric or value error: character to number conversion error` - `ORA-06502: PL/SQL: numeric or value error: NULL index table key value` - Stored procedure fails with no clear indication of which variable caused the error - Error only occurs with specific data inputs, not reproducible in testing

Common Causes - VARCHAR2 variable too small for the input data - Implicit conversion from string to number failing on non-numeric data - Assigning NULL to a variable declared as NOT NULL - Bulk collect into a collection that exceeds the defined size - Character set conversion issues (UTF-8 multi-byte characters)

Step-by-Step Fix 1. **Enable detailed error tracing": ```sql -- Enable PL/SQL warning and error tracing ALTER SESSION SET PLSQL_WARNINGS = 'ENABLE:ALL'; ALTER SESSION SET PLSCOPE_SETTINGS = 'IDENTIFIERS:ALL';

-- Check the error stack in the exception handler -- DBMS_UTILITY.FORMAT_ERROR_BACKTRACE ```

  1. 1.**Identify the specific error in the backtrace":
  2. 2.```sql
  3. 3.CREATE OR REPLACE PROCEDURE safe_procedure AS
  4. 4.BEGIN
  5. 5.-- Your code
  6. 6.NULL;
  7. 7.EXCEPTION
  8. 8.WHEN OTHERS THEN
  9. 9.DBMS_OUTPUT.PUT_LINE('Error: ' || SQLERRM);
  10. 10.DBMS_OUTPUT.PUT_LINE('Backtrace: ' || DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);
  11. 11.DBMS_OUTPUT.PUT_LINE('Call Stack: ' || DBMS_UTILITY.FORMAT_CALL_STACK);
  12. 12.RAISE;
  13. 13.END;
  14. 14.`
  15. 15.**Fix variable size issues":
  16. 16.```sql
  17. 17.-- BAD: fixed size may be too small
  18. 18.-- v_name VARCHAR2(50);
  19. 19.-- v_name := input_value; -- Fails if input > 50 chars

-- GOOD: anchor to column type v_name customers.customer_name%TYPE;

-- Or use sufficient size with validation v_name VARCHAR2(4000); IF LENGTH(input_value) > 4000 THEN RAISE_APPLICATION_ERROR(-20001, 'Input too long: ' || LENGTH(input_value)); END IF; ```

  1. 1.**Handle character-to-number conversion safely":
  2. 2.```sql
  3. 3.-- BAD: implicit conversion
  4. 4.-- v_number := v_string; -- Fails if v_string is not numeric

-- GOOD: explicit conversion with error handling BEGIN v_number := TO_NUMBER(v_string); EXCEPTION WHEN VALUE_ERROR THEN DBMS_OUTPUT.PUT_LINE('Invalid number: ' || v_string); v_number := NULL; END; ```

Prevention - Anchor all PL/SQL variables to column types using `%TYPE` and `%ROWTYPE` - Use `VARCHAR2(4000)` or `CLOB` for string variables when size is uncertain - Implement comprehensive exception handling with backtrace logging - Validate all input parameters before processing - Use `UTL_CALL_STACK` in Oracle 12c+ for better error diagnostics - Test with boundary values (max length, special characters, NULL) - Set `PLSQL_WARNINGS` at the schema level for development environments