Oracle Fusion ERP RICE: Running Dynamic SQL Queries Against Oracle Fusion from a VBCS App

The Problem

Pulling ad-hoc data out of Oracle Fusion usually means writing a query inside a BI Publisher data model. That works, but it has two annoying limitations:

Every query change means editing the data model. There's no way to just type a new query and run it.
XML output caps out at 200 rows. Anything larger requires generating a full report from the data model instead of a quick preview.

This post walks through a small custom app that removes both restrictions: a VBCS front end where you type any SQL query, run it, and see the results in a table — with no data model edits and no row limit — via a BI Publisher data model + report that accepts the query dynamically, and an OIC integration that glues the two together.

Architecture at a Glance

Component	Role
BIP Data Model	Accepts a Base64-encoded SQL string, decodes it, opens a ref cursor, and returns the result set
BIP Report	Wraps the data model so it can be run as a proper report (no row cap)
OIC Integration	Receives the encoded query from VBCS, runs the BIP report, and returns the report bytes
VBCS App	UI for entering a query, triggering the integration, and rendering the results as a table (with CSV export)

1. BI Publisher Data Model

The core trick is a data model built on a PL/SQL block that decodes a Base64 string into a query and opens it as a ref cursor:

The data model needs two parameters:

query1 — a string parameter that holds the Base64-encoded SQL query. String parameters cap out at 32,767 characters; 32,000 is used here to leave some headroom.
xdo_cursor — the ref cursor returned by the PL/SQL block.

Why Base64? Passing a raw SQL string as a data model parameter tends to error out (special characters, quotes, line breaks, etc. all cause problems). Encoding it sidesteps all of that.

The data model query block is:

DECLARE

TYPE refcursor IS REF CURSOR;

xdo_cursor refcursor;

var VARCHAR2(32000);

BEGIN

var := utl_raw.cast_to_varchar2(utl_encode.base64_decode(utl_raw.cast_to_raw(:query1)));

OPEN :xdo_cursor FOR var;

END;

Testing it

Now lets run a query and save the sample data so that we can use the saved sample data to create report.

Lets write a simple query(There should not be any semicolon at the end):

select invoice_id, invoice_num from ap_invoices_all

Now we need to convert this to base64 because if we pass the query directly then it will give error.

We can use any text editor or use online tool to convert to base64.

The encoded string for above query is:

c2VsZWN0IGludm9pY2VfaWQsIGludm9pY2VfbnVtIGZyb20gYXBfaW52b2ljZXNfYWxs

Now lets pass this in our datamodel query1 parameter and click on view:

We will get the output like below:

Save this as sample data.

Now lets create a report.

2). A BIP report.

Select the data model for report:

No need to have a layout as it will give data XMl as output. We can create template and give output type as well.

Save the report and then view report.

We can export this report as Data xml.

3). An OIC integration to run report and provide output to VBCS app

We will create a simple OIC integration which will accept the base64 encoded query from VBCS ,runs the BIP report and returns base64 report bytes to VBCS.

Now call BIP report using a soap adapter:

Give attributeFormat as xml

Finally send the response back to VBCS:

4). A VBCS app to provide UI for running query and seeing results.

This is the start and end of the whole process.

In the sample app we have given a text area to enter the query ad then a run icon.

There is a progress bar, to display the progress of steps in query execution, a table to display the results and an export button to export the result in csv.

The UI looks like below:

Now lets run a query:

The Result will look like below:

We can export this output to csv.

Another query example with commenting the previous one:

We need to do below things in VBCS:

From the text area whatever query we are having , we need to encode that to base64 through javascript:

Sample Function:

EncodeToBase64(arg1) {

let encoded = window.btoa(arg1);

return encoded;

}

The argument will be the query text from text area.

Call rest endpoint(the OIC integration) by passing this base64 string

The response from integration will be base64 report byte . We need to decode that using JS.

The sample Function:

DecodeOutput(arg1) {

let decoded = window.atob(arg1);

return decoded;

}

This will return the xml, we need to parse this xml and convert to json.
We will use fxp(fast-xml-parser) library.

We can get that from here: https://naturalintelligence.github.io/fast-xml-parser/lib/fxp.min.js

For the import in VBCS we need to do below things:

Place the library in our resources/js folder.

Make an entry in index.html as follows : <script src="resources/js/fxp.min.js"></script>

The sample js function to convert xml to json will be:

ConvertXmlToJson(arg1) {

return new Promise(function(resolve, reject) {

let data = arg1;

const parser = new fxp.XMLParser();

let result;

try {

result = parser.parse(data);

//console.log(result);

resolve(result);

} catch (err) {

console.log(err);

reject(err);

}

});

}

Now we have the json. We need to display this data tin a table.

We need to have column headers and data.

We will create two variables for table column and data:

Table Column data var as any type and table data var as ADP.

Below is the sample function to create column header for the table:

ParseReportJsonReturnTableHeaders(arg1) {

var ColumnArrayOfJsonObject = [];

var columnAttributes = ["headerText", "field"];

var valueVar = {};

var columnObjArray = [];

var i = 0;

const obj = JSON.parse(arg1);

var MaxHdrCounter = 1;

for (var key in obj) {

if (key == "ROWSET") {

// console.log("Rowset Key is: " + key);

//console.log("Objects under Rowset :" + obj[key]);

var count1 = Object.keys(obj[key]).length;

//console.log("Number of row item : " + count1);

for (var rowkey in obj[key]) {

if (MaxHdrCounter > 1)

break;

if (Array.isArray(obj[key][rowkey])) {

if (MaxHdrCounter > 1)

break;

console.log("it is an array");

for (var actualrowkeyArray in obj[key][rowkey]) {

if (MaxHdrCounter > 1)

break;

for (var innermostkeyArray in obj[key][rowkey][actualrowkeyArray]) {

//console.log("Actual Key is: " + innermostkeyArray);

//console.log("Actual value: " + obj[key][rowkey][actualrowkeyArray][innermostkeyArray]);

valueVar.headerText = innermostkeyArray;

valueVar.field = innermostkeyArray;

ColumnArrayOfJsonObject.push({

...valueVar

});

//console.log(JSON.stringify(ColumnArrayOfJsonObject));

MaxHdrCounter = MaxHdrCounter + 1;

}

} else {

for (var actualrowkeyNonArray in obj[key][rowkey]) {

//console.log("Actual Key in Non Array : " + actualrowkeyNonArray);

//console.log("Actual value in Non Array: " + obj[key][rowkey][actualrowkeyNonArray]);

valueVar.headerText = actualrowkeyNonArray;

valueVar.field = actualrowkeyNonArray;

ColumnArrayOfJsonObject.push({

...valueVar

});

console.log(JSON.stringify(ColumnArrayOfJsonObject));

MaxHdrCounter = MaxHdrCounter + 1;

}

return ColumnArrayOfJsonObject;

}

Similarly we need to do for data in table.

We will be using Array Data Provider.

By default it will not be available to use in the JS function. We need to import it like below:

At the begining(first line) we need to update like this:

define(['ojs/ojarraydataprovider'], (ArrayDataProvider) => {

Below is the sample function to create data for the table:

ParseReportJsonReturnTableData(arg1) {

var DataArrayOfJsonObject = [];

var JsonObjVar = {};

var columnName = [];

var dataRowArray = [];

var i = 0;

const obj = JSON.parse(arg1);

var MaxHdrCounter = 1;

for (var key in obj) {

if (key == "ROWSET") {

//console.log("Rowset Key is: " + key);

// console.log("Objects under Rowset :" + obj[key]);

var count1 = Object.keys(obj[key]).length;

//console.log("Number of row item : " + count1);

for (var rowkey in obj[key]) {

if (Array.isArray(obj[key][rowkey])) {

//console.log("it is an array");

for (var actualrowkeyArray in obj[key][rowkey]) {

for (var innermostkeyArray in obj[key][rowkey][actualrowkeyArray]) {

//console.log("Actual Key is: " + innermostkeyArray);

//console.log("Actual value: " + obj[key][rowkey][actualrowkeyArray][innermostkeyArray]);

columnName[i] = innermostkeyArray;

//console.log("Current Column Name is : "+columnName[i]);

dataRowArray[columnName[i]] = obj[key][rowkey][actualrowkeyArray][innermostkeyArray];

i = i + 1;

}

JsonObjVar = dataRowArray;

DataArrayOfJsonObject.push({

...JsonObjVar

});

}

} else {

for (var actualrowkeyNonArray in obj[key][rowkey]) {

// console.log("Actual Key in Non Array : " + actualrowkeyNonArray);

//console.log("Actual value in Non Array: " + obj[key][rowkey][actualrowkeyNonArray]);

columnName[i] = actualrowkeyNonArray;

//console.log("Current Column Name is : "+columnName[i]);

dataRowArray[columnName[i]] = obj[key][rowkey][actualrowkeyNonArray];

i = i + 1;

}

JsonObjVar = dataRowArray;

DataArrayOfJsonObject.push({

...JsonObjVar

});

// console.log("Data Array :"+dataRowArray);

}

//console.log("Data Array for Table: "+JSON.stringify(DataArrayOfJsonObject));

return new ArrayDataProvider(DataArrayOfJsonObject, {

keyAttributes: '@index'

});

}

In the action chain we will update table column data var and table data var with the response of above two functions.

Now in table we can map table column and data separately like below:

In the code mode it will look like below:

Note:- We can combine some funtions together which we saw above, to reduce total number of functions.

This is a simple app which explains flow of how we can query dynamically from VBCS , present the data in tabular form after parsing and also providing option to download data in csv( We need to seacrh and install export data component from components).

The mapping for export data will look like below:

We can Enhance this application to have features like

Format Query
Query history - Store the executed queries in ATP with env, query,user, unique id and status
Rerunning the past queries - Fetch the query from ATP and allow to rerun
Running queries for different Fusion Environments from the same page - Create OIC connection for diff ENV and have branches in the int for each env. Have LOV in VBCS page and accordingly pass to integration.
Having links for fusion tables etc - This will help making easy to find the Fusion tables, We can give OER link for each module tables.

as shown in below screenshots.

Before Formatting: