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

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.

High Level Implementation Details

1. Modify the query utility BIP report to return JSON result instead of XML by using JSON_OBJECT and JSON_ARRAYAGG.
2. Use lexicals approach to generate results dynamically.
3. Simplify the OIC integration by declaring binary output with application/json content type.

Detailed Implementation Details

1. 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:
     
2. 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:
     
3. Test the results: