Prompt Profiles

With a prompt profile, you can create, version, and reuse an AI prompt template in your whole application.

Do not write a prompt into your code. A prompt profile holds your prompt templates in one place, with parameter substitution, model configuration, and version control. The prompts then stay consistent, you can change a prompt without a code change, and you can test another version.

Why use prompt profiles?

A prompt profile gives you:

• Centralized Management: keep all prompts in the database, and not in your code
• Parameter Substitution: use a placeholder in a template. UC AI replaces it with a value at run time
• Version Control: create several versions of one prompt, and release them step by step
• Configuration Management: keep the model settings, the structured output schemas, and the other configuration next to the prompt
• Status Management: mark a profile as draft, active, or archived, to control which version runs
• Reusability: use one prompt template in different parts of your application

How it works

A prompt profile holds:

• Code: a unique identifier for the profile, for example SUMMARIZE_TEXT or EXTRACT_DATA
• Version: Numeric version to support multiple iterations
• Status: Draft, active, or archived
• Templates: System and user prompt templates with placeholders
• Configuration: Provider, model, and optional model settings
• Schemas: Optional JSON schemas for structured output and parameters

When you execute a profile, UC AI:

1. Reads the profile by code and version, or by ID
2. Validates that every placeholder has a parameter
3. Replaces each placeholder with its value
4. Applies model configuration settings
5. Calls the AI with the prepared prompts

Creating a prompt profile

Use uc_ai_prompt_profiles_api.create_prompt_profile to create a new profile:

DECLARE
  l_profile_id NUMBER;
BEGIN
  l_profile_id := uc_ai_prompt_profiles_api.create_prompt_profile(
    p_code => 'SUMMARIZE_TEXT',
    p_description => 'Summarizes text content in a specified style',
    p_system_prompt_template => 'You are a {style} assistant that creates concise summaries.',
    p_user_prompt_template => 'Summarize the following text: {text}',
    p_provider => uc_ai.c_provider_openai,
    p_model => uc_ai_openai.c_model_gpt_5_6_luna,
    p_version => 1,
    p_status => uc_ai_prompt_profiles_api.c_status_draft
  );

  COMMIT;
END;
/

Parameters

• p_code: Unique identifier for the profile
• p_description: Human-readable description
• p_system_prompt_template: System prompt with placeholders ({placeholder})
• p_user_prompt_template: User prompt with placeholders
• p_provider: AI provider (for example uc_ai.c_provider_openai)
• p_model: Model identifier (for example uc_ai_openai.c_model_gpt_5_6_luna)
• p_model_config_json: Optional JSON configuration (see Configuration)
• p_response_schema: Optional JSON schema for structured output
• p_parameters_schema: Optional JSON schema defining expected parameters
• p_version: Version number (defaults to 1)
• p_status: Status - ‘draft’, ‘active’, or ‘archived’ (defaults to ‘draft’)

Tip

Start with the ‘draft’ status for your tests. Change it to ‘active’ when the profile is ready for production.

Using placeholders

With a placeholder, a prompt becomes dynamic. Use the {placeholder_name} syntax in your templates:

-- Template with placeholders
p_system_prompt_template => 'You are a {role} assistant.',
p_user_prompt_template => 'What is the capital of {country}?'

Give a value for each placeholder when you run the profile:

DECLARE
  l_result json_object_t;
  l_params json_object_t := json_object_t();
BEGIN
  l_params.put('role', 'geography');
  l_params.put('country', 'France');

  l_result := uc_ai_prompt_profiles_api.execute_profile(
    p_code => 'GEOGRAPHY_HELPER',
    p_parameters => l_params
  );

  DBMS_OUTPUT.PUT_LINE('Answer: ' || l_result.get_clob('final_message'));
END;
/

Note

A placeholder match is case-insensitive. Both {Country} and {country} match a parameter with the name ‘country’.

In an agent run, a placeholder can also take its value from the run context. A caller that starts the run with p_run_context => json_object_t('{"document_id":"7"}') does not have to pass document_id a second time as an input parameter. An input parameter of the same name wins.

Placeholder validation

UC AI validates that every placeholder in your templates has a parameter. For a missing parameter, UC AI raises this error:

Missing parameter for placeholder: {text}

A placeholder name can hold only letters, numbers, and the underscore: {valid_name_123}

Executing a profile

Execute by code

Read and run the latest active version:

DECLARE
  l_result json_object_t;
  l_params json_object_t := json_object_t();
BEGIN
  l_params.put('text', 'Long article text here...');
  l_params.put('style', 'professional');

  l_result := uc_ai_prompt_profiles_api.execute_profile(
    p_code => 'SUMMARIZE_TEXT',
    p_parameters => l_params
  );

  DBMS_OUTPUT.PUT_LINE(l_result.get_clob('final_message'));
END;
/

Execute specific version

Target a specific version:

l_result := uc_ai_prompt_profiles_api.execute_profile(
  p_code => 'SUMMARIZE_TEXT',
  p_version => 2,  -- Use version 2 specifically
  p_parameters => l_params
);

Execute by ID

If you have the profile ID:

l_result := uc_ai_prompt_profiles_api.execute_profile(
  p_id => 42,
  p_parameters => l_params
);

Model configuration

The p_model_config_json parameter stores the model settings in the profile:

DECLARE
  l_profile_id NUMBER;
  l_config CLOB := '{
    "g_enable_reasoning": true,
    "g_reasoning_level": "high"
  }';
BEGIN
  l_profile_id := uc_ai_prompt_profiles_api.create_prompt_profile(
    p_code => 'COMPLEX_ANALYSIS',
    p_description => 'Analyzes complex scenarios with reasoning',
    p_system_prompt_template => 'You are an analytical assistant.',
    p_user_prompt_template => 'Analyze: {scenario}',
    p_provider => uc_ai.c_provider_openai,
    p_model => uc_ai_openai.c_model_gpt_5_6_terra,
    p_model_config_json => l_config
  );
END;
/

Available configuration options

Each configuration option maps to a UC AI global variable:

Root-level options:

• g_base_url: Base URL for API endpoints (string)
• g_enable_reasoning: Enable reasoning mode (boolean)
• g_reasoning_level: Reasoning level - ‘low’, ‘medium’, or ‘high’ (string)
• g_enable_tools: Enable tools/functions (boolean)
• g_enable_programmatic_tools: Also give the uc_ai__run_code meta-tool of code mode next to the normal tools (boolean). It needs g_enable_tools and the MLE sandbox
• g_max_tool_calls: Maximum number of tool calls (number)
• g_tool_tags: Tool tags that filter the available tools (an array of strings, or one string)
• g_apex_web_credential: APEX web credential static ID (string)
• g_extra_headers: Extra HTTP request headers for every provider request (an object of name and value pairs, for example {"X-Tenant-Id": "acme"})

Provider-specific options:

These options sit under a provider key, for example "openai": { ... }:

• OpenAI (openai):

  • g_reasoning_effort: Reasoning effort level (string)
  • g_apex_web_credential: Provider-specific web credential (string)

• Anthropic (anthropic):

  • g_max_tokens: Maximum response length (number)
  • g_reasoning_budget_tokens: Token budget for reasoning (number)
  • g_apex_web_credential: Provider-specific web credential (string)

• Google (google):

  • g_reasoning_budget: Reasoning budget (number)
  • g_apex_web_credential: Provider-specific web credential (string)
  • g_embedding_task_type: Task type for embeddings (string)
  • g_embedding_output_dimensions: Output dimensions for embeddings (number)

• xAI (xai):

  • g_reasoning_effort: Reasoning effort level (string)
  • g_apex_web_credential: Provider-specific web credential (string)

• OpenRouter (openrouter):

  • g_reasoning_effort: Reasoning effort level (string)
  • g_apex_web_credential: Provider-specific web credential (string)

• Mistral (mistral):

  • g_apex_web_credential: Provider-specific web credential (string)

• Ollama (ollama):

  • g_apex_web_credential: Provider-specific web credential (string)

• OCI (oci):

  • g_apex_web_credential: Provider-specific web credential (string)

Example with provider-specific settings:

{
  "g_enable_reasoning": true,
  "anthropic": {
    "g_max_tokens": 2000,
    "g_reasoning_budget_tokens": 5000
  }
}

UC AI applies the configuration when it runs the profile. It sets the global variable of each option.

Structured output

Define a response schema for a consistent JSON answer that your code can parse:

DECLARE
  l_profile_id NUMBER;
  l_schema CLOB := '{
    "type": "object",
    "properties": {
      "summary": {
        "type": "string",
        "description": "Brief summary of the text"
      },
      "key_points": {
        "type": "array",
        "items": {"type": "string"},
        "description": "List of key points"
      },
      "sentiment": {
        "type": "string",
        "enum": ["positive", "neutral", "negative"]
      },
      "confidence": {
        "type": "number",
        "minimum": 0,
        "maximum": 1
      }
    },
    "required": ["summary", "key_points", "sentiment", "confidence"]
  }';
BEGIN
  l_profile_id := uc_ai_prompt_profiles_api.create_prompt_profile(
    p_code => 'ANALYZE_TEXT',
    p_description => 'Analyzes text with structured output',
    p_system_prompt_template => 'Analyze text and provide structured results.',
    p_user_prompt_template => 'Analyze: {text}',
    p_provider => uc_ai.c_provider_openai,
    p_model => uc_ai_openai.c_model_gpt_5_6_luna,
    p_response_schema => l_schema
  );
END;
/

Run the profile and parse the structured answer:

DECLARE
  l_result json_object_t;
  l_params json_object_t := json_object_t();
  l_output json_object_t;
  l_key_points json_array_t;
BEGIN
  l_params.put('text', 'Your text here...');

  l_result := uc_ai_prompt_profiles_api.execute_profile(
    p_code => 'ANALYZE_TEXT',
    p_parameters => l_params
  );

  -- Parse structured output
  l_output := json_object_t(l_result.get_clob('final_message'));

  DBMS_OUTPUT.PUT_LINE('Summary: ' || l_output.get_string('summary'));
  DBMS_OUTPUT.PUT_LINE('Sentiment: ' || l_output.get_string('sentiment'));
  DBMS_OUTPUT.PUT_LINE('Confidence: ' || l_output.get_number('confidence'));

  -- Access array
  l_key_points := l_output.get_array('key_points');
  FOR i IN 0..l_key_points.get_size - 1 LOOP
    DBMS_OUTPUT.PUT_LINE('Point ' || (i+1) || ': ' || l_key_points.get_string(i));
  END LOOP;
END;
/

Tip

Our JSON schema builder helps you write a schema.

Version management

Creating a new version

Create a new version from an existing version:

DECLARE
  l_new_version_id NUMBER;
BEGIN
  l_new_version_id := uc_ai_prompt_profiles_api.create_new_version(
    p_code => 'SUMMARIZE_TEXT',
    p_source_version => 1,
    p_new_version => 2  -- Optional, defaults to source_version + 1
  );

  -- New version starts in 'draft' status
  -- Modify it before activating
END;
/

The new version is an exact copy of the source version, but its status is ‘draft’.

Changing status

Control which version is available:

BEGIN
  -- Activate version 2
  uc_ai_prompt_profiles_api.change_status(
    p_code => 'SUMMARIZE_TEXT',
    p_version => 2,
    p_status => uc_ai_prompt_profiles_api.c_status_active
  );

  -- Archive old version 1
  uc_ai_prompt_profiles_api.change_status(
    p_code => 'SUMMARIZE_TEXT',
    p_version => 1,
    p_status => uc_ai_prompt_profiles_api.c_status_archived
  );

  COMMIT;
END;
/

Available status values:

• c_status_draft: Under development, not for production
• c_status_active: Ready for production
• c_status_archived: No longer in use

Note

A call by code without a version reads only an ‘active’ profile. UC AI uses the active version with the highest version number.

Updating profiles

Read the row with get_prompt_profile, change the columns that you need, and pass the row to update_prompt_profile. The columns that you do not touch keep their values.

DECLARE
  l_profile uc_ai_prompt_profiles%rowtype;
BEGIN
  l_profile := uc_ai_prompt_profiles_api.get_prompt_profile(
    p_code    => 'SUMMARIZE_TEXT',
    p_version => 2
  );

  l_profile.system_prompt_template := 'Updated system prompt with {param}';
  l_profile.model                  := uc_ai_openai.c_model_gpt_5_6_terra;

  uc_ai_prompt_profiles_api.update_prompt_profile(p_profile => l_profile);

  COMMIT;
END;
/

The call selects the row by its id. It does not change code, version, or status. To change the status, call change_status.

Caution

If a version runs in production, create a new version. Do not update that version.

Runtime overrides

You can overwrite the provider, the model, or the configuration when you run a profile:

DECLARE
  l_result json_object_t;
  l_params json_object_t := json_object_t();
  l_override_config json_object_t := json_object_t();
BEGIN
  l_params.put('text', 'Some text');

  -- Override configuration
  l_override_config.put('g_max_tokens', 2000);

  l_result := uc_ai_prompt_profiles_api.execute_profile(
    p_code => 'SUMMARIZE_TEXT',
    p_parameters => l_params,
    p_provider_override => uc_ai.c_provider_anthropic,
    p_model_override => uc_ai_anthropic.c_model_claude_5_sonnet,
    p_config_override => l_override_config
  );
END;
/

Use an override for:

• An A/B test of two models
• A parameter change for one use case
• A temporary change of the provider

Using with tools

Enable tools/functions in your profile:

DECLARE
  l_profile_id NUMBER;
  l_config CLOB := '{
    "g_enable_tools": true,
    "g_max_tool_calls": 5,
    "g_tool_tags": ["user_lookup"]
  }';
BEGIN
  l_profile_id := uc_ai_prompt_profiles_api.create_prompt_profile(
    p_code => 'USER_LOOKUP',
    p_description => 'Looks up user information',
    p_system_prompt_template => 'You are an assistant with access to user data.',
    p_user_prompt_template => 'What is the email of {user_name}?',
    p_provider => uc_ai.c_provider_openai,
    p_model => uc_ai_openai.c_model_gpt_5_6_luna,
    p_model_config_json => l_config
  );
END;
/

Run the profile:

DECLARE
  l_result json_object_t;
  l_params json_object_t := json_object_t();
BEGIN
  l_params.put('user_name', 'Jim');

  l_result := uc_ai_prompt_profiles_api.execute_profile(
    p_code => 'USER_LOOKUP',
    p_parameters => l_params
  );

  DBMS_OUTPUT.PUT_LINE('Response: ' || l_result.get_clob('final_message'));
  DBMS_OUTPUT.PUT_LINE('Tool calls: ' || l_result.get_number('tool_calls_count'));
END;
/

Retrieving profiles

Get by ID

DECLARE
  l_profile uc_ai_prompt_profiles%ROWTYPE;
BEGIN
  l_profile := uc_ai_prompt_profiles_api.get_prompt_profile(p_id => 42);

  DBMS_OUTPUT.PUT_LINE('Code: ' || l_profile.code);
  DBMS_OUTPUT.PUT_LINE('Version: ' || l_profile.version);
  DBMS_OUTPUT.PUT_LINE('Status: ' || l_profile.status);
END;
/

Get by code

Read the latest active version:

DECLARE
  l_profile uc_ai_prompt_profiles%ROWTYPE;
BEGIN
  l_profile := uc_ai_prompt_profiles_api.get_prompt_profile(
    p_code => 'SUMMARIZE_TEXT'
    -- p_version => NULL returns latest active version
  );
END;
/

Get a specific version:

l_profile := uc_ai_prompt_profiles_api.get_prompt_profile(
  p_code => 'SUMMARIZE_TEXT',
  p_version => 2
);

Deleting profiles

Delete by ID

BEGIN
  uc_ai_prompt_profiles_api.delete_prompt_profile(p_id => 42);
  COMMIT;
END;
/

Delete by code and version

BEGIN
  uc_ai_prompt_profiles_api.delete_prompt_profile(
    p_code => 'SUMMARIZE_TEXT',
    p_version => 1
  );
  COMMIT;
END;
/

Caution

Archive a profile instead of a delete. The history then stays available.

Complete example

This example shows the full workflow:

DECLARE
  l_profile_id NUMBER;
  l_result json_object_t;
  l_params json_object_t := json_object_t();
  l_schema CLOB;
  l_output json_object_t;
BEGIN
  -- Define structured output schema
  l_schema := '{
    "type": "object",
    "properties": {
      "category": {
        "type": "string",
        "enum": ["bug", "feature", "question", "documentation"]
      },
      "priority": {
        "type": "string",
        "enum": ["low", "medium", "high", "urgent"]
      },
      "summary": {
        "type": "string",
        "description": "Brief summary of the issue"
      },
      "estimated_effort": {
        "type": "string",
        "enum": ["small", "medium", "large"]
      }
    },
    "required": ["category", "priority", "summary", "estimated_effort"]
  }';

  -- Create profile
  l_profile_id := uc_ai_prompt_profiles_api.create_prompt_profile(
    p_code => 'CLASSIFY_ISSUE',
    p_description => 'Classifies customer support issues',
    p_system_prompt_template => 'You are a support ticket classifier. Analyze issues and categorize them accurately.',
    p_user_prompt_template => 'Classify this issue: {issue_text}',
    p_provider => uc_ai.c_provider_openai,
    p_model => uc_ai_openai.c_model_gpt_5_6_luna,
    p_response_schema => l_schema,
    p_version => 1,
    p_status => uc_ai_prompt_profiles_api.c_status_draft
  );

  DBMS_OUTPUT.PUT_LINE('Created profile ID: ' || l_profile_id);

  -- Activate it
  uc_ai_prompt_profiles_api.change_status(
    p_id => l_profile_id,
    p_status => uc_ai_prompt_profiles_api.c_status_active
  );

  -- Use the profile
  l_params.put('issue_text', 'The export button is not working when I try to download the report. I get an error message.');

  l_result := uc_ai_prompt_profiles_api.execute_profile(
    p_code => 'CLASSIFY_ISSUE',
    p_parameters => l_params
  );

  -- Parse the structured response
  l_output := json_object_t(l_result.get_clob('final_message'));

  DBMS_OUTPUT.PUT_LINE('Category: ' || l_output.get_string('category'));
  DBMS_OUTPUT.PUT_LINE('Priority: ' || l_output.get_string('priority'));
  DBMS_OUTPUT.PUT_LINE('Summary: ' || l_output.get_string('summary'));
  DBMS_OUTPUT.PUT_LINE('Effort: ' || l_output.get_string('estimated_effort'));

  COMMIT;
END;
/