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.