Tools / Functions

Start with the shortest example

The Quickstart registers one tool without parameters. It then shows the model calling this tool. This guide adds parameters, best practices, and examples that read and write data.

Tools give the AI access to your data

Register a tool to give the AI access to your database or to an external API. During the conversation, the AI decides if it needs a tool and requests a call. UC AI then calls the tool and returns the result to the AI. The AI uses this result in its answer.

You can divide tools into two categories:

• Additional Context: too much information in a conversation makes the answers slower, more expensive, and less accurate. A tool gives this context only when the user asks for it.
• Actions: a tool lets the AI create, update, or delete data in your database. A tool can also start an action in another system with a REST call.

Tools call a PL/SQL function

In UC AI, a tool is a PL/SQL function that the AI can call. The function must have these attributes:

• The AI passes one parameter of type CLOB. This parameter holds JSON, so it can carry more than one argument. It can also hold an empty object.
• The function returns a CLOB.

Like Structured Output, a tool needs a description of its parameters in a JSON Schema. A tool GET_WEATHER has the two parameters city and unit, and unit defaults to celsius. Its schema looks like this:

{
  "$schema": "http://json-schema.org/draft-07/schema#",
  "type": "object",
  "title": "get weather parameters",
  "properties": {
    "city": {
      "type": "string",
      "description": "Name of the city to get weather information for"
    },
    "unit": {
      "type": "string",
      "description": "Temperature unit. Defaults to celsius",
      "enum": ["celsius", "fahrenheit", "kelvin"]
    }
  },
  "required": ["city"]
}

A precise schema definition makes the tool call more reliable.

Tip

Our JSON schema builder helps you write your own schemas.

Best practices

• Always return feedback: every piece of information helps the AI. If an error occurs, return a text that describes the error. The AI can then call the tool again with corrected input. After a successful action, return a success message also. The AI then knows the result.
• Validate inputs: make sure that all required parameters are present. Make sure also that each value obeys its constraints for enum, minimum, and maximum. If a value breaks a constraint, return an error text. The AI can then call the tool again with corrected parameters.
• Describe the tool completely: more context about a tool helps the AI decide when to call it. Write careful descriptions and constraints in your JSON schema. For some providers, examples in the system prompt help also. Give an example call and example parameters for each tool.
• The providers differ greatly: the intelligence of the model controls how well it picks and calls tools. Reasoning helps the AI plan its steps and call tools more precisely. Some providers are still weaker at tool calls than others. We recommend Anthropic for tool calls today.
• Restrict the available tools with tags: many registered tools add much context. This context makes the decision harder for the AI. Filter the tools by tag to give the AI fewer options.

Registering a tool

Use uc_ai_tools_api.create_tool_from_schema to register a tool:

declare
  l_schema  json_object_t;
  l_tool_id uc_ai_tools.id%type;
begin
  l_schema := json_object_t.parse('
{
  "$schema": "http://json-schema.org/draft-07/schema#",
  ...
}');

    -- Create the tool
  l_tool_id := uc_ai_tools_api.create_tool_from_schema(
    p_tool_code => 'GET_WEATHER',
    p_description => 'Get the current weather for a given city',
    p_function_call => 'return my_pkg.get_weather(:parameters);',
    p_json_schema => l_schema,
    p_tags => apex_t_varchar2('weather','weather-api')
  );
  commit;
end;

Parameters:

• p_tool_code: a unique identifier
• p_description: a description of the task of your tool. A good description improves the results.
• p_function_call: the PL/SQL function that UC AI calls. This function must return a CLOB. If the tool has parameters, use :parameters to pass them into a CLOB parameter.
• p_json_schema: the JSON schema of the parameters
• p_tags: the tags of the tool. Tags enable or disable a tool for one call.

create_tool_from_schema is create-only. If a tool with the same p_tool_code exists, the function raises an error.

Upserting a tool with merge_tool_from_schema

uc_ai_tools_api.merge_tool_from_schema creates the tool, or updates its description, parameters, and tags. It takes the same parameters as create_tool_from_schema. Use it in idempotent deployment scripts:

l_tool_id := uc_ai_tools_api.merge_tool_from_schema(
  p_tool_code     => 'GET_WEATHER',
  p_description   => 'Get the current weather for a given city',
  p_function_call => 'return my_pkg.get_weather(:parameters);',
  p_json_schema   => l_schema,
  p_tags          => apex_t_varchar2('weather','weather-api')
);

An update replaces the parameters and the tags of the tool completely. The function deletes the rows of that tool in uc_ai_tool_parameters and uc_ai_tool_tags. It then writes new rows from the new schema and the new tags.

The run context

A tool gets its arguments from the model. For some values that is not safe. In a “talk to this document” agent, the tool must read document 7 because the run is bound to document 7. It must not read document 42 because the model asked for it.

The run context carries such values. The caller supplies them when the run starts. UC AI adds them to the arguments of every tool in that run, under the reserved key _ctx. No model sees this key, and no model can set it.

l_result := uc_ai_agents_api.execute_agent(
  p_agent_code  => 'DOC_AGENT',
  p_run_context => json_object_t('{"document_id": "7", "tenant_id": "ACME"}')
);

The handler of the tool then receives:

{
  "query": "invoice terms",
  "_ctx": { "document_id": "7", "tenant_id": "ACME" }
}

Leave the scoped value out of the schema

Do not declare the scoped value as a tool parameter. The model then cannot name a document, and the value has one source:

l_schema := json_object_t.parse('{
  "type": "object",
  "properties": {
    "query": { "type": "string", "description": "What to look for in the document" }
  },
  "required": ["query"]
}');

l_tool_id := uc_ai_tools_api.create_tool_from_schema(
  p_tool_code     => 'DOC_SEARCH',
  p_description   => 'Search the current document',
  p_function_call => 'return doc_pkg.search(:parameters);',
  p_json_schema   => l_schema,
  p_tags          => apex_t_varchar2('doc')
);

The handler reads the document from _ctx:

function search(p_params in clob) return clob
as
  l_args json_object_t := json_object_t(p_params);
  l_doc  number;
begin
  l_doc := to_number(l_args.get_object('_ctx').get_string('document_id'));
  -- l_doc is 7, whatever the model sent
  return doc_pkg.search_chunks(l_doc, l_args.get_string('query'));
end search;

What you must know

_ctx is always present. It holds an empty object when the run carries no context. A tool that needs a value must decide what to do when the value is missing. A clear text result is better than an error, because the model can read it:

if l_doc is null then
  return 'Error: this run is not bound to a document.';
end if;

_ctx is a reserved name. A tool that declares a parameter with this name is rejected at registration.

Read the arguments by name. A handler that counts the keys, or that walks all of them, sees _ctx as one more key. The run context adds this key to the arguments of every tool, and such a handler still compiles. The built-in MEMORY tool had this defect: it treated “one key” as a wrapper object of a provider, and it stopped finding the wrapper. Read l_args.get_string('city'), not the shape of the object.

The run context reaches a tool through the arguments. It does not reach a PL/SQL workflow step, because a step is not a tool.

Caution

The run context binds the arguments of one tool. It does not limit which tools the agent has. A scoped DOC_SEARCH gives no protection when the same tag set also holds a tool that can read any table. Give a document agent only the tools that the run context can bind.

Where else the run context applies

The same values also fill prompt placeholders and can scope the memory of an agent. Read Prompt profiles and Agent memory.

Outside an agent, pass the context to generate_text:

l_result := uc_ai.generate_text(
  p_user_prompt => 'Summarize this document',
  p_provider    => uc_ai.c_provider_openai,
  p_model       => uc_ai_openai.c_model_gpt_5_6_sol,
  p_run_context => json_object_t('{"document_id": "7"}')
);

Using tools

You must enable tools before the AI can use them. By default, the AI gets every enabled tool. Set uc_ai.g_tool_tags to give the AI a subset of the tools.

declare
  l_result json_object_t;
begin
  uc_ai.g_enable_tools := true;
  -- optionally restrict tools to certain tags
  uc_ai.g_tool_tags := apex_t_varchar2('weather');

  l_result := uc_ai.generate_text(
    p_user_prompt => 'How is the weather in Paris?',
    p_provider => UC_AI.c_provider_openai,
    p_model => UC_AI_OPENAI.c_model_gpt_5_6_sol,
    p_max_tool_calls => 6
  );
end;

p_max_tool_calls limits how many tools the AI can call in one interaction.

Examples

Look up data

Data structure

This table holds data about employees:

SQL> select first_name, last_name, email from tt_users;

FIRST_NAME    LAST_NAME    EMAIL
_____________ ____________ ___________________________________
Michael       Scott        michael.scott@dundermifflin.com
Pam           Beesly       pam.beesly@dundermifflin.com
Dwight        Schrute      dwight.schrute@dundermifflin.com
Jim           Halpert      jim.halpert@dundermifflin.com
Angela        Martin       angela.martin@dundermifflin.com
Kevin         Malone       kevin.malone@dundermifflin.com

6 rows selected.

Function to return data

Now create a function that returns this data as one CLOB in JSON format:

FUNCTION get_all_users_json
RETURN CLOB IS
    l_json CLOB;
BEGIN
    SELECT JSON_ARRAYAGG(
        JSON_OBJECT(
            'user_id' VALUE user_id,
            'first_name' VALUE first_name,
            'last_name' VALUE last_name,
            'email' VALUE email,
            'hire_date' VALUE TO_CHAR(hire_date, 'YYYY-MM-DD'),
            'is_active' VALUE is_active
        )
        ORDER BY last_name, first_name
        RETURNING CLOB
    )
    INTO l_json
    FROM tt_users
    ;

    RETURN COALESCE(l_json, '[]');
END get_all_users_json;

Return CLOB

An AI communicates in text. Your tool must therefore return all data in one CLOB value. The AI then uses this value in its answer.

Tool registration

Now register a tool. It tells the AI how to call this function:

declare
  l_tool_id uc_ai_tools.id%type;
begin
    -- Create the tool
  l_tool_id := uc_ai_tools_api.create_tool_from_schema(
    p_tool_code => 'TT_GET_USERS',
    p_description => 'Get information on all the users in the system',
    p_function_call => 'return tt_timetracking_api.get_all_users_json();',
    p_json_schema => null,
    p_tags => apex_t_varchar2('users','testing-tools')
  );
  commit;
end;
/

Function call

The function body must return a CLOB value.

Testing

declare
  l_result JSON_OBJECT_T;
  l_final_message CLOB;
begin
  uc_ai.g_enable_tools := true; -- enable tools usage

  l_result := uc_ai.generate_text(
    p_user_prompt => 'What is the email address of Jim?',
    p_system_prompt => 'You are an assistant to a time tracking system.
    Your tools give you access to user, project and timetracking information.
    Answer concise and short.',
    p_provider => uc_ai.c_provider_google,
    p_model => uc_ai_google.c_model_gemini_3_7_flash
  );

  l_final_message := l_result.get_clob('final_message');
  sys.dbms_output.put_line('AI Response: ' || l_final_message);
  -- Jim's email address is jim.halpert@dundermifflin.com.
end;
/

This is the message array from the return object:

{
  // ...
  "messages": [
    {
      "role": "system",
      "content": "You are an assistant to a time tracking system. Your tools give you access to user, project and timetracking information. Answer concise and short."
    },
    {
      "role": "user",
      "content": [
        {
          "type": "text",
          "text": "What is the email address of Jim?"
        }
      ]
    },
    {
      "role": "assistant",
      "content": [
        {
          "type": "tool_call",
          "toolCallId": "call_Xk2MJTRWFbIZWSGk2jAnQR9n",
          "toolName": "TT_GET_USERS",
          "args": "{}"
        }
      ]
    },
    {
      "role": "tool",
      "content": [
        {
          "type": "tool_result",
          "toolCallId": "call_Xk2MJTRWFbIZWSGk2jAnQR9n",
          "toolName": "TT_GET_USERS",
          "result": "[{\"user_id\":2,\"first_name\":\"Pam\",\"last_name\":\"Beesly\",\"email\":\"pam.beesly@dundermifflin.com\",\"hire_date\":\"2024-11-01\",\"is_active\":\"Y\"},{\"user_id\":4,\"first_name\":\"Jim\",\"last_name\":\"Halpert\",\"email\":\"jim.halpert@dundermifflin.com\",\"hire_date\":\"2024-02-01\",\"is_active\":\"Y\"},{\"user_id\":6,\"first_name\":\"Kevin\",\"last_name\":\"Malone\",\"email\":\"kevin.malone@dundermifflin.com\",\"hire_date\":\"2024-10-01\",\"is_active\":\"N\"},{\"user_id\":5,\"first_name\":\"Angela\",\"last_name\":\"Martin\",\"email\":\"angela.martin@dundermifflin.com\",\"hire_date\":\"2024-05-01\",\"is_active\":\"Y\"},{\"user_id\":3,\"first_name\":\"Dwight\",\"last_name\":\"Schrute\",\"email\":\"dwight.schrute@dundermifflin.com\",\"hire_date\":\"2024-02-01\",\"is_active\":\"Y\"},{\"user_id\":1,\"first_name\":\"Michael\",\"last_name\":\"Scott\",\"email\":\"michael.scott@dundermifflin.com\",\"hire_date\":\"2024-05-01\",\"is_active\":\"Y\"}]"
        }
      ]
    },
    {
      "role": "assistant",
      "content": [
        {
          "type": "text",
          "text": "Jim's email address is jim.halpert@dundermifflin.com.",
          "providerOptions": {
            "refusal": null,
            "annotations": []
          }
        }
      ]
    }
  ]
}

Write data and parameters

The system in this example is a time tracking system. Each employee in the users table can clock in on any project in the projects table, with a note.

This needs three tools:

• TT_GET_USERS: to get the users
• TT_GET_PROJECTS: to get the projects (the same pattern as the users example)
• TT_CLOCK_IN: to clock in on a project

Function definition

The clock-in tool needs parameters. Examine the TT_CLOCK_IN function:

FUNCTION clock_in_json(
    p_parameters in clob
) return clob
as
    l_json json_object_t;
    l_user_email VARCHAR2(255 char);
    l_project_name VARCHAR2(255 char);
    l_notes VARCHAR2(4000 char);
begin
    logger.log_info('clock_in_json called with parameters: ' || p_parameters);

    l_json := json_object_t(p_parameters);
    l_user_email := l_json.get_string('user_email');
    l_project_name := l_json.get_string('project_name');
    l_notes := l_json.get_string('notes');

    if l_user_email is null then
        return 'Error: user_email is required';
    elsif l_project_name is null then
        return 'Error: project_name is required';
    end if;

    begin
        return clock_in(
            p_email => l_user_email,
            p_project_name => l_project_name,
            p_notes => l_notes
        );
    exception
        when others then
            return 'Error: ' || sqlerrm || ' - Backtrace: ' || sys.dbms_utility.format_error_backtrace;
    end;
end clock_in_json;

The function has one parameter of type CLOB. The AI communicates in JSON. The function must therefore extract each attribute from this CLOB.

The function returns a CLOB again, because an LLM expects text. After a successful clock-in, it returns a text such as “User (X) clocked in on project (Y) with note (Z)”.

The function raises no error. It returns the error message as text instead. The LLM reads this message and can recover. For a project name with a typo, the function returns “Error: Project ‘ProjecX’ not found”. The AI then asks the user for the correct name, or calls the project lookup tool again.

Create tool definition

Now create the tool definition for the clock-in function. A detailed description with example parameters helps the AI. The AI then knows what it can do with the tool.

declare
  l_tool_id uc_ai_tools.id%type;
  l_schema  json_object_t;
begin
  l_schema := json_object_t('{
  "$schema": "http://json-schema.org/draft-07/schema#",
  "type": "object",
  "properties": {
    "user_email": {
      "type": "string",
      "description": "The email address of the user to clock in"
    },
    "project_name": {
      "type": "string",
      "description": "The name of the project to clock in to"
    },
    "notes": {
      "type": "string",
      "description": "Optional description of what the user is working on"
    }
  },
  "required": [
    "user_email",
    "project_name"
  ]
}');

    -- Create the tool
  l_tool_id := uc_ai_tools_api.create_tool_from_schema(
    p_tool_code => 'TT_CLOCK_IN',
    p_description => 'Clock in a user to the time tracking system. This needs a user_email and project_name as parameters. You can get these from other tools. Optionally pass notes if given by the user.
Example parameters: {"user_email": "user@example.com","project_name": "TV Marketing", "notes": "Look for actors"} or {"user_email": "john.doe@gmail.com","project_name": "Inventing Teleportation"}',
    p_function_call => 'return tt_timetracking_api.clock_in_json(:parameters);',
    p_json_schema => l_schema,
    p_tags => apex_t_varchar2('time-tracking','users','projects')
  );
  commit;
end;
/

Provider (server-side) tools

The tools above run in your database. Most providers also have their own server-side tools that run on the infrastructure of the provider. Examples are Anthropic web search, the OpenAI Responses API web search, and Google search. UC AI can pass these tools through. Set uc_ai.g_provider_tools to a JSON array of provider-native tool definitions. UC AI appends them to the tools array of the request without a change. The provider runs them, never your database:

BEGIN
  uc_ai.g_provider_tools := json_array_t(
    '[{"type": "web_search_20250305", "name": "web_search", "max_uses": 5}]'
  );

  -- next generate_text call lets the model search the web provider-side
END;
/

Provider tools work together with your local tools. UC AI sends them also when g_enable_tools is false. See Provider (server-side) tools for the full reference and per-provider formats.

Tools vs. MCP

MCP and tools are similar. In both, you tell the AI what it can do. The AI then decides from the conversation if it needs a call.

The architecture is the main difference. MCP targets distributed systems. In such a system, the AI chat runs on your system but needs context from another system. An email provider is a good example. It gives you an MCP server for your emails. Your system then works with the emails on the server of the email provider.

UC AI calls the AI from inside the database, next to your data. It therefore needs no MCP server. You read your data directly with SQL and PL/SQL, and you give it to the AI in a tool definition.

UC AI does not call external MCP servers today. A future version can add this feature. Your users then reach their emails through the MCP of their email provider.