Ejemplos de uso de Select AI 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;
/

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

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

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

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

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

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;

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;

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;

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

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

BEGIN
  DBMS_CLOUD_AI_AGENT.CREATE_TEAM(
    team_name  => 'ReturnAgency',
    attributes => '{"agents": [{"name":"Customer_Return_Agent","task" : "Return_And_Price_Match"}],
                    "process": "sequential"}');
END;
/

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

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;

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

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

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

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

--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!

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;

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

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

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;

--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 desc

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

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