This blog was written in 2015 for Oracle BI Cloud Service (BICS) + Oracle Application Express (Oracle Apex).

It has not been re-tested on Oracle Analytics Cloud (OAC) with Autonomous Data Warehouse (ADW) - nor are there plans to do so.

This blog has been left active as it may have some general value to Oracle Apex or Oracle SQL developers.

 Background

This article outlines how to automate the loading of a JSON response with PL/SQL

The main components of the PL/SQL code are as follows:

1)    Read in the JSON response as a CLOB using the apex_web_service.make_rest_request function.

2)    Convert the CLOB to a JSON_LIST data type.

3)    Loop through rows of the JSON_LIST.

4)    Use the JSON_EXT.GET_STRING function to parse out the JSON attributes.

5)    Insert the JSON attributes into a relational table.

The rationale for the article is to overcome the following constraints:

1)    Currently the REST API Oracle Business Intelligence Cloud Service can not read the JSON structure depicted in the article. This makes it difficult to achieve automated cloud-to-cloud data transfers without additional file conversions.

2)    The APEX_JSON package is only available in Oracle Apex 5.0 and higher. If using Apex 5.0+, it may be more preferable to use the functionality available in the APEX_JSON package rather than that in the PL/JSON package suggested in this article.

This article is intended for developers looking for a simple straightforward method to read JSON using PL/SQL. The primary requirements are:

1)    That the JSON response is accessible via a URL.

2)    The JSON response can be interpreted by the JSON_LIST data type installed with the PL/JSON package.

The JSON response may originate from a source such as (but not limited to) a mobile device, a website or another cloud application.

The sample code supplied parses the JSON data to a relational database table - making it readily available to model. Oracle Database 12c has built in JSON functions that allows JSON data to be stored in its original format. This article does not cover these features. However, pending how the data will be consumed, this may be something worth considering.

It is recommended to follow the steps below in chronological order. This will facilitate successful replication and assist with debugging.

Step One - Build the PL/SQL to return the JSON Response

Step Two - Load the PL/JSON Package

Step Three - Create the table to store the data

Step Four - Build the PL/SQL to parse the JSON and insert into the database

Step Five - Merge the code from Step One and Step Four

 Build the PL/SQL to return the JSON Response

Steps 1.1 to 1.3: Explains how to build the PL/SQL.

Steps 1.4 to 1.5: Describes how to run the PL/SQL - through Oracle Apex SQL Workshop.

Steps 1.6 to 1.7: Illustrates a sample JSON response.

1.1 - Replace the following values (that are highlighted in yellow in the below code sample box).

a)    l_ws_url - replace with JSON request URL

b)    g_request_headers - replace with JSON request headers

1.2 - Confirm that the PL/SQL returns the JSON response successfully.

1.3 - For a text version of this sql code click here

DECLARE
l_ws_response_clob CLOB;
l_ws_url VARCHAR2(500) := 'https://YourURL';
BEGIN
apex_web_service.g_request_headers(1).name := 'Accept';
apex_web_service.g_request_headers(1).value := 'application/json; charset=utf-8';
apex_web_service.g_request_headers(2).name := 'Content-Type';
apex_web_service.g_request_headers(2).value := 'application/json; charset=utf-8';
l_ws_response_clob := apex_web_service.make_rest_request
(
p_url => l_ws_url,
p_http_method => 'GET'
);
dbms_output.put_line(dbms_lob.substr(l_ws_response_clob,24000,1));
dbms_output.put_line(dbms_lob.substr(l_ws_response_clob,24001,24000));
END;

1.4 - From Oracle APEX -> SQL Workshop

1.5 - Copy the SQL into the SQL Command window and Run -> Confirm that the Results return the JSON response.

1.6 - Below is an example JSON response.

[{"avatarDefinitionId":"13","attributes":{"Name":"HP Jet Pro Wireless","Status":"1","Manufacturer":"HP","Model":"P1102W","Type":"LaserJet"},"lastUpdatedTime":"2015-07-28T12:01:55.919Z"},{"avatarDefinitionId":"13","attributes":{"Name":"Epson Stylus Personal Printer","Status":"1","Manufacturer":"Epson","Model":"C88","Type":"Inkjet"},"lastUpdatedTime":"2015-07-28T12:01:00.000Z"},{"avatarDefinitionId":"13","attributes":{"Name":"Canon PIXMA","Status":"1","Manufacturer":"Cannon","Model":"MG3520","Type":"Wireless All-in-One "},"lastUpdatedTime":"2015-07-28T12:00:21.923Z"},{"avatarDefinitionId":"13","attributes":{"Name":"Konica Minolta Bizhub","Status":"4","Manufacturer":"Konica","Model":"C360","Type":"Color Copier"},"lastUpdatedTime":"2015-07-28T11:51:57.993Z"}]

1.7 - For a text version of the JSON response click here

 Load the PL/JSON Package

Step Two describes how to load the artifacts from the PL/JSON Package into the database schema.

The schema name is visible from Oracle Apex SQL Workshop (in the Schema drop-down - top right).

It can also be gathered by running the SQL below.

The scripts should be ran by a user the "Database Administrator" role.

SELECT
sys_context('USERENV', 'CURRENT_SCHEMA') CURRENT_SCHEMA
FROM dual;

2.1 - Download the PL/JSON Package from here

2.2 - Click Download Zip

2.3 - Extract pljson-master.zip

2.4 - The extracted file contains the following:

2.5 - Either run install.sql or run each of the below scripts individually in the order they appear in install.sql

json_value.typ
json_list.typ
json.typ
json_parser.sql
json_printer.sql
json_value_body.typ
json_ext.sql
json_body.typ
json_list_body.typ

2.6 - If loading the scripts through APEX, use the SQL Scripts loader.

       SQL Workshop -> SQL Scripts -> Upload -> Run

2.7 - If running the install.sql though SQLDeveloper, change the "select default path to look for scripts" to match where the zip file was extracted.

Tools -> Preferences -> Database -> Worksheet

2.8 - Confirm all objects have a status of VALID

 Create the table to store the data

CREATE TABLE PRINTER_INFO
(
RECORD_NUM number,
NAME varchar(100),
STATUS  varchar(100),
MANUFACTURER  varchar(100),
MODEL varchar(100)
);

 Build the PL/SQL to parse the JSON and insert into the database

4.1 - Save the JSON output from Step One to a local text file.

Download it from here

Rename the file json.txt

Save it to C:\Temp

4.2 - From Apex go to SQL Workshop -> RESTful Services

4.3 - Create a RESTful Service Module.

Name: Printer.info

URI Prefix: Printer/

4.4 - Add a URI Template

URI Template: PostStatus/

4.5 - Add a Resource Handler

Method: POST

Source Type: PL/SQL

Source: Copy SQL from below (#4.7)

4.6 - Modify the highlighted SQL to match the JSON file and table column names.

Add additional columns and change attribute names where needed.

4.7 - For a text version of this SQL code click here

declare
l_clob clob;
l_warning varchar2(32767);
l_list json_list;
l_dest_offset   integer;
l_src_offset    integer;
l_lang_context  integer;
l_col1 VARCHAR2(100);
l_col2 VARCHAR2(100);
l_col3 VARCHAR2(100);
l_col4 VARCHAR2(100);
begin
l_dest_offset := 1;
l_src_offset := 1;
l_lang_context := dbms_lob.default_lang_ctx;
DBMS_LOB.createtemporary(l_clob, FALSE);
DBMS_LOB.CONVERTTOCLOB(
dest_lob       => l_clob,
src_blob       => :body,
amount         => DBMS_LOB.LOBMAXSIZE,
dest_offset    => l_dest_offset,
src_offset     => l_src_offset,
blob_csid      => dbms_lob.default_csid ,
lang_context   => l_lang_context,
warning        => l_warning
);
l_list := json_list(l_clob);
for i in 1..l_list.count LOOP
l_col1   := json_ext.get_string(json(l_list.get(i)),'attributes.Name');
l_col2   := json_ext.get_string(json(l_list.get(i)),'attributes.Status');
l_col3   := json_ext.get_string(json(l_list.get(i)),'attributes.Manufacturer');
l_col4   := json_ext.get_string(json(l_list.get(i)),'attributes.Model');
INSERT INTO PRINTER_INFO(RECORD_NUM,Name,Status,Manufacturer,Model) VALUES (i,l_col1,l_col2,l_col3,l_col4);
end loop;
end;

Note: Should your JSON data contain numerical values that are not in double quotes. You should then use get_number() instead of get_string().

For example:

{
"cityCode": 1630209128,
"cityName": "San Francisco",
"lat": 37.78099132971981,
"lon": -122.41374476733462,
"value": 3,
"duration": 120,
"time": "08/24/2015 11:09",
"date": "20150824"
},

The below example also illustrates how to convert the JSON "time" string into an Oracle TIMESTAMP field.

l_list := json_list(l_ws_response_clob);
for i in 1..l_list.count LOOP
l_col1 := json_ext.get_number(json(l_list.get(i)),'cityCode');
l_col2 := json_ext.get_string(json(l_list.get(i)),'cityName');
l_col3 := json_ext.get_number(json(l_list.get(i)),'lat');
l_col4 := json_ext.get_number(json(l_list.get(i)),'lon');
l_col5 := json_ext.get_number(json(l_list.get(i)),'value');
l_col6 := json_ext.get_number(json(l_list.get(i)),'duration');
l_col7 := json_ext.get_string(json(l_list.get(i)),'time');
l_col8 := json_ext.get_string(json(l_list.get(i)),'date');
l_col7_timestamp := TO_TIMESTAMP(l_col7,'MM/DD/YYYY HH24:MI');
INSERT INTO TABLE("cityCode","cityName","lat","lon","value","duration","time","date") VALUES (l_col1,l_col2,l_col3,l_col4,l_col5,l_col6,l_col7_timestamp,l_col8);
end loop;

4.8 - *** Apply Changes ***

4.9 - Run through curl to test. Download curl from here.

Replace the following highlighted items:

username:password

-k with the Apex URL

-d with the path to the JSON file saved locally (as defined in Step Four - #1)

4.10 - For a text version of the curl command click here

curl -u username:password -X POST -v -k "https://mytrial123-us123.db.usa.oracle.com/apex/Printer/PostStatus/" -H "Content-Type: application/json" -d@C:\temp\json.txt

4.11 - Confirm data loaded correctly

select * from PRINTER_INFO;

 Merge the code from Step One and Step Four

5.1 - Edit the Resource Handler Source. If preferred, this may also be ran in SQL Workshop.

To get to the Resource Handler Source go to:

SQL Developer -> RESTful Services -> Module -> URI Template -> POST SOURCE

5.2 - Combine the PL/SQL developed in steps one and four. Replace the l_ws_url, g_request_headers, JSON attributes, and insert table columns.

5.3 - For a text version of the PL/SQL click here

declare
l_ws_response_clob CLOB;
l_ws_url VARCHAR2(500) := 'YourURL';
l_list json_list;
l_col1 VARCHAR2(100);
l_col2 VARCHAR2(100);
l_col3 VARCHAR2(100);
l_col4 VARCHAR2(100);
begin
--get JSON
apex_web_service.g_request_headers(1).name := 'Accept';
apex_web_service.g_request_headers(1).value := 'application/json; charset=utf-8';
apex_web_service.g_request_headers(2).name := 'Content-Type';
apex_web_service.g_request_headers(2).value := 'application/json; charset=utf-8';
l_ws_response_clob := apex_web_service.make_rest_request(
p_url => l_ws_url,
p_http_method => 'GET'
);
--dbms_output.put_line(dbms_lob.substr(l_ws_response_clob,24000,1));
--dbms_output.put_line(dbms_lob.substr(l_ws_response_clob,24001,24000));
--convert clob to json_list
l_list := json_list(l_ws_response_clob);
for i in 1..l_list.count LOOP
l_col1   := json_ext.get_string(json(l_list.get(i)),'attributes.Name');
l_col2   := json_ext.get_string(json(l_list.get(i)),'attributes.Status');
l_col3   := json_ext.get_string(json(l_list.get(i)),'attributes.Manufacturer');
l_col4   := json_ext.get_string(json(l_list.get(i)),'attributes.Model');
INSERT INTO PRINTER_INFO(RECORD_NUM,Name,Status,Manufacturer,Model) VALUES (i,l_col1,l_col2,l_col3,l_col4);
end loop;
end;

5.4 - Apply Changes

5.5 - Delete the records that were previously loaded

Delete from PRINTER_INFO;

5.6 - Re-run through curl. This time omitting the -H and -d flags.

For a text version of the curl command click here

curl -u username:password -X POST -v -k "https://mytrial123-us123.db.usa.oracle.com/apex/Printer/PostStatus/"

5.7 - Confirm data loaded successfully

select * from PRINTER_INFO;

 Want to Learn More?

Click here for more A-Team OAC Blogs

Click here for more A-Team BICS Blogs

Click here for more A-Team OBIEE Blogs

Click here for the Application Express API Reference Guide -  MAKE_REST_REQUEST Function

Click here for the PL/JSON Reference Guide (version 1.0.4)

Click here for a Guide to "Introducing JSON"

 Summary

This article describes how to integrate JSON with PL/SQL. The solution requires the installation of the referenced PL/JSON package and the code is accessed through the APEX RESTful Services. That said, the APEX RESTful Services are not mandatory and other technologies more appropriate to the individual use case can be utilized. For illustration purposes Curl has been used to transfer the data. It is anticipated in a production environment alternative data transfer methods may be preferred.