Fusion SaaS: improved method for running SQL queries by using generic OIC integration and BIP lexicals

Author Alex Rabinovich

Updated on October 22, 2026

Time to read 2 min

General

In the previous post we have presented a generic OIC integration that can receive an SQL parameter and return JSON array result,
The solution presented there had limited number of resulting columns and due to the predefined response schema the results records had also common names; COL1, COL2, etc.
This post will show how the previous solution can be improved by using BIP lexicals and OIC binary output.
The solution that is presented in this post will remove the resulting columns limit, will provide a readable output in a more efficient manner.
The wrapper Groovy code presented before can be still used to expose the integration to VB AppUIs.

Detailed Implementation Details

Create a BIP data model and report called RunSQLToJson with p_sql parameter and 2 data sets: G_META and G_DATA

Create dataset G_META with the following query:
select :p_sql query_to_process from dual
Create dataset G_DATA with the following query:
select json_arrayagg(json_object(* returning clob) returning clob) as json_doc from &query_to_process)
Create a report RunSQLToJson pointing to the model created
Test the report:

Create a new integration triggered by a REST call

Define JSON payload for input
Define binary for output
Initialize response variable:
Call BIP utility report and save the result into a staging file:
Read the stage file and check if the query returned any data:
Assign results to a variable initialized before:
Map the results to binary output, use the following function:
oraext:decodeBase64ToReference (oraext:encodeBase64 ($res ) )
Final OIC layout:

Test the results:
Screenshot 2024 10 20 at 19.11.52

3 thoughts on “Fusion SaaS: improved method for running SQL queries by using generic OIC integration and BIP lexicals”

Venkat November 1, 2024 at 09:01

Dear,
Thanks a lot for your blog and explain in detail.

While creating the data model in Fusion, I encountered the error ‘ORA-00942: table or view does not exist.’ Could you please explain how you were able to proceed without this issue? Additionally, could you advise if I am missing any steps?

Create dataset G_DATA with the following query:
select json_arrayagg(json_object(* returning clob) returning clob) as json_doc from &query_to_process)

Thanks
Venkat

Reply
1. Alex Rabinovich November 1, 2024 at 10:26
  
  Dear Venkat,
  
  When you save the G_DATA query, a popup is shown and the &query_to_process is empty, type there a query like: select 1 num from dual
  
  You can use any query there.
  
  Feel free to contact us with any questions.
  
  Reply
  1. Venkat November 1, 2024 at 12:33
    
    Dear Alex,
    
    Thanks a lot for your quick support, Now its working the bi query. If possible can you share me BI Report CatLog and .iar file.
    my email id: pvrmsc@gmail.com
    
    Thanks
    Venkat
    
    Reply

Fusion SaaS: improved method for running SQL queries by using generic OIC integration and BIP lexicals

General

High Level Implementation Details

Detailed Implementation Details

Recommended

OIC: Get the BIP PDF Output of an ESS Job by using ESS REST and ScheduleReportWSSService

CID Fusion Query v3.3.0 — Ready for Fusion 26C’s New BI Publisher SQL Injection Check, Plus Paged Results and Custom Row Limits

AI Revolution in OIC Development and Analysis: Searching Inside 500+ Integration Flows

Visual Builder Studio Issues: Run a Fusion RICE Delivery Process by using a Documented Epic/Task Model — and Load It in Bulk with a Free Python Tool

OIC: Passwordless connections for Fusion BI Publisher — why SOAP is the last holdout, and the CID adapter that closes the gap

3 thoughts on “Fusion SaaS: improved method for running SQL queries by using generic OIC integration and BIP lexicals”

Leave a ReplyCancel reply