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.

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.