Building an AI Financial Analyst — Part 3

If you have got a working agent and are wondering how much of your own code you can put behind it, this is the part that made the whole exercise worth doing.

Parts 1 and 2 got a two-tool agent running. In this post I will go over registering your own functions as tools, running Python inside the database, and the authentication problem underneath it, which turned out to have a better answer than the documented one.

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

A custom tool is just a function

The three PL/SQL tools, trend, forecast and 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" : "Analyse expense trends over time for ACME Corp departments.
                       Returns period-over-period changes and growth rates.",
      "function"    : "ACME_TREND_ANALYSIS"
    }'
  );
END;
/

Two things matter more than they look.

Write the instruction 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.

Parameters map by name, case included. A function parameter p_department is supplied by the agent as "P_DEPARTMENT". Get the case wrong and the call fails in a way that looks like the function is broken.

There is also a quieter detail. The agent introspects your function's argument metadata to work out how to call it, and introspection runs against the compiled object. A function that exists but is INVALID produces an agent-level error that reads like a missing object entirely. That is Part 4's territory, but it starts here.

Python inside the database

ACME_PYTHON_TOOL runs through Oracle Machine Learning for Python (OML4Py) Embedded Python Execution (EPE), which runs Python inside Autonomous Database with no external Python environment anywhere. The wrapper queries data in PL/SQL, serialises it to JSON, hands it to a registered Python function via pyqEval, and numpy does the statistics.

The concept is elegant. Getting there took more effort than the documentation suggests, and one thing worth mentioning is that 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:

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, and Oracle expands it and stores the full instance hostname. So what pyqGetHostAce hands back looks different from what you passed in. That is correct rather than a bug, but it is why several plausible-looking variants of this call circulate.

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. So the question that invariably arises is where the password comes 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 the username, never the password.

This isn't a documentation gap, it is rather 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 is the documented approach, but it leaves a live password sitting 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.

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 is 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 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 deserialises it for you and Python gets a native list, so json.loads() fails with "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, in that 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, so connecting back in trips the ACL. Query in PL/SQL, pass through par_lst, and 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, for different purposes. For one department in my dataset that is $41,199 against $43,698.

Minor on its own, but it matters when the SQL tool and the Python tool can both answer related questions. You don't want a different number depending on which one the agent happened to pick. Use STDDEV_POP() in SQL when they must agree.

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.

Everything else is drop-and-recreate. That sounds worse than it is. Recreating a profile under the same name leaves every tool referencing it valid, because tools resolve the profile by name. Changing a task instruction only requires dropping the team and the task, not the agent. Changing an agent role means the whole team, task and agent chain comes down and goes back up, and don't forget the orphan AGENT$ profile from Part 2.

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

Enjoy!

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