How Do I Create a Faceted Search Map Report in Oracle APEX Using USCG_DATA

Creating an interactive, location-based application in Oracle APEX is simpler than ever using the built-in Map region and Faceted Search components. When working with spatial or geo-tagged data like USCG_DATA, which includes geographic features in a JSON-based GEOMETRY column, APEX lets you combine powerful filtering with visual map rendering. This allows users to search and explore data by category, date, or region—while immediately seeing results plotted on a map. In this blog, you’ll learn how to build a Faceted Search page tied to a Map region using real coordinates extracted from the USCG_DATA table.Step-by-Step: Creating a Faceted Search Map Report with USCG_DATA

1. Prepare the USCG_DATA Table

   Ensure your USCG_DATA table includes a GEOMETRY column (JSON), along with identifying fields like ID, TYPE, NAME, and DATE_RECORDED. Make sure GEOMETRY contains a type and coordinates array structured like:

   {
     "type": "Point",
     "coordinates": [-87.12345, 29.98765]
   }
   

2. Extract Coordinates in a SQL View or Query
   Use SQL to extract clean longitude and latitude values:

   SELECT
     ID,
     NAME,
     TYPE,
     DATE_RECORDED,
     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
   WHERE JSON_VALUE(GEOMETRY, '$.type') = 'Point'
   

3. Create a New Faceted Search Page
   In APEX:

   • Go to App Builder → Create → Page.

   • Select Faceted Search with Map as the main report region.

   • Use the query or view you created above as the source.

   • Assign longitude and latitude to the correct columns in the Map attributes.

   • Set a primary key column (e.g., ID), and optionally set NAME as the label.

4. Configure Facets for Filtering
   Add facets for:

   • TYPE (Checklist facet)

   • DATE_RECORDED (Date range)

   • Any other meaningful metadata in your table (e.g., status or location category)

5. Customize the Map Region

   • Under Map region settings:

     • Choose the map style (e.g., Light or Satellite).

     • Optionally enable clustering if your data is dense.

     • Use NAME as the popup label.

     • Use TYPE as the marker category for color differentiation.

6. Test and Enhance

   • Preview the page and apply filters using facets.

   • Ensure markers appear correctly and facets refresh the map in real time.

   • Optionally link map markers to detail pages or modal dialogs.

Best Practices

• Always validate and convert coordinates safely using REGEXP_LIKE before calling TO_NUMBER.

• Use a view to centralize JSON extraction logic so the APEX page stays clean.

• Enable null checks in your WHERE clause to avoid blank or invalid markers.

• Consider adding a region below the map to list filtered records in tabular form.

• Use meaningful categories in facets—avoid overwhelming users with long picklists.

• Store derived lat/lon columns if performance becomes an issue.

 

A Faceted Search Map Report in Oracle APEX allows users to filter and visualize geographic data on an interactive map using multiple search facets. This tutorial will guide you through the process of setting up a faceted search with a Map Chart report using the USCG_DATA table.

Understanding the USCG_DATA Table

The table contains geospatial data in GeoJSON format within the GEOMETRY column. The key columns for creating a map report are:

• Longitude: Extracted from JSON_VALUE(GEOMETRY, '$.coordinates[0]')

• Latitude: Extracted from JSON_VALUE(GEOMETRY, '$.coordinates[1]')

• Attributes: Various PROPERTIES_* fields, such as PROPERTIES_MAG (magnitude), PROPERTIES_PLACE (location), and PROPERTIES_TIME (timestamp).

This data can be used to display locations on a Map Chart while allowing filtering through Faceted Search.

How Do I Create the Faceted Search Map Report

Step 1: Create a New Page

1. Open Oracle APEX and navigate to your application.

2. Click Create Page → Select Faceted Search.

3. Click Next, choose your table USCG_DATA, and set the Primary Key to ID.

4. Click Next, then Create.

Step 2: Configure the Faceted Search Region

The Faceted Search component will allow filtering the map data based on relevant attributes.

1. Go to the Faceted Search region settings.

2. Add facets for filtering, such as: 

• Magnitude (PROPERTIES_MAG) → Numeric Range

• Location (PROPERTIES_PLACE) → Search Box

• Earthquake Type (PROPERTIES_TYPE) → Select List

• Time (PROPERTIES_TIME) → Date Range Picker

• Tsunami Alert (PROPERTIES_TSUNAMI) → Checkbox

3. Click Save.

Step 3: Create the Map Report

1. Go to Page Designer and add a new Map Chart region.

2. Set Region Source to: 

SELECT 

   ID, 

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

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

   PROPERTIES_MAG AS MAGNITUDE, 

   PROPERTIES_PLACE AS PLACE, 

   PROPERTIES_TYPE AS EVENT_TYPE, 

   PROPERTIES_TSUNAMI AS TSUNAMI_ALERT

FROM USCG_DATA

WHERE (:P1_MAG_MIN IS NULL OR PROPERTIES_MAG >= :P1_MAG_MIN)

  AND (:P1_MAG_MAX IS NULL OR PROPERTIES_MAG <= :P1_MAG_MAX)

  AND (:P1_PLACE IS NULL OR LOWER(PROPERTIES_PLACE) LIKE LOWER('%' || :P1_PLACE || '%'))

  AND (:P1_EVENT_TYPE IS NULL OR PROPERTIES_TYPE = :P1_EVENT_TYPE)

  AND (:P1_TSUNAMI_ALERT IS NULL OR PROPERTIES_TSUNAMI = :P1_TSUNAMI_ALERT)

3. Under Attributes, set: 

• Longitude Column: LONGITUDE

• Latitude Column: LATITUDE

• Title Column: PLACE

• Popup Info: 

<b>Location:</b> &P1_PLACE.<br>

<b>Magnitude:</b> &P1_MAGNITUDE.<br>

<b>Event Type:</b> &P1_EVENT_TYPE.<br>

<b>Tsunami Alert:</b> &P1_TSUNAMI_ALERT.

• Layer Type: Point

Step 4: Connect Facets to the Map Report

1. Navigate to Faceted Search > Filters.

2. Assign facet items to the report’s query parameters: 

• Magnitude → P1_MAG_MIN and P1_MAG_MAX

• Location → P1_PLACE

• Event Type → P1_EVENT_TYPE

• Tsunami Alert → P1_TSUNAMI_ALERT

3. Set Refresh Region to refresh the Map Chart when filters are applied.

This approach provides a powerful Faceted Search Map Report in Oracle APEX, allowing users to interactively filter and explore geospatial data. By leveraging JSON functions and dynamic filters, this solution enhances the visualization of location-based data.

EXAMPLE:

Using the following code:

SELECT

    ID,

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

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

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

    "PROPERTIES_TZ" , 

    "PROPERTIES_CDI" , 

    "PROPERTIES_GAP" , 

    "PROPERTIES_IDS" , 

    "PROPERTIES_MAG", 

    "PROPERTIES_MMI", 

    "PROPERTIES_NET" , 

    "PROPERTIES_NST" , 

    "PROPERTIES_RMS" , 

    "PROPERTIES_SIG" , 

    "PROPERTIES_URL" , 

    "PROPERTIES_CODE" , 

    "PROPERTIES_DMIN", 

    "PROPERTIES_FELT" , 

    "PROPERTIES_TIME" , 

    "PROPERTIES_TYPE" , 

    "PROPERTIES_ALERT", 

    "PROPERTIES_PLACE" , 

    "PROPERTIES_TITLE" , 

    "PROPERTIES_TYPES" , 

    "PROPERTIES_DETAIL", 

    "PROPERTIES_STATUS" , 

    "PROPERTIES_MAGTYPE" , 

    "PROPERTIES_SOURCES" , 

    "PROPERTIES_TSUNAMI", 

    "PROPERTIES_UPDATED" , 

    "APEX$SYNC_STEP_STATIC_ID" , 

    "APEX$ROW_SYNC_TIMESTAMP" 

FROM USCG_DATA;

HOW DO I Create a new Report:

Select the MAP.

Create a Map – Page definition.

Add the query found at the start of this section into the SQL Select box.

Select the table:

Select Next.

Set the Map’s Settings.

Depending on your data type, you can also do the following:

Helpful Oracle APEX Documentation Links

• Creating Map Regions in APEX

• Faceted Search Pages

• Working with JSON in SQL

Conclusion

Combining faceted search with a map visualization in Oracle APEX gives your users a powerful, intuitive way to explore location-based data. By properly parsing the GEOMETRY column in USCG_DATA, validating coordinates, and using native Map features, you can quickly build a responsive, filterable, and interactive UI. This kind of design is perfect for maritime, environmental, logistics, or emergency applications—where seeing data on a map brings clarity and speed to decision-making.

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.

Debugging in Oracle APEX

Debugging in Oracle APEX often begins with identifying why a report region returns no data, a form loads with missing values, or a dynamic action doesn’t behave as expected. Many of these issues can be traced back to the underlying SQL query. When queries depend on page items, dynamic filters, or conditional logic, it's important to understand exactly what values are being passed and how the database interprets them. A powerful technique is to rewrite the query to make it more “debuggable”—by exposing bind values, adding logic checks, and validating assumptions directly in the SELECT clause. This blog post shows how to build more detailed queries to simplify debugging and speed up development.

How to Build a More Detailed Query for Debugging in APEX

Start by identifying the part of the query that depends on page items. These might include :P1_ID, :P6_STATUS, or any filter used inside WHERE, HAVING, or even CASE expressions.

1. Expose Bind Variables in the SELECT Clause
   This shows what the database sees:

   SELECT 
     employee_id,
     department_id,
     :P6_DEPT_ID AS p6_dept_value,
     CASE 
       WHEN department_id = :P6_DEPT_ID THEN 'MATCH'
       ELSE 'NO MATCH'
     END AS match_check
   FROM employees
   

   Adding the bind value and conditional logic directly into the output helps you confirm that the filter is working.

2. Use CASE Statements to Validate Logic
   Validate logic that might be hidden inside the WHERE clause:

   SELECT 
     hire_date,
     :P6_START_DATE AS start_date,
     :P6_END_DATE AS end_date,
     CASE 
       WHEN hire_date BETWEEN :P6_START_DATE AND :P6_END_DATE 
       THEN 'IN RANGE'
       ELSE 'OUT OF RANGE'
     END AS hire_range_check
   FROM employees
   

3. Temporarily Remove the WHERE Clause
   Start without filters so you can observe what values are being compared. This helps you spot issues like nulls or mismatched data types.

4. Check Bind Variable Values in SQL Workshop
   If you want to simulate how the query runs in SQL Workshop, hard-code the values temporarily:

   SELECT ...
   FROM ...
   WHERE department_id = 10 -- Instead of :P6_DEPT_ID
   

5. Use APEX Debug Logs
   For more insight:

   • Turn on debug mode from the Developer Toolbar.

   • Open “View Debug” after submitting the page.

   • Look for page item values, query execution details, and rendered SQL statements.

6. Log Messages from PL/SQL
   Inside PL/SQL blocks:

   apex_debug.log_message('P6_STATUS = ' || :P6_STATUS);
   

Best Practices for Debug Queries

• Always display page item values in your query results to verify input.

• Use CASE statements to test matching conditions or ranges.

• Don’t rely on hidden filters during debugging—make everything visible.

• Combine query changes with apex_debug logs for full traceability.

• Remove or comment out detailed logic once the issue is fixed to keep your queries clean.

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.

Official Oracle APEX Documentation

• APEX_DEBUG API

• SQL Workshop Guide

• Understanding Bind Variables

 Conclusion

Debugging queries in Oracle APEX doesn’t need to be time-consuming. By adding clarity to your SQL with bind value displays and logical tests, you can make your data behavior fully visible. This approach turns confusing results into understandable outputs and transforms trial-and-error into a structured debugging method. Combined with the APEX debug log and SQL Workshop, these techniques help you identify problems faster and develop more confidently.

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.

How Do I Use PL/SQL to Process JSON (Optional) in APEX

 In Oracle APEX development, working with JSON has become increasingly common—especially when building dynamic, API-driven applications or integrating with external services. Whether the JSON payload comes from a RESTful Web Source, JavaScript callback, or a plug-in, being able to parse and process it with PL/SQL gives you full control over your business logic. This post explores how to handle optional JSON using PL/SQL within APEX—where keys may or may not exist—and how to write code that safely checks, parses, and handles that data without raising exceptions.

When JSON input is optional or partially structured, your PL/SQL logic must account for missing keys or null values. APEX developers can take advantage of Oracle’s JSON_OBJECT_T, JSON_ARRAY_T, and related data types to work directly with JSON structures stored in variables or CLOBs. Below is a detailed walkthrough using native PL/SQL APIs.

Step-by-Step: Parsing Optional JSON in APEX Using PL/SQL

1. Get the JSON Payload

   • Typically passed as a CLOB or VARCHAR2 item from a page process, REST call, or JavaScript block.

   • Example: :P1_JSON_INPUT

2. Check for NULL or Empty JSON

   if :P1_JSON_INPUT is not null and apex_json.is_json(:P1_JSON_INPUT) then
     -- Safe to parse
   else
     apex_debug.log_message('No valid JSON input provided.');
   end if;
   

3. Parse JSON Using JSON_OBJECT_T

   declare
     l_json_text clob := :P1_JSON_INPUT;
     l_json_obj  json_object_t;
   begin
     if l_json_text is not null then
       l_json_obj := json_object_t.parse(l_json_text);
   
       if l_json_obj.has('username') then
         apex_debug.log_message('Username: ' || l_json_obj.get_string('username'));
       else
         apex_debug.log_message('Username key is missing');
       end if;
   
       if l_json_obj.has('age') then
         apex_debug.log_message('Age: ' || l_json_obj.get_number('age'));
       end if;
     end if;
   end;
   

4. Process JSON Arrays (If Present)

   declare
     l_items_arr json_array_t;
     l_item_obj  json_object_t;
   begin
     if l_json_obj.has('items') then
       l_items_arr := l_json_obj.get_array('items');
       for i in 0 .. l_items_arr.get_size - 1 loop
         l_item_obj := l_items_arr.get_object(i);
         apex_debug.log_message('Item name: ' || l_item_obj.get_string('name'));
       end loop;
     end if;
   end;
   

5. Handling Default or Missing Values

   • Use has() to avoid runtime errors.

   • Optionally fallback to default values using coalesce() or conditional logic.

   • Always wrap get_* calls in existence checks.

Best Practices for JSON Processing in PL/SQL (APEX)

• Always check IS NULL or use IS_JSON() before parsing.

• Use JSON_OBJECT_T.has('key') to confirm key existence.

• Use apex_debug.log_message() for tracing and debugging inside APEX environments.

• Use exception handling (begin...exception...end) to catch parse errors when dealing with unknown or user-input JSON.

• Sanitize JSON inputs received from external clients to avoid injection or unexpected formats.

• Avoid deep and nested traversals when possible—flatten your JSON schema if performance and clarity are priorities.

• Validate the JSON structure client-side with JavaScript before submitting to PL/SQL.

Useful Oracle Documentation Links

• Oracle PL/SQL JSON Developer’s Guide

• Oracle JSON_OBJECT_T Reference

• APEX_DEBUG Package Reference

Conclusion

Handling optional JSON data in Oracle APEX using PL/SQL is a practical and powerful approach to making your application more robust and flexible. By using Oracle’s native JSON APIs, you can safely parse, inspect, and process JSON objects—regardless of whether all fields are present. Always validate, check for missing keys, and log carefully using apex_debug to streamline troubleshooting. This approach ensures that your APEX applications are resilient and future-proof, even in loosely structured or dynamic data environments.