APEX_AI is the PL/SQL interface to the AI services and vector providers of an APEX workspace. It calls the same providers as DBMS_VECTOR_CHAIN, but through APEX's configuration instead of database credentials: the service's provider, model, key, and token limit, and the application's handlers and consent, apply to every call. Use APEX_AI in APEX applications, and DBMS_VECTOR_CHAIN in database code that has nothing to do with APEX.
This guide covers APEX_AI in APEX 26.1: calling it outside a page, GENERATE with a system prompt and a JSON schema, CHAT for conversations, GET_VECTOR_EMBEDDINGS, and tools that the model calls and APEX runs.
Code for This Guide
The examples are files 01 to 07 in the examples/ch17 folder of the Oracle AI code repository on GitHub, each with its output. They use application 200 of the sample workspace, apex/f200.sql.
These examples come from AI Applications with Oracle Database 26ai and APEX 26.1, a book of 237 tested examples of AI in Oracle Database and APEX.
They call the workspace's Gemini service, static ID gemini, and the vector provider atlas-minilm. Services are set up as described in how to configure Generative AI services in Oracle APEX.
The Main Subprograms
| Subprogram | Purpose |
|---|---|
| GENERATE | A response to a prompt, from a service or an AI agent; with a system prompt, JSON schema, attachments, and tools |
| CHAT | A response within a conversation, whose messages it keeps |
| GET_VECTOR_EMBEDDINGS | An embedding, from a vector provider, an ONNX model, or a function |
| IS_ENABLED | Whether AI is enabled for the workspace |
| IS_USER_CONSENT_NEEDED, SET_USER_CONSENT, REVOKE_USER_CONSENT | The consent behind an application's consent message |
| SET_TOOL_RESULT | Returns the result of a tool call from a response handler |
Use APEX_AI Outside a Page
On a page, APEX_AI runs in the application's session and uses its settings. Elsewhere, in SQLcl or a scheduler job, it needs a session of the application whose settings it should use, which APEX_SESSION.CREATE_SESSION creates.
Example:
-- outside a page, APEX_AI needs a session of the application whose AI settings it uses
begin
apex_session.create_session(p_app_id => 200, p_page_id => 1, p_username => 'ADMIN');
end;
/
declare
l_answer clob;
begin
dbms_output.put_line('AI enabled: '
|| case when apex_ai.is_enabled then 'Yes' else 'No' end);
l_answer := apex_ai.generate(
p_prompt => 'Name the largest planet. One word.',
p_service_static_id => 'gemini');
dbms_output.put_line('Answer: ' || l_answer);
end;
/Output:
PL/SQL procedure successfully completed. AI enabled: Yes Answer: Jupiter PL/SQL procedure successfully completed.
The other examples create the same session first.
Generate Text with GENERATE
Syntax:
apex_ai.generate( p_prompt clob, p_system_prompt clob default null, p_service_static_id varchar2 default null, p_temperature number default null, p_attachments apex_ai.t_attachments default null, p_response_json_schema clob default null, p_tools apex_ai.t_tools default null, p_request_handler_procedure varchar2 default null, p_response_handler_procedure varchar2 default null, p_max_tool_roundtrips pls_integer default null) return clob
Without P_SERVICE_STATIC_ID, the application's default service is used. The options are provider-independent: APEX translates a system prompt, a temperature, and a JSON schema into each provider's own format.
Example:
select apex_ai.generate(
p_prompt => 'Can I get my money back for a duplicate charge?',
p_system_prompt => 'You are the support assistant of Atlas Software. '
|| 'Answer in one sentence of plain text. '
|| 'Refunds of duplicate charges are made '
|| 'by support as soon as they are reported.',
p_temperature => 0) as answer
from dual;Output:
ANSWER ________________________________________________________________________________________________ Yes, our support team will issue a refund for duplicate charges as soon as they are reported.
A JSON schema is written in standard JSON Schema rather than any provider's dialect.
Example:
select apex_ai.generate(
p_prompt => 'Classify this ticket: "Our invoice shows the wrong VAT '
|| 'rate since we moved to Germany."',
p_temperature => 0,
p_response_json_schema => '{
"type": "object",
"properties": {
"category": {"type": "string",
"enum": ["Account", "Billing", "Bug", "Question",
"Feature Request"]},
"product": {"type": "string"}},
"required": ["category", "product"]}') as answer
from dual;Output:
ANSWER
_______________________________________________
{"category":"Billing","product":"Invoicing"}GENERATE returns the JSON as text, and JSON_VALUE reads its fields.
Hold a Conversation with CHAT
CHAT is GENERATE for conversations. It takes the earlier messages in P_MESSAGES, adds the new question and answer to them, and sends the whole conversation each time, so the model can refer to what was said.
Syntax:
apex_ai.chat( p_prompt clob, p_system_prompt clob default null, p_service_static_id varchar2 default null, p_temperature number default null, p_messages in out nocopy apex_ai.t_chat_messages, ...) return clob
Example:
-- a conversation: CHAT adds each question and answer to the messages
declare
l_messages apex_ai.t_chat_messages := apex_ai.c_chat_messages;
l_system clob := 'You are the support assistant of Atlas Software. '
|| 'Answer in one sentence of plain text. '
|| 'Facts: card refunds take 5 to 10 business days; '
|| 'direct debit refunds take up to 3 business days.';
l_answer clob;
begin
l_answer := apex_ai.chat(p_prompt => 'How long does a refund to my card take?',
p_system_prompt => l_system, p_messages => l_messages);
dbms_output.put_line('1: ' || l_answer);
l_answer := apex_ai.chat(p_prompt => 'And by direct debit?',
p_system_prompt => l_system, p_messages => l_messages);
dbms_output.put_line('2: ' || l_answer);
dbms_output.put_line('Messages in the conversation: ' || l_messages.count);
end;
/Output:
1: A refund to your card takes 5 to 10 business days. 2: Direct debit refunds take up to 3 business days. Messages in the conversation: 4 PL/SQL procedure successfully completed.
The second answer understood "And by direct debit?", which means nothing on its own. After two turns, the conversation holds four messages, and every turn sends them all, so long conversations cost tokens in proportion to their length.
Embed Text with a Vector Provider
GET_VECTOR_EMBEDDINGS returns the embedding of a text from a workspace vector provider, a model in the database, or a function.
Syntax:
apex_ai.get_vector_embeddings(p_value clob, p_service_static_id varchar2) return vector
Example:
select vector_dimension_count(v) as dimensions,
round(vector_distance(v, (select embedding from kb_articles
where article_id = 'KB-401'), cosine), 3)
as distance_to_kb_401
from (select apex_ai.get_vector_embeddings(
p_value => 'The phone app closes right after I open it',
p_service_static_id => 'atlas-minilm') as v
from dual);Output:
DIMENSIONS DISTANCE_TO_KB_401
_____________ _____________________
384 0.549384 dimensions: the provider runs the same in-database model that embedded the stored articles, so the two can be compared. Code that embeds through the provider follows the workspace configuration when the model changes.
Give the Model Tools
A tool is a function the model may ask to call while it writes its answer. The model runs nothing: it replies with the tool's name and arguments, APEX runs the tool, sends the result back, and asks the model to continue. This is function calling, the mechanism behind AI agents.
A tool has a definition of type APEX_AI.T_TOOL, with a name, a description that tells the model when to use it, and its parameters, plus a callback procedure that APEX calls with the arguments.
Syntax:
procedure callback_name ( p_param in apex_ai.t_tool_exec_param, p_result in out nocopy apex_ai.t_tool_exec_result);
P_PARAM.ARGS_JSON holds the arguments, and the procedure puts its result, as text, in P_RESULT.RESULT. This example creates a tool that returns a ticket's status as JSON, or an error the model can understand, and a function that returns its definition.
Example:
-- a tool: the status of a ticket, for the model to call when it needs it
create or replace procedure ticket_status_tool (
p_param in apex_ai.t_tool_exec_param,
p_result in out nocopy apex_ai.t_tool_exec_result)
is
l_ticket_id number := p_param.args_json.get_number('ticket_id');
begin
select json_object('ticket_id' value ticket_id, 'subject' value subject,
'status' value status, 'priority' value priority,
'created_at' value to_char(created_at, 'YYYY-MM-DD'))
into p_result.result
from tickets
where ticket_id = l_ticket_id;
exception
when no_data_found then
p_result.result := '{"error": "There is no ticket ' || l_ticket_id || '."}';
end;
/
-- the tool's definition for the model: its name, what it does, and its parameters
create or replace function atlas_tools return apex_ai.t_tools
is
begin
return apex_ai.t_tools(
apex_ai.t_tool(
name => 'get_ticket_status',
description => 'Returns the subject, status, priority, and creation date '
|| 'of a support ticket',
parameters => apex_ai.t_tool_parameters(
apex_ai.t_tool_parameter(
name => 'ticket_id',
description => 'The number of the ticket',
data_type => apex_ai.c_tool_param_type_number)),
callback_procedure => 'ticket_status_tool'));
end;
/Output:
Procedure TICKET_STATUS_TOOL compiled Function ATLAS_TOOLS compiled
A Known Issue with Gemini
This example asks about ticket 15 with the tool.
Example (fails with Gemini in APEX 26.1):
-- the model may call the tool; APEX runs it and sends the result back
declare
l_answer clob;
begin
l_answer := apex_ai.generate(
p_prompt => 'What is the status of ticket 15, and what was it about?',
p_system_prompt => 'You are the support assistant of Atlas Software. '
|| 'Answer in one sentence of plain text.',
p_tools => atlas_tools);
dbms_output.put_line(l_answer);
end;
/Output:
declare * ERROR at line 1: ORA-20954: The HTTP request to Generative AI Service at https://generativelanguage.googleapis.com/v1beta/models/gemini-flash-latest:generateContent failed with HTTP-400: 400: Role 'function' is not supported. Please use a valid role: SYSTEM, SYSTEM_1, USER, ASSISTANT, DEVELOPER, CONTEXT, USER_CONTEXT, MODEL, USER. ORA-06512: at "APEX_260100.WWV_FLOW_AI", line 4389 ORA-06512: at "APEX_260100.WWV_FLOW_AI", line 4554 ORA-06512: at "APEX_260100.WWV_FLOW_AI_API", line 228 ORA-06512: at line 4
The model asked for the tool, APEX ran it, and Gemini rejected the message carrying the result: APEX 26.1 sends tool results to Gemini with the role function, which Gemini's current models do not accept. Providers change their APIs and frameworks follow later. An application request handler can repair the request before it is sent, which is exactly what handlers are for.
Conclusion
APEX_AI calls the workspace's AI services with the application's settings: create a session with APEX_SESSION.CREATE_SESSION when calling it outside a page, use GENERATE with a system prompt, temperature, and standard JSON schema, CHAT for conversations that keep their messages, and GET_VECTOR_EMBEDDINGS for embeddings from a vector provider. Tools are a T_TOOL definition plus a callback procedure; with Gemini in APEX 26.1, add a request handler that restates tool results as user messages.
