Build an AI Agent in Oracle APEX 26.1 Using Gemini AI

Oracle APEX 26.1 is a serious step forward for enterprise AI inside low-code applications. Earlier releases made it possible to connect APEX to generative AI services and call models from apps. APEX 26.1 goes further by making AI Agents and AI Tools a first-class development pattern.

That matters because most business teams do not need a generic chatbot bolted onto an application. They need a governed assistant that understands the page context, can retrieve trusted data, can ask for confirmation before sensitive actions, and can stay inside the permissions and business rules of the APEX app.

In this guide, we will build a practical AI Agent for an HR application using Google Gemini as the AI provider. The agent can answer user questions, retrieve department salary metrics from the database, and appear inside the application through the built-in AI Assistant experience.

What Changed in APEX 26.1

APEX 26.1 introduces a stronger model for agentic application behavior:

• AI Agents are managed as shared components.
• AI Tools define what an agent is allowed to retrieve or perform.
• Tools can be implemented with SQL, PL/SQL, or JavaScript.
• On Demand tools are called only when the AI service needs them.
• Augment System Prompt tools can enrich the agent context at the start of an exchange.
• Tool parameters support types such as VARCHAR2, CLOB, NUMBER, and BOOLEAN.
• Sensitive tools can require user approval before execution.
• Agents can be used declaratively from dynamic actions and page processes, or programmatically through APEX_AI.
• APEX 26.1 also adds broader AI provider support, including Anthropic Claude, Google Gemini, Mistral AI, Ollama, and OpenAI-compatible APIs.

The architecture is simple: the AI Agent manages the conversation, while AI Tools expose only the specific capabilities the developer approves.

Use Case: HR Copilot

For this example, imagine a manager viewing an employee profile in an APEX application. Instead of moving between reports, charts, and salary dashboards, the manager opens an assistant and asks:

What is the average salary in this employee’s department?

The agent should not invent an answer. It should call an approved tool, retrieve live data from Oracle Database, and respond with a concise explanation.

The application will use:

• A Gemini-backed Generative AI Service configured at the workspace level.
• An AI Agent named HR_COPILOT.
• A SQL-based Retrieve Data tool named GET_DEPARTMENT_SALARY_METRICS.
• A button and dynamic action on the employee profile page.

Step 1: Configure Gemini as the Generative AI Service

Start in your APEX workspace:

1. Go to Workspace Utilities > Generative AI.
2. Create a new Generative AI Service.
3. Choose Google Gemini as the AI provider.
4. Name the service gemini-hr-agent.
5. Enter the Gemini endpoint, a chat-capable Gemini model name, and your Gemini API key.
6. Enable the service for App Builder if you want APEX Assistant support while developing.
7. Set practical usage controls such as token limits and timeout values.
8. Test the connection.

Keep credentials server-side. APEX stores them through its credential framework, so the API key does not need to be exposed in browser-side code.

For production applications, create separate Gemini services for development, test, and production. This makes it easier to control model selection, usage limits, and audit behavior across environments.

When choosing the Gemini model, use a model intended for conversational text generation. If your organization has different Gemini models for cost, latency, or data-handling requirements, start with the smallest model that produces reliable tool-calling behavior for your prompts.

Step 2: Create the Base APEX Application

Create or reuse an APEX application with employee and department data. A simple demo structure could include:

• EMP or EMPLOYEES for employee records.
• DEPT or DEPARTMENTS for department metadata.
• An Employee Directory interactive report.
• An Employee Profile form page.

After the app exists, open the application definition and select the Gemini AI service as the application default. You can also define a consent message so users understand when AI features are being used.

Consent is important in business applications. Even when the agent is only reading approved data, users should know when a model is involved in the interaction.

Step 3: Create the AI Agent

Go to Shared Components > AI Agents and create a new agent.

Use a configuration like this:

• Name: HR Copilot
• Static ID: HR_COPILOT
• Service: Application Default, or the specific gemini-hr-agent service you configured
• Response Format: Text
• Temperature: Low to moderate, such as 0.2 to 0.5, for factual business answers
• Welcome Message: How can I help you review this employee record?

Use a clear system prompt:

You are an HR assistant embedded in an Oracle APEX application.
Answer business questions using only the tools and context available to you.
When salary or department metrics are requested, use the approved tools.
Be concise, factual, and avoid making policy recommendations unless the available data supports them.
Do not expose internal SQL, credentials, or implementation details to the user.

The system prompt should describe the role, boundaries, and expected tone. The tool descriptions will handle when and how specific capabilities are called.

Step 4: Add a Retrieve Data Tool

Inside the AI Agent, add an AI Tool.

Use this configuration:

• Name: GET_DEPARTMENT_SALARY_METRICS
• Type: Retrieve Data
• Execution Point: On Demand
• Data Source Type: SQL Query
• Description: Use this tool when the user asks for department-level salary metrics, including employee count, minimum salary, maximum salary, and average salary.

Example SQL:

select
    d.department_name,
    count(e.employee_id) as total_employees,
    min(e.salary) as min_salary,
    max(e.salary) as max_salary,
    round(avg(e.salary), 2) as avg_salary
from departments d
left join employees e
    on e.department_id = d.department_id
group by d.department_name
order by d.department_name

This gives the agent trusted data without letting it freely generate and execute arbitrary SQL. The agent can only call the tool you define.

Step 5: Add Tool Parameters

A more useful version accepts a department parameter. Add a parameter such as:

• Parameter Name: P_DEPARTMENT_NAME
• Data Type: VARCHAR2
• Required: No
• Description: Department name provided by the user, if the question refers to one department.

Then update the SQL:

select
    d.department_name,
    count(e.employee_id) as total_employees,
    min(e.salary) as min_salary,
    max(e.salary) as max_salary,
    round(avg(e.salary), 2) as avg_salary
from departments d
left join employees e
    on e.department_id = d.department_id
where :P_DEPARTMENT_NAME is null
   or upper(d.department_name) = upper(:P_DEPARTMENT_NAME)
group by d.department_name
order by d.department_name

Parameters help the model call the tool with structured intent. They also keep the query controlled and readable for developers.

Step 6: Add Guardrails for Sensitive Tools

Salary data is sensitive. Depending on your policy, you may want the assistant to summarize only aggregate salary metrics and avoid exposing individual compensation values.

Good guardrails include:

• Use aggregate SQL rather than row-level salary queries.
• Apply APEX authorization schemes to the page and region.
• Use row-level security or database views where appropriate.
• Keep tool SQL narrow and purpose-specific.
• Require user approval for tools that perform changes or access sensitive data.
• Log tool usage for audit and support review.

APEX 26.1 tool guardrails are especially useful for action-oriented tools. For example, if an agent can start a workflow, send an email, update a record, or call a client-side action, require confirmation before execution.

Step 7: Show the Agent in the Application

Open the Employee Profile page in Page Designer.

Create a button near the employee details:

• Button Name: ASK_HR_COPILOT
• Label: Ask HR Copilot
• Template: Text with Icon
• Icon: fa-robot

Create a dynamic action on click:

• Action: Show AI Assistant
• AI Agent: HR Copilot
• Service: Application Default
• Display As: Dialog
• Title: HR Copilot
• Welcome Message: Ask about department salary benchmarks, staffing levels, or this employee's profile context.

Run the page, open an employee record, and click the button. The assistant dialog should appear and use the configured agent.

Try prompts such as:

• What is the average salary in the Sales department?
• Which department has the highest average salary?
• How many employees are in Finance?
• Compare this employee's department with the company average.

The agent should call the Retrieve Data tool when the answer requires database metrics.

Step 8: Use PL/SQL for More Control

The declarative shared-component approach is ideal for most application experiences. APEX 26.1 also enhances the APEX_AI package so developers can build ad-hoc AI behavior in PL/SQL.

That is useful when you need to:

• Compose tools dynamically.
• Run an agent-like workflow inside a package.
• Use AI as part of a controlled back-office process.
• Return structured output to another PL/SQL routine.
• Keep the interaction out of the browser UI.

For example, you may use a declarative Gemini-backed AI Agent for an employee-facing assistant, while using APEX_AI.GENERATE with JSON output for a scheduled classification or summarization process.

Step 9: Consider JSON Output for System Workflows

APEX 26.1 adds stronger support for structured AI output. Instead of asking a model to “return JSON” and hoping it follows instructions, you can define a JSON schema for supported use cases.

Use structured output when the result needs to feed another process, such as:

• Classifying support tickets.
• Extracting tags from a description.
• Producing a risk score and explanation.
• Returning fields for a draft workflow request.
• Summarizing a document into a fixed response shape.

For conversational assistant dialogs, plain text usually works best. For process automation, JSON is safer and easier to validate.

Production Checklist

Before releasing an AI Agent to users, review:

• Data access: Does every tool use approved tables, views, or APIs?
• Authorization: Can only the right users open the page and call the agent?
• Tool scope: Does each tool do one clear job?
• Prompt quality: Does the system prompt define boundaries and expected behavior?
• Approval: Do sensitive tools require user confirmation?
• Consent: Does the application clearly communicate AI usage?
• Observability: Can administrators review token usage, errors, and tool behavior?
• Gemini model choice: Is the selected model appropriate for latency, privacy, cost, and accuracy?
• Fallbacks: What should users see if the AI provider is unavailable?

AI inside APEX should feel powerful, but it should also feel governed. The best agents are not the ones with unlimited access. They are the ones with the right access.

Final Thoughts

APEX 26.1 makes AI Agents practical for real business applications because it keeps the developer in control. The model can reason over a request, but the application defines what the model can actually do.

That is the right pattern for enterprise AI: conversational where it helps, declarative where it matters, and grounded in Oracle Database where the business data already lives.

Start small. Build one agent, give it one trusted tool, test the behavior with real user questions, and expand only when the value is clear.

References

• Build Your First AI Agent in Oracle APEX by MaxAPEX
• What’s New in Oracle APEX 26.1
• Oracle APEX 26.1 Release Notes
• Managing AI Agents and AI Tools
• APEX_AI API Reference