Set up the demo and call a model

All tutorials

By the end of this lesson, you can call a model from PL/SQL with invoice data in the prompt.

Lesson 1 of 7~10 minScript: 00_setup.sql, sc_desk_pkg.pks, sc_desk_pkg.pkb, 00_precheck.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

What you build in this course

You build the service-contract desk: an agent that answers questions about one service contract and can raise a credit note against an invoice on it.

A support engineer opens a contract and asks the agent about it. The agent reads the contract, its service calls, and its invoices. When the engineer asks for a credit note, the database decides whether the contract allows it.

The examples use these terms:

• A service call is one visit of a technician to a machine under contract. Each call produces one invoice.
• A credit note cancels part of an invoice. It is money the customer does not pay.
• A contract has a coverage level. GOLD covers parts and labor. SILVER covers labor only.
• A contract has a coverage window: the dates between which it covers service calls. A call outside the window is not covered.

Before you start

You need a UC AI installation that can reach OpenAI. The installation guide covers the API key, and the network setup guide covers the network grants a DBA must give. The precheck below tests the connection and API key.

Download the files from examples/build-an-agent into one local directory. Open SQLcl in that directory and connect to your demo schema. Use the same schema for lessons 1–6.

Each lesson states which scripts to run. Code labeled as an excerpt explains part of a script. Run additional blocks only where the page asks you to. Recorded model answers can differ in wording, tool calls, and token counts.

1. 00_setup.sql deletes any existing demo tables and their data. Run @00_setup.sql in SQLcl to create and populate the six demo tables.

2. Run @sc_desk_pkg.pks. Then run @sc_desk_pkg.pkb. These files compile the tool handlers used in later lessons. Use show errors after each file to read any compilation errors.

3. Run @00_precheck.sql. It tests the UC AI installation, provider connection, and demo tables. It prints a suggested fix for each failure.

The precheck prints this when your database is ready:

1. UC AI is installed. Version 26.3.
2. The provider answered: READY
   Tokens used: 16.
3. The demo schema is in place (6 tables).

If compilation or the precheck fails, correct the reported error before you continue.

Provider configuration:

• Contract data leaves your network. The example provider calls send data to OpenAI over HTTPS. If your data cannot leave your network, run Ollama on a host you own and change the provider and model values in the examples and scripts. Select a model that supports tool calls for lessons 3–7.
• Provider calls incur charges. Usage depends on the model, the number of tool calls, and how often you repeat the examples.
• The recorded output comes from gpt-5.6-terra. Other models can produce different results. Use the verification sections to assess their answers.

Remove the demo schema

00_teardown.sql deletes the six tables and every row this course registers in UC AI. Run it when you are finished.

Call the model with invoice data

uc_ai.generate_text sends a prompt to a provider and returns the model response.

The model has no database connection. Run this block to include invoice numbers and totals in the prompt:

declare
  l_rows   clob;
  l_result json_object_t;
begin
  select json_arrayagg(
           json_object('invoice_no' value i.invoice_no
                     , 'total'      value i.parts_amount + i.labour_amount)
           returning clob)
    into l_rows
    from sc_invoices i
   where i.contract_id = 88;

  l_result := uc_ai.generate_text(
    p_user_prompt   => 'How many invoices are on this contract?' || chr(10)
                       || 'Contract data:' || chr(10) || l_rows
  , p_system_prompt => 'You are the service-contract desk. Answer from the contract data only.'
  , p_provider      => uc_ai.c_provider_openai
  , p_model         => uc_ai_openai.c_model_gpt_5_6_terra
  );

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

Recorded response:

Recorded answergpt-5.6-terra2026-08-24

There are 3 invoices on this contract.

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

Ask about remaining credit

Replace the question in p_user_prompt with the following text, then run the block again. Keep the query unchanged:

Q: How much can I still credit on INV-1003?

Recorded answer, and it is wronggpt-5.6-terra2026-08-24

You can still credit up to 200 on INV-1003.

Your wording will differ, and the model can also refuse instead. The lesson explains why this answer cannot be trusted.

The correct answer is 0. The prompt sent a total of 200 and no credit data, so the model answered 200. The query under Verification shows that INV-1003 is credited in full.

This approach has three limits:

• You had to choose which rows to paste before the engineer asked.
• The pasted rows have invoice numbers and totals only. Credits, coverage windows, service calls, and assets are missing.
• Acting on the incorrect amount can produce a duplicate credit unless the application checks the remaining amount.

Credit data supplies the missing information for this question. Other questions can need different data from the six demo tables. The next lessons add tools so the model can request data during a run.

Responses can vary

Sometimes the same question gets “the remaining creditable amount cannot be determined” instead. The supplied rows do not contain the remaining credit amount.

Verification

Run this query to compare each invoice total with its existing credits:

select i.invoice_no
     , c.coverage_level
     , i.parts_amount + i.labour_amount as invoice_total
     , nvl(( select sum(cn.amount)
               from sc_credit_notes cn
              where cn.invoice_id = i.id ), 0) as credited
  from sc_invoices i
  join sc_contracts c on c.id = i.contract_id
 order by i.invoice_no;

Your output must match this, because the setup script fixes every value:

INVOICE_NO   COVERAGE_LEVEL   INVOICE_TOTAL   CREDITED
INV-1001     GOLD                       780          0
INV-1002     GOLD                       350          0
INV-1003     GOLD                       200        200
INV-2001     SILVER                     850          0

INV-1003 has 200 of 200 credited. Its remaining credit amount is 0.

Demo data and external content

This tutorial uses fixed demo data. External content, such as supplier emails or uploaded files, can contain instructions that redirect the model. Secure an Agent explains how to handle that content.

Key takeaways

• The prompt must include the data needed to answer the question.
• Incomplete rows can return a plausible wrong number. The remaining credit on INV-1003 is an example.
• This example passes four parameters to uc_ai.generate_text: p_user_prompt, p_system_prompt, p_provider, p_model.

Full reference: generate_text has the parameters and result structure.

Next lesson: Create a prompt profile and agentStore the prompt in a profile and create an agent that uses it.