agent: online

AI Agents in Oracle APEX — Part 2: Building the Live Demo

The config, the tools, the button, and what to type at it.

This picks up straight from Part 1 — no theory this time, just the build. I'm using two small tables, EMP_D and DEPT_D, so the classic schema stays out of the way of the actual point.

By the end, you'll have an agent that looks up employees, summarizes salaries by department, hires someone new, and highlights a row on the page — all from a chat box.

01

Create the AI Agent

Shared Components → Tasks → AI Agents → Create.

First, APEX needs an AI Service configured — that's where your provider API key (OpenAI, Gemini, etc.) lives.

• Workspace Utilities → AI Services → Create
• Static ID + pick your model — I used command-a-03-2025, fast and cheap enough to hammer with test prompts
• Paste your API key under Credentials — APEX auto-wraps it in a Web Credential, so it never sits exposed in page source
• Hit Test Connection before moving on

Lesson learnedSkipping Test Connection cost me ten minutes debugging what looked like a broken tool — it was just a bad key.

Then the agent itself:

• Static ID: emp-dept-ai-agent
• AI Service: the one above
• Response Format: Text (Plain text)

Click Create and a new Tools tab appears. That's where the real work happens.

02

Give it Tools

An agent with no tools is an assistant wearing a different badge — it can talk about your app, but not touch it. Here's every tool I built, across all three tool types:

Read-only tools

get_employee_by_idSQL

Looks up one employee's full record by employee number. The most basic tool in the set, and the one I used to sanity-check the whole pipeline first.

search_employeesSQL

Searches employees by partial name, job title, and/or department. Every parameter is optional, so the model searches on whatever the user actually gave it.

list_departmentsSQL

Returns the full department list — id, name, location. Loaded at Augment System Prompt time so the agent already knows valid names before it's asked.

get_department_salary_summarySQL

Headcount and salary stats — avg, min, max, total commission — per department or all of them. Makes "compare salaries across departments" actually work.

get_org_chartCLOB

Builds a text-tree org chart of manager → direct-report relationships using CONNECT BY. Shows a Retrieve tool returning a function body instead of flat SQL.

Write / action tools

create_employeePL/SQL

Hires a new employee — the fullest parameter list in the demo, eight fields including two optional ones. A good stress test for parameter mapping.

update_employee_salaryPL/SQL

Changes salary and, optionally, commission. Returns a clear failure message via apex_ai.set_tool_result if the EMPNO doesn't exist.

transfer_employeePL/SQL

Moves an employee to a new department and optionally reassigns their manager. Best demo of multi-field, partial updates.

delete_employeePL/SQLConfirm

Permanently removes an employee record. Requires confirmation, so the model has to get a human "yes" before it runs.

create_departmentPL/SQL

Creates a new department with a name and optional location. Straightforward insert that rounds out department-side CRUD.

update_departmentPL/SQL

Renames a department or changes its location. Both fields optional, so only what's asked for gets changed.

delete_departmentPL/SQLConfirm

Deletes a department, but blocks itself if employees are still assigned. The tool enforces the safety check, not just the dialog.

Client-side tool

confirm_destructive_actionJS

Pops a browser confirmation dialog before any delete tool runs, and only lets the agent proceed if the user clicks "yes." This is the tool that keeps the delete tools honest.

03

Wire up the button

• Page Designer → right-click the region → Create Button
• Name: ASK_AGENT, Label: "Ask Agent(or) Try it now", Icon: fa-robot
• Right-click the button → Create Dynamic Action
• Event: Click → True Action: Show AI Assistant
• Point AI Agent at Emp_Dept_Details_AI_Agent

That's the entire wiring — no custom modal, no chat-window JavaScript. APEX supplies the floating assistant dialog and the first-run consent prompt for free.

04

What to actually type at it

A blank chat box is intimidating, so I set a welcome message on the agent:

"Hi! I'm your HR data assistant. I can help you look up employees and departments, check salary and headcount summaries, and make updates like hiring, transfers, or salary changes. What would you like to do?"

1. Show me everyone in department 20
2. What's the average salary in each department?
3. Give Jones a raise to 3200
4. Highlight employee 7566 in the report
5. Delete employee 7900this one shows the confirmation dialog actually stopping the model from deleting unattended

The model didn't guess, didn't hallucinate a row count, and didn't quietly delete anything. It called a tool, the tool reported what happened, and where it mattered, a human still clicked "yes."

Want to try it yourself?

Play with the live agent, or grab the full APEX export and PL/SQL from GitHub.

Live Demo GitHub Code

Where I landed

Less setup than expected, more time spent rewriting tool descriptions than expected — the model kept sending the wrong thing to tools that were, technically, working fine. Writing for an agent turns out to be a different skill than writing PL/SQL.

Next up: chaining a client-side confirmation tool in front of a server-side delete, so the agent asks in the chat itself before it ever reaches the confirmation dialog. Interested? Let me know in the comments and I'll write Part 3.