Generate Text

Parameters

The uc_ai.generate_text() function has two overloaded signatures:

Function Signatures

Signature 1: Individual Prompts

FUNCTION generate_text (
  p_user_prompt           IN CLOB,
  p_system_prompt         IN CLOB DEFAULT NULL,
  p_provider              IN provider_type,
  p_model                 IN model_type,
  p_max_tool_calls        IN PLS_INTEGER DEFAULT NULL,
  p_response_json_schema  IN JSON_OBJECT_T DEFAULT NULL
) RETURN JSON_OBJECT_T;

Signature 2: Message Array

FUNCTION generate_text (
  p_messages              IN JSON_ARRAY_T,
  p_provider              IN provider_type,
  p_model                 IN model_type,
  p_max_tool_calls        IN PLS_INTEGER DEFAULT NULL,
  p_response_json_schema  IN JSON_OBJECT_T DEFAULT NULL
) RETURN JSON_OBJECT_T;

Parameter Reference

For Signature 1 (Individual Prompts)

Parameter	Type	Required	Description
p_user_prompt	CLOB	Yes	The main prompt from the user. This is the primary input for the AI model.
p_system_prompt	CLOB	No	System-level instructions that guide the AI’s behavior and response style.
p_provider	provider_type	Yes	AI provider to use. See uc_ai spec for constants.
p_model	model_type	Yes	Specific AI model to use (varies by provider). See uc_ai spec for constants.
p_max_tool_calls	PLS_INTEGER	No	Maximum number of tool calls allowed in the conversation (default: 10).
p_response_json_schema	JSON_OBJECT_T	No	JSON schema for structured output. If provided, response will conform to schema.

For Signature 2 (Message Array)

Parameter	Type	Required	Description
p_messages	JSON_ARRAY_T	Yes	Array of conversation messages in the standard format (see Message Structure below).
p_provider	provider_type	Yes	AI provider to use. See uc_ai spec for constants.
p_model	model_type	Yes	Specific AI model to use (varies by provider). See uc_ai spec for constants.
p_max_tool_calls	PLS_INTEGER	No	Maximum number of tool calls allowed in the conversation (default: 10).
p_response_json_schema	JSON_OBJECT_T	No	JSON schema for structured output. If provided, response will conform to schema.

Parameter Details

p_user_prompt (Required - Signature 1)

The main user input that you want the AI to respond to. This can be a question, instruction, or any text you want the AI to process.

l_result := uc_ai.generate_text(
  p_user_prompt => 'What is Oracle APEX?',
  p_provider => uc_ai.c_provider_openai,
  p_model => uc_ai_openai.c_model_gpt_4o_mini
);

p_system_prompt (Optional - Signature 1)

System-level instructions that define the AI’s role, behavior, and response style. This helps shape how the AI interprets and responds to user prompts.

-- Professional assistant
l_result := uc_ai.generate_text(
  p_user_prompt => 'Explain database normalization',
  p_system_prompt => 'You are a professional database consultant. Provide clear, technical explanations with practical examples.',
  p_provider => uc_ai.c_provider_openai,
  p_model => uc_ai_openai.c_model_gpt_4o
);

-- Casual tone
l_result := uc_ai.generate_text(
  p_user_prompt => 'How do I optimize this SQL query?',
  p_system_prompt => 'You are a friendly database expert. Use simple language and provide step-by-step guidance.',
  p_provider => uc_ai.c_provider_anthropic,
  p_model => uc_ai_anthropic.c_model_claude_3_5_haiku
);

p_messages (Required - Signature 2)

A pre-constructed conversation in the form of a JSON_ARRAY_T containing message objects. This allows you to provide a complete conversation history or continue from a previous conversation. Each message must follow the standard message format with role and content properties.

DECLARE
  l_messages JSON_ARRAY_T;
  l_result JSON_OBJECT_T;
BEGIN
  -- Build conversation messages
  l_messages := JSON_ARRAY_T();

  -- Add system message
  l_messages.append(JSON_OBJECT_T('{
    "role": "system",
    "content": "You are a helpful SQL expert."
  }'));

  -- Add user messages
  l_messages.append(JSON_OBJECT_T('{
    "role": "user",
    "content": "How do I create a table in Oracle?"
  }'));

  l_messages.append(JSON_OBJECT_T('{
    "role": "assistant",
    "content": "You can create a table using the CREATE TABLE statement..."
  }'));

  l_messages.append(JSON_OBJECT_T('{
    "role": "user",
    "content": "Now show me how to add a foreign key constraint"
  }'));

  -- Continue the conversation
  l_result := uc_ai.generate_text(
    p_messages => l_messages,
    p_provider => uc_ai.c_provider_openai,
    p_model => uc_ai_openai.c_model_gpt_4o_mini
  );
END;
/

Message Structure: Each message in the array should have this structure:

• role: One of "system", "user", "assistant", or "tool"
• content: The message content (can be null for assistant messages with tool calls)
• tool_calls: (Optional) Array of tool calls for assistant messages
• tool_call_id: (Required for tool messages) ID of the tool call being responded to

Use Cases for Message Array:

• Continue previous conversations
• Implement conversation memory/history
• Fine-tune conversation context
• Handle complex multi-turn interactions
• Process conversation logs or transcripts

Usage Examples

Choosing Between Signatures

Use Signature 1 (Individual Prompts) when:

• Starting a new conversation
• Making simple, single-turn requests
• You want the function to handle message array construction

Use Signature 2 (Message Array) when:

• Continuing an existing conversation
• You need fine control over the conversation structure
• Implementing conversation memory across multiple calls
• Processing pre-existing conversation logs

Simple Example: Question with System Prompt

Using Signature 1:

DECLARE
  l_result JSON_OBJECT_T;
BEGIN
  l_result := uc_ai.generate_text(
    p_user_prompt => 'What are the benefits of using Oracle APEX?',
    p_system_prompt => 'You are an Oracle APEX expert consultant.',
    p_provider => uc_ai.c_provider_openai,
    p_model => uc_ai_openai.c_model_gpt_4o_mini
  );
END;
/

Advanced: Conversation Continuation

This example shows how to continue a conversation using the message array signature.

ecause UC AI does two-way conversions between the provider-specific message formats and the standard format we can actually change the provider and model in the middle of a conversation, allowing for flexibility in AI interactions.

declare
  l_messages json_array_t;
  l_result json_object_t;
  l_response_messages json_array_t;
begin
  -- Initial conversation
  l_result := uc_ai.generate_text(
    p_user_prompt => 'What is the rarest chemical element?',
    p_system_prompt => 'You are an assistant for chemical students in school.',
    p_provider => uc_ai.c_provider_openai,
    p_model => uc_ai_openai.c_model_gpt_4o_mini
  );

  dbms_output.put_line('Response: ' || l_result.get_string('final_message'));

  -- Get the complete message history from the first call
  l_messages := l_result.get_array('messages');

  -- Add a follow-up question
  l_messages.append(
    uc_ai_message_api.create_simple_user_message(
      'How is it called in german, japanese and portuguese?'
    )
  );

  -- Continue the conversation with full context
  l_result := uc_ai.generate_text(
    p_messages => l_messages,
    p_provider => uc_ai.c_provider_google,
    p_model => uc_ai_google.c_model_gemini_2_5_flash
  );

  dbms_output.put_line('Follow-up response: ' || l_result.get_string('final_message'));
END;

Additional Parameter Details

p_provider (Required - Both Signatures)

Specifies which AI provider to use. Available providers:

Provider Constant	Value	Description
uc_ai.c_provider_openai	'openai'	OpenAI GPT models
uc_ai.c_provider_anthropic	'anthropic'	Anthropic Claude models
uc_ai.c_provider_google	'google'	Google Gemini models

-- Using different providers
l_result := uc_ai.generate_text(
  p_user_prompt => 'Explain machine learning',
  p_provider => uc_ai.c_provider_openai,  -- or c_provider_anthropic, c_provider_google
  p_model => uc_ai_openai.c_model_gpt_4o_mini
);

p_model (Required - Both Signatures)

The specific AI model to use. See the package headers for available models under each provider:

• uc_ai_openai
• uc_ai_anthropic
• uc_ai_google

-- Model selection examples
DECLARE
  l_result JSON_OBJECT_T;
BEGIN
  -- For simple tasks - use fast, cost-effective models
  l_result := uc_ai.generate_text(
    p_user_prompt => 'Summarize this paragraph',
    p_provider => uc_ai.c_provider_openai,
    p_model => uc_ai_openai.c_model_gpt_4o_mini
  );

  -- For complex analysis - use more capable models (GPT-4.5, Sonnet 4, Gemini 2.5 Pro, etc.)
  l_result := uc_ai.generate_text(
    p_user_prompt => 'Analyze this complex financial dataset and provide strategic recommendations',
    p_provider => uc_ai.c_provider_anthropic,
    p_model => uc_ai_anthropic.c_model_claude_4_sonnet
  );
END;
/

p_max_tool_calls (Optional - Both Signatures)

Limits the maximum number of tool calls the AI can make during the conversation. This prevents infinite loops and controls costs.

• Default: NULL (10 tool calls)

-- Limiting tool calls
l_result := uc_ai.generate_text(
  p_user_prompt => 'Calculate monthly sales totals and create a summary report',
  p_system_prompt => 'You have access to database tools to query sales data',
  p_provider => uc_ai.c_provider_openai,
  p_model => uc_ai_openai.c_model_gpt_4o,
  p_max_tool_calls => 5  -- Allow up to 5 tool calls
);

-- Check if limit was exceeded
IF l_result.get_string('finish_reason') = 'max_tool_calls_exceeded' THEN
  DBMS_OUTPUT.PUT_LINE('Consider increasing max_tool_calls for this task');
END IF;

p_response_json_schema (Optional - Both Signatures)

You can provide a custom JSON schema and force the AI to answer in this format. This is useful for ensuring consistent and predictable output structures, especially when using that output in automated systems or storing the results.

See Structured Output for more information and usage examples.

Return Object Overview

The uc_ai.generate_text() function returns a comprehensive JSON_OBJECT_T containing detailed information about the AI interaction, including the conversation history, usage statistics, and metadata about the AI model and provider used.

Structure

The return object contains the following properties:

{
  "final_message": "The AI's final response text",
  "messages": [...],           -- Complete conversation history (JSON_ARRAY_T)
  "structured_output": {...},  -- Parsed JSON object (only when p_response_json_schema provided)
  "finish_reason": "stop",     -- How the conversation ended (VARCHAR2)
  "usage": {                   -- Token usage statistics (JSON_OBJECT_T)
    "prompt_tokens": 45,
    "completion_tokens": 23,
    "total_tokens": 68
  },
  "tool_calls_count": 2,       -- Number of tools executed (NUMBER)
  "model": "gpt-4o-mini",      -- AI model used (VARCHAR2)
  "provider": "openai"         -- AI provider (VARCHAR2)
}

Properties Reference

Property	Type	Description
final_message	CLOB	The final assistant message from the conversation
messages	JSON_ARRAY_T	Complete conversation history including user, system, assistant, and tool messages
structured_output	JSON_OBJECT_T	Parsed JSON object that conforms to the schema (only present when p_response_json_schema provided)
finish_reason	VARCHAR2	Completion reason: stop, tool_calls, length, content_filter, max_tool_calls_exceeded
usage	JSON_OBJECT_T	Token usage statistics with prompt_tokens, completion_tokens, and total_tokens
tool_calls_count	NUMBER	Total number of tool calls executed during the conversation
model	VARCHAR2	The AI model used (e.g., gpt-4o-mini, claude-3-5-haiku)
provider	VARCHAR2	AI provider identifier (openai, anthropic, google)

Accessing Return Values

Basic Usage

DECLARE
  l_result JSON_OBJECT_T;
  l_final_response CLOB;
  l_finish_reason VARCHAR2(255);
BEGIN
  -- Make AI request
  l_result := uc_ai.generate_text(
    p_user_prompt => 'What is the weather like in London?',
    p_system_prompt => 'You are a helpful weather assistant.'
  );

  -- Get the final AI response
  l_final_response := l_result.get_string('final_message');
  DBMS_OUTPUT.PUT_LINE('AI Response: ' || l_final_response);

  -- Check how the conversation ended
  l_finish_reason := l_result.get_string('finish_reason');
  DBMS_OUTPUT.PUT_LINE('Finish reason: ' || l_finish_reason);
END;
/

Usage Statistics

DECLARE
  l_result JSON_OBJECT_T;
  l_usage JSON_OBJECT_T;
  l_total_tokens NUMBER;
  l_tool_calls_count NUMBER;
BEGIN
  l_result := uc_ai.generate_text(
    p_user_prompt => 'Calculate the area of a circle with radius 5',
    p_max_tool_calls => 3
  );

  -- Get usage statistics
  l_usage := l_result.get_object('usage');
  l_total_tokens := l_usage.get_number('total_tokens');
  l_tool_calls_count := l_result.get_number('tool_calls_count');

  DBMS_OUTPUT.PUT_LINE('Tokens used: ' || l_total_tokens);
  DBMS_OUTPUT.PUT_LINE('Tool calls made: ' || l_tool_calls_count);
  DBMS_OUTPUT.PUT_LINE('Model: ' || l_result.get_string('model'));
  DBMS_OUTPUT.PUT_LINE('Provider: ' || l_result.get_string('provider'));
END;
/

Message History

The messages array contains the complete conversation history with different message types:

Message Structure

[
  {
    "role": "system",
    "content": "You are a helpful assistant."
  },
  {
    "role": "user",
    "content": "What is the weather in London?"
  },
  {
    "role": "assistant",
    "content": null,
    "tool_calls": [
      {
        "id": "call_123",
        "type": "function",
        "function": {
          "name": "get_weather",
          "arguments": "{\"location\": \"London\"}"
        }
      }
    ]
  },
  {
    "role": "tool",
    "tool_call_id": "call_123",
    "content": "Temperature: 18°C, Condition: Partly cloudy"
  },
  {
    "role": "assistant",
    "content": "The current weather in London is 18°C and partly cloudy."
  }
]

Processing Message History

DECLARE
  l_result JSON_OBJECT_T;
  l_messages JSON_ARRAY_T;
  l_message JSON_OBJECT_T;
  l_role VARCHAR2(100);
  l_content CLOB;
BEGIN
  l_result := uc_ai.generate_text(
    p_user_prompt => 'Tell me a joke and then explain why it is funny'
  );

  -- Get the complete message history
  l_messages := l_result.get_array('messages');

  -- Loop through all messages
  FOR i IN 0 .. l_messages.get_size - 1 LOOP
    l_message := JSON_OBJECT_T(l_messages.get(i));
    l_role := l_message.get_string('role');
    l_content := l_message.get_string('content');

    DBMS_OUTPUT.PUT_LINE('Role: ' || l_role);
    DBMS_OUTPUT.PUT_LINE('Content: ' || SUBSTR(l_content, 1, 200));
    DBMS_OUTPUT.PUT_LINE('---');
  END LOOP;
END;
/

Finish Reasons

The finish_reason indicates how the AI conversation ended:

Finish Reason	Description
stop	The model completed its response naturally
tool_calls	The response ended with tool calls
length	Response was truncated due to max tokens limit
content_filter	Response was filtered due to content policy
max_tool_calls_exceeded	Maximum number of tool calls was reached

Handling Different Finish Reasons

DECLARE
  l_result JSON_OBJECT_T;
  l_finish_reason VARCHAR2(255);
BEGIN
  l_result := uc_ai.generate_text(
    p_user_prompt => 'Analyze this complex data...',
    p_max_tool_calls => 5
  );

  l_finish_reason := l_result.get_string('finish_reason');

  CASE l_finish_reason
    WHEN 'stop' THEN
      DBMS_OUTPUT.PUT_LINE('Conversation completed successfully');

    WHEN 'length' THEN
      DBMS_OUTPUT.PUT_LINE('Response was truncated due to length limits');

    WHEN 'max_tool_calls_exceeded' THEN
      DBMS_OUTPUT.PUT_LINE('Maximum tool calls reached - consider increasing limit');

    WHEN 'content_filter' THEN
      DBMS_OUTPUT.PUT_LINE('Response was filtered due to content policy');

    ELSE
      DBMS_OUTPUT.PUT_LINE('Unexpected finish reason: ' || l_finish_reason);
  END CASE;
END;
/

Error Handling

The uc_ai.generate_text function will raise an exception if the AI provider returns an error or if the request fails.