💡
The maximum number of characters you can enter in the static System Prompt field is 4,000 characters. Interestingly, you can exceed this 4,000-character limit by adding content to the system prompt through page item substitutions.
You can supplement the system prompt with additional session state context by referencing substitution strings like below:
```markdown
# Session Context
Current User: &APP_USER.
Current User Timezone: &AI_USER_TZ.
```
You can also use server-side [template directives](https://docs.oracle.com/en/database/oracle/apex/26.1/htmdb/using-template-directives.html#GUID-596537AB-1697-4704-9193-102658452399) 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.
![APEX AI Agent Augmented System Prompt](https://cdn.hashnode.com/uploads/covers/626b62127d5d27b992e4cf90/f1c1719a-59d6-431a-8f07-92fc63ce8716.png align="center")
![APEX AI Agent Augmented System Prompt - JavaScript Session Context](https://cdn.hashnode.com/uploads/covers/626b62127d5d27b992e4cf90/dfce9191-ef1c-4341-ab33-7b870521625b.png align="center")
```javascript
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.
![APEX 26.1 AI Agent Tools Screenshot](https://cdn.hashnode.com/uploads/covers/626b62127d5d27b992e4cf90/22aa8efe-9fa0-43bb-9248-39b9f1c6115e.png align="center")
### 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:
![APEX AI Agents Augment System Prompt SQL](https://cdn.hashnode.com/uploads/covers/626b62127d5d27b992e4cf90/5a7c1073-3ad8-49cb-94cb-76a69d183e34.png align="center")
This is appended to the system prompt as:
````markdown
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.
![Openai log showing augmented system promot](https://cdn.hashnode.com/uploads/covers/626b62127d5d27b992e4cf90/74918c3e-7784-495a-ba68-9efbfbe970f8.png align="center")
### 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
![APEX Agent Example Tool Retrieve Data](https://cdn.hashnode.com/uploads/covers/626b62127d5d27b992e4cf90/eee17668-f2f2-410e-8f09-3982d79e7971.png align="center")
* **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.
![APEX Agent Example Tool Retrieve Data - 2](https://cdn.hashnode.com/uploads/covers/626b62127d5d27b992e4cf90/38a5c472-146e-4fc9-a000-b0d9da28dd3d.png align="center")
* **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.
![](https://cdn.hashnode.com/uploads/covers/626b62127d5d27b992e4cf90/14eadc21-ef9b-4998-a70f-2802ce78dc90.png align="center")
* **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.
![](https://cdn.hashnode.com/uploads/covers/626b62127d5d27b992e4cf90/a2ba3c44-503a-48ac-a706-09415647a85d.png align="right")
* **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](https://docs.oracle.com/en/database/oracle/apex/26.1/aeapi/APEX_AI.html) 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.
![APEX AI Agent Launch in Modal Dialog](https://cdn.hashnode.com/uploads/covers/626b62127d5d27b992e4cf90/e6593c73-5d3f-4390-bfef-42c0e8b3ba70.png align="center")
**Setup**
![APEX AI Agent Launch from Modal Setup](https://cdn.hashnode.com/uploads/covers/626b62127d5d27b992e4cf90/a3c91358-c83e-481c-9957-f8999bf46456.png align="center")
* 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.
![APEX AI Agent Launch in a Region](https://cdn.hashnode.com/uploads/covers/626b62127d5d27b992e4cf90/71737b98-b96d-4879-9cfe-a4d263705e9f.png align="center")
**Setup**
![APEX AI Agent Launch in a Region - Setup 1](https://cdn.hashnode.com/uploads/covers/626b62127d5d27b992e4cf90/ed7aabe5-54d4-4003-a6bf-9e61ab277e75.png align="center")
* Create a region and give it a value for HTML DOM ID.
![APEX AI Agent Launch in a Region - Setup 2](https://cdn.hashnode.com/uploads/covers/626b62127d5d27b992e4cf90/d55a0a03-c70c-40bc-9caf-349e2aa058eb.png align="center")
* 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](https://docs.oracle.com/en/database/oracle/apex/26.1/aeapi/APEX_AI.GENERATE-Function-Signature-1.html) 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](https://blog.cloudnueva.com/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](https://blog.cloudnueva.com/ai-agents-need-boundaries-not-bigger-prompts).
# 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.