Generating JSON in APEX 5.0

In a previous blog I outlined how with APEX 5.0 you can now use the APEX_JSON package to parse JSON responses in your APEX application. For this blog I will show you how easy it is to generate your own JSON using just the in-built APEX packages.

If you want to pass data from your application to a web service, chances are you will need to do this using JSON. As an APEX developer it is extremely likely that you are proficient with SQL, and this is all you need to generate a JSON representation of your data.

I created a test application to demonstrate just how easy it is. In the image below you can see that I have created two text areas, one for your SQL query and one for the JSON representation of the results. I have entered an extremely simple query into my text area for this example but you would likely use static queries and bind variables in your code to avoid any SQL injection attempts.

Pressing the ‘Generate’ button submits the page and fires the following PL/SQL code:

We are opening a cursor based on the SQL entered in our first text area and using the APEX_JSON.WRITE procedure to generate the JSON results. The other procedures used to initialize and open the CLOB are important for this demonstration to work and can be read up on in the APEX_JSON package documentation.

Once we have generated the JSON results we can use the APEX_JSON.GET_CLOB_OUTPUT function to set the value of our JSON text area, as shown below:

And as easy as that, we have correctly formatted JSON data generated directly from the database. The query you use to generate the JSON can be a lot more complicated in a real world scenario, allowing complex JSON datasets to be generated.

Obviously this was just a short demonstration of how the data can be generated and the really useful applications of this package lie in the use of APEX as a web service itself. This requires a slightly different approach to generating the JSON as we need to include a JSON header rather than just the data itself.

In the near future I will bring together the ideas from this blog and my previous blog (Parsing JSON in APEX 5.0) to create a simple web service which creates and consumes JSON using APEX and ORDS.

Author: Colin Archer 

Job Title: Senior Oracle APEX Development Consultant

Bio: Colin is a Senior Development Consultant at DSP-Explorer with 20 years’ experience of analysis, design, and development of bespoke Oracle applications for a wide variety of business functions. Building on his previous experience of Forms and PL/SQL he is now focusing on developing high quality fit for purpose solutions using APEX.