Tools / Functions

Tools and functions are magic dust to make your AI assistant truly powerful.

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 might think that it needs to use one of the tools. UC AI will then call the tool and return the result to the AI, which can then use it in its response.

Example 1: Look up data

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

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;

Return CLOB

AIs work via text. So you have to transform all your data into a single CLOB value. The AI will then be able to use it in its response.

Tool registration

Now we can register a tool that teaches the AI how to call this function:

insert into UC_AI_TOOLS (
  CODE,
  DESCRIPTION,
  ACTIVE,
  VERSION,
  FUNCTION_CALL
) values (
  'TT_GET_USERS',
  'Get information on all the users in the system',
  1,
  '1.0'
  ,'return my_pkg.get_all_users_json();'
);

commit;

Function call

You have to provide a function body that returns a CLOB value.

Testing

declare
  l_result JSON_OBJECT_T;
  l_final_message CLOB;
begin
  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');
  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": []
          }
        }
      ]
    }
  ]
}

Example 2: Using parameters to write data

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

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

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:

    insert into UC_AI_TOOLS (
      CODE,
      DESCRIPTION,
      ACTIVE,
      VERSION,
      FUNCTION_CALL
    ) values (
      'TT_CLOCK_IN',
      '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"}',
      1,
      '1.0'
      ,'return tt_timetracking_api.clock_in_json(:parameters);'
    );

Define parameters

But actually there is a defined way to define parameters for a tool. So we can use the UC_AI_TOOL_PARAMETERS table for this.

First we need to insert a parent object parameter. Keep in mind that I used the :parameters bind variable in the function call above. We need to give the same name here.

-- get the tool id first
Insert into UC_AI_TOOL_PARAMETERS (
  TOOL_ID,NAME,DESCRIPTION,REQUIRED,DATA_TYPE,PARENT_PARAM_ID)
values (
  CHANGE_ME,'parameters','JSON object containing parameters','1','object', null
);

Under this object we can define the real parameters.

-- get id form the previous insert first
Insert into UC_AI_TOOL_PARAMETERS (
  TOOL_ID,NAME,DESCRIPTION,REQUIRED,DATA_TYPE,PARENT_PARAM_ID)
values (
  TOOL_ID,'user_email','Email of the user','1','string', PREV_PARAM_ID
);

Insert into UC_AI_TOOL_PARAMETERS (
  TOOL_ID,NAME,DESCRIPTION,REQUIRED,DATA_TYPE,PARENT_PARAM_ID)
values (
  TOOL_ID,'project_name','Name of the project','1','string', PREV_PARAM_ID
);

Insert into UC_AI_TOOL_PARAMETERS (
  TOOL_ID,NAME,DESCRIPTION,REQUIRED,DATA_TYPE,PARENT_PARAM_ID)
values (
  TOOL_ID,'notes','Optional description of what the user is working on','0','string', PREV_PARAM_ID
);

Easier tool management

I am working on an APEX app that would make managing tools easier. So keep an eye for new releases.