Chapter 3: A Select AI Agent Team in PL/SQL

The building blocks

Select AI Agent is a set of database objects created with DBMS_CLOUD_AI_AGENT. I think of them as a small analytics department:

• Tool: a skill the department has. A tool is something an agent can call: a built-in type such as SQL (NL2SQL through a Select AI profile), RAG or web search, or my own PL/SQL function. Its instruction tells the agent when the tool is useful. Here it is one SQL tool over the sales tables.
• Agent: the analyst. An agent has a role (who it is, what it may and may not do) and its own Select AI profile, which defines the LLM it reasons with. The agent decides how to solve a request: which tool to call, with what input, and when it has enough to answer.
• Task: the assignment brief. A task says what has to be done: its instruction contains a {query} placeholder for the user's question and the expected shape of the answer, and it lists the tools the agent may use for it.
• Team: the department. A team pairs agents with tasks and defines the process (here sequential). The team is the object I actually run.
• Conversation: the case file. It keeps the context of a run, so follow-up questions in the same conversation can build on earlier ones.

The SQL tool brings its own Select AI profile, which is not necessarily the agent's: the agent's profile is for reasoning, the tool's profile for generating SQL over the objects in its object_list. In my setup both are OABOOTCAMP_AI.

How they work together

When I run the team with a question:

1. RUN_TEAM starts the team in a conversation and hands the question to the first agent–task pair.
2. The task instruction is filled in ({query} becomes the user's question) and given to the agent together with its role.
3. The agent plans, then calls an allowed tool. The SQL tool turns its input into SQL with Select AI, runs it and returns the result.
4. The agent looks at the result and either calls a tool again or decides it has enough.
5. The agent writes the final answer in the form the task asks for, and the team returns it.

RUN_TEAM(question) ──► Team ──► Agent + Task ──► Tool (SQL) ──► Select AI ──► tables
                                     ▲                │
                                     └──── result ────┘   (repeat until done)

Steps 3 and 4 are the difference from plain Select AI: the agent can split one question into several tool calls and combine the results.

Each piece carries different knowledge, so it helps to decide up front where each rule lives:

All blocks below run as OABOOTCAMP. I use q'[...]' quoting so single quotes inside instructions need no escaping.

Tool

A built-in tool of type SQL, bound to the OABOOTCAMP_AI profile:

BEGIN
  DBMS_CLOUD_AI_AGENT.CREATE_TOOL(
    tool_name  => 'OABOOTCAMP_SALES_SQL',
    attributes => q'[{
      "tool_type": "SQL",
      "tool_params": {"profile_name": "OABOOTCAMP_AI"},
      "instruction": "Use this tool for any question about sales, revenue, profit, discounts, units, orders, customers, products, geography or time periods. Use runsql to answer questions with data, showsql when the user asks to see the query, and explainsql when the user asks how the query works."
    }]',
    description => 'NL2SQL over OABOOTCAMP sales star schema'
  );
END;
/

Agent

BEGIN
  DBMS_CLOUD_AI_AGENT.CREATE_AGENT(
    agent_name => 'SALES_ANALYST_AGENT',
    attributes => q'[{
      "profile_name": "OABOOTCAMP_AI",
      "role": "You are a sales analyst for a consumer electronics company. You answer business questions using data from the sales database only, never from general knowledge. Revenue is gross unless the user asks for net revenue (revenue minus discount). Profit is revenue minus fixed and variable cost. Include all order statuses unless the user explicitly asks about a specific status. Sales data covers January 2024 to September 2026.",
      "enable_human_tool": "false"
    }]',
    description => 'Sales analyst over OABOOTCAMP data'
  );
END;
/

enable_human_tool is false because OPAF will call the agent non-interactively; the agent must make reasonable assumptions instead of pausing for clarification.

Task

BEGIN
  DBMS_CLOUD_AI_AGENT.CREATE_TASK(
    task_name  => 'SALES_ANALYSIS_TASK',
    attributes => q'[{
      "instruction": "Answer the following business question: {query}. Use the OABOOTCAMP_SALES_SQL tool with the runsql action to retrieve data. Prefer a single query that returns all needed numbers at once (for example grouped by year and channel) instead of several separate queries. Report only numbers exactly as returned by the tool; never estimate or invent values. You may compute simple differences between returned numbers. Return a concise answer with the key numbers formatted with thousand separators, followed by one or two sentences of interpretation. State any assumption about time period or metric definition.",
      "tools": ["OABOOTCAMP_SALES_SQL"],
      "enable_human_tool": "false"
    }]',
    description => 'Answer sales questions with data'
  );
END;
/

Two instructions keep answers repeatable: one grouped query instead of several separate ones, and reporting only numbers returned by the tool.

Team

BEGIN
  DBMS_CLOUD_AI_AGENT.CREATE_TEAM(
    team_name  => 'OABOOTCAMP_SALES_TEAM',
    attributes => q'[{
      "agents": [{"name": "SALES_ANALYST_AGENT", "task": "SALES_ANALYSIS_TASK"}],
      "process": "sequential"
    }]',
    description => 'In-database sales analyst team'
  );
END;
/

All four objects should show ENABLED:

SELECT 'TOOL'  AS type, tool_name  AS name, status FROM user_ai_agent_tools
UNION ALL
SELECT 'AGENT', agent_name, status FROM user_ai_agents
UNION ALL
SELECT 'TASK',  task_name,  status FROM user_ai_agent_tasks
UNION ALL
SELECT 'TEAM',  agent_team_name, status FROM user_ai_agent_teams;

Running the team

RUN_TEAM needs a real conversation ID from DBMS_CLOUD_AI.CREATE_CONVERSATION. To check that answers are repeatable, I run the same question three times, each in a fresh conversation:

SET SERVEROUTPUT ON
DECLARE
  l_conv   VARCHAR2(100);
  l_answer CLOB;
BEGIN
  FOR i IN 1 .. 3 LOOP
    l_conv := DBMS_CLOUD_AI.CREATE_CONVERSATION();

    l_answer := DBMS_CLOUD_AI_AGENT.RUN_TEAM(
      team_name   => 'OABOOTCAMP_SALES_TEAM',
      user_prompt => 'Compare profit in 2025 vs 2024 by sales channel and tell me which channel improved the most',
      params      => '{"conversation_id": "' || l_conv || '"}'
    );

    DBMS_OUTPUT.PUT_LINE('===== RUN ' || i || ' =====');
    DBMS_OUTPUT.PUT_LINE(DBMS_LOB.SUBSTR(l_answer, 4000, 1));
  END LOOP;
END;
/

All three runs returned the same numbers. One of them:

• Online: 2024 = $1,438,821.21, 2025 = $2,099,559.05, Increase = $660,738
• Store: 2024 = $2,223,219.07, 2025 = $3,498,467.01, Increase = $1,275,248
• Catalog: 2024 = $699,472.58, 2025 = $1,360,396.31, Increase = $660,924

All channels saw higher profit in 2025, but the Store channel improved the most, with an increase of about $1.28 million. (Profit is calculated as revenue − fixed cost − variable cost, including all order statuses.)

The agent planned the comparison, retrieved profit for both years per channel, computed the deltas and picked the winner. This is the kind of question a single SELECT AI call struggles with.

Checking against ground truth

Before I trust an agent in a demo, I compare its answer with hand-written SQL that involves no LLM:

SELECT f.channel_name,
       t.cal_year,
       ROUND(SUM(f.revenue))                                  AS revenue,
       ROUND(SUM(f.cost_fixed))                               AS cost_fixed,
       ROUND(SUM(f.cost_variable))                            AS cost_variable,
       ROUND(SUM(f.revenue - f.cost_fixed - f.cost_variable)) AS profit
FROM   f_revenue f
JOIN   d_time t ON f.time_bill_dt = t.day_dt
WHERE  t.cal_year IN (2024, 2025)
GROUP  BY f.channel_name, t.cal_year
ORDER  BY f.channel_name, t.cal_year;

The agent's answers match exactly. These numbers are the ground truth for every flow in the following chapters.

What made it repeatable

A business rule that lives only in comments and role text may be applied in one run and skipped in the next. Three things made the team return the same answer every time:

• rules stated explicitly and positively ("include all order statuses"),
• temperature 0 in the profile,
• a task instruction asking for one grouped query and only numbers returned by the tool.

The team works in the database. In Chapter 4 I put it, and plain Select AI, in front of users with OPAF's native Select AI nodes.

Back to the introduction and list of chapters.