Building an AI Financial Analyst — Part 4

If you have ever had a deploy script report success on every statement, then watched the thing fall over at run time with an error pointing somewhere else entirely, you will recognise what this post is about.

Parts 1 through 3 built the agent. In this post I will go over the one behaviour that cost me the most time, what it looks like from the agent's side, and the order I now investigate things in. This is the part I would actually want to hand to someone starting out.

github.com/BASoapbox/ACME-Corp-Select-AI-Agent-Project

Success is not the same claim as working

Here is the behaviour underneath most of what follows.

A PL/SQL object with a compile error still gets created. It just gets created INVALID. The database raises ORA-24344: success with compilation error, and depending on your driver that is very often treated as a warning rather than an exception. python-oracledb, cx_Oracle and plenty of Java Database Connectivity (JDBC) setups will all happily report success for an object that fails the moment anything calls it.

So your script says fine. Your deploy log says fine. And nothing surfaces until something several layers away tries to use the broken object.

The check that takes five seconds

SELECT object_name, status FROM user_objects WHERE status <> 'VALID';

If anything comes back, the real error, with line and column numbers, is sitting in USER_ERRORS:

SELECT line, position, text FROM user_errors
WHERE  name = 'ACME_PYTHON_EXPENSE_ANALYSIS' ORDER BY sequence;

I now run that as a habit after creating any PL/SQL object involving DBMS_CLOUD or DBMS_CLOUD_AI_AGENT. It costs nothing when the object is valid, and when it isn't, it is the difference between a five-second fix and starting your debugging at the wrong end of the stack.

What it looks like from the agent

Here is what that failure looked like from the agent's side, with no other context:

ORA-20053: Job ACME_ANALYST_TEAM_TASK_0 failed: ORA-20051:
Task Order 0 failed with error: Task failed: ORA-20052:
Error getting argument metadata: ORA-20052: Procedure not found:
ACME_CORP.ACME_PYTHON_EXPENSE_ANALYSIS

Read literally, that is a missing object or a missing grant. Neither was true, as the function existed and the grants were correct.

The actual cause, an invalid object, appears nowhere in the message. DBMS_CLOUD_AI_AGENT is reporting a symptom: it tried to introspect the function's argument metadata to work out how to call it, and introspection fails against an INVALID object. Three layers of error wrapping, and the useful information is in none of them.

The order to investigate in

So the question that invariably arises is where to start when a custom tool misbehaves. Three checks, in this order, each ruling out a category.

1. USER_ERRORS, is the object actually valid? Rules out compile errors, which covers everything above. If it is invalid, stop here. Nothing downstream matters yet.

2. A direct call, SELECT your_function(...) FROM dual. Bypasses the agent entirely and rules out tool registration, routing, and the reasoning loop. If it works standalone but fails through the agent, the problem is in the registration or the call, not the function.

3. USER_AI_AGENT_TOOL_HISTORY. Once you know the function is valid and works alone, this shows what the agent actually sent and what came back.

SELECT tool_name, agent_name, start_date, SUBSTR(tool_output,1,200)
FROM   user_ai_agent_tool_history
ORDER  BY start_date DESC FETCH FIRST 10 ROWS ONLY;

Sensible parameters and correct data but a wrong final answer is a narration problem, usually the task instruction, and a different fix altogether. Wrong tool is a routing problem. An error in the output is a function problem. The view tells the three apart, which is more than the error messages manage.

The order matters because each layer's errors impersonate a different layer's problems convincingly. An invalid function produces an agent-level "not found" that reads like a grants issue. A poor routing instruction produces wrong data that reads like a SQL bug. Working bottom-up means you are never debugging the wrong layer, which is exactly what I did for most of a day.

The same pattern, three more times

Once you start looking for "reported success, didn't work", it turns up in places that have nothing to do with PL/SQL compilation.

The agent that answers without tools. The tools array on CREATE_AGENT is optional. Omit it and the agent creates, RUN_TEAM runs, and the answers come back confident, well formatted, and entirely invented. No error anywhere. The only way to catch it is USER_AI_AGENT_TOOL_HISTORY showing nothing fired.

The SQL tool that generates but never executes. Put profile_name at the top level of CREATE_TOOL instead of inside tool_params and it is ignored without complaint. The tool registers. At run time SQL is generated and never run, and the agent narrates something plausible over the top.

The INSERT that never happened. Load data containing a literal ampersand, such as P&L, FP&A, anything finance-shaped, and SQL*Plus treats &L as a substitution variable. It prompts, the prompt cancels, and the statement is skipped. The word "error" appears nowhere in the output. I found nine missing rows this way, in a load that reported clean.

Case and point, that last one changed how I verify things. I had grepped the log for ORA- and found nothing, and concluded the load was fine. The message was Substitution cancelled, with no error code and no ORA-. Searching for error text is not a test. Counting the rows you expected is.

What made these expensive

None of the fixes were complicated. A credential lookup became a Vault call. .text became GET_RESPONSE_TEXT(...). SQLERRM got assigned to a variable first. SET DEFINE '^'. One or two lines each.

What made them expensive is that the tooling told me each one had succeeded. DBMS_CLOUD-adjacent PL/SQL has several places where "the statement ran without an exception" and "the thing I built works" are not the same claim, and the gap doesn't show up until something several layers away fails with a message that doesn't point back at it.

A USER_ERRORS check after every CREATE OR REPLACE, a fixed order of investigation, and verifying by counting what you expected rather than searching for what you feared, are all cheap to adopt. Worth doing before you need them.

Part 5 is about what happened after I had built this twice by hand and decided I never wanted to do it a third time. If you have run into a different flavour of the silent-success problem, and I would guess there are more than the ones here, please let me know.

Enjoy!

Built on Oracle Autonomous Database 26ai · OCI Generative AI · OML4Py