Dynamic JSON Storage in Oracle APEX Forms

Introduction

Oracle APEX applications often require storing complex, structured data in a flexible way. Instead of creating multiple tables for dynamic data structures, we can leverage JSON storage in a CLOB column. This method provides flexibility while ensuring data integrity.

In this post, we will demonstrate how to:
Store JSON data in a CLOB column in Oracle APEX
Use PL/SQL to manipulate JSON data
Retrieve and display JSON values dynamically in APEX Forms

Why Use JSON Storage?

Flexibility: No need for rigid table structures.
Simplified Data Model: Store complex data in a single field instead of multiple related tables.
Easy Integration: JSON is widely used in REST APIs and modern applications.
Query Power: Oracle supports JSON querying using SQL/PLSQL (JSON_TABLE, APEX_JSON, IS JSON, etc.).

Step 1: Create a Table with a JSON Column

We will create a table to store JSON data in a CLOB column.

CREATE TABLE customer_data (
id NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
customer_name VARCHAR2(100),
json_details CLOB CHECK (json_details IS JSON) — Ensuring valid JSON
);

The CHECK (json_details IS JSON) ensures that only valid JSON is stored.

Step 2: Create an APEX Form to Store JSON Data

Create a Form

In APEX, create a new Form (Editable) on the customer_data table.
Include fields for customer_name, but exclude json_details.

Create a JSON Textarea

Add a Text Area item (P1_JSON_DETAILS) to capture JSON input.
Set Escape Special Characters to No.
Set Height (e.g., 10 rows) for better visibility.

Step 3: Storing JSON Data Using a Process

When the form is submitted, we need to store the JSON data in the table.

Create a BEFORE INSERT or BEFORE UPDATE process with the following PL/SQL code:

Step 4: Retrieving and Displaying JSON Data

To display JSON values dynamically in APEX:

Create a PL/SQL Dynamic Content Region to parse and display JSON fields.

Example using APEX_JSON:

DECLARE
v_json CLOB;
v_name VARCHAR2(100);
v_age NUMBER;
BEGIN
SELECT json_details INTO v_json
FROM customer_data
WHERE id = :P1_ID;

APEX_JSON.parse(v_json);
v_name := APEX_JSON.get_varchar2(p_path => ‘name’);
v_age := APEX_JSON.get_number(p_path => ‘age’);

HTP.p(‘Name: ‘ || v_name || ‘ ’);
HTP.p(‘Age: ‘ || v_age || ‘ ’);
END;

Step 5: Query JSON Fields in SQL Reports

Oracle allows querying JSON directly using JSON_TABLE.

Example: Extracting values in an Interactive Report:

SELECT id,
customer_name,
json_details,
jt.name AS json_name,
jt.age AS json_age
FROM customer_data,
JSON_TABLE(json_details, ‘$’ COLUMNS
name VARCHAR2(50) PATH ‘$.name’,
age NUMBER PATH ‘$.age’
) jt;

This query extracts name and age from the JSON structure for reporting.

Step 6: Updating JSON Data in PL/SQL

To update a specific field in JSON without modifying the whole object:

DECLARE
v_json CLOB;
v_new_json CLOB;
BEGIN
— Get current JSON
SELECT json_details INTO v_json
FROM customer_data
WHERE id = :P1_ID;

— Modify JSON
v_new_json := APEX_JSON.UPDATE_JSON(v_json, ‘age’, 30);

— Update table
UPDATE customer_data
SET json_details = v_new_json
WHERE id = :P1_ID;

COMMIT;
END;

APEX_JSON.UPDATE_JSON allows updating a JSON field dynamically.

Conclusion

This solution enables flexible JSON storage in Oracle APEX forms while leveraging PL/SQL and built-in JSON functions for querying and manipulation.

Key Takeaways:
Store JSON in a CLOB column with validation
Query JSON fields in SQL using JSON_TABLE
Use APEX_JSON for parsing and manipulation
Update JSON fields without rewriting the entire JSON

chinnappa