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

 Introduction:

Creating interactive and user-friendly map reports in Oracle APEX has become increasingly valuable for applications that rely on geospatial data. One powerful way to enhance usability is by combining faceted search with a map report—allowing users to dynamically filter and explore data based on specific attributes. In this blog post, we’ll walk through how to build a Faceted Search Map Report in Oracle APEX using the USCG_DATA table. This table includes location-based records such as lighthouses, stations, or assets from the U.S. Coast Guard. By leveraging built-in APEX components, you can empower users to quickly filter map data through intuitive facets such as district, type, or status.

To create a Faceted Search Map Report in Oracle APEX using the USCG_DATA table, follow these steps carefully. This guide assumes your USCG_DATA table includes location-based records such as name, type, district, and geographic coordinates (latitude and longitude).

1. Prepare Your Table (USCG_DATA):
Ensure that the table includes at minimum the following columns:

• ID – primary key

• NAME – name of the asset

• TYPE – type of facility (e.g., lighthouse, station)

• DISTRICT – regional grouping

• LATITUDE and LONGITUDE – coordinates for map placement

2. Create a New Page with Faceted Search and Map Report:
In APEX, go to your application and click Create > Page.
Choose Faceted Search as the report type.
Select Based on a Table or View and choose USCG_DATA.
In the options, check the box Include Map and continue.
Give the page a meaningful name, such as USCG Map Search.
Click Finish to create the page.

3. Configure the Facets:
APEX will auto-generate some facets based on column data types. You can fine-tune them:

• Navigate to Page Designer, and under the Facets section:

  • Set DISTRICT as a Checkbox Group

  • Set TYPE as a Select List or Link List

  • Add a Search Bar on the NAME column to allow keyword searches

4. Set Up the Map Attributes:
Scroll to the Map region and configure these properties:

• Location Column Type: Latitude/Longitude

• Latitude Column: LATITUDE

• Longitude Column: LONGITUDE

• Title Column: NAME

• Subtitle or Tooltip: use TYPE or DISTRICT
  You can also enable clustering if your dataset is large.

5. Add Navigation or Detail Links (Optional):
To link each map point to a detail page:

• In the Map Layer settings, use the Link section to define a dynamic action or a page link.

• Pass the ID or other identifying column as a URL item.

6. Run and Test the Page:
Save your changes and run the page.
Use the facet controls to filter data. The map should automatically update to reflect the selected criteria.

Optional Enhancements:

• Add custom icons based on TYPE using marker settings

• Include a region that lists results below the map

• Use Dynamic Actions to highlight or zoom to specific areas when a facet changes

With this setup, your users will have a highly interactive way to explore geographic data using intuitive filters, and Oracle APEX will handle the underlying complexity of filtering and visualization seamlessly.

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;

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:

Add Facet Searches

Step 1.  Add the facet search on the left hand side, in the Search region.

Step 2.  Add the type as “Checkbox Group” and  Give the label a name for the Region.

Step 3. List of value type: “Distinct Values”

Step 4. Set the Database Column that we want to use for searching.

Results:

Add a Facet Search

Lets add another faceted search for States where the earquake occurred. For this we will use the “PROPERTIES_NET” field from the database table.

Step 1. Add the facet

Step 2. Change the Label to “State

Step 3. Ste the List of Values to “distinct Values”

Step 4.  Set the Source to the column “PROPERTIES_NET”

Step 5. Save and Run

Results:

Conclusion:

With just a few steps, Oracle APEX enables developers to create a dynamic and visually engaging Faceted Search Map Report that provides both high usability and powerful filtering options. Using the USCG_DATA table as a base, we connected geospatial data with facets to allow users to interactively narrow down the results and visualize the filtered data directly on a map. This type of report is ideal for operational dashboards, situational awareness tools, and geographic exploration apps. By combining faceted navigation with map visualization, your APEX application can deliver both data clarity and an enhanced user experience.

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.

How Do I use A_TABLE for Flattening Nested Arrays

 Using A_TABLE to flatten nested arrays is an important technique in Oracle APEX and PL/SQL when working with complex JSON or collection data structures. Nested arrays can store multiple layers of data, but they often need to be transformed into a simpler, flat table format for reporting, processing, or integration. The A_TABLE function allows you to convert nested collections or arrays into rows, enabling easier access and manipulation of each element within the nested structure. This approach streamlines data handling and improves query performance when dealing with hierarchical or multi-level array data.

In Oracle APEX and PL/SQL, handling nested arrays—especially those coming from JSON or collection types—can be challenging when you want to query or display the data in a flat, tabular format. The A_TABLE function is an essential tool to flatten nested arrays or collections into rows, allowing easier processing and reporting.

Here is how to use A_TABLE for flattening nested arrays in detail:

1. Understand the Data Structure
   Nested arrays usually come as collections of collections or JSON arrays within JSON arrays. For example, you may have a JSON object containing an array of orders, and each order contains an array of items. To work with these in SQL or PL/SQL, you need to flatten the inner arrays.

2. Define the Nested Table Type
   To use A_TABLE, you first need to define a nested table type in PL/SQL corresponding to your array elements. For example:

   CREATE OR REPLACE TYPE item_type AS OBJECT (
     item_id NUMBER,
     description VARCHAR2(100),
     quantity NUMBER
   );
   
   CREATE OR REPLACE TYPE item_table_type AS TABLE OF item_type;
   

3. Populate the Nested Table
   When you have data stored as nested collections or from JSON parsing, assign the nested array to a variable of the nested table type.

4. Use A_TABLE to Flatten
   The A_TABLE function converts the nested table into rows, so you can query each element as a row. For example:

   SELECT t.item_id, t.description, t.quantity
   FROM TABLE(my_nested_array_variable) t;
   

   This will return each item in the nested array as a separate row, flattening the data.

5. Handling Nested JSON Arrays
   When working with JSON, you can parse the JSON data into nested collections using JSON_TABLE or PL/JSON and then use A_TABLE to flatten those collections.

6. Combining with Parent Data
   Often, you want to join the flattened nested array with its parent data. For example, if you have orders and nested items, you can join the order information with the flattened items like so:

   SELECT o.order_id, t.item_id, t.description, t.quantity
   FROM orders o,
        TABLE(o.items_collection) t;
   

7. In Oracle APEX
   In APEX, you can use these techniques inside SQL queries for reports or inside PL/SQL processes. This lets you show detailed nested data in classic or interactive reports by flattening nested arrays on the fly.

In summary, using A_TABLE is a powerful method to work with nested arrays by turning them into a simple row set. This approach simplifies complex hierarchical data structures, making it easier to process, display, or analyze within Oracle APEX applications.

If the "GEOMETRY" JSON column contains a more complex nested array or object, you might want to use JSON_TABLE to flatten the array into rows.

For example:

SELECT *

FROM USCG_DATA,

     JSON_TABLE(

         GEOMETRY,

         '$.coordinates[*]' COLUMNS (

             longitude NUMBER PATH '$[0]',

             latitude NUMBER PATH '$[1]'

         )

     ) jt;

This query will flatten the coordinates array in the JSON into individual rows, displaying each coordinate's longitude and latitude as separate rows in the result.

Use PL/SQL to Process JSON (Optional)

If you need more advanced processing of the JSON data, you can also use the APEX_JSON package in a PL/SQL block.

Example: Using APEX_JSON in PL/SQL

DECLARE

    l_json CLOB;

    l_type VARCHAR2(255);

    l_longitude NUMBER;

    l_latitude NUMBER;

BEGIN

    -- Fetch JSON data from the GEOMETRY column

    SELECT GEOMETRY INTO l_json

    FROM USCG_DATA

    WHERE ID = :P1_ID;

    -- Parse JSON data

    APEX_JSON.parse(l_json);

    

    -- Extract values

    l_type := APEX_JSON.get_varchar2('$.type');

    l_longitude := APEX_JSON.get_number('$.coordinates[0]');

    l_latitude := APEX_JSON.get_number('$.coordinates[1]');

    -- Assign to page items (or use in further processing)

    :P1_GEOMETRY_TYPE := l_type;

    :P1_LONGITUDE := l_longitude;

    :P1_LATITUDE := l_latitude;

    

END;

This PL/SQL block parses the JSON from the GEOMETRY column, extracts the relevant values, and assigns them to APEX page items (e.g., P1_GEOMETRY_TYPE, P1_LONGITUDE, P1_LATITUDE).

 

Debugging Error:  01722. 00000 - "unable to convert string value containing %s to a number: %s"

The ORA-01722: invalid number error typically occurs when Oracle tries to convert a value that isn't a valid number into a numeric type. In the context of your query, this could happen if one of the values you're extracting from the JSON data is expected to be a number but isn't actually a valid numeric value (e.g., it's a string or contains non-numeric characters).

The ORA-01722: invalid number error happens when Oracle tries to convert a non-numeric string into a number. To handle this in your query:

• Use regular expressions (REGEXP_LIKE) to check if the value is numeric before converting it to a number.

• Handle invalid data gracefully by returning NULL or using CASE statements to filter out invalid values.

• Debug the raw JSON values if necessary, to ensure that all values in the "coordinates" field are valid numbers.

Debug and Resolving the Issue

1. Check Data in the GEOMETRY Column: Before troubleshooting further, ensure that the data in the "GEOMETRY" column is in the expected format, especially for the numeric fields you're trying to extract. For instance, ensure that the values within "coordinates" are indeed numeric (e.g., valid decimal numbers for longitude and latitude).

Use JSON_VALUE Safely with NULL Handling: If you're extracting a value from the JSON and converting it to a number, it's essential to ensure that the value exists and is numeric. You can use TRY_CAST or CASE statements to handle cases where the value isn't a valid number.

Mastering how to use A_TABLE for flattening nested arrays empowers developers to efficiently extract and present detailed data in Oracle APEX applications. By transforming complex nested arrays into manageable rows, you can build clearer reports, create interactive dashboards, and perform advanced data operations without complicated coding. This technique enhances your ability to work with modern data formats, making your Oracle APEX solutions more flexible and powerful.