Difficulty: Advanced
TL;DR We brought the full power of SQL into Fusion AI Agent Studio. Once the action below is in place, a business analyst can build an agent that answers real operational questions from any Fusion table — no IT ticket, no developer, no OIC integration — working entirely inside the Studio. The technical hurdle was that the Business Object Functions an agent calls are object-level, and object-level Groovy can’t open the SOAP connection our generic “run SQL and get JSON” utility depends on; we bridged it with a thin object-level wrapper that delegates to the existing row-level function. Stack: Fusion AI Agent Studio + Application Composer (Groovy) + BIP + the
fa-internalSOAP pattern. Read this if you want an AI agent to read live Fusion data via SQL without standing up OIC, a separate REST service, or a hardcoded record ID.
Tested on: Fusion 26B · Application Composer enabled · AI Agent Studio enabled · a BIP report that runs parametrized SQL and returns JSON (from our earlier utility).
Series — SQL from VB AppUIs (latest link) Earlier: v1 (Groovy + OIC, Feb 2024) → v2 (generic REST utility, Mar 2024) → v3 (BIP lexicals, Oct 2024) → v4 (P2T-safe via
fa-internal, no OIC, Nov 2024). This post repurposes v4 so the consumer is an AI agent instead of a VB AppUI, and adds the object-level wrapper that makes the call ID-free.
Why we wanted SQL inside AI Agent Studio
On a recent client engagement the business analysts wanted an AI agent that could answer operational questions grounded in live Fusion data — not the curated subset exposed through standard REST resources, but whatever the business actually asked for, including tables and columns no REST endpoint surfaces. Normally that means a development cycle: someone builds a REST service or an OIC integration, the BA waits, the requirement has already shifted by the time it ships. We wanted to flip that. If the agent could run SQL directly, the analyst could compose and refine the agent’s behaviour entirely inside the Studio — phrasing the questions, shaping the answers, iterating in minutes — with no developer in the loop for each new scenario.
We already had the hard part solved. In our P2T hassle-free generic SQL query REST utility we built a BIP report that takes a SQL string and returns JSON, and an Application Composer Groovy function that calls that report over the internal fa-internal.oracleoutsourcing.com:10663 hostname — no OIC, no REST connection to repoint after a P2T clone. The one-time engineering effort below exposes that to the agent; from then on, the SQL lives in the Studio and the analyst owns it.
Why we changed this
In the Nov 2024 version the Groovy function had to be row-level, because object-level functions cannot open the external SOAP connection the BIP call relies on. A row-level action requires the caller to address a specific custom object record — fine from a VB action chain that already holds a row, but awkward for an agent that just wants to “run this SQL.” AI Agent Studio binds to a Business Object Function as a collection-level action, with no record context to hand it. So we added a thin object-level wrapper that locates the single utility record itself and delegates to the row-level function — the agent calls one action, no record ID required.
Our solution
- Keep the existing row-level Groovy function (
getSqlToJson) that calls the BIP report over fa-internal and returns a JSON string — unchanged from the Nov 2024 post. - Add an object-level Groovy function (
getSqlToJsonAsList) that opens a view on the custom object, reads its single seeded record, invokes the row-level function on that record, and parses the JSON string into aList<Map<String,String>>. - Register that object-level function as a Business Object Function so it appears as a callable action in AI Agent Studio.
- Add the action to the agent team and let the agent call it with a
sqlTextparameter.
Implementation details
1. The object-level wrapper function
In Application Composer, on the utility custom object, add an object-level function named getSqlToJsonAsList. It takes a single String parameter sqlText, returns java.util.List<java.util.Map<java.lang.String, java.lang.String>>, and its visibility is set to Callable by External Sys so it surfaces as a Business Object Function.
The body fetches the single record of the custom object, calls the existing row-level getSqlToJson function on it, and parses the result:
def vo = newView('ACRESTUtil_c') def rec = vo.first() def jsonRes = rec.getSqlToJson(sqlText) def res = (java.util.List<java.util.Map<java.lang.String,java.lang.String>>)adf.util.parseJson(jsonRes) return res
Two things are doing the work here. newView('ACRESTUtil_c') plus vo.first() retrieves the custom object’s single record without the caller ever supplying an ID — that’s what turns a row-level capability into a collection-level action. And adf.util.parseJson converts the JSON string the row-level function returns into a real list of maps, so the agent receives structured rows rather than a string it has to parse itself.
The row-level getSqlToJson function it delegates to is unchanged from the Nov 2024 post — it builds the BIP report request, calls the report over the fa-internal SOAP connection, decodes the Base64 response, and unescapes the JSON payload out of the XML wrapper.
2. Register it as a Business Object Function
The wrapper lives on a small custom object — in our utility it is AC Rest Util (code ORA_CX_OTHER_ACRESTUTIL, resource path /crmRestApi/resources/11.13.18.05/ACRESTUtil_c). Once the object-level function is in place, define a Business Object Function against it. We named ours run_SQL_With_Param, pointing at the getSqlToJsonAsList action; its resource path resolves to /crmRestApi/resources/11.13.18.05/ACRESTUtil_c/action/getSqlToJsonAsList.
3. Wire the function into the agent
In AI Agent Studio, add the Business Object Function as an action on the agent team. We labelled the action RunSQL and bound it to the run_SQL_With_Param function on the AC Rest Util business object, exposing the single sqlText string parameter to the agent. Enable Fix any JSON issues so minor formatting irregularities in the model-supplied SQL don’t fail the call.
That is the entire integration. No OIC, no separate REST service, no API key — the agent calls a native Fusion action over the same secured channel the rest of Fusion uses. And from this point on there is nothing left to develop: every new scenario is just a different prompt and a different SQL statement, both authored by the analyst inside the Studio.
How to verify
Run the agent and ask it something that requires the action. In our test the agent issued select * from all_tables where table_name like 'egp%sys%' and the RunSQL step returned the matching ALL_TABLES rows as JSON in roughly three seconds.
Check, in order:
- The agent run trace. Expand the action step — it shows input, output, and latency. A clean JSON array confirms the wrapper parsed and returned correctly.
- Latency. Each call carries the full BIP report round-trip. Two to three seconds per query is normal; design the agent so it doesn’t fan out into dozens of sequential SQL calls.
- The custom object has exactly one record.
vo.first()assumes a single seeded row. If the object is empty the call fails; if it has many rows you simply use the first.
Gotchas / production notes
- Object-level functions still can’t open the SOAP connection themselves. The wrapper does not call BIP directly — it delegates to the row-level function, which runs in a row context where the external connection is permitted. Don’t try to move the report call up into the object-level function; it won’t work. The two-function split is the whole point.
Callable by External Sysvisibility is mandatory. If the object-level function isn’t marked callable by external systems it won’t appear as a selectable Business Object Function in AI Agent Studio.- The SQL runs with the function owner’s data access. This is a generic SQL gateway. Treat the agent action as privileged: constrain what the agent is allowed to ask for, and never expose this action to an agent surface that untrusted users can drive freely. An agent that will compose arbitrary SQL is effectively a query console.
Fix any JSON issuesearns its keep. Models occasionally emit SQL with stray escaping or trailing characters. The toggle absorbs minor issues before they reach the function, but it is not a substitute for guarding the action’s reachability.- P2T-safe by inheritance. Because the underlying call uses the
fa-internalhostname rather than an OIC or REST connection, nothing here needs repointing after a Production-to-Test clone — the same property that made the Nov 2024 utility hassle-free carries straight through to the agent.
Conclusion
With one small wrapper function we brought the full power of SQL into AI Agent Studio. After this one-time setup, the heavy lifting moves out of IT and into the Studio: a business analyst can stand up an agent that reads any Fusion table, answers a real operational question, and gets refined in minutes — no developer, no OIC integration, no REST service, and not a single credential to manage per scenario. The pattern reuses work the customer had already paid for and turns it into a self-service capability that solves new problems as fast as the business can describe them.
Want to put live Fusion data — all of it, not a thin slice of REST resources — into the hands of your AI agents and the analysts who build them? We’ve shipped this pattern in production and we can do the same in your environment. Reach out at info@cidsolutions.co.il or WhatsApp — let’s get it solved.
Related posts: