_______________
What is a Generative AI Agent in APEX 26.1
Oracle APEX 26.1 introduced a completely new concept called a Generative AI Agent. This is one of the headline new features of the 26.1 release and it changes how you can build intelligent database driven applications. At its core it is a configured AI assistant that lives inside your APEX application and can have natural language conversations with your users. What makes it different from a simple chatbot is the tool system. You can give the agent access to your database through tools and the AI will automatically decide when to query data and what to return based on whatever the user asks.
The agent is a Shared Component which means you define it once and then reuse it anywhere in your application through multiple mechanisms. You can display it using the Show AI Assistant Dynamic Action, generate text with it using the Generate Text With AI Dynamic Action, trigger it from a page process using the Generate Text With AI Page Process which is also new in 26.1, or call it directly from PL/SQL using APEX_AI.generate or APEX_AI.chat.
Each agent has a System Prompt, a Welcome Message, a Temperature setting, a Response Format setting which is new in 26.1, and a collection of Tools. The Augment System Prompt execution point existed before 26.1 under the name RAG Sources and was renamed in this release. The On Demand execution point is completely new in 26.1.
_______________
Tool Execution Points Compared
Augment System Prompt
- Executes always, before every REST request
- Purpose: Provide additional context (RAG)
- Result role: System
- Has Parameters: No
- User Approval Support: No
- Notification Support: No
- Executes when invoked by the LLM
- Purpose: Provide additional context (RAG) or Perform Tasks
- Result role: Tool
- Has Parameters: Yes
- User Approval Support: Yes
- Notification Support: Yes
Retrieve Data - Language: SQL or PL/SQL Function Body - Use for fetching data to return to the AI as context or results
Execute Server-side Code - Language: PL/SQL - Use for running tasks like sending email, starting a workflow, or writing to a table
Execute Client-side Code - Language: JavaScript - Use for browser side tasks like getting user location, refreshing a report region, or reading page state
_______________Tip: For Execute Server-side Code tools you can use APEX_AI.set_tool_result inside your PL/SQL to override the default tool result returned to the AI.
Setting Up the Sales Analytics Agent
Navigate to Shared Components then Generative AI then AI Agents and click Create.
Agent Configuration
- Name: Sales Analytics Agent
- Static ID: sales-analytics-agent
- Service: Openai Chatgpt
- Response Format: Text
- Temperature: 0.3
Code: Select all
You are a Sales Analytics Assistant for this demo application.
Answer questions about sales, revenue, products, customers and stores
using the available tools.
Always address the logged-in user by their first name using context
provided by get_context tool.
When user asks about their own orders or spending, filter data using
their email from the context.
For general sales questions use On Demand tools.
Always present data in clean formatted tables.
Use $ prefix for all currency values.
Keep responses concise and professional.
Augment System Prompt Tools
Three Augment tools are configured on this agent. Each one runs before every message and their results combine to give the AI a rich picture of the current user and business state before any question is asked.
Tool 1: get_context
This is a Function Body tool that looks up the logged in user by their APEX session username from the CUSTOMERS table using APP_USER, returns their full name, email, and personal order history, and instructs the AI to address them by first name in every response.
- Execution Point: Augment System Prompt
- Type: Function Body
- Max Tokens: 500
Code: Select all
DECLARE
l_clob CLOB;
l_full_name VARCHAR2(255);
l_email VARCHAR2(255);
l_order_count NUMBER;
l_total_spent NUMBER;
l_err VARCHAR2(4000);
BEGIN
SELECT FULL_NAME, EMAIL_ADDRESS
INTO l_full_name, l_email
FROM CUSTOMERS
WHERE UPPER(EMAIL_ADDRESS) = UPPER(V('APP_USER'));
SELECT COUNT(DISTINCT ORDER_ID), NVL(SUM(ORDER_TOTAL), 0)
INTO l_order_count, l_total_spent
FROM CUSTOMER_ORDER_PRODUCTS
WHERE UPPER(EMAIL_ADDRESS) = UPPER(V('APP_USER'));
INSERT INTO AI_TOOL_LOG (TOOL_NAME, EXECUTED_BY, PARAMETERS)
VALUES ('get_context', V('APP_USER'), 'Augment System Prompt');
COMMIT;
l_clob :=
'=== CURRENT USER CONTEXT ===' || CHR(10) ||
'Logged In User : ' || l_full_name || CHR(10) ||
'Email : ' || l_email || CHR(10) ||
'Current Date : ' || TO_CHAR(SYSDATE, 'DD-Mon-YYYY HH24:MI') || CHR(10) ||
CHR(10) ||
'=== USER ORDER HISTORY ===' || CHR(10) ||
'Total Orders : ' || l_order_count || CHR(10) ||
'Total Spent : $'|| TO_CHAR(l_total_spent,'999,999,990.00') || CHR(10) ||
CHR(10) ||
'=== INSTRUCTIONS ===' || CHR(10) ||
'Always address the user by first name: ' || l_full_name || CHR(10) ||
'When user asks about my orders filter by email: ' || l_email || CHR(10) ||
'For general sales questions use the On Demand tools.';
RETURN l_clob;
EXCEPTION
WHEN NO_DATA_FOUND THEN
l_err := 'Customer not found for: ' || V('APP_USER');
INSERT INTO AI_TOOL_LOG (TOOL_NAME, EXECUTED_BY, PARAMETERS)
VALUES ('get_context', V('APP_USER'), l_err);
COMMIT;
RETURN 'Current User: ' || NVL(V('APP_USER'), 'Guest') || CHR(10) ||
'Current Date: ' || TO_CHAR(SYSDATE, 'DD-Mon-YYYY HH24:MI');
WHEN OTHERS THEN
l_err := SQLERRM;
INSERT INTO AI_TOOL_LOG (TOOL_NAME, EXECUTED_BY, PARAMETERS)
VALUES ('get_context', V('APP_USER'), l_err);
COMMIT;
RETURN 'Context unavailable: ' || l_err;
END;
Tool 2: get_alertsSince APEX workspace users are typically not customer email addresses, insert a matching row in the CUSTOMERS table for any APEX user you want to test with. For example if you log in as ADMIN then insert a customer with EMAIL_ADDRESS equal to ADMIN.
This tool scans for products with no completed orders in the last three months and stores with no activity in the last 30 days. The results are injected into the system prompt so the AI proactively mentions relevant alerts.
- Execution Point: Augment System Prompt
- Type: Function Body
- Max Tokens: 500
This tool returns only the stores the current logged in user has previously placed orders at. It demonstrates role based data access using APP_USER.
- Execution Point: Augment System Prompt
- Type: SQL Query
- Max Tokens: 500
Code: Select all
SELECT S.STORE_NAME, S.PHYSICAL_ADDRESS, S.WEB_ADDRESS
FROM STORES S
WHERE S.STORE_ID IN (
SELECT DISTINCT O.STORE_ID
FROM ORDERS O
JOIN CUSTOMERS C ON C.CUSTOMER_ID = O.CUSTOMER_ID
WHERE UPPER(C.EMAIL_ADDRESS) = UPPER(V('APP_USER'))
)
ORDER BY S.STORE_NAME
On Demand Tools
Five On Demand tools cover the main sales analytics scenarios. Each tool is SQL Query type which is the standard APEX 26.1 approach. The Description field is critically important because the AI uses it to decide which tool to call.
Tool 4: get_sales_by_store
- Execution Point: On Demand
- Type: SQL Query
- Max Tokens: 500
- Description: Use this tool when the user asks about sales performance by store, store revenue, store comparison, which store sells the most, or any location based sales question.
- Data Description: Returns total sales broken down by store location including complete, paid and shipped sales amounts, total revenue and total order count per store, ordered by highest revenue first.
Code: Select all
SELECT
STORE_NAME,
ADDRESS,
SUM(CASE WHEN ORDER_STATUS = 'COMPLETE' THEN TOTAL_SALES ELSE 0 END) AS COMPLETE_SALES,
SUM(CASE WHEN ORDER_STATUS = 'PAID' THEN TOTAL_SALES ELSE 0 END) AS PAID_SALES,
SUM(CASE WHEN ORDER_STATUS = 'SHIPPED' THEN TOTAL_SALES ELSE 0 END) AS SHIPPED_SALES,
SUM(TOTAL_SALES) AS TOTAL_SALES,
SUM(ORDER_COUNT) AS TOTAL_ORDERS
FROM STORE_ORDERS_STATUS
GROUP BY STORE_NAME, ADDRESS
ORDER BY SUM(TOTAL_SALES) DESC
- Execution Point: On Demand
- Type: SQL Query
- Parameter: P_LIMIT (NUMBER)
- Max Tokens: 500
- Description: Use this tool when the user asks about top selling products, best performing products, highest revenue products, or product rankings. Pass P_LIMIT based on user request, default to 10 if not mentioned.
- Data Description: Returns top products ranked by total completed sales revenue, including product name, order status, total sales amount and order count.
Code: Select all
SELECT PRODUCT_NAME, ORDER_STATUS, TOTAL_SALES, ORDER_COUNT
FROM PRODUCT_ORDERS
WHERE ORDER_STATUS = 'COMPLETE'
ORDER BY TOTAL_SALES DESC
FETCH FIRST NVL(:P_LIMIT, 10) ROWS ONLY
- Execution Point: On Demand
- Type: SQL Query
- Parameter: P_MONTHS (NUMBER)
- Max Tokens: 500
- Description: Use this tool when the user asks about monthly revenue, revenue trends, sales over time, monthly performance, or any time based sales analysis. Pass P_MONTHS based on user request, default to 12 if not mentioned.
- Data Description: Returns monthly revenue summary including month, order count, total revenue and average order value, excluding cancelled and refunded orders, ordered by most recent month first.
Code: Select all
SELECT
TO_CHAR(O.ORDER_DATETIME, 'Mon-YYYY') AS MONTH,
TO_CHAR(O.ORDER_DATETIME, 'YYYY-MM') AS SORT_KEY,
COUNT(DISTINCT O.ORDER_ID) AS ORDER_COUNT,
SUM(OI.UNIT_PRICE * OI.QUANTITY) AS REVENUE,
ROUND(AVG(OI.UNIT_PRICE * OI.QUANTITY), 2) AS AVG_ORDER_VALUE
FROM ORDERS O
JOIN ORDER_ITEMS OI ON OI.ORDER_ID = O.ORDER_ID
WHERE O.ORDER_DATETIME >= ADD_MONTHS(SYSDATE, -NVL(:P_MONTHS, 12))
AND O.ORDER_STATUS NOT IN ('CANCELLED','REFUNDED')
GROUP BY
TO_CHAR(O.ORDER_DATETIME, 'Mon-YYYY'),
TO_CHAR(O.ORDER_DATETIME, 'YYYY-MM')
ORDER BY SORT_KEY DESC
- Execution Point: On Demand
- Type: SQL Query
- Parameter: P_LIMIT (NUMBER)
- Max Tokens: 500
- Description: Use this tool when the user asks about top customers, customer spending, best customers, customer order history, or customer rankings. Pass P_LIMIT based on user request, default to 10 if not mentioned.
- Data Description: Returns top customers ranked by total spend including full name, email, total orders, total amount spent, average order value and last order date, excluding cancelled and refunded orders.
Code: Select all
SELECT
FULL_NAME,
EMAIL_ADDRESS,
COUNT(DISTINCT ORDER_ID) AS TOTAL_ORDERS,
SUM(ORDER_TOTAL) AS TOTAL_SPENT,
ROUND(AVG(ORDER_TOTAL), 2) AS AVG_ORDER_VALUE,
MAX(ORDER_DATETIME) AS LAST_ORDER_DATE
FROM CUSTOMER_ORDER_PRODUCTS
WHERE ORDER_STATUS NOT IN ('CANCELLED','REFUNDED')
GROUP BY FULL_NAME, EMAIL_ADDRESS
ORDER BY TOTAL_SPENT DESC
FETCH FIRST NVL(:P_LIMIT, 10) ROWS ONLY
- Execution Point: On Demand
- Type: SQL Query
- Parameter: P_PRODUCT_NAME (VARCHAR2, optional)
- Max Tokens: 1000
- Description: Use this tool when the user asks about product reviews, product ratings, customer feedback, best rated products, or worst rated products. Pass P_PRODUCT_NAME to filter by specific product, leave empty for all products.
- Data Description: Returns product review summary including product name, average rating, review count, minimum and maximum rating and all review texts, ordered by highest average rating first.
Code: Select all
SELECT
PRODUCT_NAME,
AVG_RATING,
COUNT(RATING) AS REVIEW_COUNT,
MIN(RATING) AS MIN_RATING,
MAX(RATING) AS MAX_RATING,
LISTAGG(REVIEW, ' | ')
WITHIN GROUP (ORDER BY RATING DESC) AS REVIEWS
FROM PRODUCT_REVIEWS
WHERE UPPER(PRODUCT_NAME) LIKE '%' || UPPER(NVL(:P_PRODUCT_NAME, PRODUCT_NAME)) || '%'
GROUP BY PRODUCT_NAME, AVG_RATING
ORDER BY AVG_RATING DESC
Logging Tool Execution
A logging table captures which tools executed during a conversation. The Augment tools log reliably because they run on every message.
Log Table DDL
Code: Select all
CREATE TABLE AI_TOOL_LOG (
LOG_ID NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
TOOL_NAME VARCHAR2(100) NOT NULL,
EXECUTED_BY VARCHAR2(255),
EXECUTED_AT TIMESTAMP DEFAULT SYSTIMESTAMP NOT NULL,
PARAMETERS VARCHAR2(4000)
);
This tool is for illustration purposes to show how Execute Server-side Code tools work and how parameters are passed from the AI to PL/SQL.
- Type: Execute Server-side Code
- Execution Point: On Demand
- Parameters: P_TOOL_NAME (VARCHAR2), P_PARAMETERS (VARCHAR2)
Code: Select all
BEGIN
INSERT INTO AI_TOOL_LOG (TOOL_NAME, EXECUTED_BY, PARAMETERS)
VALUES (:P_TOOL_NAME, V('APP_USER'), :P_PARAMETERS);
COMMIT;
END;
_______________To demonstrate this tool directly use a prompt like: Log tool as ERPstuff Tool with parameter Sikandar Hayat. The AI will call log_tool_execution and you can query the AI_TOOL_LOG table immediately to see the inserted row.
Tool Configuration Options Worth Knowing
- Requires Confirmation - User Approval section - Turn on for any tool that writes to the database so the AI asks the user before executing
- Notification Message - Notification section - Set a friendly message like Fetching sales data that shows while the tool runs
- Server-Side Condition - Server-Side Condition section - Use to disable a tool temporarily with Never or restrict it to certain pages
- Authorization Scheme - Security section - Restrict a tool to specific APEX roles so only managers can see certain data
- Build Option - Advanced section - Tag a tool as DEV ONLY to exclude it from production builds automatically
Advanced: Request and Response Handlers
APEX 26.1 also introduced application level Request and Response Handlers for all AI activity. You configure these under Shared Components then Application Definition then the AI tab. Unlike tools which are scoped to a specific agent, these handlers apply to every AI interaction across the entire application.
The Request Handler fires before any outgoing request is sent to the AI service. The Response Handler fires after the AI service returns a response. This is the most reliable way to implement comprehensive audit logging for all AI activity because these handlers always fire without exception.
Code: Select all
CREATE OR REPLACE PROCEDURE app_ai_request_handler (
p_param IN apex_ai.t_chat_request_handler_param,
p_result IN OUT NOCOPY apex_ai.t_chat_request_handler_result )
AS
BEGIN
-- inspect p_param.component, p_result.request
-- log or modify the outgoing request here
END;
Code: Select all
CREATE OR REPLACE PROCEDURE app_ai_response_handler (
p_param IN apex_ai.t_chat_response_handler_param,
p_result IN OUT NOCOPY apex_ai.t_chat_response_handler_result )
AS
BEGIN
-- inspect p_param.component, p_result.response
-- log or modify the incoming response here
END;
_______________After creating these procedures go to Shared Components then Application Definition then the AI tab and register them. Once registered every AI request and response in your application passes through these handlers giving you full visibility and control.
Test Prompts
Use these prompts in order to test the agent. You can also load these as an LOV in your APEX page so users can pick from a list rather or type.
- Hello, who am I?
- Any alerts I should know about?
- Which stores can I access?
- Show me top 5 products by revenue
- What is revenue for last 3 months?
- Show reviews for Women's Jeans
- Who are my top 10 customers?
- Which store has the highest sales?
- Show me revenue for last 12 months
- What are my total orders and how much have I spent?
- Log tool as ERPstuff Tool with parameter Sikandar Hayat
- Hello, who am I - Personalization from get_context (Augment)
- Any alerts - Proactive alert injection from get_alerts (Augment)
- Which stores can I access - Role based context from get_store_access (Augment)
- Top 5 products - Parameterized On Demand call with P_LIMIT=5
- Revenue last 3 months - Date filtered aggregation with P_MONTHS=3
- Reviews for Women's Jeans - Product filter with P_PRODUCT_NAME
- Top 10 customers - Customer ranking with P_LIMIT=10
- Highest sales store - Store comparison with no parameters
- Revenue last 12 months - Full year trend with P_MONTHS=12
- Total orders and spent - Personal history answered from Augment context with no tool call
- Log tool prompt - Directly demonstrates Execute Server-side Code tool with parameters
Key Lessons
On Demand is completely new in 26.1. Augment System Prompt existed before under the name RAG Sources. On Demand is the genuinely new capability in this release. It is what makes the agent intelligent rather than just a context injection mechanism.
SQL Query is the right default. Every On Demand tool in this demo is a SQL Query type. This is the standard APEX way and it keeps things simple. Only use Function Body when you genuinely need PL/SQL logic like the get_context personalization.
The Description field is the most important thing you write. The AI uses the tool description to decide when to call it. Write descriptions that match real user language not technical database language.
Augment tools are guaranteed, On Demand tools are not. Never put critical business logic or mandatory audit trails in On Demand tools. The AI calls them when it decides to.
Use Request and Response Handlers for real audit logging. The log_tool_execution tool in this demo is for illustration only. In a production application register Request and Response Handler procedures under Application Definition.
One tool call per response is the typical behavior. OpenAI processes tool calls sequentially and stops once it has enough data to answer. Do not design your agent assuming the AI will always chain multiple tool calls.
The system prompt and Augment tools work together. Think of the system prompt as the permanent instructions and the Augment tools as the live data injected on top at runtime.
_______________
ERPstuff.com | admin@erpstuff.com | Oracle ACE Pro | oracle-apex-26-1-generative-ai-agent-sales-analytics