From Chatbot to Co-Worker: Building a Production Ready AI Agent in Oracle APEX
Most AI Agent demos stop the moment the chat box says something clever. I wanted to know what happens after that, when a real support team asks who can call this thing, what happens if it gets something wrong, and can it run without me sitting there watching a chat window.
Why I built a support desk instead of a toy demo
Every AI Agent tutorial I read followed the same pattern. Open the builder, add a tool, type a question in a chat box, watch it call the tool, done. That is a fine five minute video. It is not something you can show your team lead and say "this is ready for staff to use."
So I picked something boring on purpose, a support ticket desk, because boring is where the real questions show up. Who is allowed to close a ticket. What stops someone from deleting a record by accident. Can this thing run on its own at 7 in the morning and email a summary before anyone logs in.
This post is that build, table by table, tool by tool, including the parts where I got it wrong first.
The tables
Three tables to start, a fourth one comes later for the audit log. Customers, staff with a role column, and the tickets themselves.
CREATE TABLE customers_d (
cust_id NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
cust_name VARCHAR2(120) NOT NULL,
email VARCHAR2(200),
tier VARCHAR2(20) DEFAULT 'STANDARD'
);
CREATE TABLE staff_roles (
username VARCHAR2(120) PRIMARY KEY,
full_name VARCHAR2(150),
role VARCHAR2(20) DEFAULT 'STAFF'
);
CREATE TABLE tickets_d (
ticket_id NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
cust_id NUMBER REFERENCES customers_d(cust_id),
subject VARCHAR2(400) NOT NULL,
priority VARCHAR2(10) DEFAULT 'MEDIUM',
status VARCHAR2(20) DEFAULT 'OPEN',
assigned_to VARCHAR2(120),
created_date DATE DEFAULT SYSDATE,
closed_date DATE
);I loaded around 22 sample tickets across every status and priority, a few left unassigned on purpose so the reassign tool would have something real to do. Two staff accounts got the SUPERVISOR role, the rest stayed as plain STAFF. That split mattered a lot later.
Connecting the AI service
Before an agent exists, APEX needs a place to send the prompt. Workspace Utilities, AI Services, Create. Give it a static id, pick a provider, drop the API key under Credentials.
Creating the agent and the read only tools
Shared Components, AI Agents, Create. I named mine support_desk_agent, pointed it at the service I just tested, and gave it a plain system prompt telling it to help staff look up tickets, check workload, and always confirm before closing or deleting anything.
Saving the agent opens a Tools tab. An agent with nothing in that tab can chat about your app all day but cannot touch a single row. So the first four tools I added were all read only.
| Tool | Type | Execution point | Job |
|---|---|---|---|
| get_ticket_by_id | Retrieve Data (SQL) | On Demand | Looks up one ticket |
| search_tickets | Retrieve Data (SQL) | On Demand | Filters by status, priority, customer |
| get_workload_summary | Retrieve Data (SQL) | On Demand | Open counts and average age by priority |
| list_support_staff | Retrieve Data (Static) | Augment System Prompt | Valid assigned_to names, injected every message |
The tools that actually change data
Create, reassign, escalate, close, delete. Five write tools, each one doing exactly one job on purpose, no tool that tries to do two things at once.
BEGIN
UPDATE tickets_d
SET status = 'CLOSED', closed_date = SYSDATE
WHERE ticket_id = :P_TICKET_ID;
IF SQL%ROWCOUNT = 0 THEN
apex_ai.set_tool_result(
p_result => 'No ticket found with ID ' || :P_TICKET_ID,
p_success => FALSE
);
ELSE
apex_ai.set_tool_result(
p_result => 'Ticket ' || :P_TICKET_ID || ' closed.',
p_success => TRUE
);
END IF;
END;One tool that touches the page, not the database
highlight_ticket_row is different from the rest, it is Execute Client-side Code, and it never goes near the database at all. When the model calls it with a ticket id, a small script scrolls to and highlights that row in the Interactive Report.
const row = document.querySelector(`tr[data-id="${ticketId}"]`);
if (row) {
row.scrollIntoView({ behavior: "smooth", block: "center" });
row.classList.add("t-Report-row--highlight");
}The rule I kept repeating to myself while sorting the eleven tools, anything that touches the page belongs client side, anything that touches data belongs server side. That split alone answered most of the "where does this tool go" questions.
Putting the button on the page took less time than writing this paragraph, a button, a dynamic action on Click, True Action Show AI Assistant, pointed at support_desk_agent. APEX supplies the floating chat window on its own.
Now the part every demo skips, who is actually allowed to do this
At this point I had a working agent, and that is exactly where the demo videos stop. But right now, any user who can open that chat box can ask it to delete a ticket. A confirmation dialog stops a misclick. It does nothing about someone who simply should not have that power in the first place.
The fix is an Authorization Scheme, the same mechanism APEX already uses for pages and buttons, applied straight to a tool.
SELECT 1
FROM staff_roles
WHERE username = :APP_USER
AND role = 'SUPERVISOR'Attach this scheme to close_ticket and delete_ticket. The difference this makes is bigger than it sounds. If the scheme evaluates false, the tool is not just hidden or greyed out, it is never even offered to the model as an option in the first place. A non supervisor account cannot talk its way into it with clever phrasing, because the capability was never on the table to begin with. Read only tools stayed open to everyone.
Letting the platform enforce confirmation instead of hoping the model asks
Every tool has a Requires Confirmation attribute set directly on the tool itself, not something the system prompt has to remind the model about.
| Field | close_ticket | delete_ticket |
|---|---|---|
| Title | Close Ticket | Delete Ticket |
| Message | This will mark ticket {{ticket_id}} as closed. Continue? | This will permanently remove ticket {{ticket_id}}. This cannot be undone. |
| Approve label | Close it | Delete |
| Cancel label | Not yet | Keep Ticket |
Click Cancel in that dialog and the tool's PL/SQL never runs at all, APEX stops it before a single line executes. That is a stronger guarantee than a tool whose only job is to ask nicely in its own response text and hope the model remembers to actually call it before acting.
Making the agent answer in JSON so a chart can use it
Free text answers are fine for a conversation, useless for wiring into a region. I built a second agent just for reporting, support_desk_reporting_agent, Response Format set to JSON Object, with a schema that pins down exactly what comes back.
{
"type": "object",
"properties": {
"by_priority": {
"type": "array",
"items": {
"type": "object",
"properties": {
"priority": { "type": "string" },
"open_count": { "type": "number" },
"avg_age_days": { "type": "number" }
},
"required": ["priority", "open_count"]
}
}
},
"required": ["by_priority"]
}DECLARE
l_response CLOB;
BEGIN
l_response := apex_ai.generate(
p_agent_static_id => 'support_desk_reporting_agent',
p_prompt => 'Give me the current workload summary by priority.'
);
apex_collection.create_or_truncate_collection(p_collection_name => 'WORKLOAD_SUMMARY');
apex_json.parse(l_response);
FOR i IN 1 .. apex_json.get_count(p_path => 'by_priority') LOOP
apex_collection.add_member(
p_collection_name => 'WORKLOAD_SUMMARY',
p_c001 => apex_json.get_varchar2(p_path => 'by_priority[%d].priority', p0 => i),
p_n001 => apex_json.get_number(p_path => 'by_priority[%d].open_count', p0 => i),
p_n002 => apex_json.get_number(p_path => 'by_priority[%d].avg_age_days', p0 => i)
);
END LOOP;
END;Same underlying tool, get_workload_summary, exists as two separate copies, one under each agent, because tools are scoped to the agent they were created under. Two different contracts on top of it though, loose text for the chat agent, guaranteed JSON for this one, and the whole hand off from model to chart happens on the server with no JavaScript needed to parse anything.
Running an agent with no chat window at all
Everything so far lives inside a Shared Component. APEX 26.1 also lets you define tools inline at call time using apex_ai.t_tools, no builder trip, no chat UI, which is exactly what a scheduled job needs.
DECLARE
l_tools apex_ai.t_tools;
l_response CLOB;
BEGIN
l_tools(1) := apex_ai.make_tool(
p_name => 'get_workload_summary',
p_description => 'Returns open ticket counts and average age, grouped by priority.',
p_callback_procedure => 'PKG_SUPPORT_AI_TOOLS.GET_WORKLOAD_SUMMARY'
);
l_response := apex_ai.generate(
p_prompt => 'Summarize today''s open ticket workload by priority. ' ||
'Flag anything CRITICAL that has been open more than 24 hours.',
p_tools => l_tools
);
apex_mail.send(
p_to => 'support-leads@example.com', p_from => 'apex-agent@example.com',
p_subj => 'Daily Ticket Digest', p_body => l_response
);
END;I set this up as an APEX Automation rather than a raw DBMS_SCHEDULER job, mainly because it stays inside the app's own session context instead of running detached at the schema level. It is more code than the declarative builder, you are matching parameter types by hand instead of clicking through fields, but for a background job that nobody watches, that trade is worth it.
Logging every action, because "the agent did it" is not an audit trail
A chat transcript is not proof of anything to an auditor. A table is.
CREATE TABLE tool_execution_log (
log_id NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
tool_name VARCHAR2(128),
called_by VARCHAR2(128) DEFAULT SYS_CONTEXT('APEX$SESSION','APP_USER'),
arguments CLOB,
tool_result CLOB,
success_flag VARCHAR2(1),
called_at TIMESTAMP DEFAULT SYSTIMESTAMP
);One insert and one update dropped into every write tool, right alongside the existing update statement. A plain Interactive Report on this table, on an admin page nobody outside supervisors sees, gives a real record, who the agent acted for, what it actually changed, independent of whatever the chat transcript claims happened.
What I would tell someone starting this today
Eleven tools, two agents, one scheduled job, one log table. But the tool count is not really the point I want you to take from this. "The agent can do X" and "the agent is allowed to do X, safely, for the right person, with a record of it afterward" are two completely different milestones.
The first one takes an afternoon. I got a working chat agent up before lunch on my first attempt. The second one, the authorization scheme, the enforced confirmation, the audit log, took the rest of the day, mostly because I kept discovering gaps by testing it as a non supervisor account and watching what the model tried to talk its way around.
That second milestone is the one that actually makes it worth putting in front of real support staff instead of just yourself.
- Test the AI Service connection before building a single tool.
- Keep write tools narrow, one job each, no tool that both reassigns and escalates at once.
- Gate destructive tools with a real Authorization Scheme, not just a confirmation dialog.
- Use Requires Confirmation on the tool itself so APEX enforces the pause, not the model's memory.
- Give reporting its own JSON agent instead of trying to parse chat text.
- Log every write, independent of the chat transcript.
Try it yourself
Delete a ticket as a plain staff account and watch the tool simply not get offered. Close one as a supervisor and watch APEX pause for confirmation before anything runs. Both the live app and the full export are open below.
Comments
Post a Comment