Skip to content
UC AI

Tools / Functions

Tools allow AI to interact with your data

If you want the AI to be able to interact with your database or external APIs, you can teach it some capabilities by registering tools. During the conversation the AI decides if it it needs to use one of the tools and requests an execution. UC AI will then call the tool and return the result to the AI, which can then use it in its response.

You can divide tools into two categories:

  • Additional Context: you don’t want to bloat the context of your conversation with too much information that only might be useful. Too much context results in slower and more expensive responses and reduced accuracy. Instead a tool can provide context and then only be used if the user asks for information about it.
  • Actions: Tools can enable the AI to create, update, or delete data in your DB or just trigger some action in another system via a REST call.

Tools call a PL/SQL function

Section titled “Tools call a PL/SQL function”

With UC AI a tool is just a PL/SQL funciton the AI can call. The PL/SQL function should have these attributes:

  • The AI can pass 1 parameter: CLOB. It will be a JSON so it is still possible to pass multiple arguments. It can also be just an empty object.
  • returns CLOB as a result

Similar to Structured Output tools require you to describe the parameters in a JSON Schema. If you have a tool that is called GET_WEATHER which has two parameters: city and unit (defaults to celsius), the schema could look 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"]
}

The better the schema definition the easier it is for AI to call the tool.

Best practices

Section titled “Best practices”
  • Always return feedback: any information you have is useful can help the AI. If there was an error, return a descriptive error text. The AI can try to call it again with a corrected input next time. Also when an action succeeded, tell the AI. Then it knows that it succeeded.
  • Validate inputs: Check that you get all required parameters and that the values follow the constraints (enum, min, max, etc.). If there is a violation return an error text. The AI can respond and call the tool again with fixed parameters.
  • Thoroughly describe the tool: The more context about a tool is available the easier it is for the AI to decide when to call it. Take time optimize the descriptions and constraints of your JSON schema. For some providers it can also help to add text to the system prompt to give examples when to call which tools and with example parameters.
  • There are huge gaps in provider performance: Picking the correct tools and calling them correctly highly depends on the intelligence of the model. Enabling Reasoning helps the AI to plan steps and thus to call tools more precisely. Still some providers are just worse at calling tools effectively than others. In my experience Anthropic is currently by far outperforming all other providers.
  • Restrict available tools via tags: if you have many tools registered it is a lot of context for the AI making decisions when to use which tools harder. If possible give less options by filtering by tags.

Registering a tool

Section titled “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: Unique identifier
  • p_description: General description of what your tool does. Having a good description can drastically improve performance
  • p_function_call: Which PL/SQL function it should call. It needs to return a CLOB and if you have parameters use :paramaters to pass them into a CLOB parameter.
  • p_json_schema: JSON schema of the parameters
  • p_tags: Add tags to the tool. You can enable/disable certain tools for a call via tags.

Using tools

Section titled “Using tools”

To allow the AI to use tools you have to manually enable them. By default it will use all tools that are enabled. You can use uc_ai.g_tool_tags to just give a subset of available tools to the AI.

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,
    p_max_tool_calls => 6
  );
end;

With p_max_tool_calls you can restrict how many tools the AI can call in one interaction.

Examples

Section titled “Examples”

Look up data

Section titled “Look up data”

Data structure

Section titled “Data structure”

We have a table with some 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

Section titled “Function to return data”

Now we create a function that will return this data as a single CLOB (as JSON):

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;

Tool registration

Section titled “Tool registration”

Now we can register a tool that teaches 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;
/

Testing

Section titled “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_2_5_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;
/

And 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

Section titled “Write data and parameters”

This example is more advanced. The system is actually a timetracking system where each employee (from the users table) can clock in on any project (projects table) with a note.

To achieve this we actually will use three tools:

  • TT_GET_USERS: to get the users
  • TT_GET_PROJECTS: to get the projects (works similar to the users example)
  • TT_CLOCK_IN: to clock in on a project

Function definition

Section titled “Function definition”

The clock in tool is more advanced as it needs parameters to work. So let’s take a closer look to 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;

Note that the only parameter is a CLOB. The AI speaks in JSON and that’s why we get a CLOB and have to manually extract the attributes from it.

We also return a CLOB again. We are talking to an LLM so it expects text responses. So on success we return somethin like “User (X) clocked in succesfuklly on project (Y) with note (Z)”.

Also note that we don’t raise errors but instead also return text with the error message. As LLMs understand the message they can actually recover. If you pass them a project with a typo, and the function returns “Error: Project ‘ProjecX’ not found”, the AI can then either ask the user to correct the project name or call the project lookup tool again and try to spot the typos.

Create tool definition

Section titled “Create tool definition”

Now let’s create the tool definition for the clock in function. Note that it is helpful to give and in-depth description even with example parameters. This helps the AI to understand 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;
/

Tools vs. MCP

Section titled “Tools vs. MCP”

MCP and tools are very similar. The main benefit is that you can tell the AI what it can do with the tools and the AI decides based on the conversation if it needs to call a tool or not.

The main difference between tools and MCPs is their architectural focus: MCPs are specifically designed for distributed systems. Meaning that you have an AI chat on your system but need to access context from another system. A nice example is your email provider that gives you access to an MCP server for your emails. On your system you can interact with the emails that are stored on the server of the mail provider.

As this package is designed to call AI from within the database (next to your data) there is no need to run an MCP server. You can directly access it via SQL and PL/SQL and make it available to the AI via the tool definitions.

One potential improvement is allowing UC AI to call foreign MCPs. So that your users can access for example their emails from the MCP of the email provider. This is not yet implemented but could be a future feature.