How do I Modify the Query to Handle Possible Non-Numeric Values

 In many Oracle APEX applications, data retrieved from user input, external files, or REST services may include values that are not strictly numeric, even when numeric input is expected. This becomes especially important when writing SQL queries or PL/SQL code that performs numeric operations. If non-numeric characters are passed into functions like TO_NUMBER, the query can throw errors and stop execution. To avoid this, developers need to build logic that safely handles such data while still achieving their goals—such as filtering, sorting, or computing numeric values only when appropriate. Modifying your query to gracefully handle these cases ensures your APEX pages stay stable, even when the data isn't perfect.

In Oracle APEX, it is common to work with datasets that may include mixed or unexpected values. When you are writing SQL queries that expect numeric values—especially when applying functions like TO_NUMBER, performing arithmetic, or using numeric comparisons—encountering non-numeric data can lead to runtime errors. To ensure your APEX applications remain stable and user-friendly, it is important to proactively handle these scenarios in your SQL query.

To modify your query to handle possible non-numeric values, follow these detailed steps:

1. Use REGEXP_LIKE to filter only numeric values
You can include a condition in your WHERE clause that ensures only values that match a numeric pattern are processed. This avoids conversion errors.

SELECT col1,
       TO_NUMBER(col2) AS numeric_col2
FROM   my_table
WHERE  REGEXP_LIKE(col2, '^\d+(\.\d+)?$');

This query will only attempt to convert col2 to a number if the value consists of digits (and optionally a decimal).

2. Use CASE to selectively convert values
If you want to include all rows and convert only when safe, use a CASE statement inside your SELECT clause:

SELECT col1,
       CASE
           WHEN REGEXP_LIKE(col2, '^\d+(\.\d+)?$') THEN TO_NUMBER(col2)
           ELSE NULL
       END AS numeric_col2
FROM   my_table;

This way, non-numeric values are replaced with NULL, which you can safely ignore or flag in your report.

3. Handle invalid data using IS NUMERIC logic
Oracle SQL doesn’t have a built-in ISNUMERIC function, but you can simulate one using REGEXP_LIKE. For example:

SELECT *
FROM   my_table
WHERE  NOT REGEXP_LIKE(col2, '^\d+(\.\d+)?$');

This query helps identify which rows have non-numeric data for cleaning or debugging purposes.

4. Avoid conversion in WHERE clause unless pre-checked
Never write something like WHERE TO_NUMBER(col2) > 100 unless you are 100% certain that col2 contains only valid numeric data. Instead, combine with a regex check:

SELECT *
FROM   my_table
WHERE  REGEXP_LIKE(col2, '^\d+(\.\d+)?$')
AND    TO_NUMBER(col2) > 100;

5. Use NVL or COALESCE for default values
If non-numeric values might occur and you want to treat them as a default (e.g., 0), wrap your logic like this:

SELECT col1,
       COALESCE(
         CASE 
           WHEN REGEXP_LIKE(col2, '^\d+(\.\d+)?$') THEN TO_NUMBER(col2)
         END, 0) AS safe_value
FROM   my_table;

This assigns 0 when col2 is non-numeric.

By incorporating regular expression checks, conditional logic, and safe conversions, you can write robust SQL that guards against unexpected input and keeps your APEX application running smoothly. Always validate your assumptions with test cases that include edge values like nulls, text strings, special characters, and decimal numbers.

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.

How do I debug with a more detailed query?

To better understand which row or data is causing the error, you can first query the raw data from the JSON fields to see if there are any issues with the data:

SELECT

    ID,

    JSON_VALUE(GEOMETRY, '$.coordinates[0]') AS longitude,

    JSON_VALUE(GEOMETRY, '$.coordinates[1]') AS latitude

FROM USCG_DATA

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

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

This query will return rows where the longitude or latitude values are not valid numbers, helping you identify the problematic data.

How do I handle NULLS and Missing data?

If you suspect that the GEOMETRY column contains missing or NULL values, you can also handle these cases gracefully by adding additional checks:

SELECT

    ID,

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

    CASE 

        WHEN JSON_VALUE(GEOMETRY, '$.coordinates[0]') IS NOT NULL

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

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

        ELSE NULL 

    END AS longitude,

    CASE 

        WHEN JSON_VALUE(GEOMETRY, '$.coordinates[1]') IS NOT NULL

             AND 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;

This ensures that the query only attempts to convert values to numbers if they are both present and valid, avoiding the ORA-01722 error.

By using functions like REGEXP_LIKE to pre-filter numeric values or CASE and NULLIF logic to prevent invalid conversions, your SQL queries can become more defensive and robust. Whether you’re building a report, calculation, or validation, safely handling non-numeric inputs helps keep your application user-friendly and error-free. Always test your queries with both valid and invalid inputs to confirm that your logic works under all expected conditions.