Examples of Using Select AI Agent
Explore examples that show how to build, configure, and interact with Select AI Agent for common tasks such as movie analysis, log analysis and customer support.
Example: Create an Agent
Create an agent to perform a defined task.
Before You Begin
Review:
Example: Create an Agent
This example creates an agent named Customer_Return_Agent responsible for handling product return-related conversations.
This example illustrates using Google as the AI provider as specified in the AI profile named GOOGLE. The AI profile identifies the LLM the agent uses for reasoning and responses. The role attribute provides instructions to guide the agent.
BEGIN
DBMS_CLOUD_AI_AGENT.CREATE_AGENT(
agent_name => 'CustomerAgent',
attributes =>'{
"profile_name": "GOOGLE",
"role": "You are an experienced customer agent who deals with customers return request."
}'
);
END;
/ Note
Note: Each agent in a multi-agent team can have a distinct AI profile and each profile may use a different AI provider and/or LLM.
Example: Create Built-In Tools
Create built-in tools such as SQL, RAG, Websearch, Email, and Slack. These tools enable agents and tasks to query data, retrieve knowledge, search the web, and send notifications.
Before You Begin
Review:
Select AI Agent accepts the following types of tools:
-
SQL -
RAG -
WEBSEARCH -
NOTIFICATION-
EMAIL -
SLACK
-
Note
Note: Built-in tools (for example, SQL and RAG) include internal instructions. You can optionally append user-provided instructions to tailor behavior for your use case. Appending an instruction can add task-specific context (for example, scope queries to a specific schema or help enforce a response format). This can improve relevance for specialized scenarios. Poorly written or overly prescriptive instructions can degrade tool performance. Keep instructions short, specific, and consistent with the tool’s purpose.
Example: SQL Tool
This example creates a SQL tool that translates natural language queries into SQL statements. SQL tool helps the agents answer data-related questions by mapping prompts into SQL queries.
This example shows using OCI as the AI provider as specified in the AI profile named nl2sql_profile. The AI profile identifies the LLM the agent uses for reasoning and responses. In this example, the AI profile nl2sql_profile defines the set of SH schema tables that the agent can query, enabling natural language access to commonly used sales history data such as customers, products, promotions, and countries. The SQL tool uses the database objects that are specified in the AI profile, ensuring that generated SQL statements remain accurate and relevant to the SH data set.
BEGIN
DBMS_CLOUD_AI.CREATE_PROFILE(
profile_name => 'nl2sql_profile',
attributes => '{"provider": "oci",
"credential_name": "GEN1_CRED",
"oci_compartment_id": "ocid
1.compartment.oc1..aaaa...",
object_list": [{"owner": "SH", "name": "customers"},
{"owner": "SH", "name": "countries"},
{"owner": "SH", "name": "supplementary_demographics"},
{"owner": "SH", "name": "profits"},
{"owner": "SH", "name": "promotions"},
{"owner": "SH", "name": "products"}]
}');
end;
/
EXEC DBMS_CLOUD_AI_AGENT.DROP_TOOL('SQL');
BEGIN
DBMS_CLOUD_AI_AGENT.CREATE_TOOL(
tool_name => 'SQL',
attributes => '{"tool_type": "SQL",
"tool_params": {"profile_name": "nl2sql_profile"}}'
);
END;
/Example: RAG Tool
This example creates a RAG (Retrieval Augmented Generation) tool. The RAG tool lets agents retrieve and ground responses in enterprise documents, improving accuracy for knowledge-based answers.
This example illustrates defining a RAG_PROFILE with credentials, a vector index, and specifying profile parameters. Then, creating a vector index RAG_INDEX in Object Storage for document embeddings and creating the RAG_TOOL linked to your profile.
BEGIN
DBMS_CLOUD_AI.CREATE_PROFILE(
profile_name =>'RAG_PROFILE',
attributes =>'{"provider": "oci",
"credential_name": "GENAI_CRED",
"vector_index_name": "RAG_INDEX",
"oci_compartment_id": "ocid1.compartment.oc1..aaaa..",
"temperature": 0.2,
"max_tokens": 3000
}');
END;
/
BEGIN
DBMS_CLOUD_AI.CREATE_VECTOR_INDEX(
index_name => 'RAG_INDEX',
attributes => '{"vector_db_provider": "oracle",
"location": "https://swiftobjectstorage.us-phoenix-1.oraclecloud.com/v1/my_namespace/my_bucket/my_data_folder",
"object_storage_credential_name": "OCI_CRED",
"profile_name": "RAG_PROFILE",
"chunk_overlap":128,
"chunk_size":1024
}');
END;
/
EXEC DBMS_CLOUD_AI_AGENT.DROP_TOOL('RAG_TOOL');
BEGIN
DBMS_CLOUD_AI_AGENT.CREATE_TOOL(
tool_name => 'RAG_TOOL',
attributes => '{"tool_type": "RAG",
"tool_params": {"profile_name": "RAG_PROFILE"}}'
);
END;
/Example: Websearch Tool
This example creates a Websearch tool for retrieving details from the internet. The Websearch tool enables agents to look up information from the web, such as product prices or descriptions.
This example illustrates adding an ACL entry for the OpenAI provider. Creating credentials OPENAI_CRED with your API key and creating the Websearch tool, describing its purpose, linking it to the credential.
Note
Note: OpenAI is the only Websearch AI provider supported.
BEGIN
DBMS_NETWORK_ACL_ADMIN.APPEND_HOST_ACE(
host => 'api.openai.com',
ace => xs$ace_type(privilege_list => xs$name_list('http'),
principal_name => 'ADB_USER',
principal_type => xs_acl.ptype_db)
);
END;
/
BEGIN
DBMS_CLOUD.CREATE_CREDENTIAL(
credential_name => 'OPENAI_CRED',
username => 'OPENAI',
password => '<`OPENAI_API_KEY`>'
);
END;
/
EXEC DBMS_CLOUD_AI_AGENT.DROP_TOOL('Websearch');
BEGIN
DBMS_CLOUD_AI_AGENT.create_tool(
tool_name => 'Websearch',
attributes => '{"instruction": "This tool can be used for searching the details about topics mentioned in notes and prepare a summary about prices, details on web",
"tool_type": "WEBSEARCH",
"tool_params": {"credential_name": "OPENAI_CRED"}}'
);
END;
/Example: Notification Tool with Email Type
This example creates an Email notification tool. The Email tool enables agents to send notification emails as part of their workflow.
This example illustrates creating credentials EMAIL_CRED with your password, allowing SMTP access for the database user, and creating a notification tool with type EMAIL, including SMTP details, sender, and recipient addresses.
Note
Note: You must generate SMTP credentials in Oracle Cloud Infrastructure before creating credential in Autonomous AI Database. See Generate SMTP Credentials for a User for more information.
BEGIN
DBMS_CLOUD.CREATE_CREDENTIAL(
credential_name => 'EMAIL_CRED',
username => '<SMTP username>',
password => '<SMTP password>');
END;
/
-- Allow SMTP access for user
BEGIN
DBMS_NETWORK_ACL_ADMIN.APPEND_HOST_ACE(
host => 'smtp.email.us-ashburn-
1.oci.oraclecloud.com',
lower_port => 587,
upper_port => 587,
ace => xs$ace_type(privilege_list => xs$name_list('SMTP'),
principal_name => 'ADB_USER',
principal_type => xs_acl.ptype_db));
END;
/
EXEC DBMS_CLOUD_AI_AGENT.DROP_TOOL('Email');
BEGIN
DBMS_CLOUD_AI_AGENT.CREATE_TOOL(
tool_name => 'EMAIL',
attributes => '{"tool_type": "NOTIFICATION",
"tool_params": {"notification_type" : "EMAIL",
"credential_name": "EMAIL_CRED",
"recipient": "example_recipient@oracle.com",
"smtp_host": "smtp.email.us-ashburn-1.oci.oraclecloud.com",
"sender": "example_sender@oracle.com"}}'
);
END;
/Example: Notification Tool with Slack Type
This example creates a Slack notification tool. The Slack tool enables agents to deliver notifications directly to a Slack workspace channel.
This example illustrates adding an ACL entry for Slack and creating a notification tool with type SLACK linking it to SLACK_CRED and the target channel.
BEGIN
DBMS_NETWORK_ACL_ADMIN.APPEND_HOST_ACE (
host => 'slack.com',
lower_port => 443,
upper_port => 443,
ace => xs$ace_type(
privilege_list => xs$name_list('http'),
principal_name => 'ADB_USER',
principal_type => xs_acl.ptype_db));
END;
/
BEGIN
DBMS_CLOUD_AI_AGENT.create_tool(
tool_name => 'slack',
attributes => '{"tool_type": "SLACK",
"tool_params": {"credential_name": "SLACK_CRED",
"channel": "<channel_number>"}}'
);
END;
/Example: Describe and Run a Select AI Agent Tool
Use tool APIs to describe a tool and run a tool by name. DESCRIBE_TOOL returns the tool description, attributes, and argument schema. RUN_TOOL calls the tool with a JSON input payload and returns the tool result as JSON.
Before You Begin
Review:
- Perform Prerequisites for Select AI
- Prerequisites for Using Select AI Agent
- DESCRIBE_TOOL Function
- RUN_TOOL Function
- CREATE_TOOL Procedure
This example creates sample log data, registers a PL/SQL function as a Select AI Agent tool, demonstrates how to inspect the tool schema before using it, and calls the tool directly from SQL or PL/SQL by using JSON input. The same APIs can be used by MCP-compatible integrations to expose tool discovery and tool call capabilities.
If no Select AI Agent tools have been created, tool discovery and tool call APIs return an empty result. Create one or more tools by using DBMS_CLOUD_AI_AGENT.CREATE_TOOL to make tools available for discovery and calling.
DECLARE
l_result CLOB;
BEGIN
l_result := DBMS_CLOUD_AI_AGENT.DESCRIBE_TOOL(
tool_name => 'FETCH_LOG_TOOL'
);
DBMS_OUTPUT.PUT_LINE(l_result);
END;
/
SELECT DBMS_CLOUD_AI_AGENT.RUN_TOOL(
tool_name => 'FETCH_LOG_TOOL',
input => '{"P_SINCE":"2026-01-01"}'
) AS tool_result
FROM dual;The following example shows how to create a tool and use DESCRIBE_TOOL and RUN_TOOL.
BEGIN
EXECUTE IMMEDIATE 'DROP TABLE app_log_messages PURGE';
EXCEPTION
WHEN OTHERS THEN
IF SQLCODE != -942 THEN
RAISE;
END IF;
END;
/
CREATE TABLE app_log_messages (
log_timestamp DATE,
severity VARCHAR2(10),
message VARCHAR2(4000)
);
INSERT INTO app_log_messages VALUES (DATE '2026-01-01', 'INFO', 'Routine performance check completed.');
INSERT INTO app_log_messages VALUES (DATE '2026-01-03', 'WARN', 'Query response time exceeded the review threshold.');
COMMIT;
CREATE OR REPLACE FUNCTION fetch_logs (
p_since IN DATE
) RETURN CLOB IS
l_logs CLOB;
BEGIN
DBMS_LOB.CREATETEMPORARY(l_logs, TRUE);
FOR rec IN (
SELECT TO_CHAR(log_timestamp, 'YYYY-MM-DD') || ' ' ||
severity || ': ' || message AS line
FROM app_log_messages
WHERE log_timestamp >= p_since
ORDER BY log_timestamp
) LOOP
DBMS_LOB.WRITEAPPEND(l_logs, LENGTH(rec.line) + 1, rec.line || CHR(10));
END LOOP;
RETURN l_logs;
END;
/
BEGIN
DBMS_CLOUD_AI_AGENT.CREATE_TOOL(
tool_name => 'FETCH_LOG_TOOL',
attributes => '{"instruction":"Returns log messages on or after the requested date.","function":"FETCH_LOGS"}',
description => 'Fetch application log messages by date.'
);
END;
/
DECLARE
l_result CLOB;
BEGIN
l_result := DBMS_CLOUD_AI_AGENT.DESCRIBE_TOOL(
tool_name => 'FETCH_LOG_TOOL'
);
DBMS_OUTPUT.PUT_LINE(l_result);
END;
/
SELECT DBMS_CLOUD_AI_AGENT.RUN_TOOL(
tool_name => 'FETCH_LOG_TOOL',
input => '{"P_SINCE":"2026-01-01"}'
) AS tool_result
FROM dual;Example: Create a Task
Create a task an agent can perform.
Before You Begin
Review:
Note
Note: Only a DBA can grant EXECUTE privileges and network ACL procedure.
Example: Create an Email Task
This example creates Generate_Email_Task that instructs the LLM to produce a standard confirmation email using structured data.
The following example illustrates using the instruction attribute and providing instructions on what the email should include.
BEGIN DBMS_CLOUD_AI_AGENT.DROP_TASK('Generate_Email_Task');
EXCEPTION WHEN OTHERS THEN NULL; END;
/
BEGIN
DBMS_CLOUD_AI_AGENT.CREATE_TASK(
task_name => 'Generate_Email_Task',
attributes => '{"instruction": "Use the customer information and product details to generate an email in a professional format. The email should:' \|\|
'
1. Include a greeting to the customer by name' \|\|
'2. Specify the item being returned, the order number, and the reason for the return' \|\|
'3. If it is a refund, state the refund will be issued to the credit card on record.' \|\|
'4. If it is a replacement, state that the replacement will be shipped in 3-5 business days."}'
);
END;Example: Create a Fetch Log Task
This example creates a FETCH_LOGS_TASK that retrieves logs based on the request.
This example illustrates creating a task that uses the log_fetcher tool to retrieve logs. This custom tool specifies the PL/SQL procedure fetch_logs. The task instruction specifies what the task needs accomplish. The attribute enable_human_tool is set to true so a person can step in to guide or approve the task flow if needed. See Example: Fetch and Analyze Log Reports for a complete example.
BEGIN
DBMS_CLOUD_AI_AGENT.CREATE_TASK(
task_name =>'FETCH_LOGS_TASK',
attributes =>'{
"instruction": "Fetch the log entries from the database based on user request: {query}",
"tools": ["log_fetcher"],
"enable_human_tool" : "true"
}'
);
END;
/Example: Create a Log Analysis Task
This example creates a ANALYZE_LOG_TASK that analyzes the fetched logs.
The following example illustrates using FETCH_LOGS_TASK as the input attribute. The task named ANALYZE_LOG_TASK starts after logs have been gathered to have FETCH_LOGS_TASK output as an input. The task instruction specifies what the task needs accomplish. The attribute enable_human_tool is set to false indicating the absence of human-in-the-loop review.
BEGIN
DBMS_CLOUD_AI_AGENT.CREATE_TASK(
task_name =>'ANALYZE_LOG_TASK',
attributes =>'{"instruction": "Analyze the fetched log entries retrieved based on user request: {query} ' ||
'Generate a detailed report include issue analysis and possible solution.",
"input" : "FETCH_LOGS_TASK",
"enable_human_tool" : "false"
}'
);
END;
/Example: Create an Agent Team
Create agent teams to accomplish your tasks.
Before You Begin
Review:
Example: Create an Agent Team
This example creates the ReturnAgency team and includes a single agent Customer_Return_Agent. The task Return_And_Price_Match is assigned to the agent. This task manages return requests by asking for the reason and updating the order status in your database. The process is set to sequential to run the tasks in a defined order.
BEGIN
DBMS_CLOUD_AI_AGENT.CREATE_TEAM(
team_name => 'ReturnAgency',
attributes => '{"agents": [{"name":"Customer_Return_Agent","task" : "Return_And_Price_Match"}],
"process": "sequential"}');
END;
/See Example: Create a Product Return Agent for a complete example.
Example: Create Multi-Agent Team
This example creates the Ops_Issues_Solution_Team team and includes two agents to handle log analysis and troubleshooting: Data_Engineer and Ops_Manager. The Data_Engineer agent runs the task fetch_logs_task and Ops_Manager agent runs the task analyze_log_task. The process is set to sequential to run the tasks in a defined order.
BEGIN
DBMS_CLOUD_AI_AGENT.create_team(
team_name => 'Ops_Issues_Solution_Team',
attributes => '{"agents": [{"name":"Data_Engineer","task" : "fetch_logs_task"},
{"name":"Ops_Manager","task" : "analyze_log_task"}],
"process": "sequential"
}');
END;
/See Example: Fetch and Analyze Log Reports for a complete example.
Example: Discover an Agent Team and Check Team State
Use team discovery APIs to list available teams, describe a selected team, and retrieve the latest run state for a team conversation. Team descriptions include the skills that the teams expose. These skills represent the underlying Select AI Agent tools and are not separate database objects.
Before You Begin
Review:
- Perform Prerequisites for Select AI
- Prerequisites for Using Select AI Agent
- CREATE_TEAM Procedure
- LIST_TEAMS Function
- GET_TEAM_STATE Function
This example lists available agent teams, describes the capabilities exposed by a selected team, and retrieves the latest run state for a conversation. These APIs can also support Agent2Agent-compatible integrations that discover published agent teams and inspect the skills they expose.
If no Select AI agent teams have been created, agent team discovery and run state APIs return an empty result. Create one or more agent teams by using DBMS_CLOUD_AI_AGENT.CREATE_TEAM to enable team discovery and execution.
For Agent2Agent delegation, use LIST_TEAMS to return teams that the calling user owns and that contain exactly one agent. Before selecting a team, use DESCRIBE_TEAM to review its capabilities.
Use the same conversation ID that was passed to RUN_TEAM when you need the run state for a specific conversation. For Agent2Agent integration, use one conversation identifier for the team run and the subsequent state lookup. The application can discover a team, inspect its skills, delegate work, and then poll the execution state.
SELECT DBMS_CLOUD_AI_AGENT.LIST_TEAMS() AS teams
FROM dual;
SELECT DBMS_CLOUD_AI_AGENT.DESCRIBE_TEAM(
team_name => 'CUSTOMER_AGENT_ONLY_TEAM'
) AS team_description
FROM dual;
SELECT DBMS_CLOUD_AI_AGENT.GET_TEAM_STATE(
team_name => 'CUSTOMER_AGENT_ONLY_TEAM',
params => '{"conversation_id":"<CONVERSATION_ID>"}'
) AS team_state
FROM dual;For Agent2Agent integration, use one conversation identifier for the team run and the subsequent state lookup. The application can discover a team, inspect its skills, delegate work, and then poll the execution state.
DECLARE
l_conversation_id VARCHAR2(36);
l_response CLOB;
l_state VARCHAR2(30);
BEGIN
l_conversation_id := DBMS_CLOUD_AI.CREATE_CONVERSATION();
l_response := DBMS_CLOUD_AI_AGENT.RUN_TEAM(
team_name => 'CUSTOMER_AGENT_ONLY_TEAM',
user_prompt => 'I need help returning a damaged item.',
params => '{"conversation_id":"' || l_conversation_id || '"}'
);
l_state := DBMS_CLOUD_AI_AGENT.GET_TEAM_STATE(
team_name => 'CUSTOMER_AGENT_ONLY_TEAM',
params => '{"conversation_id":"' || l_conversation_id || '"}'
);
DBMS_OUTPUT.PUT_LINE(l_response);
DBMS_OUTPUT.PUT_LINE('Current state: ' || l_state);
END;
/The state result reports the latest run state for the conversation, such as RUNNING, WAITING_FOR_HUMAN, SUCCEEDED, or FAILED.
Example: Create an Agent That Calls Tools Directly
Agent-direct tool calls support open-ended interactive sessions. In this mode, the team entry contains the agent name and does not assign a Select AI Agent task to the agent. The application or user controls session completion by calling SET_EXECUTION_STATE.
Before You Begin
Review:
This example creates an agent that calls configured tools directly without using task-based execution. It demonstrates an agent-direct session in which the application determines when the interaction is complete. Use task-based workflows when the workflow should follow predefined task instructions and completion criteria. Use agent-direct run when the application must keep the conversation open and determine when the session ends.
Agent-direct tool call APIs return an empty result if no agents with associated tools have been created. Create an agent and associate one or more tools with it before calling agent-direct tool calls.
BEGIN
DBMS_CLOUD_AI_AGENT.CREATE_AGENT(
agent_name => 'CUSTOMER_AGENT_ONLY',
attributes => '{
"profile_name": "<AI_PROFILE_NAME>",
"role": "You help customers with return requests. Ask for the return reason, collect the order number, confirm the requested action, and call EMAIL_TOOL only after the customer confirms the action.",
"enable_human_tool": "true",
"short_term_memory_length": 4,
"tools": ["EMAIL_TOOL"]
}'
);
END;
/
BEGIN
DBMS_CLOUD_AI_AGENT.CREATE_TEAM(
team_name => 'CUSTOMER_AGENT_ONLY_TEAM',
attributes => '{"agents":[{"name":"CUSTOMER_AGENT_ONLY"}],
"process":"sequential"}'
);
END;
/
DECLARE
l_response CLOB;
l_conversation_id VARCHAR2(128);
BEGIN
l_conversation_id := DBMS_CLOUD_AI.CREATE_CONVERSATION();
l_response := DBMS_CLOUD_AI_AGENT.RUN_TEAM(
team_name => 'CUSTOMER_AGENT_ONLY_TEAM',
user_prompt => 'I need help returning order 7820.',
params => '{"conversation_id":"' || l_conversation_id || '"}'
);
DBMS_OUTPUT.PUT_LINE(l_response);
END;
/
BEGIN
DBMS_CLOUD_AI_AGENT.SET_EXECUTION_STATE(
team_name => 'CUSTOMER_AGENT_ONLY_TEAM',
state => 'SUCCEEDED',
params => '{"conversation_id":"<CONVERSATION_ID>"}'
);
END;
/Example: Configure Short-Term and Long-Term Memory Length
Memory length controls how much prior context is available when an agent calls the LLM. short_term_memory_length controls recent conversation turns included in each LLM call for the current run. long_term_memory_length controls prior task-level summaries included when a new run starts.
| Memory Type | Attribute | Scope | Default |
|---|---|---|---|
| Short-term memory | short_term_memory_length |
Configured on an agent. | 30 |
| Long-term memory | long_term_memory_length |
Configured on a team. | 30 |
Conversation memory uses a default memory depth of 30 conversation turns. Use the default value for most workloads. Increase the memory depth when the agent needs additional conversational context across longer interactions, or decrease it to reduce context processing for shorter, task-oriented conversations.
Before You Begin
Review:
- Perform Prerequisites for Select AI
- Prerequisites for Using Select AI Agent
- CREATE_AGENT Procedure
- CREATE_TEAM Procedure
This example configures short-term memory for an agent and long-term memory for a team, showing how to control the amount of conversational context available to the LLM for different workload requirements.
BEGIN
DBMS_CLOUD_AI_AGENT.CREATE_AGENT(
agent_name => 'MEMORY_AWARE_RETURN_AGENT',
attributes => '{
"profile_name": "<AI_PROFILE_NAME>",
"short_term_memory_length": 5,
"role": "You help customers with product return requests and keep the current conversation context concise."
}'
);
END;
/
BEGIN
DBMS_CLOUD_AI_AGENT.CREATE_TEAM(
team_name => 'MEMORY_AWARE_RETURN_TEAM',
attributes => '{"agents":[{"name":"MEMORY_AWARE_RETURN_AGENT","task":"RETURN_REQUEST_TASK"}],
"long_term_memory_length": 5,
"process":"sequential"}'
);
END;
/Example: Create a Movie Analysis Agent with Built-In Tools
This example shows how you can create a movie analysis agent using Select AI Agent. In this example, you set up a movie analysis agent that retrieves data, answers questions, searches the web, and emails the analysis or sends Slack notifications.
Before You Begin
Review:
The following example assumes that the data is available to you.
This example creates a MOVIE_ANALYST agent and uses multiple built-in tools such as SQL, RAG, WEBSEARCH, and NOTIFICATION tool of type: EMAIL and SLACK. The agent answers movie related questions using natural language prompts.
In this example, after your DBA grants EXECUTE privileges for: the DBMS_CLOUD_AI_AGENT, DBMS_CLOUD_AI, DBMS_CLOUD_PIPELINE packages, ACL access for your AI provider, SMTP access, and Slack access, you begin by creating the MOVIE_ANALYST agent.
Create an Agent
You create an agent called MOVIE_ANALYST with a profile (GROK) and role of answering questions about movies, actors, and genres.
Create Task
You create a task called ANALYZE_MOVIE_TASK with instructions to answer movie-related queries.
Create Team
You create a MOVIE_AGENT_TEAM team with MOVIE_ANALYST as the agent and the task as ANALYZE_MOVIE_TASK and set it as an active team.
You can later attach tools such as SQL, RAG, Websearch, or Notification to extend its capabilities.
Run the Select AI Agent Team
You now run the agent team by using select ai agent as a prefix to your prompts.
Create Tools
You then attach different built-in agent tools:
-
SQL: Uses an NL2SQL profile to translate questions into SQL queries and other supported Select AI actions. -
RAG: Retrieve knowledge-based context from stored documents. -
WEBSEARCH: Gather movie details or prices online. -
NOTIFICATION:-
EMAIL: Send answers to user prompts, movie reports, or reviews to a recipient. -
SLACK: Send answers to user prompts, summaries, or updates directly to Slack.
-
The complete example is as follows:
--Grants EXECUTE privilege to ADB_USER
--
GRANT EXECUTE on DBMS_CLOUD_AI_AGENT to ADB_USER;
GRANT EXECUTE on DBMS_CLOUD_AI to ADB_USER;
GRANT EXECUTE on DBMS_CLOUD_PIPELINE to ADB_USER;
-- Websearch tool accesses OPENAI endpoint, allow ACL access
BEGIN
DBMS_NETWORK_ACL_ADMIN.APPEND_HOST_ACE(
host => 'api.openai.com',
ace => xs$ace_type(privilege_list => xs$name_list('http'),
principal_name => 'ADB_USER',
principal_type => xs_acl.ptype_db)
);
END;
/
-- To allow Email tool in Autonomous AI Database, allow SMTP access
BEGIN
DBMS_NETWORK_ACL_ADMIN.APPEND_HOST_ACE(
host => 'smtp.email.us-ashburn-1.oci.oraclecloud.com',
lower_port => 587,
upper_port => 587,
ace => xs$ace_type(privilege_list => xs$name_list('SMTP'),
principal_name => 'ADB_USER,
principal_type => xs_acl.ptype_db));
END;
/
-- Allow ACL access to Slack
BEGIN
DBMS_NETWORK_ACL_ADMIN.APPEND_HOST_ACE (
host => 'slack.com',
lower_port => 443,
upper_port => 443,
ace => xs$ace_type(
privilege_list => xs$name_list('http'),
principal_name => 'ADB_USER',
principal_type => xs_acl.ptype_db));
END;
/
PL/SQL procedure successfully completed.
--Create an agent
BEGIN
DBMS_CLOUD_AI_AGENT.CREATE_AGENT(
agent_name => 'MOVIE_ANALYST',
attributes => '{"profile_name": "GROK",
"role": "You are an AI Movie Analyst. Your can help answer a variety of questions related to movies. "
}'
);
END;
/
PL/SQL procedure successfully completed.
BEGIN
DBMS_CLOUD_AI_AGENT.CREATE_TASK(
task_name => 'ANALYZE_MOVIE_TASK',
attributes => '{"instruction": "Help the user with their request about movies. User question: {query}",
"enable_human_tool" : "true"
}'
);
END;
/
PL/SQL procedure successfully completed.
BEGIN
DBMS_CLOUD_AI_AGENT.CREATE_TEAM(
team_name => 'MOVIE_AGENT_TEAM',
attributes => '{"agents": [{"name":"MOVIE_ANALYST","task" : "ANALYZE_MOVIE_TASK"}],
"process": "sequential"
}');
END;
/
PL/SQL procedure successfully completed.
EXEC DBMS_CLOUD_AI_AGENT.SET_TEAM('MOVIE_AGENT_TEAM');
PL/SQL procedure successfully completed.
select ai agent who are you?;
RESPONSE
--------------------------------------------------------------------------------
I'm MOVIE_ANALYST, an AI Movie Analyst here to assist with any questions or topi
cs related to movies. Whether you need information on films, actors, directors,
genres, or recommendations, I'm ready to help. What can I assist you with regard
ing movies?
-- SQL TOOL
BEGIN
DBMS_CLOUD_AI.CREATE_PROFILE(
profile_name =>'nl2sql_profile',
attributes => '{"provider": "oci",
"credential_name": "GENAI_CRED",
"oci_compartment_id" : "ocid1.compartment.oc1..aaaaa...",
"object_list": [{"owner": "ADB_USER", "name": "GENRE"},
{"owner": "ADB_USER", "name": "CUSTOMER"},
{"owner": "ADB_USER", "name": "WATCH_HISTORY"},
{"owner": "ADB_USER", "name": "STREAMS"},
{"owner": "ADB_USER", "name": "MOVIES"},
{"owner": "ADB_USER", "name": "ACTORS"}]
}');
END;
/
PL/SQL procedure successfully completed.
BEGIN
DBMS_CLOUD_AI_AGENT.CREATE_TOOL(
tool_name => 'SQL',
attributes => '{"tool_type": "SQL",
"tool_params": {"profile_name": "nl2sql_profile"}}'
);
END;
/
PL/SQL procedure successfully completed.
EXEC DBMS_CLOUD_AI_AGENT.drop_task('ANALYZE_MOVIE_TASK');
PL/SQL procedure successfully completed.
BEGIN
DBMS_CLOUD_AI_AGENT.CREATE_TASK(
task_name => 'ANALYZE_MOVIE_TASK',
attributes =>'{"instruction": "Help the user with their request about movies. User question: {query}. ' ||
'You can use SQL tool to search the data from database",
"tools": ["SQL"],
"enable_human_tool" : "true"
}'
);
END;
/
PL/SQL procedure successfully completed.
EXEC DBMS_CLOUD_AI_AGENT.CLEAR_TEAM;
PL/SQL procedure successfully completed.
EXEC DBMS_CLOUD_AI_AGENT.SET_TEAM('MOVIE_AGENT_TEAM');
PL/SQL procedure successfully completed.
-- SQL tool retrieves the movie with the highest popularity view count from the watch_history table
select ai agent what is the most popular movie?;
RESPONSE
----------------------------------------------------------------
The most popular movie is "Laugh Out Loud" released in 2008.
-- RAG TOOL
BEGIN
DBMS_CLOUD_AI.CREATE_PROFILE(
profile_name => 'RAG_PROFILE',
attributes =>'{"provider": "oci",
"credential_name": "GENAI_CRED",
"vector_index_name": "RAG_INDEX",
"oci_compartment_id": "ocid1.compartment.oc1..aaaaa...",
"temperature": 0.2,
"max_tokens": 3000
}');
END;
/
PL/SQL procedure successfully completed.
BEGIN
DBMS_CLOUD_AI.CREATE_VECTOR_INDEX(
index_name => 'RAG_INDEX',
attributes => '{"vector_db_provider": "oracle",
"location": "https://swiftobjectstorage.us-phoenix-1.oraclecloud.com/v1/my_namespace/my_bucket/my_data_folder",
"object_storage_credential_name": "MY_OCI_CRED",
"profile_name": "RAG_PROFILE",
"vector_dimension": 1024,
"vector_distance_metric": "cosine",
"chunk_overlap":128,
"chunk_size":1024
}');
END;
/
PL/SQL procedure successfully completed.
BEGIN
DBMS_CLOUD_AI_AGENT.CREATE_TOOL(
tool_name => 'RAG_TOOL',
attributes => '{"tool_type": "RAG",
"tool_params": {"profile_name": "RAG_PROFILE"}}'
);
END;
/
PL/SQL procedure successfully completed.
BEGIN
DBMS_CLOUD_AI_AGENT.CREATE_TASK(
task_name =>'ANALYZE_MOVIE_TASK',
attributes => '{"instruction": "Help the user with their request about movies. User question: {query}. ' ||
'You can use RAG tool to search the information from the knowledge base user give.",
"tools": ["RAG_TOOL"],
"enable_human_tool" : "true"
}'
);
END;
/
PL/SQL procedure successfully completed.
EXEC DBMS_CLOUD_AI_AGENT.CLEAR_TEAM;
PL/SQL procedure successfully completed.
EXEC DBMS_CLOUD_AI_AGENT.SET_TEAM('MOVIE_AGENT_TEAM');
PL/SQL procedure successfully completed.
-- Rag seach the object store to find review or comments of Movie Laugh out Loud
select ai agent Please find the comments of Movie Laugh out Loud;
RESPONSE
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
The comments for the movie "Laugh Out Loud" (2008) are as follows:
1. A lighthearted comedy that delivers plenty of laughs, though the plot is fairly predictable.
2. The performances are fun, especially from the lead actor who keeps the energy high.
3. Some jokes feel a bit outdated, but overall it is an enjoyable watch for a casual movie night.
4. Good chemistry between the cast members, which makes the humor more natural.
5. Not a groundbreaking comedy, but it does what it promises makes you laugh out loud.
-- WEBSEARCH TOOL
BEGIN
DBMS_CLOUD.CREATE_CREDENTIAL(
credential_name => 'OPENAI_CRED',
username => 'OPENAI',
password => '<API_KEY>'
);
END;
/
PL/SQL procedure successfully completed.
BEGIN
DBMS_CLOUD_AI_AGENT.CREATE_TOOL(
tool_name => 'WEBSEARCH_TOOL',
attributes => '{"instruction": "This tool can be used for searching the details about topics mentioned in notes and prepare a summary about prices, details on web",
"tool_type": "WEBSEARCH",
"tool_params": {"credential_name": "OPENAI_CRED"}}'
);
END;
/
PL/SQL procedure successfully completed.
BEGIN
DBMS_CLOUD_AI_AGENT.CREATE_TASK(
task_name => 'ANALYZE_MOVIE_TASK',
attributes => '{"instruction": "Help the user with their request about movies. User question: {query}. ' ||
'You can use WEBSEARCH_TOOL tool to search the information from internet.",
"tools": ["WEBSEARCH_TOOL"],
"enable_human_tool" : "true"
}'
);
END;
/
PL/SQL procedure successfully completed.
EXEC DBMS_CLOUD_AI_AGENT.CLEAR_TEAM;
PL/SQL procedure successfully completed.
EXEC DBMS_CLOUD_AI_AGENT.SET_TEAM('MOVIE_AGENT_TEAM');
PL/SQL procedure successfully completed.
select ai agent What is the most popular movie of 2023?;
RESPONSE
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Determining the most popular movie of 2023 can depend on various metrics such as box office earnings, streaming viewership, critical acclaim, or audience reception. Based on global box office data, which is often a strong indicator of popularity, the most successful movie of 2023 is "Barbie," directed by Greta Gerwig. Released in July 2023, it grossed over $1.4 billion worldwide, making it the highest-grossing film of the year and one of the biggest cultural phenomena, often discussed alongside "Oppenheimer" due to the "Barbenheimer" trend. Its widespread appeal, marketing, and social media buzz further solidify its status as the most popular movie of 2023. However, if you are looking for popularity based on a different metric (like streaming numbers or awards), please let me know, and I can adjust the analysis accordingly.
-- NOTIFICATION TOOL WITH EMAIL TYPE
BEGIN
DBMS_CLOUD_AI_AGENT.CREATE_TOOL(
tool_name => 'EMAIL',
attributes => q'[{"tool_type": "Notification",
"tool_params": {"notification_type" : "EMAIL",
"credential_name": "EMAIL_CRED",
"recipient": "example@oracle.com",
"smtp_host": "smtp.email.us-ashburn-1.oci.oraclecloud.com",
"sender": "example@oracle.com"}}]'
);
END;
/
PL/SQL procedure successfully completed.
BEGIN
DBMS_CLOUD_AI_AGENT.CREATE_TASK(
task_name => 'ANALYZE_MOVIE_TASK',
attributes =>'{"instruction": "Help the user with their request about movies. User question: {query}. ' ||
'You can use EMAIL TOOL tool to send email to the user.",
"tools": ["EMAIL"],
"enable_human_tool" : "true"
}'
);
END;
/
PL/SQL procedure successfully completed.
EXEC DBMS_CLOUD_AI_AGENT.CLEAR_TEAM;
PL/SQL procedure successfully completed.
EXEC DBMS_CLOUD_AI_AGENT.SET_TEAM('MOVIE_AGENT_TEAM');
PL/SQL procedure successfully completed.
select ai agent Please help me write a review of Movie "Barbie" and send the review to my email;
RESPONSE
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
I have written a review for the movie "Barbie" (2023) and sent it to your email. Please check your inbox for the detailed review. If you have any additional feedback or would like me to revise the review, let me know!
-- NOTIFICATION TOOL WITH SLACK TYPE
DBMS_CLOUD_AI_AGENT.CREATE_TOOL(
tool_name => 'SLACK_TOOL',
attributes => '{"tool_type": "SLACK",
"tool_params": {"credential_name": "SLACK_CRED",
"channel": "<channel_number>"}}'
);
END;
/
PL/SQL procedure successfully completed.
BEGIN
DBMS_CLOUD_AI_AGENT.CREATE_TASK(
task_name => 'ANALYZE_MOVIE_TASK',
attributes => '{"instruction": "Help the user with their request about movies. User question: {query}. ' ||
'You can use SLACK TOOL to send the notification to the user.",
"tools": ["SLACK_TOOL"],
"enable_human_tool" : "true"
}'
);
END;
/
PL/SQL procedure successfully completed.
select ai agent Please help me find the top 3 most-watched movies of 2023 and send them to me on slack;
RESPONSE
------------------------------------------------------------------------------------------------------------------------------------
I have sent the list of the top 3 most-watched movies of 2023 to you via Slack. Please check your Slack notifications for the details.Example: Create a Product Return Agent
This example shows how you can create a multi-turn conversational agent using Select AI Agent. In this example, you set up a customer-service agent that handles product returns and updates the return status in your database.
Before You Begin
Review:
This example creates an agent named Customer_Return_Agent and a tool named Update_Order_Status_Tool and then defines a task and a team to handle product return.
In this example, after your DBA grants EXECUTE privileges for: the DBMS_CLOUD_AI_AGENT and DBMS_CLOUD_AI, you begin by creating a sample data of customers, customer order status, and a function to update the customer order status. Then, create an agent named Customer_Return_Agent.
Create an Agent
You create an agent called Customer_Return_Agent with a profile (OCI_GENAI_GROK) and role to manage return requests.
Create Tools
You then create an agent tool named Update_Order_Status_Tool to update the status of the order in your database.
Create Task
You create a task called Handle_Product_Return_Task to guide the flow: ask for the reason (no longer needed, arrived too late, box broken, or defective). Proceed with a defective return flow.
Create Team
You create an agent team called Return_Agency_Team with Customer_Return_Agent as the agent and set it as an active team.
Run the Select AI Agent Team
You now run the agent team by using select ai agent as a prefix to your prompts.
The complete example is as follows:
--Grants EXECUTE privilege to ADB_USER
--
GRANT EXECUTE on DBMS_CLOUD_AI_AGENT to ADB_USER;
GRANT EXECUTE on DBMS_CLOUD_AI to ADB_USER;
BEGIN
DBMS_CLOUD_AI_AGENT.CREATE_AGENT(
agent_name => 'Customer_Return_Agent',
attributes => '{"profile_name": "OCI_GENAI_GROK",
"role": "You are an experienced customer return agent who deals with customers return requests."}');
END;
/
PL/SQL procedure successfully completed.
--Sample customer data
CREATE TABLE CUSTOMERS (
customer_id NUMBER(10) PRIMARY KEY,
name VARCHAR2(100),
email VARCHAR2(100),
phone VARCHAR2(20),
state VARCHAR2(2),
zip VARCHAR2(10)
);
INSERT INTO CUSTOMERS (customer_id, name, email, phone, state, zip) VALUES
(1, 'Alice Thompson', 'alice.thompson@example.com', '555-1234', 'NY', '10001'),
(2, 'Bob Martinez', 'bob.martinez@example.com', '555-2345', 'CA', '94105'),
(3, 'Carol Chen', 'carol.chen@example.com', '555-3456', 'TX', '73301'),
(4, 'David Johnson', 'david.johnson@example.com', '555-4567', 'IL', '60601'),
(5, 'Eva Green', 'eva.green@example.com', '555-5678', 'FL', '33101');
--create customer order status
CREATE TABLE CUSTOMER_ORDER_STATUS (
customer_id NUMBER(10),
order_number VARCHAR2(20),
status VARCHAR2(30),
product_name VARCHAR2(100)
);
INSERT INTO CUSTOMER_ORDER_STATUS (customer_id, order_number, status, product_name) VALUES
(2, '7734', 'delivered', 'smartphone charging cord'),
(1, '4381', 'pending_delivery', 'smartphone protective case'),
(2, '7820', 'delivered', 'smartphone charging cord'),
(3, '1293', 'pending_return', 'smartphone stand (metal)'),
(4, '9842', 'returned', 'smartphone backup storage'),
(5, '5019', 'delivered', 'smartphone protective case'),
(2, '6674', 'pending_delivery', 'smartphone charging cord'),
(1, '3087', 'returned', 'smartphone stand (metal)'),
(3, '7635', 'pending_return', 'smartphone backup storage'),
(4, '3928', 'delivered', 'smartphone protective case'),
(5, '8421', 'pending_delivery', 'smartphone charging cord'),
(1, '2204', 'returned', 'smartphone stand (metal)'),
(2, '7031', 'pending_delivery', 'smartphone backup storage'),
(3, '1649', 'delivered', 'smartphone protective case'),
(4, '9732', 'pending_return', 'smartphone charging cord'),
(5, '4550', 'delivered', 'smartphone stand (metal)'),
(1, '6468', 'pending_delivery', 'smartphone backup storage'),
(2, '3910', 'returned', 'smartphone protective case'),
(3, '2187', 'delivered', 'smartphone charging cord'),
(4, '8023', 'pending_return', 'smartphone stand (metal)'),
(5, '5176', 'delivered', 'smartphone backup storage');
--Create a update customer order status function
CREATE OR REPLACE FUNCTION UPDATE_CUSTOMER_ORDER_STATUS (
p_customer_name IN VARCHAR2,
p_order_number IN VARCHAR2,
p_status IN VARCHAR2
) RETURN CLOB IS
v_customer_id customers.customer_id%TYPE;
v_row_count NUMBER;
BEGIN
-- Find customer_id from customer_name
SELECT customer_id
INTO v_customer_id
FROM customers
WHERE name = p_customer_name;
UPDATE customer_order_status
SET status = p_status
WHERE customer_id = v_customer_id
AND order_number = p_order_number;
v_row_count := SQL%ROWCOUNT;
IF v_row_count = 0 THEN
RETURN 'No matching record found to update.';
ELSE
RETURN 'Update successful.';
END IF;
EXCEPTION
WHEN OTHERS THEN
RETURN 'Error: ' || SQLERRM;
END;
--Create Tool
BEGIN
DBMS_CLOUD_AI_AGENT.CREATE_TOOL(
tool_name => 'Update_Order_Status_Tool',
attributes => '{"instruction": "This tool updates the database to reflect return status change. Always confirm user name and order number with user before update status",
"function" : "update_customer_order_status"}',
description => 'Tool for updating customer order status in database table.'
);
END;
/
--Create Task
BEGIN
DBMS_CLOUD_AI_AGENT.CREATE_TASK(
task_name => 'Handle_Product_Return_Task',
attributes => '{"instruction": "Process a product return request from a customer:{query}' ||
'1. Ask customer the order reason for return (no longer needed, arrived too late, box broken, or defective)' ||
'2. If no longer needed:' ||
' a. Inform customer to ship the product at their expense back to us.' ||
' b. Update the order status to return_shipment_pending using Update_Order_Status_Tool.' ||
'3. If it arrived too late:' ||
' a. Ask customer if they want a refund.' ||
' b. If the customer wants a refund, then confirm refund processed and update the order status to refund_completed' ||
'4. If the product was defective or the box broken:' ||
' a. Ask customer if they want a replacement or a refund' ||
' b. If a replacement, inform customer replacement is on its way and they will receive a return shipping label for the defective product, then update the order status to replaced' ||
' c. If a refund, inform customer to print out the return shipping label for the defective product, return the product, and update the order status to refund' ||
'5. After the completion of a return or refund, ask if you can help with anything else.' ||
' End the task if user does not need help on anything else",
"tools": ["Update_Order_Status_Tool"]}'
);
END;
/
SQL> BEGIN
DBMS_CLOUD_AI_AGENT.CREATE_TEAM(
team_name => 'Return_Agency_Team',
attributes => '{"agents": [{"name":"Customer_Return_Agent","task" : "Handle_Product_Return_Task"}],
"process": "sequential"}');
END;
/
PL/SQL procedure successfully completed.
SQL> EXEC DBMS_CLOUD_AI_AGENT.SET_TEAM('Return_Agency_Team');
PL/SQL procedure successfully completed.
--Validate that the agent team is set in the current session with get_team
SQL> SELECT DBMS_CLOUD_AI_AGENT.GET_TEAM from DUAL;
--Interact with the agent
SQL> select ai agent I want to return a smartphone case;
RESPONSE
Could you please tell me the reason for returning the smartphone case? Is it no longer needed, arrived too late, box broken, or defective?
SQL> select ai agent the item is defective
RESPONSE
I'm sorry to hear that the smartphone case is defective. Would you like a replacement or a refund?
SQL> select ai agent I will need a replacement
RESPONSE
Can you please confirm your name and order number so I can update the status and process the replacement?
SQL> select ai agent I''m Bob Martinez and my order number is 7820
RESPONSE
Is there anything else I can help you with?
SQL> select ai agent No, I''m all set. Thanks
RESPONSE
Thank you for reaching out, Bob. I'm glad I could assist with the return of your smartphone case and stand. If you have any further questions or need assistance in the future, feel free to contact us. Have a great day!Example: Use Select AI Agent with Database Actions and APEX
If you are using web SQL clients such as Database Actions, run the agent team by using the DBMS_CLOUD_AI_AGENT.RUN_TEAM function and supply your prompt within the function.
Note
Note: Do not use DBMS_CLOUD_AI_AGENT.SET_TEAM in Database Actions or APEX Service. Instead, specify the agent team using the team_name argument of DBMS_CLOUD_AI_AGENT.RUN_TEAM.
The following example creates a conversation ID to track the customer conversation history and then uses the DBMS_CLOUD_AI_AGENT.RUN_TEAM function to interact with the product return agent.
See RUN_TEAM Function for more information.
CREATE OR REPLACE PACKAGE my_globals IS
l_team_cov_id varchar2(4000);
END my_globals;
/
-- Create conversation
DECLARE
l_team_cov_id varchar2(4000);
BEGIN
l_team_cov_id := DBMS_CLOUD_AI.create_conversation();
my_globals.l_team_cov_id := l_team_cov_id;
DBMS_OUTPUT.PUT_LINE('Created conversation with ID: ' || my_globals.l_team_cov_id);
END;
--Interact with the agent
DECLARE
v_response VARCHAR2(4000);
BEGIN
v_response := DBMS_CLOUD_AI_AGENT.RUN_TEAM(
team_name => 'Return_Agency_Team',
user_prompt => 'I want to return a smartphone case',
params => '{"conversation_id": "' \|\| my_globals.l_team_cov_id \|\| '"}'
);
DBMS_OUTPUT.PUT_LINE(v_response);
END;Example: Fetch and Analyze Log Reports
This example shows how you can create an agent team using two agents-task pairs to retrieve logs and analyze them.
Before You Begin
Review:
-
Perform Prerequisites for Select AI
-
Create an AI profile with Google as the provider. See Example: Select AI with Google.
-
Create an AI profile with OpenAI as the provider. See Example: Select AI with OpenAI.
-
This example creates:
-
a two-agent workflows for troubleshooting: Data_Engineer and Ops_Manager
-
one tool:
log-fetcherand then defines tasks and team to fetch logs and analyze them.
Note
Note: Only a DBA can run EXECUTE privileges and network ACL procedure.
In this example, after your DBA grants EXECUTE privileges for: the DBMS_CLOUD_AI_AGENT package, DBMS_CLOUD_AI package, and ACL access for your AI providers, you begin by creating the Data_Engineer and Ops_Manager agents.
Create an Agent
You create the agent called Data_Engineer with a profile (GOOGLE) that uses Google as the AI provider and role to retrieve and process complex data.
Create the agent called Ops_Manager with a profile (OPENAI) that uses OpenAI as the AI provider and role to analyze the data.
Create Tool
You then create an agent tool:log_fetcher: returns log entries after a given date. This uses a custom procedure fetch_logs.
Create Tasks
You define two tasks fetch_logs_task and analyze_log_task to guide the flow. The fetch_logs_task calls log_fetcher to retrieve logs based on the request. The analyze_log_task analyzes the fetched logs.
Create Team
You create Ops_Issues_Solution_Team team with Data_Engineer and Ops_Manager to run sequentially.
Run the Select AI Agent
You now set the active team and run the agent team with select ai agent as a prefix to your prompts.
The complete example is as follows:
--Grants EXECUTE privilege to ADB_USER
--
GRANT EXECUTE on DBMS_CLOUD_AI_AGENT to ADB_USER;
GRANT EXECUTE on DBMS_CLOUD_AI to ADB_USER;
-- Grant Network ACL for OpenAI endpoint
BEGIN
DBMS_NETWORK_ACL_ADMIN.APPEND_HOST_ACE(
host => 'api.openai.com',
ace => xs$ace_type(privilege_list => xs$name_list('http'),
principal_name => 'ADB_USER',
principal_type => xs_acl.ptype_db)
);
END;
/
--Grant Network ACL for Google endpoint
BEGIN
DBMS_NETWORK_ACL_ADB_USER.APPEND_HOST_ACE(
host => 'generativelanguage.googleapis.com',
ace => xs$ace_type(privilege_list => xs$name_list('http'),
principal_name => 'ADB_USER',
principal_type => xs_acl.ptype_db)
);
END;
/
PL/SQL procedure successfully completed.
--Create a table with Database logs and insert sample data
SQL> CREATE TABLE app_logs (
log_id NUMBER GENERATED BY DEFAULT AS IDENTITY,
log_timestamp DATE,
log_message VARCHAR2(4000)
);
Table created.
SQL> INSERT INTO app_logs (log_timestamp, log_message) VALUES (
TO_DATE('2025-03-22 03:15:45', 'YYYY-MM-DD HH24:MI:SS'),
'INFO: Database Cluster: Failover completed successfully. Standby promoted to primary. Downtime duration: 33 seconds. Correlation ID: dbfailover102.'
);
1 row created.
SQL> INSERT INTO app_logs (log_timestamp, log_message) VALUES (
TO_DATE('2025-03-23 08:44:10', 'YYYY-MM-DD HH24:MI:SS'),
'INFO: Switchover Process: Synchronization restored. Performing scheduled switchover. Correlation ID: dbswitchover215.'
);
1 row created.
SQL> INSERT INTO app_logs (log_timestamp, log_message) VALUES (
TO_DATE('2025-03-24 03:15:12', 'YYYY-MM-DD HH24:MI:SS'),
'ERROR: Database Cluster: Primary database unreachable, initiating failover to standby. Correlation ID: dbfailover102.'
);
1 row created.
SQL> COMMIT;
Commit complete.
-- create data engineer agent
SQL> BEGIN
DBMS_CLOUD_AI_AGENT.CREATE_AGENT(
agent_name => 'Data_Engineer',
attributes => '{"profile_name": "GOOGLE",
"role": "You are a specialized data ingestion engineer with expertise in ' \|\|
'retrieving and processing data from complex database systems."
}'
);
END;
/
PL/SQL procedure successfully completed.
-- create ops manager agent
SQL> BEGIN
DBMS_CLOUD_AI_AGENT.CREATE_AGENT(
agent_name => 'Ops_Manager',
attributes => '{"profile_name": "OPENAI",
"role": "You are an experienced Ops manager who excels at analyzing ' \|\|
'complex log data, diagnosing the issues."
}'
);
END;
/
PL/SQL procedure successfully completed.
-- create log_fetcher tool
-- fetch_logs is a customized procedure.
-- Please make sure you have created your own procedure before use it in the tool
--Create a customized fetch_logs procedure
SQL> CREATE OR REPLACE FUNCTION fetch_logs(since_date IN DATE) RETURN CLOB IS
l_logs CLOB;
BEGIN
SELECT JSON_ARRAYAGG(log_message RETURNING CLOB)
INTO l_logs
FROM app_logs
WHERE log_timestamp >= since_date
ORDER BY log_timestamp;
RETURN l_logs;
EXCEPTION
WHEN OTHERS THEN
RETURN 'Error fetching logs: ' || SQLERRM;
END fetch_logs;
/
Function created.
SQL> BEGIN
DBMS_CLOUD_AI_AGENT.CREATE_TOOL(
tool_name => 'log_fetcher',
attributes => '{"instruction": "retrieves and returns all log messages with a LOG_TIMESTAMP greater than or equal to the input date.",
"function": "fetch_logs"}'
);
END;
/
PL/SQL procedure successfully completed.
-- create task with log fetcher tool
SQL> BEGIN
DBMS_CLOUD_AI_AGENT.CREATE_TASK(
task_name => 'fetch_logs_task',
attributes => '{"instruction": "Fetch the log entries from the database based on user request: {query}}.",
"tools": ["log_fetcher"]}'
);
END;
/
PL/SQL procedure successfully completed.
-- create task with predefined rag and slack tool
SQL> BEGIN
DBMS_CLOUD_AI_AGENT.create_task(
task_name => 'analyze_log_task',
attributes => '{"instruction": "Analyze the fetched log entries retrieved based on user request: {query} ' ||
'Generate a detailed report include issue analysis and possible solution.",
"input" : "fetch_logs_task"
}'
);
END;
/
PL/SQL procedure successfully completed.
-- create team and set agent team
SQL> BEGIN
DBMS_CLOUD_AI_AGENT.create_team(
team_name => 'Ops_Issues_Solution_Team',
attributes => '{"agents": [{"name":"Data_Engineer","task" : "fetch_logs_task"},
{"name":"Ops_Manager","task" : "analyze_log_task"}],
"process": "sequential"
}');
END;
/
PL/SQL procedure successfully completed.
SQL> EXEC DBMS_CLOUD_AI_AGENT.SET_TEAM('Ops_Issues_Solution_Team');
PL/SQL procedure successfully completed.
SQL> select ai agent fetch and analyze the logs after 03/15 2025;
RESPONSE
-----------------------------------------------------------------------------------------------------------------------------------------------
1. Issue: High volume of 500 Internal Server Errors between 03/22 and 03/24.
Solution: Review server application logs to identify failing components; add better exception handling and fallback mechanisms to prevent service crashes.
2. Issue: Increased response time on /api/v1/user and /checkout, peaking on 03/25.
Solution: Profile back-end queries and services, apply caching where applicable, and offload static content to a CDN.
3. Issue: Detected brute-force login attack with over 500 failed POST attempts from a single IP.
Solution: Add rate-limiting, temporary IP bans, and CAPTCHA on the /login endpoint to prevent credential stuffing.
4. Issue: Suspicious User-Agent headers such as curl/7.58.0 or empty headers mimicking mobile devices.
Solution: Block malformed or uncommon User-Agent strings and log them for threat intelligence analysis.
5. Issue: DDoS-like traffic spike from distributed IPs observed on 03/20.
Solution: Enable DDoS protection at the CDN or cloud provider level, and configure autoscaling to absorb burst traffic.Example: Create a Customized HTTP Tool
This example defines a PL/SQL function that sends an HTTP GET request and returns the response text. The function is then added in the Select AI Agent tool.
Before You Begin
Review:
You can create a custom tool that sends a REST request using the DBMS_CLOUD.SEND_REQUEST procedure and returns the response as text. This example shows how to write the PL/SQL function and add it as a tool for use in agent tasks.
The function get_url_content sends a GET request to the target URL and returns the response as a CLOB. The DBMS_CLOUD_AI_AGENT.CREATE_TOOL uses the get_url_content function and creates a tool named HTTP_TOOL that Select AI agent can call when a task requires fetching content from a URL.
CREATE OR REPLACE FUNCTION get_url_content (
url IN CLOB
) RETURN CLOB
AS
l_resp DBMS_CLOUD_TYPES.RESP;
l_result CLOB;
BEGIN
l_resp := DBMS_CLOUD.SEND_REQUEST(
credential_name => NULL,
method => 'GET',
uri => url
);
l_result := DBMS_VECTOR_CHAIN.UTL_TO_TEXT(DBMS_CLOUD.GET_RESPONSE_RAW(l_resp);
RETURN l_result;
END get_url_content;
/
BEGIN
DBMS_CLOUD_AI_AGENT.create_tool(
tool_name => 'HTTP_TOOL',
attributes => '{
"instruction": "This tool fetches and returns the plain text content from the specified URL. ",
"function": "get_url_content"
}'
);
END;
/Example: View Agent Prompts and Responses from the Latest Team Run
This example helps debug recent Select AI Agent activity by displaying prompts, responses, and agent thoughts from the most recent agent team run using historical views and conversation data.
Before You Begin
Review:
This example retrieves and displays detailed logs from the most recent Select AI Agent team run. The example uses USER_AI_AGENT_TEAM_HISTORY, USER_AI_AGENT_TASK_HISTORY, and USER_CLOUD_AI_CONVERSATION_PROMPTS views to show each agent’s team name, task, associated prompts, and responses. Use this query to troubleshoot issues by reviewing both the input prompt and the generated responses from the latest team run.
WITH latest_team AS (
SELECT team_exec_id, team_name, start_date
FROM user_ai_agent_team_history
ORDER BY start_date DESC
FETCH FIRST 1 ROW ONLY
),
latest_task AS (
SELECT team_exec_id, task_name, agent_name, conversation_params, start_date,
ROW_NUMBER() OVER (PARTITION BY team_exec_id, task_name, agent_name
ORDER BY start_date DESC) as rn
FROM user_ai_agent_task_history
)
SELECT
team.team_name,
task.task_name,
task.agent_name,
p.prompt,
p.prompt_response
FROM latest_team team
JOIN latest_task task
ON team.team_exec_id = task.team_exec_id
AND task.rn = 1
LEFT JOIN user_cloud_ai_conversation_prompts p
ON p.conversation_id = JSON_VALUE(task.conversation_params, '$.conversation_id')
ORDER BY task.start_date DESC NULLS LAST,
p.created DESC NULLS LAST;This example retrieves detailed logs from tool, task, and team history views. Use the following queries to audit tool, task, and team usage and troubleshoot issues and log analysis. The queries use USER_AI_AGENT_TOOL_HISTORY, USER_AI_AGENT_TASK_HISTORY, and USER_AI_AGENT_TASK_HISTORY views.
--View the tool history
select * from USER_AI_AGENT_TOOL_HISTORY
order by START_DATE desc
--View the task history
select * from USER_AI_AGENT_TASK_HISTORY
order by START_DATE desc
--View the team history
select * from USER_AI_AGENT_TEAM_HISTORY
order by START_DATE descExample: Resume an Agent Team Run from WAITING_FOR_HUMAN State
This example shows how you can resume an agent team run when the task history status is WAITING_FOR_HUMAN. When an agent team enters the WAITING_FOR_HUMAN state, it pauses until a user provides the next input or confirmation. You can resume the run depending on how you use the agent team: on SQL command line or through DBMS_CLOUD_AI_AGENT.RUN_TEAM.
Before You Begin
Review:
Example: Resume an Agent Team Run Using SQL Command Line
If you are using SELECT AI AGENT <prompt> on the SQL command line, the Select AI Agent automatically resumes the paused team run. The conversation context, including prior prompts and reasoning, is maintained without requiring additional parameters.
Example: Resume an Agent Team Run Using DBMS_CLOUD_AI_AGENT.RUN_TEAM
If you are using the DBMS_CLOUD_AI_AGENT.RUN_TEAM procedure, you can resume a paused agent team by passing the same conversation_id that was used in the initial run. The agent team run state, including pending actions, and context, is preserved and the agent team continues exactly from where it stopped.
See Example: Create a Product Return Agent for an example using the DBMS_CLOUD_AI_AGENT.RUN_TEAM function.
--Interact with the agent
DECLARE
v_response VARCHAR2(4000);
BEGIN
v_response := DBMS_CLOUD_AI_AGENT.RUN_TEAM(
team_name => '<same initial team name>',
user_prompt => 'response to request',
params => '{"conversation_id": "<same initial conversation_id>"}'
);
DBMS_OUTPUT.PUT_LINE(v_response);
END;Example: Build and Run a Customer Support Supervisor Team
This end-to-end example is based on the multi-turn customer support scenario. It creates sample account and payment data, defines PL/SQL functions, maps those functions to tools, creates worker agents and tasks, creates the supervisor agent and team, and runs a multi-turn Select AI Agent framework conversation.
Before You Begin
Review:
- Perform Prerequisites for Select AI
- Prerequisites for Using Select AI Agent
- CREATE_TEAM Procedure
- CREATE_AGENT Procedure
The account worker can look up or update customer account details. The payment worker can retrieve payment transactions and use an email notification tool when the user confirms that a statement should be sent. The supervisor receives the user request, delegates to the account or payment worker, and returns the final response.
-- Payment System Demo
-- ============================================================================
-- STEP 1: Create user_accounts Sample Table
-- ============================================================================
-- Optional cleanup for a repeatable demo
BEGIN
EXECUTE IMMEDIATE 'DROP TABLE user_accounts';
EXCEPTION
WHEN OTHERS THEN
IF SQLCODE != -942 THEN
RAISE;
END IF;
END;
/
-- Create user_accounts table
CREATE TABLE user_accounts (
user_id NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
user_name VARCHAR2(100) NOT NULL UNIQUE,
email VARCHAR2(100),
billing_address VARCHAR2(200),
phone VARCHAR2(20),
account_status VARCHAR2(20),
created_date DATE DEFAULT SYSDATE,
updated_date DATE DEFAULT SYSDATE
);
-- Insert three example rows
INSERT INTO user_accounts (user_name, email, billing_address, phone, account_status)
VALUES ('john_smith', '<USER1_EMAIL>', '123 Main St, San Francisco, CA 94102', '415-555-0100', 'ACTIVE');
INSERT INTO user_accounts (user_name, email, billing_address, phone, account_status)
VALUES ('mary_jones', '<USER2_EMAIL>', '456 Oak Ave, Austin, TX 78701', '512-555-0200', 'ACTIVE');
INSERT INTO user_accounts (user_name, email, billing_address, phone, account_status)
VALUES ('david_wilson', '<USER3_EMAIL>', '789 Pine Rd, Seattle, WA 98101', '206-555-0300', 'ACTIVE');
COMMIT;
-- ============================================================================
-- STEP 2: Create PL/SQL Functions
-- ============================================================================
-- Function 1: check_account - retrieves user account information
CREATE OR REPLACE FUNCTION check_account(
user_name IN VARCHAR2
) RETURN VARCHAR2
IS
l_account_info VARCHAR2(4000);
l_user_name VARCHAR2(100) := user_name;
l_email VARCHAR2(100);
l_billing_address VARCHAR2(200);
l_phone VARCHAR2(20);
l_account_status VARCHAR2(20);
BEGIN
-- Query user account
SELECT
user_name,
email,
billing_address,
phone,
account_status
INTO
l_user_name,
l_email,
l_billing_address,
l_phone,
l_account_status
FROM user_accounts
WHERE UPPER(user_name) = UPPER(l_user_name);
--Format account information as readable text
l_account_info := 'Account Information for ' || l_user_name || CHR(10) ||
'--------------------------------' || CHR(10) ||
'Email: ' || NVL(l_email, 'Not set') || CHR(10) ||
'Billing Address: ' || NVL(l_billing_address, 'Not set') || CHR(10) ||
'Phone: ' || NVL(l_phone, 'Not set') || CHR(10) ||
'Account Status: ' || NVL(l_account_status, 'Unknown');
RETURN l_account_info;
EXCEPTION
WHEN NO_DATA_FOUND THEN
RETURN 'Error: User "' || user_name || '" not found in the system.';
WHEN OTHERS THEN
RETURN 'Error retrieving account: ' || SQLERRM;
END check_account;
/
-- Function 2: update_account - updates user account information
CREATE OR REPLACE FUNCTION update_account(
user_name IN VARCHAR2,
column_name IN VARCHAR2,
new_value IN VARCHAR2
) RETURN VARCHAR2
IS
-- Uses an autonomous transaction so the tool can commit the account update
-- independently of the surrounding Select AI Agent call.
PRAGMA AUTONOMOUS_TRANSACTION;
l_sql VARCHAR2(500);
l_rows_updated NUMBER;
l_result VARCHAR2(500);
l_valid_column BOOLEAN := FALSE;
BEGIN
-- Validate column name (security: only allow specific columns)
IF UPPER(column_name) IN ('EMAIL', 'BILLING_ADDRESS', 'PHONE', 'ACCOUNT_STATUS') THEN
l_valid_column := TRUE;
ELSE
RETURN 'Error: Column "' || column_name || '" is not allowed to be updated. ' ||
'Valid columns are: email, billing_address, phone, account_status';
END IF;
-- Build dynamic SQL for update
l_sql := 'UPDATE user_accounts SET ' ||
DBMS_ASSERT.SIMPLE_SQL_NAME(column_name) || ' = :new_value, ' ||
'updated_date = SYSDATE ' ||
'WHERE user_name = :user_name';
-- Execute update
EXECUTE IMMEDIATE l_sql
USING new_value, user_name;
l_rows_updated := SQL%ROWCOUNT;
COMMIT;
-- Return result message
IF l_rows_updated > 0 THEN
l_result := 'Successfully updated ' || column_name || ' to "' || new_value ||
'" for user "' || user_name || '".';
ELSE
l_result := 'Error: User "' || user_name || '" not found. No updates made.';
END IF;
RETURN l_result;
EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
RETURN 'Error updating account: ' || SQLERRM;
END update_account;
/
-- ============================================================================
-- STEP 3: Create Tools Based on PL/SQL Functions
-- ============================================================================
-- Tool 1: check_account_tool
BEGIN
DBMS_CLOUD_AI_AGENT.CREATE_TOOL(
tool_name => 'CHECK_ACCOUNT_TOOL',
attributes => '{
"instruction": "This tool retrieves user account information including email, billing address, phone, and account status. Provide the user_name to get the account details.",
"function": "check_account"
}'
);
END;
/
-- Tool 2: update_account_tool
BEGIN
DBMS_CLOUD_AI_AGENT.CREATE_TOOL(
tool_name => 'UPDATE_ACCOUNT_TOOL',
attributes => '{
"instruction": "This tool updates user account information. Provide user_name, column_name (email, billing_address, phone, or account_status), and new_value to update the account.",
"function": "update_account"
}'
);
END;
/
-- ============================================================================
-- STEP 4: Create Account Management Agent
-- ============================================================================
BEGIN
DBMS_CLOUD_AI_AGENT.CREATE_AGENT(
agent_name => 'ACCOUNT_AGENT',
description => 'Manages user account information including billing addresses and contact details',
attributes => '{
"profile_name": "<AI_PROFILE_NAME>",
"role": "You are a Account Management Specialist. Your role is to help users view and update their account information."
}'
);
END;
/
-- ============================================================================
-- STEP 5: Create Task for Account Management Agent
-- ============================================================================
BEGIN
DBMS_CLOUD_AI_AGENT.CREATE_TASK(
task_name => 'ACCOUNT_MANAGEMENT_TASK',
attributes => '{
"instruction": "Handle user account management requests. For account queries, use the check_account_tool with the user_name. For account updates, use the update_account_tool with user_name, the column to update (email, billing_address, phone, or account_status), and the new value. Always provide clear confirmation of any changes made.",
"tools": ["CHECK_ACCOUNT_TOOL", "UPDATE_ACCOUNT_TOOL"]
}'
);
END;
/
-- ============================================================================
-- STEP 6: Create Payment Transactions Sample Table
-- ============================================================================
-- Optional cleanup for a repeatable demo
BEGIN
EXECUTE IMMEDIATE 'DROP TABLE payment_transactions';
EXCEPTION
WHEN OTHERS THEN
IF SQLCODE != -942 THEN
RAISE;
END IF;
END;
/
-- Create payment_transactions table
CREATE TABLE payment_transactions (
transaction_id NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
user_name VARCHAR2(100) NOT NULL,
transaction_date DATE NOT NULL,
amount NUMBER(10,2) NOT NULL,
payment_method VARCHAR2(50),
status VARCHAR2(20),
description VARCHAR2(200),
created_date DATE DEFAULT SYSDATE
);
-- Create index for better query performance
CREATE INDEX idx_transactions_user_date ON payment_transactions(user_name, transaction_date);
-- Insert sample transaction data
-- Transactions for john_smith
INSERT INTO payment_transactions (user_name, transaction_date, amount, payment_method, status, description)
VALUES ('john_smith', TO_DATE('2026-01-11', 'YYYY-MM-DD'), 150.00, 'Credit Card', 'COMPLETED', 'Monthly subscription');
INSERT INTO payment_transactions (user_name, transaction_date, amount, payment_method, status, description)
VALUES ('john_smith', TO_DATE('2026-01-11', 'YYYY-MM-DD'), 75.50, 'PayPal', 'COMPLETED', 'Add-on service');
INSERT INTO payment_transactions (user_name, transaction_date, amount, payment_method, status, description)
VALUES ('john_smith', TO_DATE('2026-01-15', 'YYYY-MM-DD'), 200.00, 'Credit Card', 'COMPLETED', 'Annual renewal');
INSERT INTO payment_transactions (user_name, transaction_date, amount, payment_method, status, description)
VALUES ('john_smith', TO_DATE('2024-12-01', 'YYYY-MM-DD'), 150.00, 'Credit Card', 'COMPLETED', 'Monthly subscription');
-- Transactions for mary_jones
INSERT INTO payment_transactions (user_name, transaction_date, amount, payment_method, status, description)
VALUES ('mary_jones', TO_DATE('2024-11-11', 'YYYY-MM-DD'), 99.99, 'Debit Card', 'COMPLETED', 'Premium upgrade');
INSERT INTO payment_transactions (user_name, transaction_date, amount, payment_method, status, description)
VALUES ('mary_jones', TO_DATE('2024-11-20', 'YYYY-MM-DD'), 49.99, 'Credit Card', 'COMPLETED', 'Monthly subscription');
-- Transactions for david_wilson
INSERT INTO payment_transactions (user_name, transaction_date, amount, payment_method, status, description)
VALUES ('david_wilson', TO_DATE('2024-11-11', 'YYYY-MM-DD'), 299.00, 'Wire Transfer', 'COMPLETED', 'Annual plan');
INSERT INTO payment_transactions (user_name, transaction_date, amount, payment_method, status, description)
VALUES ('david_wilson', TO_DATE('2024-11-25', 'YYYY-MM-DD'), 25.00, 'PayPal', 'PENDING', 'Additional storage');
COMMIT;
-- ============================================================================
-- STEP 7: Create PL/SQL Function for get_payment
-- ============================================================================
CREATE OR REPLACE FUNCTION get_payment(
user_name IN VARCHAR2,
transaction_date IN VARCHAR2 -- Expected format: 'YYYY-MM-DD' or 'Nov 11' or 'November 11'
) RETURN VARCHAR2
IS
l_payment_info CLOB;
l_transaction_date DATE;
l_total_amount NUMBER := 0;
l_transaction_count NUMBER := 0;
l_date_parsed DATE;
l_user_name VARCHAR2(100) := user_name;
-- Cursor to fetch transactions
CURSOR c_transactions IS
SELECT
transaction_id,
TO_CHAR(transaction_date, 'YYYY-MM-DD HH24:MI:SS') AS txn_datetime,
amount,
payment_method,
status,
description
FROM payment_transactions
WHERE user_name = l_user_name
AND TRUNC(transaction_date) = TRUNC(l_date_parsed)
ORDER BY transaction_date;
BEGIN
-- Parse the date input (handle different formats)
BEGIN
-- Try standard format first: YYYY-MM-DD
l_date_parsed := TO_DATE(transaction_date, 'YYYY-MM-DD');
EXCEPTION
WHEN OTHERS THEN
BEGIN
-- Try format: Mon DD (e.g., 'Nov 11')
l_date_parsed := TO_DATE(transaction_date || ' ' || TO_CHAR(SYSDATE, 'YYYY'), 'Mon DD YYYY');
EXCEPTION
WHEN OTHERS THEN
BEGIN
-- Try format: Month DD (e.g., 'November 11')
l_date_parsed := TO_DATE(transaction_date || ' ' || TO_CHAR(SYSDATE, 'YYYY'), 'Month DD YYYY');
EXCEPTION
WHEN OTHERS THEN
RETURN 'Error: Unable to parse date "' || transaction_date || '". Please use format YYYY-MM-DD (e.g., 2024-11-11) or Mon DD (e.g., Nov 11).';
END;
END;
END;
-- Build header
l_payment_info := 'Payment Transactions for ' || l_user_name || ' on ' ||
TO_CHAR(l_date_parsed, 'YYYY-MM-DD') || CHR(10) ||
'================================================================' || CHR(10);
-- Fetch and format transactions
FOR rec IN c_transactions LOOP
l_transaction_count := l_transaction_count + 1;
l_total_amount := l_total_amount + rec.amount;
l_payment_info := l_payment_info || CHR(10) ||
'Transaction #' || l_transaction_count || ':' || CHR(10) ||
' Transaction ID: ' || rec.transaction_id || CHR(10) ||
' Date/Time: ' || rec.txn_datetime || CHR(10) ||
' Amount: $' || TO_CHAR(rec.amount, 'FM999,999,990.00') || CHR(10) ||
' Payment Method: ' || NVL(rec.payment_method, 'N/A') || CHR(10) ||
' Status: ' || NVL(rec.status, 'N/A') || CHR(10) ||
' Description: ' || NVL(rec.description, 'N/A') || CHR(10);
END LOOP;
-- Add summary
IF l_transaction_count = 0 THEN
l_payment_info := l_payment_info || CHR(10) ||
'No transactions found for this date.' || CHR(10);
ELSE
l_payment_info := l_payment_info || CHR(10) ||
'================================================================' || CHR(10) ||
'Summary:' || CHR(10) ||
' Total Transactions: ' || l_transaction_count || CHR(10) ||
' Total Amount: $' || TO_CHAR(l_total_amount, 'FM999,999,990.00') || CHR(10);
END IF;
RETURN l_payment_info;
EXCEPTION
WHEN OTHERS THEN
RETURN 'Error retrieving payment information: ' || SQLERRM;
END get_payment;
/
-- ============================================================================
-- STEP 8: Create Tool for get_payment
-- ============================================================================
BEGIN
DBMS_CLOUD_AI_AGENT.CREATE_TOOL(
tool_name => 'GET_PAYMENT_TOOL',
attributes => '{
"instruction": "This tool retrieves payment transaction information for a specific user on a specific date. Provide the user_name and date (format: YYYY-MM-DD). It returns all transactions for that user on that date including transaction IDs, amounts, payment methods, status, and descriptions.",
"function": "get_payment"
}'
);
END;
/
BEGIN
DBMS_CLOUD.DROP_CREDENTIAL(
credential_name => 'EMAIL_CRED' );
END;
/
BEGIN
DBMS_CLOUD.CREATE_CREDENTIAL(
credential_name => 'EMAIL_CRED',
username => '<OCI_SMTP_USERNAME>',
password => '<SMTP_PASSWORD>');
END;
/
BEGIN
-- Allow SMTP access for user ADMIN
DBMS_NETWORK_ACL_ADMIN.APPEND_HOST_ACE(
host => 'smtp.email.us-ashburn-1.oci.oraclecloud.com',
lower_port => 587,
upper_port => 587,
ace => xs$ace_type(privilege_list => xs$name_list('SMTP'),
principal_name => '<DATABASE_USERNAME>',
principal_type => xs_acl.ptype_db));
END;
/
-- ============================================================================
-- STEP 9: Configure Email Notification Tool
-- ============================================================================
EXEC DBMS_CLOUD_AI_AGENT.drop_tool('Email');
BEGIN
-- This EMAIL tool sends statements to the fixed recipient configured in the recipient parameter.
-- To send to a customer's stored email address, replace this fixed-recipient configuration
-- with logic that looks up and passes the customer's email address.
DBMS_CLOUD_AI_AGENT.create_tool(
tool_name => 'EMAIL',
attributes => q'[{"tool_type": "Notification",
"tool_params": {"notification_type" : "EMAIL",
"credential_name": "EMAIL_CRED",
"recipient": "<RECIPIENT_EMAIL>",
"subject": "Payment statement",
"smtp_host": "smtp.email.us-ashburn-1.oci.oraclecloud.com",
"sender": "<SENDER_EMAIL>"}}]'
);
END;
/
-- ============================================================================
-- STEP 10: Create Payment Management Agent
-- ============================================================================
BEGIN
DBMS_CLOUD_AI_AGENT.CREATE_AGENT(
agent_name => 'PAYMENT_AGENT',
description => 'Manages payment transactions, generates statements, and handles payment-related queries',
attributes => '{
"profile_name": "<AI_PROFILE_NAME>",
"role": "You are a Payment Management Specialist. "
}'
);
END;
/
-- ============================================================================
-- STEP 11: Create Task for Payment Management Agent
-- ============================================================================
BEGIN
DBMS_CLOUD_AI_AGENT.CREATE_TASK(
task_name => 'PAYMENT_MANAGEMENT_TASK',
attributes => '{
"instruction": "Handle payment-related requests. For payment queries, use GET_PAYMENT_TOOL with the user_name and date. Show the payment details, then ask the user whether they want to receive the statement by email. If they do, send it using EMAIL.",
"tools": ["GET_PAYMENT_TOOL", "EMAIL"]
}'
);
END;
/
-- STEP 12: Create the Supervisor Agent and Team
exec DBMS_CLOUD_AI_AGENT.clear_team;
exec DBMS_CLOUD_AI_AGENT.drop_team('CUSTOMER_SUPPORT');
EXEC DBMS_CLOUD_AI_AGENT.drop_agent('ORCHESTRATOR_AGENT');
BEGIN
DBMS_CLOUD_AI_AGENT.create_agent(
'ORCHESTRATOR_AGENT',
'{
"profile_name": "<AI_PROFILE_NAME>",
"supervisor" : true
}'
);
END;
/
BEGIN
DBMS_CLOUD_AI_AGENT.create_team(
team_name => 'CUSTOMER_SUPPORT',
attributes => '{
"process": "sequential",
"supervisor_agent": "ORCHESTRATOR_AGENT",
"agents": [
{"name":"ACCOUNT_AGENT","task" : "ACCOUNT_MANAGEMENT_TASK"},
{"name":"PAYMENT_AGENT","task" : "PAYMENT_MANAGEMENT_TASK"}
]
}',
description => 'A supervised team with orchestrator delegating to specialists'
);
END;
/
-- STEP 13: Run the Conversation
exec DBMS_CLOUD_AI_AGENT.set_team('CUSTOMER_SUPPORT');
SQL> select ai agent my user name is john_smith, I want to update my billing address to 11th Ave, Austin, TX 78701;
RESPONSE
--------------------------------------------------------------------------------
The billing address for user 'john_smith' has been successfully updated to '11th Ave, Austin, TX 78701'.
SQL> select ai agent please check my payments on Jan 11 2026;
RESPONSE
--------------------------------------------------------------------------------
Here are your payment details for January 11, 2026, john_smith:
================================================================
Payment Transactions for john_smith on 2026-01-11
Transaction #1:
Transaction ID: 1
Date/Time: 2026-01-11 00:00:00
Amount: $150.00
Payment Method: Credit Card
Status: COMPLETED
RESPONSE
--------------------------------------------------------------------------------
Description: Monthly subscription
Transaction #2:
Transaction ID: 2
Date/Time: 2026-01-11 00:00:00
Amount: $75.50
Payment Method: PayPal
Status: COMPLETED
Description: Add-on service
================================================================
RESPONSE
--------------------------------------------------------------------------------
Summary:
Total Transactions: 2
Total Amount: $225.50
Would you like to receive a statement of these transactions via email? Please confirm.
SQL> select ai agent yes send the email;
RESPONSE
--------------------------------------------------------------------------------
The payment statement for January 11, 2026, has been successfully sent to the configured recipient. If you have any further questions or need assistance, feel free to ask.