Oracle APEX 26.1: Build Ad-hoc AI Agents Entirely in PL/SQL

The well-established way to add AI tool-calling behavior in Oracle APEX is through a shared-component AI Agent. That approach is declarative, low code, and powerful for many use cases. For scenarios that benefit from greater control and dynamic composition, APEX 26.1 also introduces a programmatic path. The APEX_AI package has been enhanced to let you define AI tools inline from PL/SQL and pass them directly to APEX_AI.CHAT or APEX_AI.GENERATE – no shared component required.

This means you can now create ad-hoc agents purely in code, assemble tools at runtime, or embed AI behavior directly within a component when that level of flexibility is desired.

Here’s what this means for your development workflow and why it matters.

Beyond declarative tools in AI Agents

If you’ve built AI-powered features in APEX, you know the current model. You create an AI Agent as a shared component, define your tools declaratively, and wire them up to the AI-enabled components: Show AI Assistant, Generate Text With AI, or the APEX_AI PL/SQL APIs.

That works well for predefined, application-wide tool sets. But it creates real friction in other scenarios:

• Dynamic tool requirements. AI Agent tools do support server-side conditions, which makes individual tools conditional and dynamic. But if the tools your feature needs depend on runtime conditions beyond what server-side conditions cover, a static shared-component definition can’t flex to match.
• Rapid prototyping. If you want to call an LLM with a quick tool-augmented prompt directly from a PL/SQL script, standing up a shared component first is overhead you don’t need.
• Scaling tool management. As the number of tools grows, managing them all through one declarative configuration becomes harder to maintain and reason about.

For Oracle APEX developers tackling advanced use cases, the need is clear, a code-driven path with more control.

What ad-hoc agents look like

The core idea is straightforward. When you call APEX_AI.GENERATE or APEX_AI.CHAT, you can now pass a p_tools parameter containing one or more tool definitions. Each tool has a name, an optional description, optional parameters, and an implementation. APEX handles the agentic loop the same way it does for shared-component agents: the LLM decides when it needs a tool, APEX executes it, the result goes back to the model, and the cycle continues until the LLM produces a final answer.

A few things to note about how this works in practice:

Tools are defined inline, not in shared components. You declare tools directly in your PL/SQL call using the apex_ai.t_tools collection type. No trip to the APEX Builder, no shared-component wiring. The tools exist only for the duration of that call.

The agentic loop is the same. Programmatically-defined tools share the same infrastructure that powers declarative AI agents. Tool validation, argument handling, default responses, and the back-and-forth with the LLM all work identically. This is a different way to provide tools, not a different execution model.

These are PL/SQL-only tools. Unlike shared-component tools, ad-hoc tools don’t support client-side execution, notifications through the UI, or the full declarative authoring experience. Every tool implementation is a PL/SQL procedure. This is a coder-facing API.

Pattern 1: one callback per tool

The simplest way to use programmatic tools is to define a callback procedure for each tool. The callback procedures must follow a predefined signature precisely — much like plug-in callbacks. The procedure receives the LLM’s arguments and returns a result. APEX calls the right procedure automatically based on which tool the LLM invokes.

Here’s a working example that gives an LLM the ability to answer questions about project time tracking. Two tools are needed: one to look up a project by name, and another to pull that project’s logged hours.

create or replace procedure find_project_proc (
    p_param     in              apex_ai.t_tool_exec_param,
    p_result    in out nocopy   apex_ai.t_tool_exec_result )
as
begin
    select json_object (
               'project_id'   value p.project_id,
               'project_name' value p.project_name,
               'status'       value p.status,
               'manager'      value p.manager_name )
      into p_result.result
      from projects p
     where lower( p.project_name ) like '%' || lower( p_param.args_json.get_string( 'project_name' ) ) || '%'
       and rownum = 1;
end find_project_proc;
/




create or replace procedure get_time_entries_proc (
    p_param     in              apex_ai.t_tool_exec_param,
    p_result    in out nocopy   apex_ai.t_tool_exec_result )
as
begin
    select json_arrayagg (
               json_object (
                   'employee' value e.full_name,
                   'hours'    value t.hours_logged,
                   'task'     value t.task_description,
                   'log_date' value to_char( t.log_date, 'YYYY-MM-DD' ) ) )
      into p_result.result
      from time_entries t
      join employees e on e.employee_id = t.employee_id
     where t.project_id = p_param.args_json.get_number( 'project_id' )
       and t.log_date  >= trunc( sysdate, 'MM' );
end get_time_entries_proc;
/
 

With the tool implementations in place, you call APEX_AI.GENERATE and pass the tools inline:

declare
    l_result clob;
begin

    l_result :=
        apex_ai.generate (
            p_service_static_id => 'my-ai-service',
            p_prompt            => 'How much time has the team logged on the Alpha project this month?',
            p_tools             =>
                apex_ai.t_tools (
                    apex_ai.t_tool (
                        name               => 'find_project',
                        description        => 'Look up a project by name and return its ID and details',
                        callback_procedure => 'find_project_proc',
                        parameters         =>
                            apex_ai.t_tool_parameters (
                                apex_ai.t_tool_parameter (
                                    name => 'project_name' ) ) ),
                    apex_ai.t_tool (
                        name               => 'get_time_entries',
                        description        => 'Retrieve time tracking entries for a project in the current month',
                        callback_procedure => 'get_time_entries_proc',
                        parameters         =>
                            apex_ai.t_tool_parameters (
                                apex_ai.t_tool_parameter (
                                    name      => 'project_id',
                                    data_type => apex_ai.c_tool_param_type_number ) ) ) ) );

    sys.dbms_output.put_line( 'Result: ' || l_result );

end;
/

When the LLM receives the prompt, it recognizes it needs to identify the project first. It calls find_project with project_name => 'Alpha', APEX executes the callback, and the JSON result — containing the project_id, status, and manager — goes back to the model. The LLM then calls get_time_entries with the project_id it just learned, APEX runs that query, and the time entry data returns to the model. Finally, the LLM composes a natural-language summary of who logged what and how many total hours.

The entire flow happens in a single call to APEX_AI.GENERATE. You define the tools, APEX handles the agentic loop.

Notice that project_id is declared with data_type => apex_ai.c_tool_param_type_number. Ad-hoc tools support typed parameters — VARCHAR2, NUMBER, BOOLEAN, and CLOB — so the LLM knows what kind of value to pass, and you can read it with the matching get_number or get_string method on args_json.

This pattern is easy to understand and works well when each tool is naturally separate. One procedure, one responsibility.

Pattern 2: one response handler for all tool calls

The per-tool callback pattern is simple, but it has a limitation: each procedure only sees its own invocation in isolation. If you need visibility across all the tool calls the LLM makes in a single response, or you want to inspect the full context before executing anything, there’s a more advanced option.

Instead of assigning a callback_procedure to each tool, you define a single response handler procedure and pass it via p_response_handler_procedure. The response handler must also follow a predefined signature precisely, similar to plug-in callbacks.

APEX calls this handler once per LLM response, giving you access to the complete set of pending tool calls, the chat history, and token usage.

create or replace procedure response_handler_proc (
    p_param     in              apex_ai.t_chat_response_handler_param,
    p_result    in out nocopy   apex_ai.t_chat_response_handler_result )
as
    l_args_json sys.json_object_t;
    l_result    clob;
begin
    sys.dbms_output.put_line(
        'Tokens used: ' || p_result.response.total_tokens );

    if p_result.response.type = apex_ai.c_response_type_tool_calls then
        for i in 1 .. p_param.pending_tool_calls.count loop
            l_args_json := p_param.pending_tool_calls( i ).args_json;

            if p_param.pending_tool_calls( i ).name = 'find_project' then
                select json_object (
                           'project_id'   value p.project_id,
                           'project_name' value p.project_name,
                           'status'       value p.status,
                           'manager'      value p.manager_name )
                  into l_result
                  from projects p
                 where lower( p.project_name ) like '%' || lower( l_args_json.get_string( 'project_name' ) ) || '%'
                   and rownum = 1;

            elsif p_param.pending_tool_calls( i ).name = 'get_time_entries' then
                select json_arrayagg (
                           json_object (
                               'employee' value e.full_name,
                               'hours'    value t.hours_logged,
                               'task'     value t.task_description,
                               'log_date' value to_char( t.log_date, 'YYYY-MM-DD' ) ) )
                  into l_result
                  from time_entries t
                  join employees e on e.employee_id = t.employee_id
                 where t.project_id = l_args_json.get_number( 'project_id' )
                   and t.log_date  >= trunc( sysdate, 'MM' );
            end if;

            apex_ai.set_tool_result (
                p_response_handler_param  => p_param,
                p_response_handler_result => p_result,
                p_tool_call               => p_param.pending_tool_calls( i ),
                p_result                  => l_result );
        end loop;
    end if;
end response_handler_proc;
/

The calling code changes slightly: instead of callback_procedure on each tool, you pass p_response_handler_procedure at the top level.

declare
    l_result clob;
begin

    l_result :=
        apex_ai.generate (
            p_service_static_id          => 'my-ai-service',
            p_prompt                     => 'How much time has the team logged on the Alpha project this month?',
            p_response_handler_procedure => 'response_handler_proc',
            p_tools                      =>
                apex_ai.t_tools (
                    apex_ai.t_tool (
                        name        => 'find_project',
                        description => 'Look up a project by name and return its ID and details',
                        parameters  =>
                            apex_ai.t_tool_parameters (
                                apex_ai.t_tool_parameter (
                                    name => 'project_name' ) ) ),
                    apex_ai.t_tool (
                        name        => 'get_time_entries',
                        description => 'Retrieve time tracking entries for a project in the current month',
                        parameters  =>
                            apex_ai.t_tool_parameters (
                                apex_ai.t_tool_parameter (
                                    name      => 'project_id',
                                    data_type => apex_ai.c_tool_param_type_number ) ) ) ) );

    sys.dbms_output.put_line( 'Result: ' || l_result );

end;
/

Why the response handler pattern matters

The response handler isn’t just an alternative syntax. It unlocks a different category of control.

Consider a reassign_task tool. With per-tool callbacks, if the LLM decides to call it 50 times in one response — reassigning every task on a project — you get 50 isolated procedure executions with no opportunity to notice the scope and intervene. With a response handler, you see all 50 calls at once before any of them execute. You can log the anomaly, cap the number of reassignments, or halt the process entirely.

Beyond guardrails, the response handler gives you:

• Observability. Token usage, the full chat history, and every tool call are available in one place. You can log everything going in and out of the LLM in a single procedure.
• Batch-level decisions. You can inspect the entire set of tool calls and decide which to execute, which to skip, and how to respond.
• Error handling. The handler receives error responses like max_length or invalid_tool_call. You can decide whether to let the error propagate as an exception or handle it gracefully, for example by marking the response as complete with a note that the content may be truncated.
• Early exit. You can stop the agentic loop before APEX sends the tool results back to the LLM. The per-tool callback supports early exit too, but as a “soft” exit: if any other tool call in the same batch doesn’t exit early, the whole batch continues. The response handler provides a “hard” exit that acts on the entire batch.

The per-tool callback pattern is right when simplicity is enough. The response handler is right when you need global context, logging, validation, or stronger control over what the LLM is allowed to do.

How ad-hoc agents and shared components compare

Ad-hoc agents don’t replace shared-component AI Agents. They serve different needs.

If you’re building a standard AI assistant for your application, shared components are still the right choice. If you need tools that change based on context or you’re prototyping from a script, ad-hoc agents give you the flexibility you need.

Why this matters for your applications

This feature removes one of the major constraints in building AI-powered APEX applications. Instead of choosing between “declare everything in a shared component” and “skip APEX’s AI infrastructure entirely,” you get a code-driven path that still uses the same agentic loop, tool validation, and LLM orchestration that APEX already provides.

If you’ve been building complex workarounds to get dynamic tool behavior from the declarative model, or you’ve avoided APEX’s AI infrastructure because it couldn’t flex to your use case, this is the enhancement you’ve been waiting for.