Building Integration to Transform Data through Lookups in OIC

Data transformation is another crucial step in integrations. we quite often discuss transformation logic during integration design.  Well, every integration tool or service provides the mechanism to implement data transformations, likewise, OIC also provides lookups where you can create a mapping for the data transformation.

Lookups can be used for static values, if you want to derive values based on certain calculations or dynamic values then javascript can be used to perform the transformation logic

Here in this case study, we will read data from the database and transform the column values while publishing the final API call 

We will design an integration to read data from the database and transform the column values of Designation 

Database Value     Transformed Value

ITA                       IT Administrator

Let's create a table in the On-Premise database instance 

CREATE TABLE xx_emp_job_description (

    id          NUMBER,

    first_name  VARCHAR2(100),

    last_name   VARCHAR2(100),

    desgination VARCHAR2(100)

)

----------------------------------------------------------------------

Configure the database connection using the Oracle Database adapter

Click on Integrations and click on Create 

Select App Driven Orchestration

 Fill the mandatory fields 

Drop the REST connector 

Fill the endpoint name

provide the endpoint URI and select the operation as POST

Configure the request-response and Provide the sample Payload 

sample payload

{

  "id" : "",

  "FirstName" : "",

  "LastName" : "",

  "Designation" : ""

}

Configure the response payload as well 

 

{

"status":""

}

Click on Done

Drop the database connection on to the pallette 

fill the endpoint details 

put the DML command to insert the data into on prem database

INSERT INTO XX_EMP_JOB_DESCRIPTION( ID,FIRST_NAME,LAST_NAME,DESGINATION) VALUES (#ID,#FIRSTNAME,#LASTNAME,#DESGINATION) 

Validate the SQL query and click on Done button 

Now next step is to create Transformation values 

Go to Lookups--> Create

Fill in the mandatory details 

Select the domain name and edit the domain name 

Define the transformation mappings

For example 

IT Dir (Source)       IT Director (Destination)

Define mapping 

Map the columns  and define transformation for the column designation

Designation column values will be transformed based on the lookup value. 

Drop the lookup function onto the column designation 

Select the lookup name 

'

Click on Next 

Click on Default Value 

Drop the destination from the left palette to the expression  box

Transformation completed 

Click on Done button

Define a mapping for the response

Add a message to the status 

Enable the tracking identifier 

Activate the integration and call endpoint URL

Payload passing the value of Designation as IT Dir and it will insert database with a transformed value as Director- IT