Building an AI Financial Analyst — Part 2

If you have a Select AI Agent build ahead of you and would rather not lose a day to the same things I did, this is the foundation, in the order I would do it again.

Part 1 covered what Select AI Agent is and why I put it in the database. In this post I will go over the data, the comments that make natural language to SQL (NL2SQL) work, the profiles, and the vector index, along with the handful of things that will still catch you even running the scripts I am providing.

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

The policy that catches everyone

Before any SQL, the thing that wastes the most time on a fresh tenancy.

There are two identities in this architecture, and being a tenancy administrator only covers one of them.

The database authenticates as itself. Without a policy naming that principal, every Generative AI call and every vector-index build fails, no matter who you are. Key here is the fact that it is invisible precisely because you are an admin. Everything you personally do works, so the gap only shows when the database tries to act.

allow any-user to manage generative-ai-family in compartment <db-compartment>
  where request.principal.type = 'autonomousdatabase'

allow any-user to read object-family in compartment <bucket-compartment>
  where request.principal.type = 'autonomousdatabase'

Watch the compartments. Those two often name different ones, since databases and buckets are frequently separated. A policy only reaches its own compartment and its descendants, so siblings need the policy created in their shared parent.

While you are in the console, check your models exist:

oci generative-ai model-collection list-models -c <compartment-ocid> \
  --query 'data.items[?"lifecycle-state"==`ACTIVE`]."display-name"'

Generative AI isn't in every region, and model availability differs between the regions where it is. I built this originally against meta.llama-3.3-70b-instruct, moved the whole thing to a different region, and found that model simply doesn't exist there. If you copy a working configuration between regions, check the model list before anything else.

Table and column comments

This is the highest-return, lowest-effort thing in the whole build, so do it early.

The NL2SQL engine reads table and column comments when "comments": "true" is set on the profile, and folds them into the prompt. Without them the model has to guess what PERIOD_NAME holds, what the valid ACCOUNT_TYPE values are, and how DEPARTMENT_CODE relates to a department name.

COMMENT ON COLUMN acme_gl_transactions.period_name IS
    'Accounting period in format MON-YYYY e.g. JAN-2025, FEB-2025.
     Q1=JAN/FEB/MAR, Q2=APR/MAY/JUN, Q3=JUL/AUG/SEP, Q4=OCT/NOV/DEC.
     Query the actual data to determine which periods exist —
     do not assume a fixed range.';

Two things about that comment. Don't hardcode ranges. My first version said "data spans JAN-2025 through SEP-2025", which went stale the moment new data landed. Telling the model the format and the quarter mapping, then pointing it at the data, never needs updating.

And tell the model what not to do. That last clause addresses a failure I actually saw, the model inventing OCT-2025 before any such data existed.

Grants NL2SQL actually honours

Natural-language SQL silently ignores privileges received through a role. SELECT ANY TABLE does not satisfy it. Every source table needs an explicit direct grant:

GRANT SELECT ON acme_gl_transactions TO ACME_CORP;

This one costs people days, because the failure looks like the model being unable to write the query rather than a permissions problem.

The profiles

BEGIN
    DBMS_CLOUD_AI.CREATE_PROFILE(
        profile_name => 'ACME_NL2SQL_PROFILE',
        attributes   => '{
            "provider"           : "oci",
            "credential_name"    : "OCI$RESOURCE_PRINCIPAL",
            "oci_compartment_id" : "<YOUR_COMPARTMENT_OCID>",
            "model"              : "<MODEL_AVAILABLE_IN_YOUR_REGION>",
            "comments"           : "true",
            "constraints"        : "true",
            "conversation"       : "true",
            "temperature"        : 0.1,
            "max_tokens"         : 8000,
            "object_list"        : [
                {"owner": "ACME_CORP", "name": "ACME_GL_TRANSACTIONS"},
                {"owner": "ACME_CORP", "name": "ACME_DEPARTMENTS"}
            ]
        }'
    );
END;
/

Do not add a "region" attribute. This one surprised me. On 26ai, setting region explicitly makes the database construct a malformed inference endpoint, and every call fails with ORA-20404: Object not found naming a URL containing an unresolved my$cloud_domain placeholder. Nothing in that error points at the profile. Omit it; the database knows its own region.

Three defaults are worth overriding. max_tokens defaults to 1024, which is nowhere near enough once the agent is interpreting a question, generating SQL and narrating a result, and truncation at that layer produces genuinely baffling output. conversation defaults to false, so multi-turn context is off unless you ask for it. And "constraints": "true" lets the model read your foreign keys, so it works out table relationships without you spelling them out.

oci_compartment_id is genuinely optional, since it defaults to the database's own compartment. That default only helps if your Generative AI resources live there too. Mine don't, and leaving it out gave ORA-20052 the moment the SQL tool tried to execute.

The vector index

BEGIN
    DBMS_CLOUD_AI.CREATE_VECTOR_INDEX(
        index_name => 'ACME_VECTOR_INDEX',
        attributes => '{
            "vector_db_provider"             : "oracle",
            "location"                       : "https://objectstorage.../policy-kb/",
            "object_storage_credential_name" : "OCI$RESOURCE_PRINCIPAL",
            "profile_name"                   : "ACME_RAG_PROFILE",
            "chunk_size"                     : 1024,
            "chunk_overlap"                  : 128
        }'
    );
END;
/

That creates a backing table named after the index, ACME_VECTOR_INDEX$VECTAB, holding chunked and embedded copies of the documents. Count the rows to confirm ingestion actually happened:

SELECT COUNT(*) FROM acme_vector_index$vectab;

Worth checking, because documents must contain extractable text. A PDF that is a scanned image or a pure diagram contributes nothing, and you get silence from a document you can plainly see sitting in the bucket.

The narrate/chat trap

When the agent calls the retrieval-augmented generation (RAG) tool it uses the narrate action, and that is what triggers vector search. If you test your RAG profile by hand with SELECT AI chat, vector search never runs, because that path goes straight to the model.

The practical risk is that you test with chat, the model answers plausibly from training data, and you conclude RAG works when it doesn't. Ask something that exists only in your documents, such as a specific threshold or a retention period, and confirm the answer cites a source.

Registering the built-in tools

-- WRONG — registers fine, silently never executes
attributes => '{"tool_type":"SQL", "profile_name":"ACME_NL2SQL_PROFILE"}'

-- CORRECT — profile_name belongs inside tool_params
attributes => '{"tool_type":"SQL", "tool_params":{"profile_name":"ACME_NL2SQL_PROFILE"}}'

A top-level profile_name is ignored without complaint. The tool registers, nothing errors, and then at run time SQL gets generated and never executed, and the agent narrates something it made up. Occasionally you may run into the SQL tool firing with suspiciously round numbers coming back; check this first.

Agent, task, and team

BEGIN
    DBMS_CLOUD_AI_AGENT.CREATE_AGENT(
        agent_name  => 'ACME_ANALYST',
        attributes  => '{
            "profile_name"      : "ACME_NL2SQL_PROFILE",
            "role"              : "You are an experienced financial analyst...",
            "enable_human_tool" : "False",
            "tools"             : ["ACME_SQL_TOOL", "ACME_RAG_TOOL"]
        }'
    );
END;
/

The tools array is optional in the API, and this is the one I would most like to warn you about. Leave it out and the agent still creates, RUN_TEAM still runs, and the responses come back confident and well formatted, entirely from training data. It will invent department names, expense figures, and policy details. Nothing looks broken.

The task instruction is the routing brain, and specificity is what makes it work:

"for period-over-period trend or growth rate analysis use TREND tool;
 for forecasting future periods using linear regression use FORECAST tool;
 for detecting unusual or anomalous spending use ANOMALY tool"

Vague instructions route everything to the SQL tool.

The orphan profile

CREATE_TEAM quietly creates an internal profile named AGENT$<team_name>. DROP_TEAM does not clean it up, so the next CREATE_TEAM with the same name fails because the profile is still sitting there. I have not found this documented anywhere, so if you go looking for it in the reference you will be looking a while.

BEGIN DBMS_CLOUD_AI_AGENT.DROP_TEAM('ACME_ANALYST_TEAM');
EXCEPTION WHEN OTHERS THEN NULL; END;
/
-- the step that gets missed
BEGIN DBMS_CLOUD_AI.DROP_PROFILE('AGENT$ACME_ANALYST_TEAM', force => TRUE);
EXCEPTION WHEN OTHERS THEN NULL; END;
/

Order matters at the end too: drop the vector index before the profile it references, or the profile drop fails quietly while still in use.

Running it

DECLARE
    l_conversation_id VARCHAR2(36);
    l_response        CLOB;
BEGIN
    l_conversation_id := DBMS_CLOUD_AI.CREATE_CONVERSATION();

    l_response := DBMS_CLOUD_AI_AGENT.RUN_TEAM(
        team_name   => 'ACME_ANALYST_TEAM',
        user_prompt => 'What were Engineering expenses in August 2025?',
        params      => '{"conversation_id":"' || l_conversation_id || '"}'
    );
    DBMS_OUTPUT.PUT_LINE(l_response);
END;
/

Note the package: DBMS_CLOUD_AI.CREATE_CONVERSATION, not DBMS_CLOUD_AI_AGENT. That is an easy five minutes to lose.

params looks optional and isn't. Omit it and you get ORA-01400: cannot insert NULL into CONVERSATION_ID. And don't reach for SYS_GUID(): RUN_TEAM wants the hyphenated 36-character UUID that CREATE_CONVERSATION returns, and SYS_GUID() gives you 32 hex characters with no hyphens. The symptom is "Invalid value for conversation id", which reads like an expired session rather than a formatting problem. Reuse the same id across turns to keep context.

One more, if you load your own data

SQL*Plus and SQLcl treat & as a substitution prefix. A perfectly ordinary value like 'Prepare management P&L report' makes the client prompt for a variable called L, the prompt cancels, and the INSERT is silently skipped. No error, just missing rows.

SET DEFINE '^'   -- then use ^variable for substitutions

Finance data is full of ampersands: P&L, FP&A, R&D. Worth setting before you load anything.

Part 3 covers the custom tools, three in PL/SQL and one running Python inside the database, along with an authentication problem that turned out to have a better answer than the documented one.

Enjoy!

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