Create a prompt profile and agent

Set up the demo and call a model

By the end of this lesson, you can create and run the SC_DESK agent.

Lesson 2 of 7~15 minScript: 02_profile_agent.sql

1. Lesson 1: Set up the demo and call a model
2. Lesson 2: Create a prompt profile and agent
3. Lesson 3: Add tools that read contract data
4. Lesson 4: Read execution logs and diagnose errors
5. Lesson 5: Add a tool that creates credit notes
6. Lesson 6: Sessions and memory
7. Lesson 7: Review usage and deploy the agent

Store the prompt in a profile

A prompt profile stores the prompt, model, and configuration in a versioned database row. The application passes input values to an agent that uses this profile. You can update the profile without editing the application code.

Run @02_profile_agent.sql in SQLcl. It creates and activates the profile and agent, then displays their status. The next two sections explain the script. After those sections, run the block under Run the agent.

Create the prompt profile

A prompt profile holds the two templates, the provider, the model, and the configuration. {engineer_name}, {today} and {question} are placeholders. UC AI replaces each one with a value at run time.

declare
  l_profile_id number;
begin
  l_profile_id := uc_ai_prompt_profiles_api.create_prompt_profile(
    p_code                   => 'SC_DESK_PROFILE'
  , p_description            => 'The service-contract desk.'
  , p_system_prompt_template => 'You are the service-contract desk of a field-service company.
You help {engineer_name}, an internal support engineer. Today is {today}.

You work on ONE service contract at a time. You cannot choose the contract: the
application decides which one this conversation is about.

- Never guess a number. Read it with a tool.
- The database decides whether a credit note is allowed. You only ask.
- Keep answers short. The engineer is on the phone with the customer.'
  , p_user_prompt_template   => '{question}'
  , p_provider               => uc_ai.c_provider_openai
  , p_model                  => uc_ai_openai.c_model_gpt_5_6_terra
  , p_parameters_schema      => '{
      "type": "object",
      "properties": {
        "engineer_name": { "type": "string", "description": "Name of the support engineer" },
        "today":         { "type": "string", "description": "Todays date, as YYYY-MM-DD" },
        "question":      { "type": "string", "description": "What the engineer asked" }
      },
      "required": ["engineer_name", "today", "question"]
    }'
  );
end;
/

The system prompt already tells the desk to read numbers with a tool. It has no tools yet. Lesson 3 adds them.

Required placeholder values

If a template holds {today} and a call does not supply today, the run stops with an error. UC AI does not leave a placeholder unfilled. The p_parameters_schema above writes down what the profile expects, so the rule is visible to the next reader.

Create the agent

The application calls an agent, which selects a prompt profile and records the run. The agent provides:

• An agent code. The application calls SC_DESK. You can change its profile version or model through configuration.
• A record of every run. UC AI writes one row for each run and one row for each message. Lesson 4 shows how to read these records.
• Tools and memory. Later lessons configure these on the agent and its profile. The application supplies the contract ID through the run context.

A profile agent runs one prompt profile.

declare
  l_agent_id number;
begin
  l_agent_id := uc_ai_agents_api.create_agent(
    p_code                => 'SC_DESK'
  , p_description         => 'Service-contract desk agent'
  , p_agent_type          => uc_ai_agents_api.c_type_profile
  , p_prompt_profile_code => 'SC_DESK_PROFILE'
  );
end;
/

Both creation calls produce drafts. The script activates them with these calls:

begin
  uc_ai_prompt_profiles_api.change_status(
    p_code => 'SC_DESK_PROFILE', p_version => 1
  , p_status => uc_ai_prompt_profiles_api.c_status_active);

  uc_ai_agents_api.change_status(
    p_code => 'SC_DESK', p_version => 1
  , p_status => uc_ai_agents_api.c_status_active);

  commit;
end;
/

Activate both objects

create_prompt_profile and create_agent both make a draft. A call that does not name a version resolves the latest active version. So a draft is unavailable to that call. If either object remains a draft, the agent call can return NO_DATA_FOUND.

Run the agent

After the setup script completes, run this block:

declare
  l_result json_object_t;
begin
  l_result := uc_ai_agents_api.execute_agent(
    p_agent_code       => 'SC_DESK'
  , p_input_parameters => json_object_t('{
        "engineer_name": "Petra",
        "today":         "2026-08-24",
        "question":      "How much can I still credit on INV-1003?"
      }')
  );

  sys.dbms_output.put_line(l_result.get_clob('final_message'));
end;
/

Recorded answergpt-5.6-terra2.5s2026-08-24

I can’t access the contract records right now, so I can’t verify the remaining credit amount for INV-1003.

Your wording will differ. What must match is the data, and the checks below.

This call omits the session ID, so UC AI generates one. Lesson 6 reuses a session ID to keep two turns in the same conversation.

The agent cannot read invoice data yet. Lesson 3 adds the tools that retrieve it.

Verification

Both rows must say active. If either says draft, run the change_status block again:

select 'prompt profile' as object, code, version, status
  from uc_ai_prompt_profiles
 where code = 'SC_DESK_PROFILE'
union all
select 'agent', code, version, status
  from uc_ai_agents
 where code = 'SC_DESK';

OBJECT            CODE                 VERSION   STATUS
prompt profile    SC_DESK_PROFILE            1   active
agent             SC_DESK                    1   active

Run this query to read the latest execution record. In a shared schema, the latest record can belong to another agent:

select status, total_input_tokens, total_output_tokens, tool_calls_count
  from uc_ai_agent_executions
 order by started_at desc
 fetch first 1 row only;

STATUS       TOTAL_INPUT_TOKENS   TOTAL_OUTPUT_TOKENS   TOOL_CALLS_COUNT
completed                   283                    84                  0

tool_calls_count is 0, and that is correct: this agent has no tools yet.

uc_ai_agent_messages holds the conversation behind that row, one message at a time. Lesson 4 reads both tables and shows what the desk did on each run.

Key takeaways

• A prompt profile stores reusable prompt templates and model configuration.
• create_prompt_profile and create_agent both make a draft. Activate both, or the application cannot run the agent by name.
• uc_ai_agents_api.execute_agent(p_agent_code => 'SC_DESK', ...) runs the agent from your application.

Full reference: Prompt profiles covers versions, model configuration and runtime overrides. Profile agents covers the agent side.

Next lesson: Add tools that read contract dataRegister read tools and filter their queries with the contract ID from the run context.