How to Fix “ORA-06502: PL/SQL: Numeric or Value Error” in Oracle APEX – Causes, Real-World Solutions, and Pro Tips

Introduction

Have you ever deployed a new page or PL/SQL process in Oracle APEX, only to be greeted by the dreaded “ORA-06502: PL/SQL: numeric or value error: character to number conversion error”? You’re not alone. This error is one of the most frequently encountered problems for both beginners and advanced Oracle APEX developers.

Not only is it frustrating, but if left unchecked, it can stop your users dead in their tracks, ruin critical data flows, and create bad impressions of your application’s reliability.

But don’t worry—this guide is here to help you fix it once and for all. We’ll break down what the error actually means, why it happens, how to trace it to the root cause, and provide you with concrete examples and expert tips to solve and prevent it for good.

What is “ORA-06502: PL/SQL: Numeric or Value Error”?

The ORA-06502 error is a general PL/SQL exception that occurs whenever a numeric, value, or conversion operation fails in your Oracle database code.

Typical full error message:

ORA-06502: PL/SQL: numeric or value error: character to number conversion error

But you may also see it as:

• ORA-06502: PL/SQL: numeric or value error: number precision too large
• ORA-06502: PL/SQL: numeric or value error: string buffer too small
• ORA-06502: PL/SQL: numeric or value error: null index table key value

This error can pop up anywhere in your APEX app—in page processes, dynamic actions, validations, triggers, or even background jobs.

Why Does ORA-06502 Happen in Oracle APEX?

This error always indicates a mismatch between data types or values in your PL/SQL code or data operations. The most common causes include:

• Data Type Mismatch
  • Trying to assign a VARCHAR2 string (containing non-numeric characters) to a NUMBER variable.
  • Assigning data to a variable with a smaller size than the value provided.
• String Buffer Too Small
  • Storing a string in a variable that isn’t large enough (e.g., a 20-character string in a VARCHAR2(10) variable).
• Number Precision Issues
  • Assigning a large number to a variable with a limited precision or scale.
• Character to Number Conversion
  • Implicitly or explicitly converting strings to numbers where the string contains invalid characters (e.g., letters, spaces, special chars).
• Collection Index or Data Issues
  • Using an invalid index in a collection/array.
• Input from Forms/Reports
  • End-users entering unexpected data types (e.g., typing letters into a number field).

Key Point:
This is almost always a bug in your PL/SQL logic or APEX item mapping—not a database bug.

How to Troubleshoot and Fix ORA-06502 in Oracle APEX

Let’s walk through a proven troubleshooting workflow.

Step 1: Reproduce and Isolate the Error

• Find the exact action or input that triggers the error (e.g., form submission, process run, button click).
• Take note of the error location—does it show a page/process/component name?

Tip: In APEX, use Session State > Debug to get detailed stack traces and variable values at error time.

Step 2: Review All Related PL/SQL Code

• Page processes and validations
• Dynamic actions
• Computations
• Triggers (table, row-level)
• Custom packages or procedures

Step 3: Check Variable Declarations and Data Types

• Variable types vs. assigned value types
• String lengths (VARCHAR2(10) vs. value length)
• Number precision (NUMBER(5,2) vs. value being assigned)

Example Problem: Character to Number Conversion

DECLARE
    l_amount NUMBER;
BEGIN
    l_amount := :P1_AMOUNT;  -- P1_AMOUNT is a text field in APEX
END;

If :P1_AMOUNT contains ‘ABC’, you get ORA-06502!

Step 4: Add Defensive Conversions and Validations

Always use explicit type conversions and handle bad input gracefully.

Use TO_NUMBER Safely:

DECLARE
    l_amount NUMBER;
BEGIN
    BEGIN
        l_amount := TO_NUMBER(:P1_AMOUNT);
    EXCEPTION
        WHEN VALUE_ERROR THEN
            l_amount := NULL;
            -- Optionally set an error message for the user
    END;
END;

Check String Length Before Assignment:

DECLARE
    l_short VARCHAR2(5);
BEGIN
    IF LENGTH(:P1_TEXT) <= 5 THEN
        l_short := :P1_TEXT;
    ELSE
        -- Handle error, e.g., show a message
    END IF;
END;

Step 5: Review APEX Item Types and Session State

• Is the page item type correct? (e.g., Text Field for Number vs. Number Field)
• Is the item returning the expected value type? (APEX always stores session state as strings!)
• Are any hidden items initialized incorrectly?

Step 6: Validate Database Table Definitions

• Do column data types match what you’re inserting/updating?
• Are you inserting a string into a number column, or vice versa?
• Are column sizes sufficient for the expected data?

Step 7: Use Debug Mode and Error Logging

Turn on Debug Mode in Oracle APEX:

• Go to the “Session” menu → “Debug” in the APEX builder.
• Reproduce the error and review the debug output for variable values and failed assignments.

Or, add your own logging:

APEX_DEBUG.MESSAGE('Value of :P1_AMOUNT: ' || :P1_AMOUNT);

Real-World Examples and Solutions

Example 1: String to Number Conversion Error in APEX Process

Problem:

DECLARE
    l_id NUMBER;
BEGIN
    l_id := :P1_CUSTOMER_ID;
    -- Further logic...
END;

User accidentally enters ‘ABC123’ in P1_CUSTOMER_ID.

Fix:

DECLARE
    l_id NUMBER;
BEGIN
    BEGIN
        l_id := TO_NUMBER(:P1_CUSTOMER_ID);
    EXCEPTION
        WHEN VALUE_ERROR THEN
            APEX_ERROR.ADD_ERROR(
                p_message => 'Invalid customer ID: must be a number.',
                p_display_location => APEX_ERROR.C_INLINE_IN_NOTIFICATION
            );
            RETURN;
    END;
    -- Further logic...
END;

Example 2: String Buffer Too Small

Problem:

DECLARE
    l_short_name VARCHAR2(5);
BEGIN
    l_short_name := :P1_FULL_NAME;  -- User types "Christopher"
END;

Fix:

• Increase variable size (VARCHAR2(50))
• Or, check length before assignment as shown in Step 4.

Example 3: Numeric Precision Error

Problem:

DECLARE
    l_price NUMBER(4,2);  -- max 99.99
BEGIN
    l_price := :P1_PRICE; -- User enters 123.45
END;

Fix:

• Increase variable precision to fit larger values (NUMBER(6,2), etc.)
• Add validation in the form.

Example 4: Wrong Data Types in Table Column

Problem: You have a table column TOTAL_AMOUNT NUMBER(5,2), but users sometimes enter values over 99,999.99 in the form.

Fix:

• Change column data type to NUMBER(10,2) or higher.
• Add APEX validation to restrict input to max value.

Best Practices to Prevent ORA-06502 Errors in APEX

• Always Use Explicit Data Conversions
  • Use TO_NUMBER, TO_CHAR, TO_DATE as needed—never rely on implicit conversions.
• Match Variable and Item Types
  • Make sure your PL/SQL variables match the APEX item or table column types (including size/precision).
• Add Validation at the UI Level
  • Use APEX validations to prevent users from entering invalid values before PL/SQL runs.
• Use Defensive Coding in PL/SQL
  • Wrap assignments in BEGIN...EXCEPTION...END blocks to handle unexpected cases gracefully.
• Monitor Session State Values
  • Use APEX debug tools to watch what’s actually being passed from the page.
• Sanitize and Format All User Input
  • Apply regular expressions and input masks on forms to restrict allowed characters.
• Log and Monitor Errors
  • Use APEX_DEBUG and error logging for quick diagnosis and long-term maintenance.

Sample Code Snippets for Common Patterns

Safe Number Conversion in APEX Process

DECLARE
    l_val NUMBER;
BEGIN
    BEGIN
        l_val := TO_NUMBER(:P1_INPUT);
    EXCEPTION
        WHEN VALUE_ERROR THEN
            APEX_ERROR.ADD_ERROR(
                p_message => 'Please enter a valid number.',
                p_display_location => APEX_ERROR.C_INLINE_IN_NOTIFICATION
            );
            RETURN;
    END;
    -- Continue with l_val if valid
END;

String Length Check Before Assignment

DECLARE
    l_desc VARCHAR2(20);
BEGIN
    IF LENGTH(:P1_DESC) <= 20 THEN
        l_desc := :P1_DESC;
    ELSE
        APEX_ERROR.ADD_ERROR(
            p_message => 'Description must be 20 characters or less.',
            p_display_location => APEX_ERROR.C_INLINE_IN_NOTIFICATION
        );
        RETURN;
    END IF;
END;

Summary

The “ORA-06502: PL/SQL: numeric or value error” in Oracle APEX is common but always fixable! By understanding the causes—type mismatches, bad conversions, precision issues—and following the troubleshooting steps above, you can resolve these errors quickly and prevent them in future applications.

Key takeaways:

• Carefully match your data types in PL/SQL and APEX
• Validate user input early
• Use defensive coding and robust error handling
• Leverage Oracle APEX debugging tools to pinpoint the issue fast

If you’re struggling with a tricky ORA-06502 error in your app, let us know in the comments—we love solving APEX puzzles!

Did you find this guide helpful? Follow Nextmodlabs for more practical Oracle APEX tips, troubleshooting guides, and tutorials!