Defining an Agent to Achieve a Goal

Defining an Agent's Goal

After defining an AI Service, create an AI Agent to handle an app-specific goal using artificial intelligence. It collaborates automatically with the AI service you configure to process a request and produce a response. An end user or your business logic supplies the input.

You define your agent's mission with a system prompt in natural language. For example, if the goal is providing an HR Assistant that can answer questions for HR representatives, a simple prompt might be:

You are a friendly Woods HR assistant.
You only answer questions about Woods HR employees and no others.

You configure the unique aspects of meeting the goal, and the APEX engine handles interacting with the AI Service to answer requests.

Adding Tools for App Data and Allowed Actions

You can give your agent AI Tools to make it even smarter. They either augment the initial system prompt with dynamic data, or function as callbacks the AI Service invokes on demand to produce a final response.

AI Tools let your AI Agent expose additional capabilities that help the AI Service to achieve a goal. When responding to a user prompt, the AI Service can call your agent's tools to retrieve additional data or perform tasks.

Each tool has a name, and may have a description and parameters. Your agent runs any Augment System Prompt tools on the initial exchange with the AI Service, and gives the service a list of On Demand tool descriptions. You implement tools using SQL, PL/SQL, or JavaScript. Any tool you configure can have a Server-side condition to be included only when relevant.

Exploring the HR Assistant Agent's Two AI Tools

For example, consider the on-demand tool get_employee_info shown below. It lets the AI service retrieve employee information. As shown below, you include a description of the tool in natural language and define the parameters the AI Service can supply. Notice this tool includes two optional parameters ENAME and JOB, both of type VARCHAR2. The tool Type is set to Retrieve Data, so you can implement the tool using a SQL query.

Figure 12-11 Defining a Retrieve Data On-Demand Tool with Optional Parameters

Description of "Figure 12-11 Defining a Retrieve Data On-Demand Tool with Optional Parameters"

On the Settings tab, as shown below, you describe the data the tool returns in natural language and provide the SQL query that can reference the parameters as bind variables. Notice that since the parameters are optional, the query uses them if they have a value to filter on employee name and job. The query statement is:

SELECT e.ename, e.job, e.sal, e.hiredate, d.dname
FROM   emp e
JOIN   dept d ON d.deptno = e.deptno
WHERE  (:ENAME IS NULL OR e.ename = UPPER(:ENAME))
AND    (:JOB   IS NULL OR e.job   = UPPER(:JOB))
ORDER BY e.sal DESC

Tip:

The query can also reference APEX session state like application items as bind variables.

Figure 12-12 Writing the SQL Query to Implement a Retrieve Data Tool

Description of "Figure 12-12 Writing the SQL Query to Implement a Retrieve Data Tool"

A second give_raise tool lets the AI service increase an employee's salary by a certain percent. This one sets Type to Execute Server-side Code, specifies a natural language description, and defines P_EMPNO and P_PCT required NUMBER parameters.

Figure 12-13 Defining an Execute Server-side Code On-Demand Tool with Required Parameters

Description of "Figure 12-13 Defining an Execute Server-side Code On-Demand Tool with Required Parameters"

You implement an Execute Server-side Code tool using PL/SQL or server-side JavaScript. As shown below, the code appears on the Settings tab. It ensures the percent parameter supplied is within the range of 0 to 40, and if so attempts to update the salary of the employee identified by the P_EMPNO parameter value. If the update is successful, it sets the result message to inform the AI service of the new salary. If the percent value is out of range, or the employee is not found, it sets a helpful error message instead. It calls set_tool_result in the APEX_AI package to provide the message APEX returns to the AI service as the tool invocation result.

declare
    l_pct     number := nvl(:P_PCT,0);
    l_new_sal number;
    l_result  varchar2(255);
begin
    if     l_pct > 0
       and l_pct <= 40
    then  
        update emp
           set sal = sal * (1 + to_number(nvl(:P_PCT,0)/100))
         where empno = :P_EMPNO
         returning sal into l_new_sal;
        l_result := case 
                        when sql%rowcount > 0 
                        then 'New salary is '||l_new_sal
                        else 'No employee '||:P_EMPNO||' found'
                    end;
    else
        l_result := 'Percent must be > 0 and <= 40';
    end if;
    apex_ai.set_tool_result(l_result);
end;

Figure 12-14 Writing the PL/SQL Code to Implement an Execute Server-side Code Tool

Description of "Figure 12-14 Writing the PL/SQL Code to Implement an Execute Server-side Code Tool"

Witnessing Tools in Action in an Agent-Driven Chatbot

The Woods HR application uses role-based authorization to show HR representatives like Susan an HR Assistant button in the Navigation Bar. Clicking it, an Assistant chatbot dialog opens where Susan gets help using natural language about the employees who work at Woods. As shown below, she asks:

Which clerk earns the most? [Susan]

The assistant replies:

The highest-paid clerk at Woods HR is MILLER, earning $1,300. [Assistant]

Susan continues:

Which employee earns the least? [Susan]

The assistant says:

The employee who earns the least is SMITH, with a salary of $800. [Assistant]

Susan asks the assistant to increase Smith's pay:

Raise smith's salary by 10 percent [Susan]

The assistant answers:

I couldn't apply the 10% raise because the system requires Smith's employee number, and I only have his name/salary – not his ID. Can you provide Smith's employee number, or should I look up Smith again to retrieve it? [Assistant]

Figure 12-15 Using the HR Assistant AI Agent in an AI Assistant Chatbot

Description of "Figure 12-15 Using the HR Assistant AI Agent in an AI Assistant Chatbot"

The conversation illustrates the chatbot is using the HR Assistant AI Agent and calling the get_employee_info and considering calling the give_raise tools as needed. When HR reps complain about manually typing in the employee number to perform salary changes, you identify the problem.

The get_employee_info tool's SQL query does not include the EMPNO column in the returned data. You change the tool's query to select EMPNO as well, and update its Data Description to mention employee id. Then, the chatbot can complete the task with no further input. In the example conversation below, if Susan asks the HR assistant to try again, this time the chatbot replies:

Done – SMITH (EmpNo 7369) received a 10% raise. Salary is now $880. [Assistant]

Tip:

See Creating an Agent-Driven Chatbot for more info.

Figure 12-16 Once get_employee_info Returns EMPNO, Assistant Completes Task Directly

Description of "Figure 12-16 Once get_employee_info Returns EMPNO, Assistant Completes Task Directly"

Understanding the Lifecycle of an Agent-Driven Chatbot Request

The diagram below illustrates the flow of control from the time an end user asks a question in the browser like "Which clerk earns the most?" to when they see the answer. The APEX engine runs any Augment System Prompt tools before it sends the request to the AI Service. Their results accompany the AI Agent's system prompt in the first message sent to the service's Large Language Model (LLM).

If the AI Service has everything it needs, it may respond directly. If not, it may call one or more of your agent's On Demand tools. For example, the LLM may request to invoke the get_employee_info tool, passing in "clerk" for the value of the tool's JOB parameter.

Tip:

The AI service never sees how your tools are implemented and only sees the data your tools return in their response. APEX sends your tool name, description, and parameter definitions only. These establish the "contract" of available "functions" the LLM can invoke, and each is in full control of what it returns in response.

The APEX engine runs any tools the LLM requests to invoke, passing in parameters the service provides. APEX responds with your tools' results. This "agent loop" conversation continues until the AI Service decides to respond with the final answer. In this example, the service returns the response "MILLER earns the most at 1300", and APEX returns it to the browser where the user sees it appear as the answer to their question.

As Susan carries on a conversation of multiple messages and responses, APEX accumulates the conversation history. Since the LLM is stateless, APEX sends the full history with every request to the LLM so it has the full context to reason over. If later in the conversation Susan asks to raise Smith's salary by 10%, the same request lifecycle occurs. The only difference in that case is that the LLM requests to invoke the give_raise tool instead.

Figure 12-17 Lifecycle of a Request Involving an AI Agent

Description of "Figure 12-17 Lifecycle of a Request Involving an AI Agent"

Deciding Between Execution Points

Your AI Agent tool's Execution Point determines when it runs: On Demand or Augment System Prompt.

The APEX engine runs your agent's On Demand tools only when the AI Service requests them. In contrast, Augment System Prompt tools run before your agent sends the initial request to the AI Service. Any results become additional system prompts that precede user input.

You can configure a Server-side condition on both kinds of tools. When an On-Demand tool's condition is false, it is omitted from the list of tool descriptions sent to the AI service. When an Augment System Prompt tool's condition is false, that tool is skipped and adds no data to the initial request.

Any tool can perform Retrieval Augmented Generation (RAG). If your agent's goal benefits from up front information that will always be useful to prepare an answer, then use an Augment System Prompt tool. Otherwise, create a well-described On Demand tool instead. Define appropriate parameters and return just the information the LLM needs, when it decides it is useful to have it.

Understanding Native Tool Types

APEX offers the three native tool types:

Retrieve Data
- Returns data based on a SQL Query, Function Body returning a CLOB, or static text.
Execute Server-side Code
- Performs server-side logic using PL/SQL or JavaScript (MLE) code. Override the implicit "success" result with set_tool_result in the APEX_AI package.
Execute Client-side Code
- Request end user-input or call browser APIs in an async context. Override the implicit "success" return by returning another string.

You can use all tool types for an on-demand tool. To augment the system prompt, choose either Retrieve Data or Execute Client-side Code.

Specifying Tool Parameters

On demand tools can declare named parameters with optional description, and specify which if any is required. Supported data types include VARCHAR2, CLOB, NUMBER and BOOLEAN. Tools using SQL or PL/SQL reference parameter values as bind variables (:PERSON_ID). In JavaScript, they are available via this.data (this.data.PERSON_ID).

Ensuring a Human is "In the Loop" Before Invoking a Tool

Each AI Tool can insist that the end user is aware of and approves its invocation. If you set the Requires Confirmation switch on in the User Approval section of your tool definition, then the end user is alerted if the LLM requests the tool, and the tool is only executed if the end-user approves.

Using an Agent Declaratively and Programmatically

Your Show AI Assistant and Generate Text With AI dynamic actions can reference an AI Agent, as can your Generate Text with AI page process or workflow activity. For programmatic use, pass the AI Agent's static ID to APEX_AI package APIs. If the AI Service calls your agent's tools, APEX automatically runs them. Client-side tools are allowed when dynamic actions like Show AI Assistant, or Generate Text With AI initiate the request from the browser.

Extending Native AI Tool Types with Plug-ins

The "Generative AI Tool" plug-in type lets you create your own reusable tools to extend the built-in set.

12.3 Defining an Agent to Achieve a Goal