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.
- 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.
- Example: Create a Task
 Create a task an agent can perform.
- Example: Create an Agent Team
 Create agent teams to accomplish your tasks.
- 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.
- 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.
- 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.
Parent topic: Select AI Agent
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:
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.
Parent topic: Examples of Using Select AI Agent
Example: Create Built-In Tools
Select AI Agent accepts the following types of tools:
- 
                              
                              SQL
- 
                              
                              RAG
- 
                              
                              WEBSEARCH
- 
                              
                              NOTIFICATION- EMAIL
- SLACK
 
This example creates a SQL tool that translates
                natural language queries into SQL statements. The SQL tool lets agents answer
                data-related questions by mapping prompts into SQL queries.
                     
 This example illustrates
                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.
            
                     
BEGIN
  DBMS_CLOUD_AI.CREATE_PROFILE(
      profile_name = > 'nl2sql_profile',
      attributes => '{"provider": "oci",
        "credential_name": "GEN1_CRED",
        "oci_compartment_id": "ocid1.compartment.oc1..aaaa.."
       }');
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;
/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",
                            "vector_dimension": 1024,
                            "vector_distance_metric": "cosine",
                            "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;
/
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.
                     
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;
/
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.
            
                     
BEGIN
  DBMS_CLOUD.CREATE_CREDENTIAL(
    credential_name => 'EMAIL_CRED',
    username => '<username>',
    password => '<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;
/
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;
/Parent topic: Examples of Using Select AI Agent
Example: Create a Task
Note:
Only a DBA can grant EXECUTE privileges and network
                    ACL procedure. 
                        
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;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;
/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;
/Parent topic: Examples of Using Select AI Agent
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.
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.
Parent topic: Examples of Using Select AI Agent
Example: Create a Movie Analysis Agent with Built-In Tools
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
- 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 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.
Parent topic: Examples of Using Select AI Agent
Example: Create a Product Return Agent
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!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:
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;
Parent topic: Examples of Using Select AI Agent
Example: Fetch and Analyze Log Reports
- 
                              
                              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.
 
- a two-agent workflows for troubleshooting: Data_Engineer and Ops_Manager
- one tool:
                        log-fetcher
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 backend 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.Parent topic: Examples of Using Select AI Agent