How to Call Generative AI and Search from PL/SQL Using APEX_AI and APEX_SEARCH

Oracle APEX 26.1 gives PL/SQL direct access to features that used to live only in page components. APEX_AI sends prompts to a Generative AI service or an AI agent and keeps a conversation going, with tools the model can call. APEX_SEARCH runs the application's search configurations and turns user input into Oracle Text queries. Two smaller packages round this out: APEX_SPATIAL builds geometries for maps, and APEX_PWA sends push notifications.

This guide covers all four packages with tested examples and the output they produced, including one 26.1 limitation you will hit if you use a local Ollama model.

Quick Reference

How to Run These Examples

The examples ran in Oracle APEX 26.1 against a test application with ID 200. Its workspace has a Generative AI service with the static ID local-ollama, which points to a local Ollama server running the llama3.2:3b model. The application has an AI agent, orbit-assistant, with a tool called order_lookup that returns an order by its number, two search configurations, products and customers, and push notifications enabled. To run the examples yourself, create equivalent components in your own application.

Most examples need an APEX session of application 200, created with APEX_SESSION.CREATE_SESSION as shown in the guide to creating APEX sessions and managing session state from PL/SQL. The products and orders come from the Orbit Outfitters sample schema in the orb_tables repository on GitHub. The output under each example is exactly what the database printed.

Generative AI: APEX_AI

APEX_AI talks to a Generative AI service defined in the workspace, such as OpenAI, OCI Generative AI, Cohere, or a local Ollama server, or to an AI agent of the application. An agent adds a system prompt, a welcome message, and tools the model can call. The package needs an APEX session. Setting up the services themselves is covered in the guide to Generative AI in Oracle APEX.

A small local model is handy for trying the API, but it answers slowly and sometimes loosely. Use a larger hosted model for anything users depend on.

GENERATE

New in 26.1. GENERATE sends one prompt and returns the answer as a CLOB. It has three forms:

• With p_agent_static_id, it uses an AI agent, optionally with file attachments.
• With p_service_static_id, it calls a service directly. p_system_prompt sets the model's role, p_temperature its randomness (0 is the most predictable), p_attachments adds files, and p_response_json_schema asks for JSON in a given shape. p_tools, p_request_handler_procedure, and p_response_handler_procedure add tools and hooks.
• With p_config_static_id, it uses an AI configuration. This form is deprecated, because AI configurations became agents.

Syntax:

apex_ai.generate(p_agent_static_id in varchar2, p_prompt in clob, p_attachments in t_attachments default ...) return clob
apex_ai.generate(p_prompt in clob, p_system_prompt in clob default null, p_service_static_id in varchar2 default null,
    p_temperature in number default null, p_attachments in t_attachments default ..., p_response_json_schema in clob default null,
    p_tools in t_tools default ..., p_request_handler_procedure in varchar2 default null,
    p_response_handler_procedure in varchar2 default null, ...) return clob

This example needs a session of application 200, page 1. It asks for a product teaser, then asks for JSON in a given shape.

Example:

declare
    l_answer clob;
begin
    dbms_output.put_line('AI enabled: ' || case when apex_ai.is_enabled then 'yes' else 'no' end);

    -- one prompt, one answer, from the workspace's AI service "local-ollama" (llama3.2:3b)
    l_answer := apex_ai.generate(
                    p_prompt            => 'Write a one-sentence product teaser for the Trailblazer 2-Person Tent.',
                    p_system_prompt     => 'You write short, friendly copy for an outdoor equipment shop.',
                    p_service_static_id => 'local-ollama',
                    p_temperature       => 0);
    dbms_output.put_line(l_answer);

    -- an answer in a given JSON shape (OpenAI, OCI, and Cohere services; see the text for Ollama)
    begin
        l_answer := apex_ai.generate(
                        p_prompt               => 'Classify this review: "The zipper broke, but support replaced the tent."',
                        p_service_static_id    => 'local-ollama',
                        p_response_json_schema => '{"type":"object","properties":{"sentiment":{"type":"string"}}}');
        dbms_output.put_line(l_answer);
    exception when others then
        dbms_output.put_line(regexp_substr(regexp_replace(sqlerrm, 'ORA-\d+: '), 'failed with HTTP-\d+'));
    end;
end;
/

Output:

AI enabled: yes
"Embark on epic adventures with your best friend by your side, thanks to the Trailblazer 2-Person Tent, designed to keep you dry and comfortable in the great outdoors."
failed with HTTP-400

The second call failed with HTTP 400, and that is expected in 26.1: APEX sends the JSON schema in a form Ollama does not accept. p_response_json_schema is meant for OpenAI, OCI, and Cohere services. With Ollama, ask for JSON in the prompt instead and validate what comes back.

CHAT

CHAT continues a conversation. p_messages holds the messages so far, and each call adds the prompt, the answer, and any tool calls to it, so the next call has the full context. With an agent, the model can call the agent's tools, such as a Retrieve Data tool that runs a query. APEX runs the tool and passes the result back to the model before it answers. The forms match those of GENERATE, and the one with p_config_static_id is deprecated.

Syntax:

apex_ai.chat(p_agent_static_id in varchar2, p_prompt in clob, p_messages in out nocopy t_chat_messages,
    p_request_handler_procedure in varchar2 default null) return clob
apex_ai.chat(p_prompt in clob, p_system_prompt in clob default null, p_service_static_id in varchar2 default null,
    p_temperature in number default null, p_messages in out nocopy t_chat_messages, p_tools in t_tools default ..., ...) return clob

This example needs a session of application 200, page 1. It asks the orbit-assistant agent about an order, asks a follow-up question, and then prints the conversation.

Example:

declare
    l_messages apex_ai.t_chat_messages := apex_ai.c_chat_messages;
    l_answer   clob;
begin
    -- the lab's AI agent "orbit-assistant": a system prompt and the tool order_lookup
    l_answer := apex_ai.chat(p_agent_static_id => 'orbit-assistant',
                             p_prompt          => 'What is the status of order ORD-12259?',
                             p_messages        => l_messages);
    dbms_output.put_line('1: ' || l_answer);

    -- l_messages holds the conversation, so the follow-up question has its context
    l_answer := apex_ai.chat(p_agent_static_id => 'orbit-assistant',
                             p_prompt          => 'Why does it need approval?',
                             p_messages        => l_messages);
    dbms_output.put_line('2: ' || l_answer);

    dbms_output.put_line('conversation:');
    for i in 1 .. l_messages.count loop
        dbms_output.put_line('  ' || rpad(l_messages(i).chat_role, 10)
            || case when l_messages(i).tool_calls is not null and l_messages(i).tool_calls.count > 0 then '(calls ' || l_messages(i).tool_calls(1).name || ')'
                    else substr(replace(l_messages(i).message, chr(10), ' '), 1, 70) end);
    end loop;
end;
/

Output:

1: The status of order ORD-12259 is Pending Approval.
2: Order ORD-12259 needs manager's approval because it has a discount of 12%, which is above the 10% threshold.
conversation:
  user      What is the status of order ORD-12259?
  assistant (calls order_lookup)
  tool
  assistant The status of order ORD-12259 is Pending Approval.
  user      Why does it need approval?
  assistant (calls order_lookup)
  tool
  assistant Order ORD-12259 needs manager's approval because it has a discount of

Each t_chat_message has a chat_role (user, assistant, tool, or system), the message, and the tool_calls the assistant asked for. The conversation shows the pattern: for each question the assistant first called order_lookup, the tool's result was added with the role tool, and only then did the assistant answer. The follow-up question worked because l_messages carried the first exchange. The example cuts each line at 70 characters, which is why the last one ends mid-sentence.

Start a new conversation with apex_ai.c_chat_messages, an empty list, as the example does. The figures in the second answer come from a 3-billion-parameter model, so check anything important against the tool's data.

The Other Subprograms of APEX_AI

Search: APEX_SEARCH

Search configurations, under Shared Components, describe what a Search page searches (tables, views, REST sources, or Oracle Text indexes) and how the results look. APEX_SEARCH lets you use them outside a Search region.

SEARCH

SEARCH runs one or more configurations, named by static ID, and returns the results as rows. The columns are the same ones a Search region shows: config_label, title, subtitle, description, badge, primary_key_1 and primary_key_2, score, link, icons, and more. That makes it easy to build your own reports or REST services on top of the configurations. Pass p_apply_order_bys => 'N' to skip the configurations' sort order, and p_return_total_row_count => 'Y' to count all results.

Syntax:

apex_search.search(p_search_static_ids in apex_t_varchar2, p_search_expression in varchar2,
    p_apply_order_bys in varchar2 default 'Y', p_return_total_row_count in varchar2 default 'N')
  return apex_t_search_results pipelined

This example needs a session of application 200, page 1.

Example:

select config_label, title, subtitle, badge
  from table(apex_search.search(p_search_static_ids => apex_t_varchar2('products', 'customers'),
                                p_search_expression => 'trail tent'))
 fetch first 5 rows only;

Output:

CONFIG_LABEL TITLE                     SUBTITLE  BADGE
------------ ------------------------- -------- ------
&{PRODUCTS}. Trailblazer 2-Person Tent TNT-1002 (null)
&{PRODUCTS}. Trailblazer 1-Person Tent TNT-1001 (null)

The test application's labels are text messages, written as &{PRODUCTS}. A Search region translates them, but SEARCH returns them as they are. If you show config_label in your own report, translate it yourself, for example with APEX_LANG.MESSAGE.

QUERY_EXPERT_SEARCH and QUERY_SEARCH_ENGINE

Both functions convert what a user types into Oracle Text syntax for a CONTAINS query. QUERY_EXPERT_SEARCH understands or, quoted phrases, fuzzy-: for fuzzy matching, and ^n for weights. QUERY_SEARCH_ENGINE returns a progressive query that starts with exact matches and relaxes step by step to stems, fuzzy matches, and single words, so the best matches score highest.

This example needs no APEX session.

Example:

begin
    -- turn what users type into Oracle Text syntax, for your own CONTAINS queries
    dbms_output.put_line(apex_search.query_expert_search('(tent or shelter) "two person"'));
    dbms_output.put_line(apex_search.query_expert_search('fuzzy-: tnet'));
    dbms_output.put_line(apex_search.query_expert_search('trailblazer^3 tent'));
    dbms_output.put_line(substr(apex_search.query_search_engine('tent shelter'), 1, 120) || '...');
end;
/

Output:

({tent} ACCUM {shelter}) AND {two person}
FUZZY({tnet},80,100,W)
{trailblazer}*3 AND {tent}
<query><textquery><progression><seq>{tent} {shelter}</seq><seq>${tent} ${shelter}</seq><seq>FUZZY({tent},40,1000,W) FUZZ...

Each word is wrapped in braces, so Oracle Text reserved words in user input cannot break the query. The last line is cut at 120 characters by the example; the full value is an XML query template with several seq steps.

Maps: APEX_SPATIAL

APEX_SPATIAL builds SDO_GEOMETRY values in longitude and latitude (SRID 4326, the default), the way the Map region and the Geocoded Address item use them. It also registers spatial columns. The Map region itself is covered in the guide to maps, calendars, and trees in Oracle APEX.

The example works on a scratch table. Before it runs, it drops the table and any old metadata for it, then creates the table:

Create the scratch table first:

create table lab_store_areas (store_id number primary key, name varchar2(100), area sdo_geometry)

This example needs a session of application 200, page 1. It rolls back its rows and removes the metadata at the end.

Example:

declare
    l_geom sdo_geometry;
begin
    dbms_output.put_line('spatial available: ' || case when apex_spatial.spatial_is_available then 'yes' else 'no' end);

    -- register the column for spatial indexes, in longitude/latitude (SRID 4326)
    apex_spatial.insert_geom_metadata_lonlat(p_table_name => 'LAB_STORE_AREAS', p_column_name => 'AREA');

    l_geom := apex_spatial.point(p_lon => -104.9903, p_lat => 39.7392);
    dbms_output.put_line('point:     gtype ' || l_geom.sdo_gtype || ', srid ' || l_geom.sdo_srid);
    insert into lab_store_areas values (1, 'Denver store',
        apex_spatial.circle_polygon(p_lon => -104.9903, p_lat => 39.7392, p_radius => 5000));   -- 5 km
    insert into lab_store_areas values (2, 'Boulder region',
        apex_spatial.rectangle(p_lon1 => -105.30, p_lat1 => 39.95, p_lon2 => -105.15, p_lat2 => 40.10));

    for r in (select name, a.area.sdo_gtype as gtype,
                     round(sdo_geom.sdo_area(a.area, 0.05, 'unit=SQ_KM'), 1) as sq_km,
                     sdo_geom.relate(a.area, 'ANYINTERACT', apex_spatial.point(-104.99, 39.74), 0.05) as has_downtown
                from lab_store_areas a order by store_id) loop
        dbms_output.put_line(rpad(r.name, 15) || 'gtype ' || r.gtype || ', ' || r.sq_km || ' km2, downtown: ' || r.has_downtown);
    end loop;

    apex_spatial.change_geom_metadata(p_table_name => 'LAB_STORE_AREAS', p_column_name => 'AREA',
        p_diminfo => sdo_dim_array(sdo_dim_element('X', -180, 180, 1), sdo_dim_element('Y', -90, 90, 1)),
        p_srid => 4326);
    for m in (select column_name, srid from user_sdo_geom_metadata where table_name = 'LAB_STORE_AREAS') loop
        dbms_output.put_line('metadata:  ' || m.column_name || ', srid ' || m.srid);
    end loop;
    apex_spatial.delete_geom_metadata(p_table_name => 'LAB_STORE_AREAS', p_column_name => 'AREA');
    rollback;
end;
/

Output:

spatial available: yes
point:     gtype 2001, srid 4326
Denver store   gtype 2003, 78.1 km2, downtown: TRUE
Boulder region gtype 2003, 213.3 km2, downtown: FALSE
metadata:  AREA, srid 4326

The point has gtype 2001 (a two-dimensional point) and both areas have gtype 2003 (a two-dimensional polygon). A 5 km circle covers about 78.5 square kilometers in theory; the polygon approximation came out at 78.1. The ANYINTERACT test shows the kind of check a store locator needs: downtown Denver falls inside the Denver circle but not inside the Boulder rectangle.

Push Notifications: APEX_PWA

In a Progressive Web App with push notifications enabled, users subscribe on each device from the app's Settings page. APEX_PWA sends a notification to all of a user's subscribed devices, through a queue that a job empties.

This example needs a session of application 200, page 1.

Example:

begin
    -- subscriptions come from the browser (the "Push Notifications" settings page of a PWA)
    dbms_output.put_line('ADMIN subscribed: ' || case when apex_pwa.has_push_subscription(
        p_application_id => 200, p_user_name => 'ADMIN') then 'yes' else 'no' end);

    -- queued, and sent by a job, to every device the user subscribed with
    apex_pwa.send_push_notification(
        p_application_id => 200,
        p_user_name      => 'ADMIN',
        p_title          => 'Order ORD-12259 needs approval',
        p_body           => 'Discount 15% on $2,480.00',
        p_target_url     => apex_page.get_url(p_page => 8));
    apex_pwa.push_queue;      -- send now instead of waiting for the job
    dbms_output.put_line('queued and pushed');
end;
/

Output:

ADMIN subscribed: no
queued and pushed

ADMIN had no subscription, and the calls still succeeded: queuing a notification for a user without devices is not an error, it just reaches nobody. Check HAS_PUSH_SUBSCRIPTION first if you want to fall back to email. The browser side, asking for permission and subscribing, is done with apex.pwa, covered in the guide to apex.storage and apex.pwa. Sending push notifications from automations is covered in the guide to automations, email, and push notifications.

Conclusion

APEX_AI sends prompts to AI services and agents, keeps conversations in a message list, lets agents call tools, and returns embeddings for vector search; in 26.1, JSON schemas do not work with Ollama. APEX_SEARCH runs an application's search configurations from PL/SQL and turns user input into safe Oracle Text queries. APEX_SPATIAL creates points, rectangles, and circles and registers spatial columns. APEX_PWA queues and sends push notifications to every device a user has subscribed.