Building an AI Financial Analyst with Oracle Select AI Agent — 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'll recognize what this post is about.

This is the part I'd actually want to hand to someone starting out.

The short version

If you take one thing from this post: when something in this stack misbehaves, work bottom-up. Three checks, each ruling out a whole category.

The rest of this post is why that order, what each layer's errors look like when they impersonate another layer's problem, and the three places this stack reports success on something comprehensively broken.

Success is not the same claim as working

Here's the behavior 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 whether you ever see that depends entirely on your client.

Worth being precise here, because it is the difference between a good warning and a wrong one. SQLcl and SQL Developer do print the compile error immediately. Programmatic drivers frequently do not — python-oracledb, cx_Oracle and plenty of JDBC setups treat ORA-24344 as a warning rather than an exception. So this bites hardest when objects are created by a deploy script or an application, which is exactly where nobody is watching the output.

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's 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's a missing object or a missing grant. Neither was true — the function existed and the grants were correct.

The actual cause, an invalid object, appears nowhere in the message. What you are reading is a symptom: DBMS_CLOUD_AI_AGENT 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.

There is a second silent success hiding in here, and it is the one that lets the first one travel. CREATE_TOOL does not check that the function it is pointed at is valid — or that it exists at all. Registering a tool against a broken function succeeds without complaint:

BEGIN
  DBMS_CLOUD_AI_AGENT.CREATE_TOOL('MY_TOOL','{
     "instruction":"...",
     "function":"A_FUNCTION_THAT_IS_INVALID"}');
END;
/
-- PL/SQL procedure successfully completed.

So the chain runs: the function compiles INVALID and your client may not say so; the tool registers against it and definitely does not say so; and the first thing that actually objects is the agent, at run time, in an error that names your function and looks like a permissions problem. Three chances to catch it, two of them silent.

The order to investigate in

Back to those three checks, with what each one actually buys you.

1. USER_ERRORS — is the object actually valid? Rules out compile errors, which covers everything above. If it's 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, 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're 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 an answer comes back — with no tool having fired and no error raised anywhere. What you get instead of data varies by model: mine echoed the question back at me across four runs, but nothing prevents a more confident model from filling the gap with plausible figures. Either way the tell is the same, and it is not in the error log: USER_AI_AGENT_TOOL_HISTORY shows nothing fired.

The parameter that quietly becomes its own default. Declare a tool function's parameter as a quoted identifier — "p_department" rather than p_department — and Oracle stores it lowercase. The agent introspects faithfully and sends the lowercase name, but the bind only lands on the uppercase form. The value is dropped, the parameter's DEFAULT fires, and the tool returns valid JSON computed from the wrong input. No error at any layer. If the default is NULL meaning "all departments", every question gets a company-wide answer wearing one department's name.

The INSERT that never happened. Load data containing a literal ampersand — 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.

That last one is worth dwelling on, because it 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 — no error code, no ORA-. Searching for error text is not a test. Counting the rows you expected is.

The check that lies the other way

Everything above is the tooling reporting success it had not earned. The inverse happens too, and it cost me an hour: a check reporting failure against something that works perfectly.

The grant succeeds, without complaint:

GRANT EXECUTE ON DBMS_CLOUD TO acme_corp;

Then the obvious way to confirm it returns nothing at all:

SELECT table_name FROM dba_tab_privs
WHERE  grantee   = 'ACME_CORP'
AND    privilege = 'EXECUTE'
AND    table_name = 'DBMS_CLOUD';

The reason is that DBMS_CLOUD is a public synonym rather than a package. The real object is version-suffixed and owned by C##CLOUD$SERVICE:

DBMS_CLOUD$PDBCS_260821_1_0

The grant lands on that name. A lookup for the literal string finds nothing and reports a granted package as missing. Worse, the suffix moves when the database is patched, so a check written against today's name breaks on a schedule you do not control.

I hit this writing a verification query for a provisioning script. It printed expect 4 rows, returned 3, and the fourth grant was in place the whole time.

The fix is this post's own principle pointed the other way: do not ask the dictionary whether you can do the thing — do the thing.

BEGIN
    IF 1 = 0 THEN
        DBMS_CLOUD.CREATE_CREDENTIAL(credential_name => 'x',
                                     username        => 'y',
                                     password        => 'z');
    END IF;
END;
/

PL/SQL resolves every identifier when the block is compiled, so a missing grant fails right there. The IF 1 = 0 guarantees nothing ever executes. One compile, no side effects, and it stays correct across patches because it never names the versioned object.

One detail worth carrying: a denied EXECUTE surfaces from a PL/SQL block as PLS-00201, wrapped in ORA-06550 — not as ORA-01031. Match only ORA-01031 and a genuine denial reads to your script as a pass.

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 before use, and the substitution character became ^. One or two lines each.

What made them expensive is that the tooling told me each one had succeeded. There are several places in DBMS_CLOUD-adjacent PL/SQL 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 — all cheap to adopt. Worth doing before you need them.

Part 5 is about what happened after I'd built this twice by hand and decided I never wanted to do it a third time.

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