Fetch & update Integration lookup using Visual Builder

I often find a requirement very frequently where developers want to give functionality to fetch & update the Oracle Integration lookup using the Visual Builder screen.

So, this requirement insisted that I write this blog and spread it in the world so that everyone can know how to achieve such requirements.

To complete this blog, you must have an Oracle Integration instance and if you don’t have it you can create a new Oracle Integration instance.

This is how the Visual Builder screen looks like:

The Fetch Data from lookup button will allow fetching the lookup data. Once the data is fetched, you can update the data using inline editing and Click on the Update Lookup button to update the Lookup values.

The below screenshot shows, the CountryLookup:

To fetch and update Lookup, I have used two Oracle Integration REST API as below:

1. Export the Lookup
2. Update the Lookup

Created the Service Connection in Oracle Visual Builder using the above two endpoints.

The export lookup API produces the application/octet-stream hence to read the octet-stream below Javascript code is written in Visual Builder:

PageModule.prototype.processLookup = function (blob) {
    var reader = new FileReader();
    return new Promise(function(resolve, reject) {
        var reader = new FileReader();
        reader.onloadend = function(e) {     
           var data = reader.result;
           resolve(data);
        };
        reader.readAsBinaryString(blob); 
     });
  };

The above JS code will return the CSV data. Further, the following JavaScript is created to parse the data and return the JSON data:

PageModule.prototype.buildData = function (lookupData) {
    var array = lookupData.split(/\r\n|\n/);
    var headers = array[1].split(',');
    let result = [];
    for (let i = 2; i < array.length-1; i++) {
      let obj = {};
      let str = array[i];
      let properties = str.split(",");
      for (let j in headers) {
        obj[headers[j]] = properties[j];
        obj.id = i;
      }
      result.push(obj);
    }
    return result;
};

Once the data is updated, you need to update the data in the lookup using Update the Lookup API. The following JavaScript code is used to build the JSON for the update API:

PageModule.prototype.updateLookup = function(data,lookupName){
  var finalDataToUpdate ={};
  finalDataToUpdate.name=lookupName;
  var lookupColumns =["CountryCode","CountryName"];
  finalDataToUpdate.columns=lookupColumns;
  var rows1 = [];
   $.each(data, function (index, value) {
     var buildRowData ={};
     var rowsToUpdate=[];
     rowsToUpdate.push(value.CountryCode);
     rowsToUpdate.push(value.CountryName);
     buildRowData.rowData = rowsToUpdate;
     rows1.push(buildRowData);
    });
    finalDataToUpdate.rows = rows1;
    console.log("final string "+JSON.stringify(finalDataToUpdate));
    return finalDataToUpdate;
};
  

Look at the below video, to know how to achieve the same end-to-end:

Further readings:

Update Lookup in Oracle Integration

Convert JSON to String Oracle Integration

Oracle Integration Message Packs and Pricing

Business Identifiers in Oracle Integration

You can subscribe to my YouTube channel for further reading.