DZone
Thanks for visiting DZone today,
Edit Profile
  • Manage Email Subscriptions
  • How to Post to DZone
  • Article Submission Guidelines
Sign Out View Profile
  • Post an Article
  • Manage My Drafts
Over 2 million developers have joined DZone.
Log In / Join
Please enter at least three characters to search
Refcards Trend Reports
Events Video Library
Refcards
Trend Reports

Events

View Events Video Library

Zones

Culture and Methodologies Agile Career Development Methodologies Team Management
Data Engineering AI/ML Big Data Data Databases IoT
Software Design and Architecture Cloud Architecture Containers Integration Microservices Performance Security
Coding Frameworks Java JavaScript Languages Tools
Testing, Deployment, and Maintenance Deployment DevOps and CI/CD Maintenance Monitoring and Observability Testing, Tools, and Frameworks
Culture and Methodologies
Agile Career Development Methodologies Team Management
Data Engineering
AI/ML Big Data Data Databases IoT
Software Design and Architecture
Cloud Architecture Containers Integration Microservices Performance Security
Coding
Frameworks Java JavaScript Languages Tools
Testing, Deployment, and Maintenance
Deployment DevOps and CI/CD Maintenance Monitoring and Observability Testing, Tools, and Frameworks

Modernize your data layer. Learn how to design cloud-native database architectures to meet the evolving demands of AI and GenAI workkloads.

Secure your stack and shape the future! Help dev teams across the globe navigate their software supply chain security challenges.

Releasing software shouldn't be stressful or risky. Learn how to leverage progressive delivery techniques to ensure safer deployments.

Avoid machine learning mistakes and boost model performance! Discover key ML patterns, anti-patterns, data strategies, and more.

Related

  • Streamlining Event Data in Event-Driven Ansible
  • Dynamic Web Forms In React For Enterprise Platforms
  • Unlocking Oracle 23 AI's JSON Relational Duality
  • Loading XML into MongoDB

Trending

  • Build an MCP Server Using Go to Connect AI Agents With Databases
  • My LLM Journey as a Software Engineer Exploring a New Domain
  • Solid Testing Strategies for Salesforce Releases
  • Subtitles: The Good, the Bad, and the Resource-Heavy
  1. DZone
  2. Coding
  3. Languages
  4. Relational to JSON With APEX_JSON

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.

By 
Dan McGhan user avatar
Dan McGhan
·
Oct. 20, 17 · Tutorial
Likes (1)
Comment
Save
Tweet
Share
9.5K Views

Join the DZone community and get the full member experience.

Join For Free

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.

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

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

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.

JSON

Published at DZone with permission of Dan McGhan, DZone MVB. See the original article here.

Opinions expressed by DZone contributors are their own.

Related

  • Streamlining Event Data in Event-Driven Ansible
  • Dynamic Web Forms In React For Enterprise Platforms
  • Unlocking Oracle 23 AI's JSON Relational Duality
  • Loading XML into MongoDB

Partner Resources

×

Comments
Oops! Something Went Wrong

The likes didn't load as expected. Please refresh the page and try again.

ABOUT US

  • About DZone
  • Support and feedback
  • Community research
  • Sitemap

ADVERTISE

  • Advertise with DZone

CONTRIBUTE ON DZONE

  • Article Submission Guidelines
  • Become a Contributor
  • Core Program
  • Visit the Writers' Zone

LEGAL

  • Terms of Service
  • Privacy Policy

CONTACT US

  • 3343 Perimeter Hill Drive
  • Suite 100
  • Nashville, TN 37211
  • support@dzone.com

Let's be friends:

Likes
There are no likes...yet! 👀
Be the first to like this post!
It looks like you're not logged in.
Sign in to see who liked this post!