Tiempo de lectura: 6 minutos

---

PART 1: INTRODUCTION AND CONTEXT

The Classic Concatenation Problem

Before APEX_JSON, developers concatenated JSON strings manually:

Fragmento de código

-- ❌ The old way (PROBLEM: special characters not escaped)
declare
    v_name varchar2(100) := 'John O''Reilly';  -- Apostrophe
    v_salary number := 50000;
    v_json varchar2(4000);
begin
    v_json := '{"employee":"' || v_name || '","salary":' || v_salary || '}';
    -- INVALID Result: {"employee":"John O'Reilly","salary":50000}
    -- The apostrophe breaks the JSON
end;

This JSON is invalid. The apostrophe is not escaped. Multiply this by 1000 lines of code and you have a maintenance disaster.

The Solution: APEX_JSON

Fragmento de código

-- ✅ APEX_JSON escapes automatically
declare
    v_name varchar2(100) := 'John O''Reilly';
    v_salary number := 50000;
begin
    apex_json.initialize_clob_output;
    
    apex_json.open_object;
    apex_json.write('employee', v_name);        -- Escapes automatically
    apex_json.write('salary', v_salary);        -- Converts types automatically
    apex_json.close_object;
    
    -- VALID Result: {"employee":"John O'Reilly","salary":50000}
    dbms_output.put_line(apex_json.get_clob_output);
    apex_json.free_output;
end;

With APEX_JSON you get:

• ✅ Automatic escaping of special characters
• ✅ Automatic type handling (number, date, boolean)
• ✅ JSON structure validation
• ✅ Fluid and readable APIs
• ✅ Better performance

---

PART 2: NATIVE FUNCTIONS EXPLAINED

GROUP 1: INITIALIZATION

apex_json.initialize_clob_output()

What exactly does it do? Initializes an internal CLOB (Character Large Object) buffer in the current PL/SQL session. This buffer accumulates everything you write with apex_json.write() until you call get_clob_output().

Parameters: None

Returns: Void (modifies internal state)

When to use?

• When you need to build a JSON and return it complete (not streaming).
• When working with JSON that fits in memory (< 500MB).
• When you need access to the final JSON with get_clob_output().

Technical example:

Fragmento de código

declare
    v_json clob;
begin
    -- Step 1: Initialize CLOB buffer
    apex_json.initialize_clob_output;
    
    -- Step 2: Write data (accumulated in the internal buffer)
    apex_json.open_object;
    apex_json.write('timestamp', systimestamp);
    apex_json.write('status', 'active');
    apex_json.close_object;
    
    -- Step 3: Retrieve the full buffer
    v_json := apex_json.get_clob_output;
    
    -- Step 4: CRITICAL - Free memory
    apex_json.free_output;
    
    -- Step 5: Now you can use v_json without limitations
    insert into json_log (payload) values (v_json);
    commit;
end;

Important technical details:

• The CLOB buffer resides in the PL/SQL session, not in the SGA.
• If you don’t call free_output(), the buffer persists until the end of the session (memory leak).
• Maximum CLOB size depends on the server’s memory_target.
• Write operations are not instant—they accumulate internally.

---

apex_json.initialize_output()

What exactly does it do? Initializes an HTP (HyperText Protocol) buffer that writes directly to the HTTP response. This is different from CLOB—data is sent immediately to the client as it is generated.

Parameters: None

Returns: Void (prepares the HTTP buffer)

When to use?

• In REST endpoints that respond with JSON directly.
• When you need to return millions of rows without loading everything into memory.
• When you want the client to start receiving data immediately.
• In procedures executed from APEX as AJAX callbacks.

Advanced example (Streaming 1 million rows):

Fragmento de código

create or replace procedure stream_transactions_1m as
    nm owa.vc_arr;
    vl owa.vc_arr;
    l_rows integer := 0;
begin
    -- STEP 1: Initialize minimal CGI environment
    -- This is REQUIRED to use initialize_output
    nm(1) := 'dummy_param';
    vl(1) := 'dummy_value';
    owa.init_cgi_env(1, nm, vl);

    -- STEP 2: Initialize HTTP buffer (not CLOB)
    apex_json.initialize_output;

    -- STEP 3: Start JSON response
    apex_json.open_object;
    apex_json.write('status', 'streaming');
    apex_json.write('started_at', systimestamp);
    apex_json.open_array('data');
    
    -- STEP 4: Iterate over MILLIONS of rows
    -- Each write() is sent to the client immediately
    for c in (
        select id, amount, created_at, status 
        from large_transactions_table
        where created_at >= trunc(sysdate)
    ) loop
        apex_json.open_object;
        apex_json.write('id', c.id);
        apex_json.write('amount', c.amount);
        apex_json.write('ts', to_char(c.created_at, 'YYYY-MM-DD"T"HH24:MI:SS"Z"'));
        apex_json.write('status', c.status);
        apex_json.close_object;
        
        l_rows := l_rows + 1;
        
        -- Log every 10K rows
        if mod(l_rows, 10000) = 0 then
            dbms_output.put_line('Streamed ' || l_rows || ' rows');
        end if;
    end loop;
    
    -- STEP 5: Close structure
    apex_json.close_array;
    apex_json.open_object('summary');
    apex_json.write('total_rows', l_rows);
    apex_json.write('completed_at', systimestamp);
    apex_json.close_object;
    apex_json.close_object;
    
    -- NO free_output() needed - HTP cleans itself automatically

exception 
    when others then
        -- Error during streaming is critical—client already received data
        dbms_output.put_line('CRITICAL: Error at row ' || l_rows);
        dbms_output.put_line('Error: ' || sqlerrm);
        -- Attempt to close JSON gracefully
        apex_json.close_object;
end stream_transactions_1m;

CRITICAL technical details:

• Requires owa.init_cgi_env() to establish HTTP context.
• Data is written directly to htp.print() internally.
• If there is an exception mid-stream, the client receives incomplete/invalid JSON.
• You cannot call get_clob_output() after using initialize_output().

---

GROUP 2: BUILDING OBJECTS AND ARRAYS

apex_json.open_object() / apex_json.close_object()

What does it do? Creates a JSON object ({...}). Each open_object() must be balanced with a close_object().

Parameters:

Fragmento de código

apex_json.open_object(
    p_name varchar2  -- Optional: key for the object if inside another
)

Returns: Void

Basic example:

Fragmento de código

declare
begin
    apex_json.initialize_clob_output;
    
    -- Root object (unnamed)
    apex_json.open_object;
    apex_json.write('name', 'Juan');
    apex_json.write('age', 30);
    apex_json.close_object;
    
    -- Generated JSON: {"name":"Juan","age":30}
end;

Example with nesting and names:

Fragmento de código

declare
begin
    apex_json.initialize_clob_output;
    
    apex_json.open_object;  -- Root (unnamed)
    apex_json.write('employee_id', 12345);
    
    -- Nested object with key 'personal'
    apex_json.open_object('personal');
    apex_json.write('first_name', 'Juan');
    apex_json.write('last_name', 'García');
    apex_json.write('email', 'juan@company.com');
    apex_json.close_object;  -- Closes personal
    
    -- Another nested object
    apex_json.open_object('employment');
    apex_json.write('title', 'Senior Developer');
    apex_json.write('salary', 95000);
    apex_json.write('start_date', to_date('2020-01-15', 'YYYY-MM-DD'));
    apex_json.close_object;  -- Closes employment
    
    apex_json.close_object;  -- Closes root
end;

Common errors:

Fragmento de código

-- ❌ INCORRECT: Forgot close_object()
apex_json.open_object;
apex_json.write('name', 'Juan');
-- Missing: apex_json.close_object;
-- Result: {"name":"Juan"  ← OPEN JSON, INVALID

-- ❌ INCORRECT: Closes twice
apex_json.open_object;
apex_json.write('name', 'Juan');
apex_json.close_object;
apex_json.close_object;  -- ← Error, nothing to close
-- Result: Exception

-- ✅ CORRECT: Balanced
apex_json.open_object;
apex_json.write('name', 'Juan');
apex_json.close_object;  -- A single pair

---

apex_json.open_array() / apex_json.close_array()

What does it do? Creates a JSON array ([...]). Elements are added between open_array() and close_array().

Example: Array of objects (most common):

Fragmento de código

declare
    v_json clob;
begin
    apex_json.initialize_clob_output;
    
    apex_json.open_object;
    apex_json.write('department', 'Engineering');
    
    -- Array of employees under key 'team_members'
    apex_json.open_array('team_members');
    
    for emp in (select empno, ename, sal from emp where deptno = 20) loop
        apex_json.open_object;  -- Each element is an object
        apex_json.write('id', emp.empno);
        apex_json.write('name', emp.ename);
        apex_json.write('salary', emp.sal);
        apex_json.close_object;
    end loop;
    
    apex_json.close_array;  -- Closes team_members array
    apex_json.close_object; -- Closes root object
    
    v_json := apex_json.get_clob_output;
    apex_json.free_output;
end;

Limitations and considerations:

Fragmento de código

-- ✅ Arrays can nest
apex_json.open_array;
  apex_json.open_array;    -- Array inside array
    apex_json.write(1);
    apex_json.write(2);
  apex_json.close_array;
apex_json.close_array;
-- Result: [[1,2]]

-- ⚠️ For arrays > 100K elements, use streaming
-- In CLOB mode (initialize_clob_output), > 100K elements
-- can cause memory issues on some servers

---

GROUP 3: WRITING VALUES

apex_json.write(...) (THE MOST IMPORTANT FUNCTION)

What does it do? Writes a value (of any supported type) to the JSON.

Supported types and conversion:

Fragmento de código

-- VARCHAR2 → JSON String
apex_json.write('name', 'Juan García');
-- Result: "name":"Juan García"

-- NUMBER → JSON Number (no quotes)
apex_json.write('salary', 50000);
apex_json.write('percentage', 99.99);
-- Result: "salary":50000, "percentage":99.99

-- DATE → ISO 8601 String
apex_json.write('hire_date', to_date('2020-01-15', 'YYYY-MM-DD'));
-- Result: "hire_date":"2020-01-15T00:00:00Z"

-- TIMESTAMP
apex_json.write('updated_at', systimestamp);
-- Result: "updated_at":"2024-02-16T10:30:45.123456Z"

-- BOOLEAN (PL/SQL true/false) → JSON boolean (no quotes)
apex_json.write('is_active', true);
apex_json.write('is_deleted', false);
-- Result: "is_active":true, "is_deleted":false

-- NULL → JSON null (no quotes)
declare
    v_commission number := null;
begin
    apex_json.write('commission', v_commission);
    -- Result: "commission":null
end;

-- CLOB with special characters
declare
    v_text clob := 'Línea 1' || chr(10) || 'Línea 2';
begin
    apex_json.write('description', v_text);
    -- Result: "description":"Línea 1\nLínea 2"
    -- chr(10) converts to \n (escaped)
end;

Automatic escaping of special characters:

APEX_JSON automatically escapes characters that break JSON:

Fragmento de código

-- Double quotes
declare
    v_text varchar2(100) := 'Ella dijo "Hola"';
begin
    apex_json.write('quote', v_text);
    -- Result: "quote":"Ella dijo \"Hola\""
end;

-- Backslash
declare
    v_path varchar2(100) := 'C:\Users\Juan';
begin
    apex_json.write('filepath', v_path);
    -- Result: "filepath":"C:\\Users\\Juan"
end;

-- Control characters (newline, tab, etc.)
declare
    v_text varchar2(100) := 'Tab:' || chr(9) || 'Value';
begin
    apex_json.write('data', v_text);
    -- Result: "data":"Tab:\tValue"
end;

-- Special Unicode characters
declare
    v_emoji varchar2(100) := '🎉 Celebration 🎊';
begin
    apex_json.write('message', v_emoji);
    -- Result: "message":"🎉 Celebration 🎊"
    -- Unicode is preserved correctly
end;

---

PART 3: COMPLETE EXAMPLE 1 – ADVANCED GEOJSON

Fragmento de código

-- Locations table
create table warehouses (
    id number generated by default as identity primary key,
    warehouse_name varchar2(100) not null,
    latitude number,
    longitude number,
    category varchar2(20), -- 'vip', 'standard', 'regional'
    capacity number,
    manager_name varchar2(100),
    active_yn char(1) default 'Y'
);

-- Function that generates GeoJSON
create or replace function get_warehouses_geojson return clob is
    v_json clob;
    l_color varchar2(10);
    l_icon varchar2(30);
begin
    apex_json.initialize_clob_output;
    
    apex_json.open_object;
    apex_json.write('type', 'FeatureCollection');
    apex_json.write('name', 'Global Warehouse Network');
    apex_json.write('generated_at', to_char(systimestamp, 'YYYY-MM-DD"T"HH24:MI:SS"Z"'));
    apex_json.open_array('features');
    
    for w in (select * from warehouses) loop
        -- Logic to customize based on data
        if w.category = 'vip' then
            l_color := '#FF0000';
            l_icon := 'star';
        else
            l_color := '#0000FF';
            l_icon := 'circle';
        end if;

        apex_json.open_object;
        apex_json.write('type', 'Feature');
        
        -- Geometry (GeoJSON Standard)
        apex_json.open_object('geometry');
        apex_json.write('type', 'Point');
        apex_json.open_array('coordinates');
        apex_json.write(w.longitude);
        apex_json.write(w.latitude);
        apex_json.close_array;
        apex_json.close_object; -- geometry
        
        -- Properties
        apex_json.open_object('properties');
        apex_json.write('id', w.id);
        apex_json.write('name', w.warehouse_name);
        apex_json.write('marker-color', l_color);
        apex_json.write('marker-symbol', l_icon);
        apex_json.close_object; -- properties
        
        apex_json.close_object; -- Feature
    end loop;
    
    apex_json.close_array; -- features
    apex_json.close_object; -- FeatureCollection
    
    v_json := apex_json.get_clob_output;
    apex_json.free_output;
    
    return v_json;
end;

---

PART 5: OPTIMIZATION AND BEST PRACTICES

1. Memory Management (CRITICAL)

Fragmento de código

-- ❌ BAD: Memory leak
declare
begin
    for i in 1..10000 loop
        apex_json.initialize_clob_output;
        apex_json.open_object;
        apex_json.write('iteration', i);
        apex_json.close_object;
        -- MISSING free_output → 10,000 buffers in memory
    end loop;
end;

-- ✅ GOOD: Frees every iteration
declare
    v_json clob;
begin
    for i in 1..10000 loop
        apex_json.initialize_clob_output;
        apex_json.open_object;
        apex_json.write('iteration', i);
        apex_json.close_object;
        v_json := apex_json.get_clob_output;
        
        -- Process v_json...
        
        apex_json.free_output; -- ✅ Free
    end loop;
end;

2. Handling dynamic paths

Fragmento de código

-- For arrays, always use parameterized paths
for i in 1..l_count loop
    -- ✅ Efficient: Parameterized path
    l_value := apex_json.get_varchar2(
        p_path => '[%d].field_name',
        p0 => i,
        p_values => l_values
    );
end loop;

Next steps:

1. Test these exact examples in your environment.
2. Implement in your stored procedures.
3. Monitor memory in production.

Questions about APEX_JSON? Leave me a comment.