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

Working with JSON in Oracle APEX has become a common requirement, especially when handling data from RESTful APIs, JavaScript-based front-ends, or APEX collections. Fortunately, Oracle PL/SQL offers powerful native support for JSON processing through functions and procedures that allow you to parse, extract, and manipulate JSON data directly in SQL and PL/SQL. In this blog, we'll explore how to use PL/SQL to process optional JSON input in APEX applications, what best practices to follow, and how to ensure your code remains secure and efficient.

Using PL/SQL to Process JSON in APEX

Oracle provides the JSON_OBJECT_T, JSON_ARRAY_T, and JSON_ELEMENT_T types under the PL/SQL JSON package (since Oracle Database 12c), which allow you to process JSON natively.

Here’s a step-by-step guide on how to process optional JSON input in APEX:

• Accept JSON Input in PL/SQL Block

  declare
    l_json_input clob := :P1_JSON_PAYLOAD;
    l_json_obj   json_object_t;
  begin
    if l_json_input is not null then
      l_json_obj := json_object_t.parse(l_json_input);
  
      -- Example: safely retrieve a value if it exists
      if l_json_obj.has('email') then
        apex_debug.log_message('Email: ' || l_json_obj.get_string('email'));
      end if;
  
      if l_json_obj.has('phone') then
        apex_debug.log_message('Phone: ' || l_json_obj.get_string('phone'));
      else
        apex_debug.log_message('Phone is not provided');
      end if;
  
    else
      apex_debug.log_message('No JSON payload received');
    end if;
  end;
  

• Handle Optional JSON Keys Gracefully

  • Use has() to check if a key exists before calling get_string() or get_number().

  • Avoid assuming that all fields will be present. This prevents NO_DATA_FOUND or null pointer issues.

• Debug and Validate Input

  • Use apex_debug.log_message generously to trace issues in development.

  • Validate structure in JavaScript or client-side logic before submission when possible.

• Dynamic Processing of JSON Arrays

  declare
    l_json_input clob := :P1_JSON_PAYLOAD;
    l_json_obj   json_object_t;
    l_items_arr  json_array_t;
    l_item       json_object_t;
  begin
    if l_json_input is not null then
      l_json_obj := json_object_t.parse(l_json_input);
      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 := l_items_arr.get_object(i);
          apex_debug.log_message('Item name: ' || l_item.get_string('name'));
        end loop;
      end if;
    end if;
  end;
  

Best Practices for PL/SQL JSON Handling in APEX

• Always check for nulls and missing keys using has().

• Use apex_debug instead of dbms_output for better integration with APEX debugging.

• Minimize deep nesting in JSON structures; flatten them if possible for easier processing.

• Validate JSON format before attempting to parse using is_json() or client-side validation.

• Use json_mergepatch_t if you're implementing partial updates or merges.

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.

 

Official Documentation
You can explore the full set of JSON utilities in the Oracle documentation here:
https://docs.oracle.com/en/database/oracle/oracle-database/24/adjsn/index.html

Conclusion

Oracle APEX applications often benefit from the flexibility and power of JSON. With PL/SQL’s native JSON types, you can parse and process optional JSON payloads securely and efficiently. Whether you’re consuming RESTful services or building interactive components that return JSON from the client, mastering these techniques can simplify your backend logic and give you fine control over your data processing. Remember to validate inputs, handle nulls gracefully, and take advantage of Oracle's robust debugging and JSON APIs to build resilient applications.

 

How Do I use A_TABLE for Flattening Nested Arrays in ORACLE APEX

Introduction
In Oracle APEX, dealing with JSON arrays—especially nested ones—is a common task when integrating with RESTful APIs or handling structured data stored in tables. Oracle SQL provides powerful tools to flatten these arrays into relational rows for display or processing. One such tool is the use of A_TABLE or more precisely, JSON_TABLE with an alias that acts like a virtual table. This blog will walk through how to use JSON_TABLE to flatten nested JSON arrays, making them accessible for reports, forms, and logic in Oracle APEX.

How to Use A_TABLE (JSON_TABLE) for Flattening Nested Arrays in Oracle APEX

Step 1: Understand Your JSON Structure
Suppose you have a JSON payload like this stored in a CLOB column or retrieved from a REST source:

{
  "employee": {
    "name": "John",
    "skills": [
      { "type": "Database", "level": "Advanced" },
      { "type": "APEX", "level": "Intermediate" },
      { "type": "JavaScript", "level": "Basic" }
    ]
  }
}

Step 2: Flatten Using JSON_TABLE
You can use JSON_TABLE in SQL to extract and flatten the array into rows:

SELECT *
FROM JSON_TABLE(
  :P1_JSON, 
  '$.employee.skills[*]' 
  COLUMNS (
    skill_type  VARCHAR2(50) PATH '$.type',
    skill_level VARCHAR2(50) PATH '$.level'
  )
) skills_table;

This transforms each object in the skills array into a row with skill_type and skill_level.

Step 3: Use in a Report Region
Create a Classic or Interactive Report region and use the SQL above as the source. You can bind :P1_JSON to a page item or use a subquery to fetch it from a table.

Step 4: Handling JSON from a Table
If your JSON data is stored in a table (e.g., EMPLOYEE_DATA.JSON_DOC), your SQL would look like:

SELECT e.id, jt.*
FROM EMPLOYEE_DATA e,
     JSON_TABLE(
       e.json_doc,
       '$.employee.skills[*]'
       COLUMNS (
         skill_type  VARCHAR2(50) PATH '$.type',
         skill_level VARCHAR2(50) PATH '$.level'
       )
     ) jt;

Best Practices

• Always validate JSON data using IS JSON or ensure the structure with constraints.

• Use CLOB data type for large or nested JSON structures.

• Index JSON data with functional indexes using JSON_VALUE() or JSON_TABLE paths if querying frequently.

• In APEX, prefer REST Data Sources and Collections for performance and reusability if data is dynamic.

Oracle APEX Documentation Links

• JSON_TABLE in Oracle SQL

• Working with JSON in APEX

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.

 

Conclusion
Flattening nested arrays with JSON_TABLE in Oracle APEX allows developers to convert complex JSON structures into usable tabular formats. Whether you're reporting on REST data, storing user preferences, or integrating external services, mastering A_TABLE techniques gives you the power to work efficiently with JSON in any APEX application. With good structure, validation, and performance practices, your JSON-powered features will be both reliable and scalable.

 

Displaying JSON Data in a Form in ORACLE APEX

 Introduction

In Oracle APEX, it's common to work with JSON data—whether fetched from RESTful web services, stored in database columns, or retrieved from APIs. Displaying this JSON data in a user-friendly form layout enhances usability and allows for meaningful interaction with structured information. This blog explains how to display JSON data in an Oracle APEX form using built-in PL/SQL tools and declarative features, making your applications more dynamic and flexible.

How to Display JSON Data in a Form in Oracle APEX

To display JSON data in a form, you typically start by parsing the JSON and mapping it to individual APEX items (text fields, select lists, etc.). Here’s how to do it step by step:

Step 1: Store or Fetch JSON Data
You can either store JSON data in a table column (e.g., CLOB or VARCHAR2) or fetch it dynamically using apex_web_service.make_rest_request or a REST Data Source.

Example:

SELECT json_column 
INTO :P1_JSON_RAW 
FROM my_json_table 
WHERE id = :P1_ID;

Step 2: Parse the JSON Using APEX_JSON or JSON_VALUE
Use PL/SQL or SQL to extract values from the JSON and assign them to form page items.

PL/SQL using APEX_JSON:

BEGIN
  apex_json.parse(:P1_JSON_RAW);
  :P1_NAME := apex_json.get_varchar2(p_path => 'name');
  :P1_EMAIL := apex_json.get_varchar2(p_path => 'email');
  :P1_PHONE := apex_json.get_varchar2(p_path => 'contact.phone');
END;

Or using SQL in a process:

:P1_NAME := JSON_VALUE(:P1_JSON_RAW, '$.name');

Step 3: Create Page Items and Assign Values
On the page, create text fields for each JSON field you want to display, such as P1_NAME, P1_EMAIL, P1_PHONE. In a page load process or dynamic action, populate these items using the code above.

Step 4: Optional – Display Nested or Array Data
If your JSON includes nested arrays (e.g., list of orders), consider using JSON_TABLE to display them in an Interactive Report or Interactive Grid.

Example:

SELECT *
FROM JSON_TABLE(
  :P1_JSON_RAW,
  '$.orders[*]'
  COLUMNS (
    order_id    NUMBER PATH '$.id',
    order_total NUMBER PATH '$.total'
  )
);

Best Practices

• Always validate the JSON string using IS JSON or apex_json.is_json before parsing.

• Store large JSON in CLOB columns to avoid size limitations.

• Use apex_json.get_varchar2, get_number, or get_date depending on the expected data type.

• Use conditional display logic if some fields may be missing in the JSON.

Oracle APEX Documentation

• APEX_JSON Reference Guide

• JSON Functions in SQL

 

You can also display the parsed JSON data in form fields if needed. Here's how:

1. Create a Form:

• Create a new Form in APEX based on the USCG_DATA table.

2. Use a Query to Extract Data:

• Use a SQL query similar to the one above to extract the JSON values in the Form source. For example:

SELECT

    ID,

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

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

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

FROM USCG_DATA

WHERE ID = :P1_ID; -- Assuming :P1_ID is the primary key item for the form

3. Map the Results to Form Fields:

• Map the extracted JSON values (e.g., geometry_type, longitude, latitude) to the appropriate form items (e.g., P1_GEOMETRY_TYPE, P1_LONGITUDE, P1_LATITUDE).

Handling Complex JSON Structures

If the "GEOMETRY" column contains more complex nested JSON data, you can use the JSON_QUERY function or JSON_TABLE to extract arrays or nested objects.

Example: Using JSON_QUERY for Nested JSON

If "GEOMETRY" contains an array of coordinates and you want to extract all of them, you could use JSON_QUERY:

SELECT

    ID,

    JSON_QUERY(GEOMETRY, '$.coordinates') AS coordinates

FROM USCG_DATA;

This query will return the entire coordinates array as a JSON object. You can then manipulate it further in APEX or process it with PL/SQL.

Conclusion
Displaying JSON data in Oracle APEX forms bridges the gap between modern APIs and user-friendly interfaces. With tools like APEX_JSON and SQL/JSON functions, developers can flexibly extract, parse, and display structured data efficiently. By following best practices for performance and error handling, your APEX applications can become powerful front-ends for JSON-driven backends.

HOW DO I Use SQL to Extract JSON Data

 Introduction

Working with JSON data in Oracle APEX has become increasingly common, especially when integrating with RESTful Web Services, external APIs, or complex application logic. Oracle SQL provides powerful tools to parse and extract data directly from JSON documents stored in CLOB, VARCHAR2, or native JSON columns. This blog post explains how to use SQL to extract JSON data efficiently within APEX, from simple key-value pairs to nested arrays.

How to Extract JSON Data Using SQL in Oracle APEX

To extract data from a JSON structure in SQL, you can use built-in JSON functions and SQL/JSON query operators like JSON_VALUE, JSON_QUERY, and JSON_TABLE.

1. JSON_VALUE for Scalar Extraction
Use JSON_VALUE when you want to extract a single scalar value (e.g., string, number).

Example:

SELECT JSON_VALUE(json_column, '$.employee.name') AS employee_name
FROM my_json_table;

This retrieves the name value inside the employee object.

2. JSON_QUERY for JSON Fragments
Use JSON_QUERY when you need to retrieve a JSON object or array as a JSON string.

Example:

SELECT JSON_QUERY(json_column, '$.employee.projects') AS project_list
FROM my_json_table;

This extracts the projects array from the JSON data.

3. JSON_TABLE for Relational Mapping
JSON_TABLE is ideal for turning nested JSON into a relational result set.

Example:

SELECT *
FROM JSON_TABLE (
  (SELECT json_column FROM my_json_table),
  '$.employee.projects[*]'
  COLUMNS (
    project_name VARCHAR2(100) PATH '$.name',
    start_date   DATE          PATH '$.start'
  )
) jt;

This maps an array of projects into individual rows, with project_name and start_date columns.

4. Querying JSON Stored in APEX Collections or CLOBs
If you're storing JSON in an APEX collection or CLOB column, you can still use these JSON functions, provided the content is valid JSON.

Example:

SELECT JSON_VALUE(c001, '$.customer.email') AS email
FROM apex_collections
WHERE collection_name = 'MY_JSON_DATA';

Best Practices

• Always validate JSON syntax before querying, especially when using user-supplied data.

• Use indexes on JSON columns to improve performance (available via Oracle’s JSON search indexes).

• Prefer JSON_TABLE when you need structured or tabular outputs.

• Avoid using LIKE '%...%' on JSON; use JSON functions instead.

• Use Oracle’s IS JSON condition to ensure content validity.

Oracle APEX Documentation

• Working with JSON Data in Oracle Database

• APEX_JSON PL/SQL API Reference 

 

You can write SQL queries in APEX to extract and display the JSON data from the CLOB column ("GEOMETRY") using Oracle’s JSON functions.

Here’s an example of how to query the JSON data stored in the "GEOMETRY" column to display specific values.

Query to extract the "type" and "coordinates" from the "GEOMETRY" JSON column:

SELECT

    ID,

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

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

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

FROM USCG_DATA;

In this example:

• JSON_VALUE(GEOMETRY, '$.type'): Extracts the "type" field from the JSON.

• JSON_VALUE(GEOMETRY, '$.coordinates[0]'): Extracts the longitude from the "coordinates" array (first element).

• JSON_VALUE(GEOMETRY, '$.coordinates[1]'): Extracts the latitude from the "coordinates" array (second element).

You can use the above SQL query to display the extracted data in an APEX report, Interactive Grid, or other components.

Example: Displaying the Data in an Interactive Report (IR)

1. Create an Interactive Report (IR): 

• Go to App Builder in your Oracle APEX application.

• Create a new Interactive Report or Interactive Grid.

• In the SQL Query section of the report, use the query mentioned above:

SELECT

    ID,

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

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

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

FROM USCG_DATA;

• Run the report, and it will display the "ID", "geometry_type", "longitude", and "latitude" in the report.

Conclusion
Using SQL to extract JSON data in Oracle APEX provides a seamless way to interact with complex data structures and REST service responses. By mastering functions like JSON_VALUE, JSON_QUERY, and JSON_TABLE, developers can efficiently transform, analyze, and present JSON data in reports, charts, and forms. Leveraging these tools within APEX applications allows for powerful and dynamic user experiences built on modern data exchange formats.

ORACLE APEX: Understanding the JSON Structure in "GEOMETRY"

Introduction
When working with spatial data in Oracle APEX, especially through RESTful Web Services, map components, or external GIS integrations, you may encounter the geometry JSON object. This structure holds spatial coordinate information—points, lines, polygons—used for rendering data on maps. Understanding the geometry JSON format is essential for parsing, displaying, and interacting with geospatial data within APEX applications.

Understanding the JSON Structure in "GEOMETRY"

In most spatial data standards (e.g., GeoJSON or Esri JSON), the geometry object contains spatial information that defines the shape and location of geographic features. A typical structure may look like:

{
  "geometry": {
    "type": "Point",
    "coordinates": [102.0, 0.5]
  }
}

Or for a polygon:

{
  "geometry": {
    "type": "Polygon",
    "coordinates": [
      [
        [30, 10], [40, 40], [20, 40], [10, 20], [30, 10]
      ]
    ]
  }
}

How to Work with This in Oracle APEX

1. Consume JSON with Geometry

   • When consuming RESTful Web Services that return geometry data, configure a Web Source Module.

   • Map the geometry field as a CLOB or JSON column in your report region.

2. Parse JSON Data in APEX

   • Use APEX_JSON or JSON_TABLE in SQL queries or PL/SQL to extract coordinates.
     Example:

   SELECT jt.type, jt.longitude, jt.latitude
     FROM your_table t,
          JSON_TABLE(t.geometry, '$'
            COLUMNS (
              type VARCHAR2(20) PATH '$.type',
              longitude NUMBER PATH '$.coordinates[0]',
              latitude NUMBER PATH '$.coordinates[1]'
            )
          ) jt;
   

3. Display on a Map Region

   • Create a Map region in your APEX page.

   • Set the data source to your SQL query.

   • Ensure that the longitude and latitude columns are mapped properly.

   • Choose geometry types like Point, LineString, or Polygon in the region’s settings.

4. Storing Geometry in the Database

   • You can also store spatial data using Oracle Spatial (SDO_GEOMETRY).

   • Convert JSON geometry to SDO_GEOMETRY using PL/SQL for advanced spatial operations.

Best Practices

• Normalize the incoming geometry JSON for performance and ease of parsing.

• Store raw geometry JSON as CLOB only when necessary—prefer structured columns.

• Use Oracle Spatial for high-performance spatial queries and rendering.

• If integrating external map services, match geometry type standards (WKT, GeoJSON).

• Secure and validate incoming JSON data to prevent malformed geometry inputs.

Oracle APEX Documentation

• Using Map Components

• APEX_JSON Reference

• Oracle Spatial and Graph

 To display JSON data stored in the "GEOMETRY" column of your USCG_DATA table in Oracle APEX, you'll need to parse the JSON stored in this CLOB column and extract the relevant data for display in a readable format.

Assuming the JSON stored in the "GEOMETRY" column is a well-structured JSON object, you can use SQL queries and the Oracle SQL functions like JSON_VALUE, JSON_QUERY, or APEX_JSON to extract values from the JSON structure.

To display the JSON data from the "GEOMETRY" column in your USCG_DATA table in Oracle APEX, you can:

• Use SQL functions like JSON_VALUE or JSON_QUERY to extract values from the JSON.

• Display the extracted values in APEX components such as Interactive Reports, Forms, or Interactive Grids.

• Use PL/SQL to perform more complex processing if necessary.

Below is a step-by-step guide on how to display and extract JSON data from the "GEOMETRY" column in your APEX application.

 

Before you proceed, inspect the JSON structure in the "GEOMETRY" column. You need to understand the structure to know what specific data you want to extract. For example, if the "GEOMETRY" column stores something like:

{

  "type": "Point",

  "coordinates": [ -118.291, 34.056 ]

}

You can extract the "type" and "coordinates" values.

Conclusion
Understanding the structure and usage of geometry JSON in Oracle APEX unlocks the ability to build spatially-aware applications. From integrating with map regions to parsing location data and rendering polygons or routes, APEX provides both declarative and programmatic tools to handle this complex data format. Mastering the geometry object allows you to visualize and analyze spatial data more effectively in your Oracle APEX solutions.