On a recent project, I was tasked with developing a module within an APEX application to receive a JSON file via a REST API. This was so it could be manipulated on screen before being uploaded back using a second REST API. APEX made this easy to implement, so I thought I would share the solution I used.
The requirements were as follows:
Creating a REST data source to call the base data API and return the data in an Interactive Report is simple to do with APEX, as is calling an API from an APEX to send a JSON file. The only complication was how to take the results of a manipulated Interactive Report and send them as a JSON file to the processing API.
Well, that turned out to be very straightforward to achieve as well, thanks to the provided API libraries, specifically the APEX_REGION API.
Before developing the module, I decided to create a simple Proof of Concept application to confirm everything would work as needed, and these are the steps I took.
The first step was to create a new REST enabled workspace in APEX for the POC APIs and use the SQL Workshop to install the Customer Orders sample dataset. After that, I created the following APIs using the APEX RESTful Services interface.
Note: To keep the POC simple, I did not add authentication to the APIs.
The first API needed to return the results of a SQL query over the sample dataset in JSON format. To do this, I created a module named sales/v1/ with a template named orders/. I then added a GET handler to the template with a source type of Collection Query, which returned the results of the following SQL query as a JSON file.
SELECT c.full_name customer_name,
c.email_address customer_email,
o.order_id order_no,
trunc(o.order_datetime) order_date,
o.order_status,
i.line_item_id line_no,
p.product_name,
i.unit_price,
i.quantity
FROM customers c,
orders o,
order_items i,
products p
WHERE c.customer_id = o.customer_id
AND o.order_id = i.order_id
AND p.product_id = i.product_id
To test the new API, I simply needed to paste the generated URL into another browser and confirm the required JSON data was returned.
Next, I created a second API to receive a JSON file and write it to a new table in the database. The idea being once the data has been received and stored in the database, we can use it any way we want.
Within the sales module, I created a second template named process/v1/, to which I added a POST handler with a source type of PL/SQL, one incoming parameter for the filename and the following PL/SQL code.
BEGIN
INSERT INTO processed_data
(mime_type,
filename,
file_data,
date_created)
VALUES
('application/json',
:filename,
:body,
SYSDATE);
END;
After completing this, I had to simple REST APIs to download and upload data in JSON format.
The next step of the process was to add a second new Workspace and create the POC APEX application. Then I used the REST Data Sources option within Shared Complaints to create a new data source for the base data API.
Then I added an Interactive report with a static id of orders to the home page using the newly created REST Data Source to display the returned data, which could then be manipulated at runtime as needed.
The last step of the POC was to add the functionality to take the data displayed in the manipulated Interactive report and use it as the source for the JSON file to pass to the POST API. To do this, I added a submit button labelled Send Report Data to the page and the following PL/SQL page process.
DECLARE
CURSOR c_region IS
SELECT region_id
FROM apex_application_page_regions
WHERE application_id = :APP_ID
AND page_id = :APP_PAGE_ID
AND static_id = 'orders';
lt_export apex_data_export.t_export;
ln_region_id NUMBER;
lc_response CLOB;
BEGIN
-- Get the report region
OPEN c_region;
FETCH c_region
INTO ln_region_id;
CLOSE c_region;
-- Use export_data function to return data from IR in JSON format
lt_export := apex_region.export_data(
p_format => apex_data_export.c_format_json,
p_page_id => :APP_PAGE_ID,
p_region_id => ln_region_id,
p_as_clob => TRUE);
-- Use apex_web_service to call the POST API and send the JSON file
lc_rsp := apex_web_service.make_rest_request(
p_url => 'http://training.dsp-explorer.com/ords/apidata/sales/v1/process/',
p_http_method => 'POST',
p_body => lt_export.content_clob);
-- Display error massage if API does not return status code of 200
IF apex_web_service.g_status_code != 200 THEN
apex_error.add_error(
p_message => 'API Call Failed, status: ' || apex_web_service.g_status_code,
p_display_location => apex_error.c_inline_in_notification);
END IF;
END;
The process is executed when the page is submitted and performs the following actions.
Once everything for the POC was in place, it was time to test everything worked. Using the POC application, I confirmed the Interactive report on the home page contained all the data returned from the customer order data API. I then added filters, sorting, and a compute column to calculate the order total.
Then I used the submit button to run the page process. This extracted the data as displayed in the Interactive report and sent it as a JSON file using the POST API.
To confirm the file was received and contained my manipulated version of the data, I examined the contents of the processed_data table in the API workspace schema, which did indeed contain the data I expected.
In conclusion, thanks to the PL/SQL API library and functionality available in APEX for REST, this was an easy requirement to develop.
For more information, check out our APEX services, and if you liked this blog, check out our other APEX blogs here.