How Do I Handle NULLs and Missing Data in GEOMETRY column contains missing or NULL values

When working with real-world data in Oracle APEX—especially data coming from external APIs, JSON sources, or user uploads—it’s common to encounter missing fields or NULL values. Ignoring these cases can lead to application errors, broken reports, or invalid calculations. A good example is when extracting coordinates from a JSON-based GEOMETRY column. If the data is malformed or missing, direct conversions to numbers may fail. To maintain stability and user trust, you need to write SQL that gracefully handles NULLs and unexpected formats. This post demonstrates a safe approach to parsing values from JSON while avoiding runtime exceptions.Handling NULLs and Invalid Values in SQL with JSON Columns

Let’s say you’re working with a table called USCG_DATA that includes a GEOMETRY column storing JSON data. You're interested in extracting the type, longitude, and latitude from the JSON.

Here’s a detailed version of a safe and debug-friendly query:

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;

Explanation of Logic:

• JSON_VALUE(GEOMETRY, '$.type') extracts the type field, which could be "Point" or other geometry types.

• The longitude and latitude values are taken from the JSON array under $.coordinates[0] and $.coordinates[1].

• Before converting these values to numbers, the query:

  • Checks that the value is not NULL

  • Verifies the value matches a numeric pattern using REGEXP_LIKE

• If either condition fails, NULL is returned—preventing the ORA-01722: invalid number error.

Best Practices for Handling NULLs in APEX Queries

• Always Validate Before Conversion: Never call TO_NUMBER, TO_DATE, or similar functions directly on unknown input. Wrap them in CASE or VALIDATE_CONVERSION checks.

• Use REGEXP_LIKE for Format Validation: Especially when working with free-form or JSON data. This reduces conversion exceptions.

• Log Problematic Records: Add a debug column to flag rows with invalid formats for review.

  CASE 
    WHEN JSON_VALUE(...) IS NULL THEN 'MISSING'
    WHEN NOT REGEXP_LIKE(...) THEN 'INVALID'
    ELSE 'OK'
  END AS geometry_status
  

• Default to NULL Safely: Avoid hard-coding fallback values unless business logic requires it. NULL is usually better than a misleading 0 or placeholder.

• Use APEX Debug Tools: Enable debug mode and review View Debug to see which conditions were triggered and how APEX processed the page.

Helpful Documentation Links

• Oracle JSON_VALUE Function

• REGEXP_LIKE Function

• Oracle APEX Debugging Tools

Conclusion

Handling NULLs and missing data is critical for building reliable Oracle APEX applications. By applying simple safeguards like CASE statements and REGEXP_LIKE, you can prevent your queries from breaking when data is missing, misformatted, or incomplete. This approach ensures smoother page rendering, fewer user errors, and better data quality overall. Whether you're dealing with JSON, numbers, or dates, always validate before converting—and use APEX's debugging tools to trace and fine-tune your logic in real time.