Over a million developers have joined DZone.

Relational to JSON With APEX_JSON

DZone's Guide to

Relational to JSON With APEX_JSON

APEX_JSON was primarily intended to be used by APEX developers — but there are some hooks that can allow it to be used as a standalone PL/SQL package.

· Database Zone ·
Free Resource

Download the Scale-Out and High Availability whitepaper. Learn why leading enterprises choose the Couchbase NoSQL database over MongoDB™ after evaluating side by side.

APEX_JSON is a 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.


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 and 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...

create or replace function get_dept_apex_json(
   p_dept_id in departments.department_id%type

   return clob


   cursor manager_cur (
      p_manager_id in employees.employee_id%type
      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;



   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.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.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

      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.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
      apex_json.write('manager', '', p_write_null => true);
   end if;

   close manager_cur;


   for emp_rec in (
      select *
      from employees
      where department_id = l_dept_rec.department_id
      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);


      for jh_rec in (
         select job_id,
         from job_history
         where employee_id = emp_rec.employee_id
          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;


   return l_dept_json_clob;


   when others
      if manager_cur%isopen
         close manager_cur;
      end if;


end get_dept_apex_json;


When passed a departmentId of 10 , the function returns a CLOB populated with JSON that matches the goal 100%.

  "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"


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.

The Forrester Wave™: Big Data NoSQL report. See how the top NoSQL providers stack up. Download now.

database ,tutorial ,relational database ,plsql ,json ,api

Published at DZone with permission of

Opinions expressed by DZone contributors are their own.

{{ parent.title || parent.header.title}}

{{ parent.tldr }}

{{ parent.urlSource.name }}