PL/SQL Code snippet: easy way to generate a JSON string object in SQL queries

Introduction

When pushing the limits of Oracle APEX widgets to add custom functionality, you are limited to one column to pass values from the database to the JavaScript side. This one column is often not sufficient as you need to pass more than one value (for example generating a context menu with a generated link for each menu item). In these scenarios, a simple JSON string that contains each property of interest can be the most elegant solution.

When in a SQL query context, JSON generation can be a bit of headache as you either need to create a specific function for each context or to manually build the string yourself in the query. To solve these issues, I’ve created a general purpose utility function that takes an apex_t_varchar2 of key-value pairs and packs them into a simple JSON object string.

PL/SQL code

function generate_json(p_entries apex_t_varchar2) return clob
is
begin
    apex_json.initialize_clob_output();
    apex_json.open_object();

    -- we can only properly generate the output if there is there an entry or the count is divisble by 2 (key, value)
    if(p_entries.count > 0 and mod(p_entries.count, 2) = 0) then 
        for i in p_entries.first..p_entries.last/2 loop
            apex_json.write(p_entries((2 * i) - 1), p_entries(2 * i));
        end loop;
    end if;

    apex_json.close_object();

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

Usage example

select generate_json(apex_t_varchar2(
        'name', ename,
        'salary', sal,
        'department', dname
    )) employee_info
  from emp
  join dept using (deptno)

Running this gives the following results: