Exporting Custom Interactive Reports via REST

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.

Requirements

The requirements were as follows:

1. Call an API that returns the base data in JSON format.
2. Display returned data in an Interactive report.
3. Allow users to select and order the required columns, add computed columns, and apply filtering and sorting.
4. Send the manipulated report data back as a JSON file to the processing API.

The Solution

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.

How I did it

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.

Base data API

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.

Upload / Processing API

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.

POC APEX application

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.

1. Using the application id, page id and static id of the interactive report, obtain the region id using apex_application_page_regions.
2. Using the API function apex_region.export_data, return the data as a CLOB from the current version of the interactive report in JSON format.
3. Using the API function apex_web_service.make_rest_request, call the POST API and send the JSON file in the body.
4. Check the returned status code. If 200 is not returned, raise an error message to inform the user of the issue. Otherwise, the success message of the process is displayed.

Testing the POC

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.