Building an AI Financial Analyst with Oracle Select AI Agent — Part 3

Parts 1 and 2 got a working two-tool agent. This is the part that made the whole exercise worth doing: registering your own functions as tools, including one that runs Python inside the database.

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

---

The four custom tools

You already have these. All six tools were created by SA_03 when you ran it in Part 2 — the two built-in ones that post explained, and the four below that it didn't. This part is what they are, why they are shaped the way they are, and what it takes to build one of your own.

Three of them are cheap. The fourth is most of this post.

Tool	What it is	What it costs you
ACME_TREND_TOOL	PL/SQL, LAG()	A function and a CREATE_TOOL call
ACME_FORECAST_TOOL	PL/SQL, linear regression	The same
ACME_ANOMALY_TOOL	PL/SQL, STDDEV()	The same
ACME_PYTHON_TOOL	pandas and numpy, via OML4Py	Prerequisites from SA_01, plus somewhere to keep a password

All four live in SA_03 alongside the built-in pair, and each goes into the agent's tools array the same way. The three PL/SQL tools share one pattern you will have after the next section. The Python tool is where the work is — and where the one genuinely interesting problem in this series lives.

Important note. The Python tool's prerequisites were installed back in Part 2, by SA_01 running as ADMIN — the OML4Py roles and the Embedded Python Execution network ACL. If you skipped that script, or ran it as the schema user, nothing in the Python half of this post will work.

---

A custom tool is just a function

The three PL/SQL tools — trend, forecast, anomaly — were the easy part. Each returns a CLOB of JSON, gets registered, and goes into the agent's tools array.

BEGIN
  DBMS_CLOUD_AI_AGENT.CREATE_TOOL(
    tool_name  => 'ACME_TREND_TOOL',
    attributes => '{
      "instruction" : "Analyze expense trends over time for ACME Corp departments.
                       Returns period-over-period changes and growth rates.",
      "function"    : "ACME_TREND_ANALYSIS"
    }'
  );
END;
/

Two things in that registration matter more than they look: the instruction you write, and the parameters you don't.

1. The instruction is the routing logic

Write it like a docstring for another developer — when to use it, what it takes, what it gives back. That text is the only thing the model reads when deciding whether to call your function. Vague instruction, and the tool never fires.

2. The parameters are never declared at all

Notice what the registration does not contain: any mention of the function's parameters. Here is the function sitting behind that tool:

CREATE OR REPLACE FUNCTION acme_trend_analysis(
    p_department IN VARCHAR2 DEFAULT NULL,
    p_periods    IN NUMBER   DEFAULT 6
) RETURN CLOB IS

Two parameters, and CREATE_TOOL was told about neither. It doesn't need to be. When the agent decides to use the tool, the database reads the argument list straight off the compiled function and hands the model a parameter list to fill in. You describe the function in prose; the database supplies the signature.

You can watch it happen — USER_AI_AGENT_TOOL_HISTORY records what the agent sent:

{"P_DEPARTMENT":"Engineering","P_PERIODS":6}

Uppercase, because that is how Oracle stores an ordinary identifier. Which leads to the one thing in this section that will genuinely cost you an afternoon.

Never declare a tool function's parameters as quoted identifiers. Write p_department and Oracle folds it to P_DEPARTMENT; write "p_department" and it stays lowercase. Introspection is faithful either way — the agent reads the stored name and sends exactly that. But the bind only lands on the uppercase form. With a quoted lowercase parameter the agent sends the right name, the value is dropped, the parameter's DEFAULT fires instead, and nothing raises an error.

I tested this rather than assuming it — two identical functions differing only in quoting:

Declared as	Stored as	Agent sent	Function received
p_department	P_DEPARTMENT	{"P_DEPARTMENT":"Engineering"}	Engineering
"p_department"	p_department	{"p_department":"Engineering"}	the default

The second row is the dangerous one. The tool runs, returns valid JSON, and the agent narrates a confident answer — computed from the default rather than from what anyone asked about. If your default is NULL meaning "all departments", every question quietly gets a company-wide answer wearing one department's name.

And introspection needs a valid object, not merely an existing one. A function that compiled with an error still exists — it just exists INVALID, and its argument metadata cannot be read. The agent reports that as the function being missing altogether, naming your function in an error that looks exactly like a bad grant or a typo. Part 4 is about that whole class of failure; this is where it starts.

---

Python inside the database

ACME_PYTHON_TOOL runs through OML4Py Embedded Python Execution (EPE), which runs Python inside Autonomous Database with no external Python environment anywhere. The wrapper queries data in PL/SQL, serializes it to JSON, hands it to a registered Python function via pyqEval, and pandas and numpy do the statistics — pandas for the frame handling, numpy for the arithmetic.

Pandas is load-bearing here, even though numpy does the math. An Embedded Python function has to hand its result back in a form pyqEval can serialize, and a DataFrame is that form. The pattern is to put the whole JSON payload in a single cell:

return pd.DataFrame({"RESULT": [json.dumps(result)]})

Every return path needs that shape, and the one people miss is the guard at the top of the function — the branch that runs when no data arrived:

rows = json.loads(data_json)
if not rows:
    result = {"error": "No expense data passed to Python function"}
    return pd.DataFrame({"RESULT": [json.dumps(result)]})   # not a bare dict

That is precisely where you reach for a plain dictionary, because you are reporting a problem rather than returning results. Do that and the error path fails on its way to telling you about the error — and what surfaces is a serialization complaint, not your message.

The concept is elegant. Getting there took more effort than the documentation suggests — and notably, Oracle's own agent samples repository has ten worked examples and none of them cover EPE, so most of this came from trial and error.

There are four configuration steps, each failing differently when missing. Three ran as ADMIN in Part 2; the fourth is the one this post is really about:

Step	What	Where it happens	Symptom when missing
1	GRANT PYQADMIN	SA_01, as ADMIN	sys.pyqScriptCreate raises a privilege error
2	GRANT OML_DEVELOPER	SA_01, as ADMIN	pyqEval returns HTTP 404 even though your script is right there in USER_PYQ_SCRIPTS
3	pyqAppendHostAce	SA_01, as ADMIN	ORA-20101: Host ACL not configured
4	A valid OML token	Every call, from the wrapper	Authentication failure at call time

Steps 1 to 3 are one-time. Step 4 is not — the token expires roughly hourly, which is why it is a refresh step rather than setup, and why it needs a password from somewhere.

Step 3 is not the normal network ACL. Embedded Python Execution uses a separate PYQSYS-managed ACL, and DBMS_NETWORK_ACL_ADMIN.APPEND_HOST_ACE has no effect on it at all. I spent a frustrating stretch on DBMS_NETWORK_ACL_ADMIN variations before finding that out.

-- signature is (USERNAME, HOST_ROOT_DOMAIN) — schema first, two arguments, no ports
EXEC pyqAppendHostAce('ACME_CORP', 'adb.us-ashburn-1.oraclecloudapps.com');

You pass the root domain; Oracle expands it and stores the full instance hostname. So what pyqGetHostAce hands back looks different from what you passed in. That's correct, not a bug — but it's why several plausible-looking variants of this call circulate.

One thing that will waste your time: pyqGetHostAce can only be run by ADMIN. Call it as the agent schema and you get ORA-20100: 'ADMIN' user is required to execute this function, which reads like the ACL is broken when it is perfectly fine. Verify from an ADMIN session, not from the schema.

---

Step 4, and the interesting part

Embedded Python Execution authenticates with a short-lived OML token from an OAuth password-grant exchange. It expires roughly hourly, so you want a refresh step rather than one-time setup.

That exchange needs a real username and password in the POST body. Which raises the obvious question: where does the password come from?

What doesn't work

The intuitive answer is to store it in a DBMS_CLOUD credential and read it back when needed. That is not possible, and not because I had a view name wrong. Oracle deliberately does not expose a stored credential's password through any view, function or procedure — USER_CREDENTIALS and ALL_CREDENTIALS confirm a credential exists and show its username, never the password.

This isn't a documentation gap; it's the whole point. A credential store you could SELECT out of wouldn't be meaningfully different from a plaintext config table.

Oracle's own OML4Py reference implementation resolves this by hardcoding the password in the PL/SQL body. That works, and it's the documented approach, but it leaves a live password in a function.

What does work

The credential store is one-way. The Vault is not.

OCI Vault has a REST API, and the database can call it with its own Resource Principal — the same identity it already uses for Generative AI and Object Storage. The password never appears in the script at all.

Seven steps, and the whole thing runs on every call to the Python tool:

Step	What happens	Between	Worth knowing
1	GET /secretbundles/<ocid>	Database → OCI Vault	Authenticated as OCI$RESOURCE_PRINCIPAL. No stored credential, no password in the script — the database proves its own identity
2	Password returned, base64	Vault → Database	Decoded in memory with UTL_ENCODE.BASE64_DECODE. The value never lands in a table, a variable you can query, or a log
3	POST grant_type=password	Database → OML endpoint	The OAuth exchange, and the reason steps 1 and 2 exist — this call needs a real username and password in the body
4	accessToken, expires ~60 min	OML endpoint → Database	Short-lived by design. This is what makes it a refresh step on every call rather than one-time setup
5	pyqSetAuthToken(token)	Inside the database	Sets the token for the session. The wrapper then queries the GL data in PL/SQL and serializes it to JSON
6	pyqEval(par_lst)	Database → Embedded Python	The data crosses as a JSON string, not a list. Getting this wrong is the first trap below
7	JSON result	Embedded Python → Database	Returned as a single-cell DataFrame, which arrives in PL/SQL as a CLOB for the agent to narrate

Steps 1 to 4 are the part worth stealing. Nothing in them is specific to OML4Py — any API that wants a password in a request body can be fed this way, and the password stays in the Vault where it belongs.

CREATE OR REPLACE FUNCTION acme_get_secret(p_secret_ocid IN VARCHAR2)
RETURN VARCHAR2 IS
    v_resp DBMS_CLOUD_TYPES.resp;
    v_b64  VARCHAR2(32767);
BEGIN
    v_resp := DBMS_CLOUD.SEND_REQUEST(
        credential_name => 'OCI$RESOURCE_PRINCIPAL',
        uri             => 'https://secrets.vaults.<region>.oci.oraclecloud.com'
                        || '/20190301/secretbundles/' || p_secret_ocid,
        method          => 'GET');
    v_b64 := JSON_VALUE(DBMS_CLOUD.GET_RESPONSE_TEXT(v_resp),
                        '$.secretBundleContent.content');
    RETURN UTL_RAW.CAST_TO_VARCHAR2(
               UTL_ENCODE.BASE64_DECODE(UTL_RAW.CAST_TO_RAW(v_b64)));
END acme_get_secret;
/

One policy statement enables it:

allow any-user to read secret-bundles in compartment <c>
  where request.principal.type = 'autonomousdatabase'

There's a second benefit I didn't anticipate. If you type the password when creating the schema and separately store it in Vault, you now have two copies that can silently drift apart. Vault-first makes the secret the single source: the schema-creation script (SA_00) reads it, and so does the Python tool. One value, one place, never typed twice.

---

Two traps in the wrapper

Both compile cleanly and fail only at run time.

Passing data to Python. data_json must reach Python as a string, not a list:

-- WRONG — Python receives a list, json.loads() fails
v_par_lst := '{"data_json":' || v_data || '}';

-- CORRECT — quoted string value
v_par_lst := '{"data_json":"' || REPLACE(v_data, '"', '\"') || '"}';

Unquoted, pyqEval deserializes it for you and Python gets a native list, so json.loads() fails. Running both forms against the same script, the difference is exact:

quoted   -> {"python_expense_analysis": {"department_filter": "Engineering", ...
unquoted -> ORA-20100: the JSON object must be str, bytes or bytearray, not list

Logging from a function the SQL engine called. A function invoked from a SQL statement cannot perform DML. Every attempt to write to an error-log table raises ORA-14551. This one has a particular sting: the logging you added so failures would be traceable is itself the thing that fails. Route it through an autonomous procedure:

CREATE OR REPLACE PROCEDURE acme_log_error(p_source VARCHAR2, p_msg VARCHAR2) IS
    PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
    INSERT INTO acme_error_log(error_source, error_msg) VALUES(p_source, p_msg);
    COMMIT;
END;
/

And don't call oml.connect() inside an EPE function. Some OML4Py examples show it, but inside EPE the Python engine is already running in the database — connecting back in trips the ACL. Query in PL/SQL, pass through par_lst, let Python compute only.

---

Reconciling SQL and Python statistics

Once the Python tool ran, my means matched and my standard deviations didn't.

numpy's std() is population standard deviation, dividing by n. SQL's STDDEV() is sample, dividing by n−1. Both are correct; they answer slightly different questions, and only one of them is labeled in the output.

Run both over the same nine periods of expense data:

SELECT d.department_name,
       ROUND(STDDEV(t.amt), 2)     AS stddev_sample,
       ROUND(STDDEV_POP(t.amt), 2) AS stddev_population
FROM  (SELECT department_code, period_name, SUM(debit_amount) amt
       FROM   acme_gl_transactions
       WHERE  account_type = 'EXPENSE'
       GROUP  BY department_code, period_name) t
JOIN   acme_departments d ON d.department_code = t.department_code
GROUP  BY d.department_name;

Department	STDDEV (sample)	STDDEV_POP (population)	Difference
Engineering	45,255.83	42,667.61	2,588.22
Finance	43,698.14	41,199.01	2,499.13
Sales	26,059.90	24,569.51	1,490.39
Corporate	3,021.91	2,849.08	172.83
Operations	474.34	447.21	27.13

Ask the Python tool for Engineering and it reports 42,667.61 — the population column, exactly. The two tools are not disagreeing. They are answering different questions, and the agent narrates whichever one it happened to call.

Notice that every row is out by the same 6.07%. That is not a coincidence: the two forms differ by a factor of √(n/(n−1)), which at nine periods is √(9/8) = 1.0607, regardless of the data. The gap shrinks as periods accumulate and never quite closes.

Six percent sounds ignorable, and on its own it is. The real problem is that the form is not the only thing that varies. Ask "what is the standard deviation of Finance expenses?" and three tools in this stack return three defensible answers:

Answering tool	What it actually measures	Finance
SQL tool	STDDEV over individual transactions	50,676.67
Anomaly tool	STDDEV over monthly totals	43,698.14
Python tool	np.std over monthly totals	41,199.01

A 23% spread, and none of them is wrong — each is correct for the question it actually answered. The user asked one question; the agent chose the tool.

Making them agree

Change one side, not both, or you have only swapped which tool is the odd one out.

The anomaly tool, in SA_03:

-- from
STDDEV(SUM(t.debit_amount))     OVER (PARTITION BY d.department_name) AS stddev_expense
-- to
STDDEV_POP(SUM(t.debit_amount)) OVER (PARTITION BY d.department_name) AS stddev_expense

Or the Python tool, in the registered EPE script:

# from
std = float(np.std(vals))
# to -- sample form, matching SQL's default STDDEV
std = float(np.std(vals, ddof=1))

The SQL tool is the one you cannot edit, because it writes its own SQL from the question. Asked this twice, it produced STDDEV(...) over raw transaction rows both times — sample form, and the wrong grain. Your only lever is the one from Part 2, a column comment:

COMMENT ON COLUMN acme_gl_transactions.debit_amount IS
    'Expense amount per transaction. For period-level variability, aggregate to
     the period first and use STDDEV_POP, so results match the Python tool.';

That is a hint rather than a guarantee. Check USER_AI_AGENT_TOOL_HISTORY to see which form was actually generated.

---

Changing things later

Worth knowing before you need it: there is no UPDATE_PROFILE, UPDATE_AGENT, or UPDATE_TASK. The only UPDATE_ procedures in the packages are UPDATE_CONVERSATION and UPDATE_VECTOR_INDEX.

That is not the same as saying you have to drop and recreate, which is what I assumed for far too long. The in-place path exists — it just isn't called UPDATE_:

-- profiles
DBMS_CLOUD_AI.SET_ATTRIBUTE('ACME_NL2SQL_PROFILE','max_tokens','4000');

-- tools, tasks, agents, teams — note the object type as the second argument
DBMS_CLOUD_AI_AGENT.SET_ATTRIBUTE(
    object_name     => 'ACME_ANALYST_TASK',
    object_type     => 'TASK',
    attribute_name  => 'instruction',
    attribute_value => '...new routing instruction...');

Both packages also carry a plural SET_ATTRIBUTES for changing several at once. This is worth knowing precisely because searching the documentation for update finds nothing and sends you off to write a teardown script you never needed.

When you genuinely do need a rebuild, recreating a profile under the same name leaves every tool referencing it valid, because tools resolve the profile by name — and remember the orphan AGENT$ profile from Part 2.

---

Part 4 is the one I'd most want to hand someone starting out: how PL/SQL can report success on something that is comprehensively broken, and the order to investigate things when a tool misbehaves.

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