One of the most common questions that comes up when building custom tools in the Select AI Agent framework is whether an existing PL/SQL procedure with multiple OUT parameters can be exposed directly as a tool.

It’s an important question because many real-world Oracle environments already rely on mature, heavily tested PL/SQL procedures that return more than one value. Those procedures often represent proven business logic that no one wants to rewrite just to fit a new tool interface.

The good news is that you do not need to redesign that logic. The best approach is to keep the procedure in place and add a lightweight wrapper function around it. That function can call the procedure, collect the OUT values, and return them as a single JSON payload. In practice, this is the cleanest and most reusable way to surface existing multi-output PL/SQL logic as a tool in the Select AI Agent framework.

The challenge

A PL/SQL procedure like this is completely normal:

procedure proc1(
  input_1  in  varchar2,
  input_2  in  number,
  input_3  in  date,
  out_1    out varchar2,
  out_2    out number
);

This works well inside PL/SQL, where multiple outputs are easy to manage through OUT parameters. But tools are simpler to work with when they return one structured result – a single payload the model can interpret cleanly.

The right pattern

When you want to reuse an existing procedure with multiple outputs, the most effective pattern is:

  • keep the procedure exactly as it is
  • create a PL/SQL function that accepts the tool inputs
  • call the procedure from inside that function
  • capture the OUT parameters into local variables
  • return a single JSON document containing all outputs

That gives you the best of both worlds. You preserve the proven procedure logic while presenting a response shape as required by the Select AI Agent framework.

A generic example

Suppose you have a procedure with three inputs and two outputs. A wrapper function can look like this:

create or replace function foo(
  input_1 in <datatype>,
  input_2 in <datatype>
) return clob
is
  l_out_1 <datatype>;
  l_out_2 <datatype>;
begin
  proc1(
    input_1 => input_1,
    input_2 => input_2,
    out_1   => l_out_1,
    out_2   => l_out_2
  );

  return json_object(
           'result1' value l_out_1,
           'result2' value l_out_2
         );
end;

Then that wrapper function becomes the implementation you use when creating the tool:

begin
  dbms_cloud_ai_agent.create_tool(
    tool_name  => 'PROC1_WRAPPER_TOOL',
    attributes => '{
      "instruction": "Use this tool to run proc1 business logic and return the results as JSON.",
      "function": "FOO"
    }'
  );
end;

This pattern is simple, readable, and easy to repeat across existing PL/SQL assets.

Why this works so well

There are a few reasons this is the best design.

  • It preserves proven logic – You do not have to touch the original procedure. That matters when the procedure has already been validated, deployed, and trusted in production.
  • It gives the agent one clean response – A single JSON payload is much easier for the Select AI Agent framework to consume than a set of positional outputs. JSON gives each result a name and a structure, which makes the output more self-describing.
  • It improves readability – Instead of passing around unlabeled output variables, you can return something explicit like:
{
  "return_status": "APPROVED",
  "refund_amount": 149.99
}

That is easier for developers to understand and easier for the model to reason over.

  • It scales better – If your procedure later grows from two outputs to four, you can extend the JSON payload without changing the overall tool pattern.

A more realistic example

Imagine you already have a returns procedure like this:

procedure process_return(
  p_order_id         in  number,
  p_reason_code      in  varchar2,
  p_customer_id      in  number,
  p_return_status    out varchar2,
  p_refund_amount    out number
);

You can expose it through the Select AI Agent framework with a wrapper function like this:

create or replace function process_return_tool(
  p_order_id    in number,
  p_reason_code in varchar2,
  p_customer_id in number
) return clob
is
  l_return_status varchar2(100);
  l_refund_amount number;
begin
  process_return(
    p_order_id      => p_order_id,
    p_reason_code   => p_reason_code,
    p_customer_id   => p_customer_id,
    p_return_status => l_return_status,
    p_refund_amount => l_refund_amount
  );

  return json_object(
    'return_status' value l_return_status,
    'refund_amount' value l_refund_amount
  );
end;

Then use it when creating the tool:

begin
  dbms_cloud_ai_agent.create_tool(
    tool_name  => 'PROCESS_RETURN_TOOL',
    attributes => '{
      "instruction": "Process a return request and return the status and refund amount as JSON.",
      "function": "PROCESS_RETURN_TOOL"
    }'
  );
end;

Now the tool returns one structured result that is far easier to use than separate PL/SQL output bindings.

This approach gives you a low-risk way to modernize existing database logic for agent use without introducing unnecessary churn into code that already works.

Resources

To learn more about the Select AI Agent framework and tool creation, check out these resources: