OIC - Save Integration request payload XML/JSON into database

Usecase: 

Here, We have a project requirement that we have to save the integration source data XML or Json into a database table so that the support person can track the payload if required. In addition, we need to save the metadata details like instance id, name, identifier etc.

Though Oracle integration allows us to track the complete complete using activity stream if debug or audit tracing is enabled. Saving the payload in the activity may have a security rick as the payload may contain sensitive data. 

But having a payload handy is essential for the operations or support team to debug the issues. Instead of storing the payload in the activity stream, we can save it into a persistent store like a database.

Note: here, we will save a Request Json data to database table, the same you can do using xml data.

Logic steps:

1. Create a database table where payload column is as clob type.
2. Create a database connection and a rest trigger connection.
3. Create an appdriven integration and configure Rest to pass the json data as request paylaod.
4. Write the request content into a stage location using the XML or JSON structure which ever required.
5. Configure the database to insert
6. Map the staged payload reference to database paylaod column using decodebase64(encodeReferenceToBase64(FileReference) functions.

Table create:

CREATE TABLE OIC_TRACKING(

Id INTEGER PRIMARY KEY,

Payload CLOB,

Integration_Name VARCHAR2(100),

Integration_Identifier VARCHAR2(100));

Detailed steps (with screenshots):

Integration flow:

Configure rest trigger to send request

Assign some integration metadata specific details like instance id, name, identifier.

Write the JSON file into stage location

Configure the database to insert data

Map the stage payload to database payload column.

Test: