Introduction

I have built several agents using custom PL/SQL code while waiting for the release of APEX 26.1. Now that APEX 26.1 has been released, it is time to see how useful AI Agents in APEX 26.1 will be. To test it out, I created a realistic demo for managing the dispatch of technicians to work orders.

The goal of this post is to discuss the features of APEX 26.1 AI Agents in the context of a realistic use case. It is not intended to be a step-by-step guide.

Use Case

My use case is a technician scheduling agent. This agent helps a dispatcher manage jobs/cases and technicians. The agent will have access to the following tools:

• Customer Case Summary

• Asset Lookup

• Technician Recommendation

• Search Open Cases

• Upsert a Case

• Upsert a Case Visit

What Is an APEX AI Agent?

AI agents are essentially a loop. APEX sends the conversation context and available tool definitions to the LLM. The LLM can request that a tool be called. APEX executes the tool, returns its result to the LLM, and repeats the process until the LLM produces a final response.

The good news is that APEX AI Agent functionality manages the agent loop for you. This can save you hundreds of lines of code compared to writing this yourself.

Setup

APEX Workspace Setup

APEX Web Credential

The first step is to set up an APEX Web Credential to store the API key for your AI Provider.

I will be using OpenAI in all of my examples.

The example above is for OpenAI. For OpenAI, set the Credential Name to Authorization. Set the Credential Secret to Bearer, followed by one space and the API key.

Example API key in the Credential Secret field:
Bearer www-6AZH0UP3-mwzWqbYxobKw56pa4SAM...

For Anthropic, you need to pass an HTTP Header called x-api-key, which goes in the Credential Name field, and the raw API token, which goes in the Credential Secret field. Do not include a "Bearer" prefix; the Credential Secret must contain only the API token itself.

Example API token in the Credential Secret field: sk-ant-api03-6AZH0UP3-mwzWqbYxobKw56pa4SAM...

Generative AI Service Setup

Next is the Generative AI Service. Select your AI Provider, choose the APEX Web Credential you created above, and enter the model name.

A Note About OpenAI Chat Completions vs. Responses

For its OpenAI integration, APEX 26.1 currently sends requests to the Chat Completions API rather than the newer Responses API
(introduced in March 2025). Per OpenAI's documentation, the company recommends using the Responses API.

The Responses API is our new API primitive, an evolution of Chat Completions which brings added simplicity and powerful agentic primitives to your integrations. While Chat Completions remains supported, Responses is recommended for all new projects.

This may not sound like a big deal, but it has real-world implications. The current APEX/OpenAI integration does not provide a declarative way to set the reasoning effort for newer OpenAI reasoning models. As a result, the model uses its default reasoning setting, which can affect response quality, latency, and cost. The default varies by model, so check the OpenAI documentation for the specific model you are using.

I created a forum post on this topic, and Oracle has said it is on the roadmap to switch to the Responses API. I recommend subscribing to the forum post for updates.

Agent Setup

Generative AI Tab

Service

This is where we reference the AI Service we created earlier.

System Prompt

This is where we give the agent its identity and purpose. I recommend you develop this with the help of ChatGPT or Claude. Start with something like:
“Create a system prompt for an AI agent that manages service requests and schedules service technicians. Ask me for any missing business rules, tool capabilities, security restrictions, and approval requirements before drafting the prompt.”

You can supplement the system prompt with additional session state context by referencing substitution strings like below:

# Session Context
Current User: &APP_USER.
Current User Timezone: &AI_USER_TZ.

You can also use server-side template directives in the system prompt. Template Directives allow you to add IF and CASE logic to conditionally include context to the system prompt.

You can also augment the system prompt by using an Augmented System Prompt tool. In the example below, I am showing a Client-side (JavaScript) tool that fetches the APEX Username, current date, and user timezone.

const userName = apex.env.APP_USER;
const timeZone = Intl.DateTimeFormat().resolvedOptions().timeZone;
const dateParts = new Intl.DateTimeFormat(navigator.language, {
  timeZone,
  year: 'numeric',
  month: '2-digit',
  day: '2-digit'
}).formatToParts(new Date());

const year  = dateParts.find(part => part.type === 'year').value;
const month = dateParts.find(part => part.type === 'month').value;
const day   = dateParts.find(part => part.type === 'day').value;

const prompt =
  `Current Username: ${userName}. Today's Date: ${year}-${month}-${day}. User's Time Zone: ${timeZone}.`;
return prompt;

LLM prompt caching lets the provider reuse previously processed tokens when the beginning of a prompt is identical or mostly identical across requests, reducing latency and cost for repeated static context.

Put variable values at the end of the prompt because caching usually works from the prefix forward: if you change early text, the cache match breaks sooner; if you keep stable instructions, examples, schemas, and reference material first, then append request-specific values last, more of the prompt remains cacheable.

Welcome Message

Enter a welcome message. This is only applicable for chatbot-style agents.

Tools

With the available tools, the dispatcher can ask questions such as “Which technician should handle this case?”, “Show me open cases near San Diego,” or “Schedule a visit for tomorrow morning.” The agent then decides whether it needs to look up cases, inspect assets, recommend a technician, or create/update visit records.

Tools make agents both useful and actionable.

Augment System Prompt Tools

I already covered augmented system prompt tools, but here is an example that augments the system prompt using a SQL query to get the user's database session timezone offset:

This is appended to the system prompt as:

The time zone of the user’s session.
```csv
sessiontimezone
"	+00:00"
```

If you capture logs from your LLM provider, you can see what APEX is actually sending to the LLM. The example below is from an OpenAI log that shows the augmented system prompt from my Client-side example above. The augmented system prompt is passed as a separate 'System' message.

On Demand Tools

Eligible on-demand tool definitions are included in each model request made during the agent loop. A single user turn may result in multiple model requests and tool executions.

On-demand tools provide the LLM with tools it can call to help answer a user's questions. If the LLM thinks a tool will help answer the question, it will request that APEX execute the tool and return the results.

There are three types of tools:

• Retrieve Data

  • Enter a SQL statement. If the LLM selects the tool, APEX executes the SQL and returns the result to the LLM as CSV content.

• Execute Server-side code

  • Enter a PL/SQL block that APEX executes when the LLM selects the tool. By default, the tool returns a generic success result to the LLM. Use apex_ai.set_tool_result when you need to return a custom result, display a notification, report an error, or stop the agent loop early.

• Execute Client-side code

  • Enter JavaScript code. If the LLM selects the tool, APEX runs the JavaScript in the user's browser.

Example Retrieve Data Tool

• Description: The tool description is sent to the LLM and the LLM will use this plus the parameter descriptions to decide if the tool would be useful. Make sure the description clearly states what the tool does without being overly lengthy.

• Parameters: Tool parameters are passed into the SQL statement as bind variables. It is important to ensure you provide clear, brief descriptions of each parameter so the LLM knows what values to pass. Providing Allowed Values helps guide the LLM toward valid parameter values. You must still validate all values in the SQL or PL/SQL implementation.

• Data Description: This is passed to the LLM to provide additional information on what data to expect to receive back after calling the tool.

• Type:

  • SQL Query Data is sourced from a SQL Query from the local database. Data is returned to the LLM in CSV format.

  • Function Body Data is sourced from a Function Body that returns a CLOB. This is the way to go if you want to return data formatted in JSON or if you have existing code that fetches data in an alternative format.

  • Static User-defined static text, suitable for entering hard-coded text information that is not sourced from your database.

• User Approval: If you turn this switch on, the agent will open a popup and the user must confirm they want to proceed. This is a great way to keep a human in the loop for tools that create/update or delete data.

  

• Notification: If you provide text for a notification, the agent will display the notification before the response. This is a nice way to show the user which tools are being called.

  

• Server Side Condition: This is a standard APEX Server Side condition. If the condition is not met, the tool will not be sent to the LLM.

• Security - Authorization Scheme: This is a standard APEX Authorization scheme. If the Authorization Scheme returns false then the tool will not be sent to the LLM.

Agent Usage

Once an agent is defined, it can be used either as a chatbot or via the PL/SQL APEX_AI API.

Chatbot

There are two options for deploying Agents as a chatbot.

In a Modal

You can launch your agent based on a Trigger Action on a button.

Setup

• Create a button with a Trigger Action of 'Show AI Assistant'.

• Select your agent definition in the Agent field.

• Under Appearance, select Display As > Dialog.

In a Region

You can also launch by identifying the region you want it to appear in and launching it from a Dynamic Action. In the example below I launch the agent on page load but it could be based on a different Dynamic Action event.

Setup

• Create a region and give it a value for HTML DOM ID.

• Create an On Page Load Dynamic Action of 'Show AI Assistant'.

• Appearance

  • Display As > Inline

  • Container Selector > The HTML DOM ID from the previous step prefixed with the # selector. e.g. #agent-region

PL/SQL API

If you don't require user interaction, you can call an agent using the apex_ai.generate function.

When calling the agent from PL/SQL, there is no interactive user confirmation step, so tool options like Requires Confirmation are not considered. This means you should not rely on Requires Confirmation as your only safety control if the same agent can also be called from PL/SQL. Use authorization schemes, server-side conditions, and validation in your PL/SQL tools.

Take a look at my previous post Using an APEX AI Agent to Turn Purchase Orders into JSON for details on how this works.

The API is a great option for performing agentic actions from a workflow or a background process.

Lessons Learned

• Tool and parameter descriptions matter. If the descriptions are vague, the LLM ends up calling the wrong tool and passing the wrong parameters.

• API validations should return useful information to both the model and the user. Use apex_ai.set_tool_result to provide a contextual tool result through p_result, and use p_notification_message when the user should also see a notification. For example, return “Service Case XYZ could not be found. It may not exist, or you may not have access to it” instead of “Service Case not found.” This gives both the model and the user something they can act on.

  • For errors that the LLM cannot resolve, consider setting p_early_exit => true. This prevents APEX from sending the failed tool result back to the model for another iteration, reducing latency and avoiding an unnecessary API request.

• Treat database content, uploaded documents, API responses, and user-generated text as untrusted tool output. Leave p_is_safe at its default value unless the returned content is fully controlled and cannot contain prompt-injection instructions.

• Authorization schemes and server-side conditions are not optional. They are how you keep tools scoped to the right users and contexts. If you are adding an agent to an existing application, reuse existing authorization schemes where applicable.

• The human approval feature in APEX AI agents is useful for interactive chatbot flows, but it is not a substitute for validation when agents are called from PL/SQL. You are responsible for security, not the LLM.

• The APEX agent loop removes a lot of custom code, but you still need to design the tools carefully. Tool design is as important as any other aspect of APEX design.

• Augment your system prompt with useful session context. Most LLMs do not reliably know today’s date. I always pass today's date, the user's session timezone, and the user's name. This allows the model to perform relative date and time calculations, and address the user by name.

• Manage LLM context carefully. As data volumes and the number of agent-loop iterations increase, the context sent to the LLM also grows. Bloated context increases token usage and latency and can make it harder for the model to identify the most relevant instructions and data. It is up to you (not the LLM) to manage context. I cover context in detail in this post.

Conclusion

APEX 26.1 AI Agents provide a practical way for APEX developers to add tool-calling workflows to business applications without implementing the agent loop themselves.

APEX handles the repeated exchange between the LLM and application tools, but developers remain responsible for the difficult parts: defining focused tools, validating inputs, enforcing authorization, controlling data access, and deciding where human approval is required.

The strongest use cases extend beyond chat. An agent can retrieve application data, recommend an action, and perform a controlled business transaction using familiar SQL, PL/SQL, JavaScript, and APEX security components.

There are still limitations, particularly in the current OpenAI integration, but APEX 26.1 substantially lowers the amount of custom orchestration required to build useful agents.