AI Agents are proliferating across many tools and now Oracle APEX 26.1 introduces built in both AI Agents and AI Tools. To demonstrate this facility, I will use the EMP and DEPT tables from my Oracle 26ai database and build a small People Cost Review page within an application which invokes an AI agent supported by tools. This page will allow a user to review salary and commission findings, ask an AI Agent to explain what needs attention, drill into the people behind a finding, and also create follow-up actions.
We will not allow the LLM model to query anything it likes, rather we will give the Agent a set of approved tools each of which supports a specific job.
In a previous article we looked at Interactive Reports (IR) which now have Narrative AI power. An Interactive Report is useful when the user knows what they want to filter (e.g. “show employees in Sales earning more than 1500 and highlight them in yellow”) whereas an Agent is useful when the user wants help with processes provided by tools, such as (“Which departments need attention” and then “Log an Action for someone to review this”).
In the previous blog I linked to above, I show how to add a Generative AI Service to the workspace, once we have that we can use the AI to generate a sample application for us.
Create A New Application
Add New Tables and Metadata
Create a table to hold actions raised during the review.
We will define the review rules, this is so we have complete control over the “meaning” of elements including salaries/commissions that are worthy of review without giving the LLM “agency” to determine what these are. We will use a simple settings table and seed it.
Create A Monitoring Page with an AI Agent
This will have a static region with a button called AI_REVIEW ( we will complete the setup of the button when we create the AI Agent ) and also two interactive reports. The first will show things under review and the second actions that an agent will have recorded.
Create a new IR called Review Findings using the SQL below. This is using the metadata we created in the PEOPLE_COST_REVIEW_SETTINGS tables and enforces the business scope and rules of the items that we want to concentrate on. We determine the boundaries for a high salary or commission to be reviewed, taken live from the underlying tables and referencing the metadata values we have defined. As well as individuals we also assess departments as a whole. This can then be used as the backbone of a flexible report and also as input into an agent tooling so we can demonstrate how the agentic tools work.
with settings as (
select
max(case when setting_name = 'EMP_HIGH_SALARY' then setting_value end) as emp_high_salary,
max(case when setting_name = 'EMP_HIGH_COMMISSION' then setting_value end) as emp_high_commission,
max(case when setting_name = 'DEPT_HIGH_TOTAL_SALARY' then setting_value end) as dept_high_total_salary,
max(case when setting_name = 'DEPT_HIGH_AVERAGE_SALARY' then setting_value end) as dept_high_average_salary
from people_cost_review_settings
), dept_summary as (
select
d.deptno,
d.dname,
d.loc,
count(e.empno) as employee_count,
sum(e.sal) as total_salary,
round(avg(e.sal), 2) as average_salary,
sum(nvl(e.comm, 0)) as total_commission
from dept d,
emp e
where e.deptno(+) = d.deptno
group by
d.deptno,
d.dname,
d.loc
), employee_findings as (
select
'EMPLOYEE' as finding_level,
e.empno,
e.ename,
e.deptno,
d.dname,
e.job,
e.sal,
e.comm,
case
when e.sal >= s.emp_high_salary then 'HIGH_SALARY'
when e.comm is not null and e.comm >= s.emp_high_commission then 'HIGH_COMMISSION'
end as finding_type,
case
when e.sal >= s.emp_high_salary then 'Employee salary is at or above the configured review threshold.'
when e.comm is not null and e.comm >= s.emp_high_commission then 'Employee commission is at or above the configured review threshold.'
end as finding_reason
from emp e,
dept d,
settings s
where d.deptno = e.deptno
and (e.sal >= s.emp_high_salary
or nvl(e.comm, 0) >= s.emp_high_commission)
), department_findings as (
select
'DEPARTMENT' as finding_level,
cast(null as number) as empno,
cast(null as varchar2(100)) as ename,
ds.deptno,
ds.dname,
cast(null as varchar2(100)) as job,
ds.total_salary as sal,
ds.total_commission as comm,
case
when ds.employee_count = 0 then 'NO_EMPLOYEES'
when ds.total_salary >= s.dept_high_total_salary then 'HIGH_TOTAL_SALARY'
when ds.average_salary >= s.dept_high_average_salary then 'HIGH_AVERAGE_SALARY'
end as finding_type,
case
when ds.employee_count = 0 then 'Department has no employees.'
when ds.total_salary >= s.dept_high_total_salary then 'Department total salary is at or above the configured review threshold.'
when ds.average_salary >= s.dept_high_average_salary then 'Department average salary is at or above the configured review threshold.'
end as finding_reason
from dept_summary ds,
settings s
where ds.employee_count = 0
or ds.total_salary >= s.dept_high_total_salary
or ds.average_salary >= s.dept_high_average_salary
)
select
finding_level,
empno,
ename,
deptno,
dname,
job,
sal,
comm,
finding_type,
finding_reason
from employee_findings
union all
select
finding_level,
empno,
ename,
deptno,
dname,
job,
sal,
comm,
finding_type,
finding_reason
from department_findings
order by
finding_level,
dname,
enameCreate another new IR region called Review Actions using the SQL below. This will show all the actions that have been recorded. When we define the Agent, we will also define a tool to create actions by inserting into the EMP_REVIEW_ACTIONS table. This report will show us the actions that have been created so that we can verify what has been done by the agent. In a “real” system, then other processes would be able to pick up these open actions and ue them as inputs for downstream processing.
select *
from (
select
a.id,
a.action_type,
a.action_note,
a.status,
a.created_by,
a.created_on,
e.empno,
e.ename,
d.deptno,
d.dname
from emp_review_actions a,
emp e,
dept d
where e.empno(+) = a.empno
and d.deptno(+) = nvl(a.deptno, e.deptno)
and a.status = 'OPEN'
order by a.created_on desc
)Create an AI Agent
Let’s create a new agent in shared components called People Cost Review Agent.
It’s important ( as with any AI ) to give the agent a prompt to guide it as to which tools to use (which we will set up next ). In the help within APEX we are informed “Specify the initial instructions or context for the AI service. A well-crafted system prompt guides the AI’s behavior and sets the tone for its responses. To create an effective system prompt, start by clearly defining the role or expertise you want the AI to assume. Next, describe the specific task or type of responses you expect from the AI.” so we will create a prompt that covers the scope of the agent and the tools to be called. Note that we have not yet defined these tools.
We can also add a welcome message.
Tool #1 – Add the Get Review Findings Tool
The first tool we will define is one that returns all employee and departmental items of “interest” which is the insight we have given the tool, so the prompt reflects that.
The data description is really important context for the AI. The manual describes it as “Enter a brief description of the data that will be sent to the AI service. This description should clearly explain the nature and content of the data being provided, helping the AI to better understand and process the information.” so we will provide that level of contextual description.
The SQL query is the same as we used for the IR so we can check the results in the IR as we know the parameters that the AI was given. We have no parameters for this tool, we are allowing it to scan all the data.
Add the Get Employee Review Details Tool
This tool does the “deep dive” into the employee, returning all their details. it accepts a department and employee number so returns specific information for that employee. If we added in addition attributes from other tables then they would be used too. Note that as well as a description for the tool and a data description we also have to provide parameter descriptions to indicate to the AI the purpose of these parameters.
Let’s focus in on the full description we added for the EMPNO parameter that is used to guide the AI.
Add the Create A Review Action Tool
This is a “doing” task so we are going to use this tool to write actions. The parameter descriptions are really useful for the model to understand what values to pass when it calls the tool.
Note ( for clarity) that EMPNO, DEPTNO, ACTION_TYPE and ACTION_NOTE are tool parameters, they are not page items that the user has typed into the page. When the Agent invokes tool, the AI Service supplies values for these parameters, so when the user says:
"Create a follow-up action for KING to review the Research salary distribution."the Agent should first work out who King is, by using the employee detail tool or from earlier tool results. It can then call the create action tool and these parameters can be used in PL/SQL code.
Note also that there are additional security measures/approvals that can be applied but we are not activating them in this instance.
declare
l_emp_exists number := 0;
l_dept_exists number := 0;
begin
if :EMPNO is not null then
select count(*)
into l_emp_exists
from emp
where empno = :EMPNO;
if l_emp_exists = 0 then
apex_ai.set_tool_result(
p_result => 'No employee exists with employee number ' || :EMPNO,
p_notification_message => 'Employee not found',
p_notification_type => 'error'
);
return;
end if;
end if;
if :DEPTNO is not null then
select count(*)
into l_dept_exists
from dept
where deptno = :DEPTNO;
if l_dept_exists = 0 then
apex_ai.set_tool_result(
p_result => 'No department exists with department number ' || :DEPTNO,
p_notification_message => 'Department not found',
p_notification_type => 'error'
);
return;
end if;
end if;
insert into emp_review_actions (
empno,
deptno,
action_type,
action_note,
created_by,
created_on
)
values (
:EMPNO,
:DEPTNO,
:ACTION_TYPE,
:ACTION_NOTE,
:APP_USER,
systimestamp
);
apex_ai.set_tool_result(
p_result => 'Review action created.',
p_notification_message => 'Review action created',
p_notification_type => 'success'
);
end;Tool #4 – Add the Show Open Actions Tool
This is a useful tool which would be used when then user asks about current open actions. It will show them and use the supplied SQL to do so.
We are using the same SQL as the Open Review Actions report.
Add The Agent To The Page
Now we can add the Agent to the button we created. Right click the AI_REVIEW button and select Triggered Action ( which is itself new for 26.1) and select Show AI Assistant and select the assistant we have just created. Choosing the display as “dialog” will pop up a dialog box for the agent.
Agent In Action
We have created an agent with a set of tools which all have descriptions, data descriptions and also parameters with descriptions too. With all that in place, we can now click on the Agent button we have created to invoke the dialog box and start a conversation.
That is interesting, so I ask “Tell me more about FORD” and the assistant replies
This gives us the additional detail we were looking for so we can see the context of other employees in the department. We can then direct the Agent to take an action by recording an action event for us. So I can ask “Create a follow-up action for JONES to review departmental salaries” as JONES is the manager of the department.
We get a confirmation from the agent saying that the follow up task has been created, and we have created an IR to show all these actions so we see it on the screen as proof that it has been done and we can check the results.
Summary
We didn’t give the Agent the autonomy to decide what counts as a high salary or high commission. We could allocate it that job and ask “Do any salaries look unusual compared with for similar roles” and use the results of that for investigation, but here we have specific configured thresholds based on our business rules and use SQL to ground and surface those so we give the Agent strict rules in which to operate. What the Agent is doing is
- Understanding the user’s question
- Selecting the right tool
- Passing the right parameters
- Explaining the result
- Helping the user move from “Insight” to “action”
That is more useful than a general-purpose chat box.
This is of course an “early example” I used to look how the new AI Agent and the Agent is helping with a task inside the application. We have supplied it with some tools to use and it can use those to explain findings and create actions, but only through paths we control.