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

The Foundation: NL2SQL and RAG

Important

Disclaimer: The writing and musing of the author do not necessarily reflect the views of his employer.

By Ray Février | 15 min read | Jul 29, 2026

Part 1 covered what Select AI Agent is and why I put it in the database. This is the build — data, comments, profiles, vector index, and the four objects that stack on top of them — along with the things that will still catch you even running the scripts I'm providing.

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

The build, in order

Seven steps. Each has to work before the next one means anything, and it is worth holding on to because it is also the order you debug in — each layer's errors impersonate a different layer's problems.

#	Step	Where
1	IAM policy for the database's own principal	OCI console
2	Schema, Resource Principal, `DBMS_CLOUD` grants	`SA_00`, `SA_01`
3	OML4Py roles and the EPE network ACL	`SA_01`
4	Tables, data, and the comments NL2SQL reads	`SA_02`
5	Profiles — one for NL2SQL, one for RAG	`SA_03`
6	Vector index over the policy PDFs	`SA_03`
7	All six tools → task → agent → team	`SA_03`

Step 3 is groundwork for something that doesn't appear until Part 3. The Python tool runs through OML4Py Embedded Python Execution, and its prerequisites — PYQADMIN, OML_DEVELOPER and a PYQSYS-managed network ACL — have to be granted by ADMIN, not by the schema. They sit here because that is the last point you are still holding an ADMIN connection. Skip them now and Part 3 sends you back for a connection you have closed.

Step 7 creates all six tools in one run. SA_03 does not split them, so if you run it at the end of this post you will find six tools registered and an agent that already references all of them. Two are built in and are what this post explains. The other four are custom — three PL/SQL, one Python — and they are Part 3's subject. Nothing is missing; you will just be holding four tools I haven't accounted for yet.

The rest of this post walks that order. The scripts do all of it unattended; what follows is the part the scripts can't tell you, which is why each step is shaped the way it is and what happens when it isn't.

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.

Principal	What it does	Covered by your admin role?
You, a human	Creates the database, bucket, vault, schema	Yes
The database, via Resource Principal	Calls Generative AI, reads RAG documents	No

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. And it's invisible precisely because you're 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're 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 the models differ between the regions where it is. This is not hypothetical — checking three regions from one tenancy while writing this:

Region	`meta.llama-3.3-70b-instruct`
us-chicago-1	available
us-phoenix-1	available
us-ashburn-1	not available

That is the model this project was originally built on, and it does not exist in the region the database now runs in. 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, and the role trap that isn't about roles

If your source tables live in another schema, natural-language SQL will fail in a way that looks like the model can't write the query. The usual advice is that NL2SQL ignores role-granted privileges and you must grant directly. That is not what happens. I tested it: a table in a separate schema, SELECT granted only to a role, that role granted to the agent schema, and no direct grant anywhere.

Role state	Enabled in session	NL2SQL result
Default role	yes	"9300 units of Gasket were sold" — correct
Not a default role	no	Fails, and invents a query against tables that don't exist

NL2SQL honors role-granted privileges perfectly well. What it cannot do is use a role that isn't enabled in the session — and a freshly granted role is not automatically a default role:

GRANT SELECT ON zz_src.zz_widgets TO my_role;
GRANT my_role TO acme_corp;
ALTER USER acme_corp DEFAULT ROLE ALL;   -- the step that gets missed

Check with SELECT role FROM session_roles. If your role isn't listed, ordinary SQL fails too — which is the quickest way to tell a privilege problem from a model problem. A direct grant also works and sidesteps the question entirely, which is presumably why the folklore took hold.

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"              : "<a model that exists in your region>",
            "comments"           : "true",
            "constraints"        : "true",
            "conversation"       : "true",
            "temperature"        : 0.1,
            "max_tokens"         : 4000,
            "object_list"        : [
                {"owner": "ACME_CORP", "name": "ACME_GL_TRANSACTIONS"},
                {"owner": "ACME_CORP", "name": "ACME_DEPARTMENTS"}
            ]
        }'
    );
END;
/

Do not set a region attribute. This one is worth stating carefully, because it behaves in a way that will make you doubt your own testing. Set region to the region your database is actually in, and every call fails:

ORA-20404: Object not found -
https://inference.generativeai.us-ashburn-1.oci.my$cloud_domain/20231130/actions/chat

That my$cloud_domain is a placeholder the database never resolved — the endpoint really is malformed, and nothing in the error points at your profile.

Here is the part that makes it confusing. Three profiles, identical except for this one attribute, against a database in us-ashburn-1:

`region` attribute	Result
omitted	works
`us-ashburn-1` — the database's own region	fails
`us-chicago-1` — a different region	works

It only breaks when the value matches the region the database is already in. Set it to somewhere else and it works fine, which is a very effective way to convince yourself the attribute is harmless. Omit it entirely; the database resolves its own endpoint correctly and has no need to be told.

Three settings are worth being deliberate about:

max_tokens is widely documented as defaulting to 1024. It doesn't, at least not here. Asking the same long question three times through a profile with no max_tokens set returned 8,040 / 9,496 / 9,444 characters; the same profile pinned at 1024 returned 5,085 / 5,219 / 5,295. If the default were 1024 those would match. Set it explicitly anyway — not because the default is too low, but because you want a known value. And check your model's ceiling before raising it: I ran this at 8000 for months, and when the profile was later pointed at a Llama 4 model every call began failing with HTTP 400 — Invalid 'maxTokens': Value is greater than maximum: 4096. That limit belongs to the model, not the profile, and the error comes back from the inference endpoint without naming your configuration. 4000 is safe.
conversation governs multi-turn context, and it is also usually described as defaulting to off. With the attribute never set, I told a profile my favorite color, then asked what color I had named in a second call on the same conversation id. It answered correctly. Supplying a conversation id appears to be enough on its own. Set the attribute anyway, for the same reason as above — you want the behavior to be stated rather than inherited.
constraints set to 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 — 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, embedded copies of the documents. Count the rows to confirm ingestion actually happened:

SELECT COUNT(*) FROM acme_vector_index$vectab;

Do not stop at the row count. It is the obvious check and it will let you down. I built a PDF that was pure image with no text layer at all, dropped it in the bucket with seven ordinary documents, and rebuilt the index. The count went from seven rows to eight — a clean, reassuring one-row-per-document match. The document was still useless: asked about a figure that appears only in that file, RAG replied that the data does not contain it.

The row is there; it just has nothing in it. Look at the content length instead:

SELECT JSON_VALUE(attributes, '$.file_name') AS source,
       LENGTH(content)                       AS chars
FROM   acme_vector_index$vectab
ORDER  BY chars;

The seven real documents came back with 458 to 690 characters each. The scanned one had 26 — the file name, and nothing else. That is the signal. A short row means a document that was indexed but never actually read, and a row count alone will never show it to you.

The narrate/chat trap

When the agent calls the 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 — that path goes straight to the model.

This is easy to demonstrate. The same question, same profile, two actions:

chat    -> "I need more information ... the ACME reconciliation policy and its
            specifics are not details I have access to"
narrate -> "The intercompany balance threshold requiring CFO sign-off is $50,000.
            Sources: reconciliation_policy.pdf"

Only narrate searched the index, and only narrate cited a source. Test with something that exists only in your documents — a threshold, a retention period — and check for the citation. My model was honest enough to say it didn't know; a more confident one would have guessed, and you'd have concluded RAG was working.

Registering the built-in tools

Two of the six tools are built in. Neither needs a function behind it — they are a tool_type and a profile.

BEGIN
    DBMS_CLOUD_AI_AGENT.CREATE_TOOL(
        tool_name  => 'ACME_SQL_TOOL',
        attributes => '{
            "tool_type"   : "SQL",
            "tool_params" : {
                "profile_name" : "ACME_NL2SQL_PROFILE",
                "action"       : "runsql"
            }
        }'
    );
END;
/

BEGIN
    DBMS_CLOUD_AI_AGENT.CREATE_TOOL(
        tool_name  => 'ACME_RAG_TOOL',
        attributes => '{
            "tool_type"   : "RAG",
            "tool_params" : {
                "profile_name" : "ACME_RAG_PROFILE"
            }
        }'
    );
END;
/

Set action to runsql explicitly on the SQL tool. That is what makes the generated SQL actually execute against your tables rather than merely being produced.

profile_name belongs inside tool_params, not at the top level:

-- WRONG
attributes => '{"tool_type":"SQL", "profile_name":"ACME_NL2SQL_PROFILE"}'

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

Get it wrong and you find out immediately — ORA-20052: Invalid tool attribute - profile_name, and no tool is created. Worth knowing that it fails loudly, because the JSON shape here is easy to get wrong and it would be a reasonable thing to go hunting for later as a silent misconfiguration. It isn't one — if the tool exists, the profile is wired to it.

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;
/

Shown with two tools to keep it readable — the script lists all six, and adding the custom ones later is an edit to this array and nothing else.

The tools array is optional in the API, and this is the one I'd 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 is the routing brain — the object that decides which tool answers which kind of question. It is one attribute, and how specific you make it is the whole game:

BEGIN
    DBMS_CLOUD_AI_AGENT.CREATE_TASK(
        task_name   => 'ACME_ANALYST_TASK',
        attributes  => '{
            "instruction" : "Answer ACME Corp financial questions using only data
               returned by tools. Route as follows: for direct data lookups
               (totals, balances, period data, counts) use SQL tool; for policy,
               procedure or compliance questions use RAG tool; 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; for advanced statistical analysis such as moving
               averages, standard deviation or peak period identification use
               PYTHON tool. If a tool returns no data say so clearly.
               Never invent figures."
        }',
        description => 'ACME Corp financial analysis with 6-tool routing'
    );
END;
/

Vague instructions route everything to the SQL tool. Name the tool for each class of question and say what kind of question it is, not just what the tool does.

The last two sentences are doing real work, and they are the reason this is worth writing carefully. "If a tool returns no data say so clearly" and "never invent figures" are the difference between a system that admits a gap and one that fills it with something plausible.

The team is the thing you actually call. It binds an agent to a task:

BEGIN
    DBMS_CLOUD_AI_AGENT.CREATE_TEAM(
        team_name   => 'ACME_ANALYST_TEAM',
        attributes  => '{
            "agents"  : [{"name": "ACME_ANALYST", "task": "ACME_ANALYST_TASK"}],
            "process" : "sequential"
        }',
        description => 'ACME Corp AI Assistant Team'
    );
END;
/

Note that the agent and the task are joined here, not on the agent. An agent does not own its task, which is what lets the same agent run different tasks.

Tearing it down

Drop the vector index before the profile it references, or the profile drop fails while the index still points at it.

BEGIN DBMS_CLOUD_AI_AGENT.DROP_TEAM('ACME_ANALYST_TEAM');
EXCEPTION WHEN OTHERS THEN NULL; END;
/

You will find advice — including some of mine, elsewhere — that CREATE_TEAM leaves behind an internal AGENT$<team_name> profile which DROP_TEAM won't clean up, blocking the next team of the same name. On 23.26.3.2.0 I could not reproduce it: no such profile exists after CREATE_TEAM, after RUN_TEAM, or after DROP_TEAM, and recreating the team under the same name works. The cleanup script drops that profile defensively anyway, which costs nothing if your version behaves differently.

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's 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.

What working actually looks like

Worth knowing what to expect, because plausible and correct are hard to tell apart at a glance. Against the dataset in the repo — five departments, nine periods, three deliberately planted anomalies:

Ask: What were Engineering expenses in August 2025?

Answer: approximately $176,000 — well above Engineering's monthly average, driven by a cloud migration project ($95,000) and architecture consulting ($40,000).

That figure is the one to check first, because it is also the largest single spike in the data. If you get a smooth, round, average-looking number instead, the SQL tool generated a query and never ran it — go back to action and tool_params.

Then confirm the tool actually fired, rather than trusting the prose:

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;

An empty result with a confident answer above it is the single most useful signal in this stack. It means the model answered from training data and no tool ran at all.

A few more, each landing on a different tool:

Ask	Should call
What does the reconciliation policy require?	RAG
Show the expense trend for Engineering	TREND
Forecast Sales expenses for the next 3 periods	FORECAST
Any unusual spending in our GL data?	ANOMALY
Give me moving averages and standard deviation	PYTHON

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

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