How Do I Modify a Query to Handle Possible Non-Numeric Values in ORACLE APEX

 

How Do I Modify a Query to Handle Possible Non-Numeric Values in APEX

In Oracle APEX applications, it’s common to work with datasets that include mixed or inconsistent data types—especially when consuming external sources, working with legacy systems, or importing spreadsheets. One frequent challenge is handling values that are expected to be numeric but might contain non-numeric characters such as letters, special symbols, or even nulls. These inconsistencies can cause runtime errors, break page rendering, or distort calculations. In this blog post, we’ll explore how to write SQL queries in APEX that safely handle these non-numeric values without throwing exceptions, while keeping your application stable and accurate.

How to Handle Non-Numeric Values in APEX Queries

1. Problem Example: Query With Unsafe TO_NUMBER
   Suppose you're running a query like this:

   SELECT TO_NUMBER(column_value) AS amount
   FROM my_table
   

   If column_value contains a non-numeric string such as 'ABC' or '$12', it will raise ORA-01722: invalid number.

2. Use CASE Statement with REGEXP_LIKE to Filter Safely

   SELECT 
     CASE 
       WHEN REGEXP_LIKE(column_value, '^\s*-?\d+(\.\d+)?\s*$') THEN TO_NUMBER(column_value)
       ELSE NULL
     END AS amount
   FROM my_table
   

   • This ensures that TO_NUMBER is only called when the value is truly numeric.

   • The regex checks for optional whitespace, an optional minus sign, digits, and optional decimal portion.

3. Alternative: Use VALIDATE_CONVERSION in Oracle 12.2+

   SELECT 
     CASE 
       WHEN VALIDATE_CONVERSION(column_value AS NUMBER) = 1 THEN TO_NUMBER(column_value)
       ELSE NULL
     END AS amount
   FROM my_table
   

   • VALIDATE_CONVERSION is cleaner and easier to read than regular expressions.

   • Returns 1 if conversion is valid, 0 if not.

4. Ignore Non-Numeric Rows Entirely
   If you want to skip bad rows instead of showing nulls:

   SELECT TO_NUMBER(column_value) AS amount
   FROM my_table
   WHERE REGEXP_LIKE(column_value, '^\s*-?\d+(\.\d+)?\s*$')
   

5. Handle Formatting Issues in Source Data

   • Strip currency symbols, commas, or spaces before converting:

     SELECT 
       TO_NUMBER(
         REPLACE(REPLACE(column_value, '$'), ',', '')
       ) AS amount
     FROM my_table
     WHERE REGEXP_LIKE(REPLACE(REPLACE(column_value, '$'), ',', ''), '^\s*-?\d+(\.\d+)?\s*$')
     

   • 
     

Best Practices for Handling Non-Numeric Values in Queries

• Always validate data before casting with TO_NUMBER, TO_DATE, etc.

• Use REGEXP_LIKE or VALIDATE_CONVERSION to protect against exceptions.

• Consider storing numeric data in proper NUMBER columns—use virtual columns or ETL scripts to clean messy data at load time.

• Log or flag invalid rows using CASE so users can be informed of bad input.

• When dealing with external JSON or REST data, clean inputs before storing them in your table.

 

Let's go through the possible fixes based on the assumption that the "coordinates" or other numeric fields might not always contain valid numeric data.

1. Using CASE to Safely Convert to Numbers

You can modify your query to include CASE logic to only attempt conversion when the value is a valid number.

For example, let's handle potential non-numeric values for longitude and latitude using CASE and TO_NUMBER with exception handling.

SELECT

    ID,

    JSON_VALUE(GEOMETRY, '$.type') AS geometry_type,

    CASE 

        WHEN REGEXP_LIKE(JSON_VALUE(GEOMETRY, '$.coordinates[0]'), '^-?\d+(\.\d+)?$') 

        THEN TO_NUMBER(JSON_VALUE(GEOMETRY, '$.coordinates[0]')) 

        ELSE NULL 

    END AS longitude,

    CASE 

        WHEN REGEXP_LIKE(JSON_VALUE(GEOMETRY, '$.coordinates[1]'), '^-?\d+(\.\d+)?$') 

        THEN TO_NUMBER(JSON_VALUE(GEOMETRY, '$.coordinates[1]')) 

        ELSE NULL 

    END AS latitude

FROM USCG_DATA;

Explanation:

• REGEXP_LIKE: This checks whether the value extracted from the JSON field is a valid numeric format (integer or decimal). 

• ^-?\d+(\.\d+)?$ is a regular expression that matches valid numbers, including negative and decimal numbers.

• If the value matches the regex, it is cast to a number using TO_NUMBER.

• If the value is invalid (e.g., non-numeric), it will return NULL instead of causing an error.

Official Oracle Documentation Links

• Oracle SQL REGEXP_LIKE Function

• VALIDATE_CONVERSION Function

• TO_NUMBER Function

• APEX SQL Workshop Best Practices

Conclusion

Modifying your APEX SQL queries to gracefully handle non-numeric values is essential when working with unpredictable or user-supplied data. By leveraging REGEXP_LIKE, VALIDATE_CONVERSION, and defensive SQL techniques, you can prevent runtime errors and keep your reports and forms running smoothly. Always validate before converting, and where possible, sanitize your data upstream. This proactive approach ensures a more robust and user-friendly APEX application that won’t fail due to a rogue character in your dataset.