Thursday, October 5, 2023

OIC - Save Integration request payload XML/JSON into database


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:



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.


No comments:

Post a Comment

Featured Post

11g to 12c OSB projects migration points

1. Export 11g OSB code and import in 12c Jdeveloper. Steps to import OSB project in Jdeveloper:   File⇾Import⇾Service Bus Resources⇾ Se...