Effortless JSON Generation in Oracle APEX

If you’ve worked with Oracle APEX long enough, you’ve probably done this at least once:

built JSON manually
glued strings together
stared at a missing comma at 2am wondering what went wrong

It works… until it really doesn’t.

And the truth is:
A lot of developers are still doing JSON the hard way in Oracle.

Let’s clean that up.

The Real Problem

The issue isn’t that Oracle can’t handle JSON. It’s that we don't trust it. We’ve been burned by weird ORA-errors or slow CLOB handling in the past, so we fall back to what we know: string building.

But string-building is a trap. It’s fragile, it’s hard to read, and it makes you look like a junior dev who hasn't checked the docs since 2012. Most people either don’t realize how beefy SQL is now or they try to shove APEX_JSON into spots where it just doesn't fit.

SQL Can Already Do This

You don’t need a fancy framework. You don’t need hacks. Oracle already gives you the goods.

Look at this:

SELECT JSON_OBJECT(
  'id' VALUE e.employee_id,
  'name' VALUE e.first_name || ' ' || e.last_name
)
FROM employees e;

That’s it. That’s the whole thing. It’s clean, it’s fast, and the database handles the escaping so you don't have to lie awake wondering if a stray comma is going to wreck your AJAX callback.

Nested JSON Without Losing Your Mind

This is where people usually freak out and go back to their old, bad habits. Nesting looks scary. But if you keep your head, it’s just a query inside a query.

SELECT JSON_OBJECT(
  'department' VALUE d.department_name,
  'employees' VALUE (
    SELECT JSON_ARRAYAGG(
      JSON_OBJECT(
        'id' VALUE e.employee_id,
        'name' VALUE e.first_name
      )
    )
    FROM employees e
    WHERE e.department_id = d.department_id
  )
)
FROM departments d;

Yeah, it’s a bit dense. It’s a lot to look at. But it’s predictable. It’s structured. Most importantly, it's handled by the database engine itself, which is way better at this than your hand-rolled string-concat logic will ever be.

Let the database do its job. It’s literally what it was built for.

PL/SQL: When You Actually Need It

I see people jump into PL/SQL way too fast. You should only go there if things get truly hairy—like when your logic is full of "if-then" branches or you’re pulling data from five different places that don't talk to each other.

And even then... don't you dare go back to strings. Use the API.

A Quick Helper

function generate_json(
    p_entries apex_t_varchar2
) return clob
is
    l_clob clob;
begin
    if p_entries.count = 0 or mod(p_entries.count, 2) != 0 then
        return null;
    end if;

    apex_json.initialize_clob_output;
    apex_json.open_object;

    for i in 1 .. p_entries.count / 2 loop
        apex_json.write(
            p_entries((i * 2) - 1),
            p_entries(i * 2)
        );
    end loop;

    apex_json.close_object;

    l_clob := apex_json.get_clob_output(p_free => true);
    return l_clob;
end generate_json;

How to use it:

select
    generate_json(
        apex_t_varchar2(
            'label', e.ename,
            'salary', e.sal,
            'canCreate', case when e.sal > 3000 then 'Y' else 'N' end
        )
    ) as menu_payload
from emp e;

Edge Cases to Consider

1. Odd Number of Entries

If you’re building pairs, validate them. If you have five items for a key-value function, it’s going to crash. Use

mod(p_entries.count, 2) = 0

or just prepare for the bugs to crawl in.

2. CLOB vs VARCHAR2

Don't overthink this. If there’s any chance the data is big, use RETURNING CLOB.APEX handles it. Your users won't notice, but your error logs will.

3. NULL Values

JSON_OBJECT likes to skip nulls by default. Sometimes that's fine. Sometimes it breaks your frontend. Be loud about what you want:

json_object(
  'bonus' value e.comm null on null
)

Don’t leave it to chance.

4. Client-Side Parsing Cost

Just because you can send everything… should you?

Probably not.

keep payloads small
send only what you need

Your frontend (and your users) will thank you.

5. Performance

SQL JSON is fast. It's set-based. PL/SQL JSON is row-by-row and, frankly, much slower when you're dealing with thousands of records. Use SQL whenever you can.

The Hidden Advantage Most People Miss

When you do this in SQL, it stays close to the data. It’s easier to tune. You aren't shipping raw data to a middle layer just to wrap it in curly braces. In APEX, this makes your AJAX callbacks and REST sources feel like they’re flying.

Final Thought

SQL isn't just for grabbing rows. It's for shaping them. Once you get that, the headache goes away. It just... works.