Parsing JSON in APEX 5.0

As part of the APEX 5.0 Beta documentation there is a new APEX_JSON package available. This is a welcome addition that helps us integrate our applications with web services that serve responses using JSON. The new package allows us to easily parse the response and use the data within our applications without the need for third party packages or a custom implementation.

In this blog I will quickly demonstrate how to send a request and how to parse the response using the new APEX_JSON package.

First, create a new page with the following items:

So that your page looks something like this:

Next, create a dynamic action on the click of your “GO” button that fires the following PL/SQL code:

Make sure that the “Page Items to Return” field is set to:

This is all you need to do to send a request and parse JSON results – once you have the response values, you can use them within your application as you wish. However, due to security restrictions, this example will not run in APEX 5.0 EA3 – you will receive an ACL error when clicking the “Go” button. Once APEX 5.0 is released and available to download you will be able to configure the ACLs on your database to allow any web service requests that you need.

For now, we can test the parse procedure and get functions using the following code:

We have replaced the JSON request with a simple assignment of the response that would be received from the web service. Pressing the “Go” button should now give you the following results, with my date format changing based on my application and page item settings:

So, as you can see, it is now extremely easy to parse JSON results and integrate them into your applications, giving APEX yet another great tool to allow connectivity with external web services.

Author: Craig Sykes 

Job Title: Senior Oracle Development Consultant

Bio: Craig is a Senior Development Consultant at DSP-Explorer. Craig has an MSc in Computing Science and is an experienced software engineer, utilising development tools such as PL/SQL and APEX to provide bespoke ERP software to both UK and international businesses. Craig has experience developing solutions to connect Oracle systems to a wide range of existing external applications within business environments.