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.