How to upload data automatically to APEX through REST API

Special thanks to Alexandre Lacour who helped me discovered this nice feature from Clover and put me in the right direction.

Issue:

With APEX, data loading is quite easy through various functions however in case you must upload large quantities frequently or if you’re limited to upload some quantities, it’s becoming frustrating to split your file in multiple chunks.

Solution:

Through REST API, Clover ETL (now renamed Clover DX) could upload data through JSON chunks.

Advantages:

• No need to split files
• No need to connect to APEX
• Everything runs from the laptop
• Extra bonus: Clover DX allows transformations like Vlookup, filters, sort, etc

Drawbacks:

• Clover DX needs to be installed
• REST API needs to be implemented

In the following article, I’ll show how to setup the Clover components to connect and upload to APEX Rest API. I’ll show an example of REST API as well.

Resources:

• https://www.cloverdx.com/
• https://apex.oracle.com/en/

Clover Side

Once you’ve installed Clover ETL, you can create a new project and setup a new graph. Here are the 2 components you need to connect to APEX:

• JSONWriter – This will prepare the JSON data
• HTTPConnector – This will connect and transmit the JSON data
• An arrow with the correct metadata definition between the 2 components

The metadata on the arrow is defined as:

On the JSONWriter, you have to setup the following attributes:

• File URL to connect to the output arrow and to the correct metadata element. Value is: port:$0.json_text:discrete

• 

• Mapping to write the JSON the correct way so the APEX procedure could decipher it:

{“items”:[{“id”:”ABC789″,”region”:”EUROPE”},{“id”:”DEF789″,”region”:”ASIA”}]}

In the source tab of the mapping attribute, you should enter:

<?xml version=“1.0” encoding=“UTF-8”?>

<root xmlns:clover=“http://www.cloveretl.com/ns/xmlmapping&#8221;>

  <clover:collection clover:name=“items” clover:inPort=“0”>

    <element2>

      <clover:element clover:name=“id”>$0.Id</clover:element>

      <clover:element clover:name=“region”>$0.Sales_Region</clover:element>

    </element2>

  </clover:collection>

</root>

On the HTTPConnector, you have to setup the following attributes:

• URL: Enter here the Url of your REST API
• Request Method: POST
• Add input fields as parameters: false
• Send parameters in: Body
• Input mapping – source tab

function integer transform() {

      $out.0.requestContent = $in.0.json_text;

      return ALL;

}

• Store HTTP response to file: true
  • This allows to check the response in a file from the REST API – 200 indicates it worked.

APEX Side

This tutorial is based on APEX version 18.2 and is using the ORDS RESTful Services (Under SQL Workshop).

Aim is to create a POST handler definition as:

Here is the step by step process:

• First, create a module:
  • Enter a module name – It could be anything meaningful
  • Base path will be used in your URI for the Rest API so it’s better to choose something easy. I’ve chosen /data/
  • Leave the other entries as defaulted
  • Click Create
• Create a Resource template:
  • Enter an URI Template: again this will be used in your URI. I’ve chosen /value/
  • Leave the other entries as defaulted
  • Click Create
• Create a Resource Handler:
  • Select Method: POST
  • Leave the rest empty and click Create
  • Open the just created handler
  • Enter the following method:

DECLARE

    l_blob blob := :body;

    c_json clob;

BEGIN

    c_json := clobfromblob(l_blob);

    insert into MY_DUMMY_TABLE (ID, REGION)

    select identifier, region

      from JSON_TABLE(c_json, ‘$.items[*]’

                           COLUMNS (identifier NUMBER PATH ‘$.id’,

                                    region VARCHAR2(20) PATH ‘$.region’

                                    )

                      );

    :output := 201;

EXCEPTION

    when others then

        :output := 400;

END;

EDIT (March 2019): With APEX 19.1, there is a new package APEX_DATA_PARSER that allows to perform the above – Check this article out: https://blogs.oracle.com/apex/super-easy-csv-xlsx-json-or-xml-parsing-about-the-apex_data_parser-package

This method takes the body of the POST request as BLOB, transforms this into CLOB. Then it inserts the data into a dummy table from a SELECT statement. This statement is using a new database feature that reads CLOB and transforms JSON text into table.

More information on JSON_TABLE

Source code for clobfromblob

• In the parameters, Add a new row (Click Add Row)

• Name: output
• Bind variable: output
• Source type: Http Header
• Access method: OUT
• Data type: INTEGER
• Click Apply changes

You can then test this REST API from Postman before trying from Clover.

Memory Issues

In case when creating the JSON text in the JSONWriter component you get some memory consumption error, you can change the ETL Runtime Preferences at Window > Preferences as follow:

• Change the heap size to something greater depending on your computer (2GB in the above)
• Modify the default properties by creating a new file with these lines:
  • Record.RECORD_LIMIT_SIZE = 53554432
  • Record.FIELD_LIMIT_SIZE = 53554432
  • Graph.DIRECT_EDGE_FAST_PROPAGATE_NUM_INTERNAL_BUFFERS = 1