Prevent fast clicking on Send Email button

Earlier in 2015, I’ve read an interesting article from HBR on how to prevent one to click too fast on the send button. It happens to me quite a few times in the past where I would have preferred to re-write/correct some emails before sending.

So I’ve set this rule in my outbox folder and it helped me many times. I would recommend to set this up after any Outlook installation.

How to call and transmit data from Clover ETL to a secured Oracle APEX REST API

Thanks to John Jacob who helped me understand how to call OAuth2 from Postman.

Issue:

With APEX REST API, any operation is exposed internally or externally if not properly secured. APEX REST API could be secured using OAuth2 however if used from Clover ETL it requires extra steps.

Solution:

This article explains how to get the OAuth token and use it from Clover ETL to get and post requests to Oracle APEX REST API.

Resources:

Clover Side

Complete Graph

To call a secured REST API (Oracle APEX in my case) by OAuth, an initial token call should be made, the token extracted and then the next Http requests could be made.

Here are the few components to perform these:

  • Reader > Data Generator to simply initiate the workflow
  • Transformer > Reformat
    • Transform attribute:

function integer transform() {

      $out.0.grant_type = “client_credentials”;

      return ALL;

}

This provides the grant type method used by OAuth. The metadata that outputs from this is simply one string grant_type.

  • Others > HTTPConnector: this will push the Client Id and Secret to the OAuth service and get the token.
HttpConnector setup
  • URL: the base path of your APEX module, adding /oauth/token
APEX Module setup

  • Request method: POST
  • Add input fields as parameters: true
  • Send parameters in: Body
  • Output mapping:

function integer transform() {

      $out.0.content = $in.1.content;

      return ALL;

}

The metadata that outputs from this component is simply one string content.

  • Authentication Method: HTTP_BASIC
  • Username: ${CLIENT_ID}
    • This variable is defined in a parameter file in my case. It could also be hardcoded here.
  • Password: <your client secret>
  • Readers > JSONExtract: this will extract from the content string the OAuth token
    • File Url: port:$0.content:discrete
JSON Extract – how to setup the File Url
  • Mapping (Source Tab):

<Mappings>

      <Mapping element=“json_object” outPort=“0”

                  xmlFields=“{}access_token”

                  cloverFields=“access_token”>

      </Mapping>

</Mappings>

Again, the output metadata is simple a string access_token.

  • SimpleCopy and UniversalDataReader are 2 components used to write the token down into a local file in data-out directory
  • Others > HTTPConnector: this will do the standard request to the APEX REST API, passing the token as parameter.
HTTPConnector setup
  • URL: the path to your APEX REST API
    • Request method: GET or POST
    • Add input fields as parameters: false
    • Send parameters in: QUERY (for GET)
    • Input mapping:

function integer transform() {

      map[string,string] headers;

headers[“Authorization”] = “Bearer ” + $in.0.access_token; 

      $out.0.additionalHTTPHeaders = headers;

      return ALL;

}

The remaining setup will depend on your requirements.

APEX Side

In APEX, it’s easy to protect the REST API from the user interface. You can define a privilege to protect your REST API resources as:

APEX Privilege

Select at least one role that will be used later in the security setup.

Once this is defined, your module will appear with a green checkmark:

To get the client ID and secret, you need few extra steps described in this article in the SQL Commands part of APEX:

  1. Create a client with the grant type of “client_credentials”, enter in p_privilege_names the privilege you’ve created in APEX (See above)

  OAUTH.create_client(

    p_name            => ‘Emp Client’,

    p_grant_type      => ‘client_credentials’,

    p_owner           => ‘My Company Limited’,

    p_description     => ‘A client for Emp management’,

    p_support_email   => ‘tim@example.com’,

    p_privilege_names => ’emp_priv’

  );

  • Associate the client with the role that holds the correct privileges for the resources it needs to access.

OAUTH.grant_client_role(

    p_client_name => ‘Emp Client’,

    p_role_name   => ’emp_role’

  );

  • Get the client ID and secret from this SQL:

SELECT id, name, client_id, client_secret

FROM   user_ords_clients;

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:

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