APEX_JSON is a new PL/SQL API included with Oracle Application Express (APEX) 5.0 that provides utilities for parsing and generating JSON. While APEX_JSON was primarily intended to be used by APEX developers, there are some hooks that can allow it to be used as a standalone PL/SQL package.
Solution
The following solution uses APEX_JSON to create the JSON that represents a department in the HR schema. APEX_JSON basically writes JSON content to a buffer. By default, the buffer used is the HTP buffer in the database as that’s what APEX reads. But as you can see with line 27, it’s possible to redirect the output to a CLOB buffer instead. Once we’ve redirected the output, we can make API calls to open/close objects & arrays and write values to them. When we’re done writing out the JSON we can make a call to get_clob_output to get the JSON contents. I’ve highlighted some of the relevant lines…
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 |
create or replace function get_dept_apex_json( p_dept_id in departments.department_id%type ) return clob is cursor manager_cur ( p_manager_id in employees.employee_id%type ) is select * from employees where employee_id = manager_cur.p_manager_id; l_date_format constant varchar2(20) := 'DD-MON-YYYY'; l_dept_rec departments%rowtype; l_dept_json_clob clob; l_loc_rec locations%rowtype; l_country_rec countries%rowtype; l_manager_rec manager_cur%rowtype; l_job_rec jobs%rowtype; begin apex_json.initialize_clob_output; select * into l_dept_rec from departments where department_id = get_dept_apex_json.p_dept_id; apex_json.open_object(); --department apex_json.write('id', l_dept_rec.department_id); apex_json.write('name', l_dept_rec.department_name); select * into l_loc_rec from locations where location_id = l_dept_rec.location_id; apex_json.open_object('location'); apex_json.write('id', l_loc_rec.location_id); apex_json.write('streetAddress', l_loc_rec.street_address); apex_json.write('postalCode', l_loc_rec.postal_code); select * into l_country_rec from countries cou where cou.country_id = l_loc_rec.country_id; apex_json.open_object('country'); apex_json.write('id', l_country_rec.country_id); apex_json.write('name', l_country_rec.country_name); apex_json.write('regionId', l_country_rec.region_id); apex_json.close_object(); --country apex_json.close_object(); --location open manager_cur(l_dept_rec.manager_id); fetch manager_cur into l_manager_rec; if manager_cur%found then apex_json.open_object('manager'); apex_json.write('id', l_manager_rec.employee_id); apex_json.write('name', l_manager_rec.first_name || ' ' || l_manager_rec.last_name); apex_json.write('salary', l_manager_rec.salary); select * into l_job_rec from jobs job where job.job_id = l_manager_rec.job_id; apex_json.open_object('job'); apex_json.write('id', l_job_rec.job_id); apex_json.write('title', l_job_rec.job_title); apex_json.write('minSalary', l_job_rec.min_salary); apex_json.write('maxSalary', l_job_rec.max_salary); apex_json.close_object(); --job apex_json.close_object(); --manager else apex_json.write('manager', '', p_write_null => true); end if; close manager_cur; apex_json.open_array('employees'); for emp_rec in ( select * from employees where department_id = l_dept_rec.department_id ) loop apex_json.open_object(); --employee apex_json.write('id', emp_rec.employee_id); apex_json.write('name', emp_rec.first_name || ' ' || emp_rec.last_name); apex_json.write('isSenior', emp_rec.hire_date < to_date('01-jan-2005', 'dd-mon-yyyy')); apex_json.write('commissionPct', emp_rec.commission_pct, p_write_null => true); apex_json.open_array('jobHistory'); for jh_rec in ( select job_id, department_id, start_date, end_date from job_history where employee_id = emp_rec.employee_id ) loop apex_json.open_object(); --job apex_json.write('id', jh_rec.job_id); apex_json.write('departmentId', jh_rec.department_id); apex_json.write('startDate', to_char(jh_rec.start_date, l_date_format)); apex_json.write('endDate', to_char(jh_rec.end_date, l_date_format)); apex_json.close_object(); --job end loop; apex_json.close_array(); --jobHistory apex_json.close_object(); --employee end loop; apex_json.close_array(); --employees apex_json.close_object(); --department l_dept_json_clob := apex_json.get_clob_output; apex_json.free_output; return l_dept_json_clob; exception when others then if manager_cur%isopen then close manager_cur; end if; raise; end get_dept_apex_json; |
Output
When passed a department id of 10, the function returns a CLOB populated with JSON that matches the goal 100%.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 |
{ "id": 10, "name": "Administration", "location": { "id": 1700, "streetAddress": "2004 Charade Rd", "postalCode": "98199", "country": { "id": "US", "name": "United States of America", "regionId": 2 } }, "manager": { "id": 200, "name": "Jennifer Whalen", "salary": 4400, "job": { "id": "AD_ASST", "title": "Administration Assistant", "minSalary": 3000, "maxSalary": 6000 } }, "employees": [ { "id": 200, "name": "Jennifer Whalen", "isSenior": true, "commissionPct": null, "jobHistory": [ { "id": "AD_ASST", "departmentId": 90, "startDate": "17-SEP-1995", "endDate": "17-JUN-2001" }, { "id": "AC_ACCOUNT", "departmentId": 90, "startDate": "01-JUL-2002", "endDate": "31-DEC-2006" } ] } ] } |
Summary
I really enjoyed working with APEX_JSON – it’s my new “go-to” for PL/SQL based JSON generation. APEX_JSON has a very light footprint (it’s just a single package) and it takes a minimalistic approach. Rather than compose objects as one would do with PL/JSON, you simply use the package to write JSON to a buffer.
This approach yields some performance benefits as well. In a basic test where I generated the JSON for every department in the HR schema 100 times in a loop, the APEX_JSON based solution finished at around 3.5 seconds whereas the PL/JSON based solution took around 17 seconds. That means APEX_JSON about 3.8 times faster than PL/JSON when it comes generating JSON and converting it to a CLOB.
Unfortunately, APEX_JSON is only included with APEX 5.0+. Upgrading your database’s APEX instance seems a little extreme if all you want to do is work with JSON (though it is free and doesn’t take too long), but if you already have APEX 5.0 then it’s a very nice tool to be able to leverage.
What’s the advantage of writing to a CLOB instead of direct to the HTP buffer?
Hi Scott,
Thanks for your question! There’s no advantage! 🙂 In fact, if what you’re doing is in the context of a web application then it’s more performant to write directly to the HTP buffer, avoiding the additional buffering to a CLOB.
The goal in this example was simply to convert the relational data to JSON. For that you don’t “need” to be in the context of a web application – but to do it outside of that context you do have to redirect the default output. Also, I didn’t want my testing to any network latency when comparing APEX_JSON to PL/JSON.
Regards,
Dan
Thanks, that makes sense.
I found the style stood out between my example and Erick’s first reply here https://community.oracle.com/message/13618534
I executed my anon block in APEX SQL Workshop so I saw htp buffer, Erick sent his to dbms_ouput, and you return the clob, which could be used however the client process wants, right?
Exactly. The best solution will depend on the use case, requirements, etc…