A schema the model must fill

By the end of this lesson, the same PDF comes back as named fields, and a German invoice fills the same schema with no change to it.

Lesson 2 of 5~15 minScript: 02_extract_schema.sql

Define fields for the extracted data

Lesson 1 ended with two correct paragraphs that no two lines of PL/SQL can read.

The fix is to describe the answer you want, up front, and let the provider hold the model to it.

What a JSON schema is

A JSON schema is a JSON document that describes the shape of another JSON document. It says which fields exist, what type each one is, which ones must be there, and which values are allowed. It is a form, and the model has to fill it in.

The smallest one looks like this:

{
  "type": "object",
  "properties": {
    "invoice_number": { "type": "string" },
    "total_amount":   { "type": "number" }
  },
  "required": ["invoice_number", "total_amount"],
  "additionalProperties": false
}

Four keywords do the work there:

"type": "object" — the answer is a JSON object, not a list and not a string.
"properties" — the fields, each with its own type.
"required" — the fields the answer must contain.
"additionalProperties": false — the model cannot add a field you did not ask for.

You pass that to generate_text as p_response_json_schema, and the answer comes back as JSON that matches it. This is called structured output. UC AI asks the provider for it in strict mode, which means the provider refuses to return an answer that does not match the schema.

The schema for an invoice

The real one has three parts.

The header fields name what an invoice is:

{
  "document_type":    { "type": "string",
                        "enum": ["INVOICE", "CREDIT_NOTE", "DELIVERY_NOTE", "OTHER"] },
  "supplier_name":    { "type": "string" },
  "invoice_number":   { "type": "string" },
  "invoice_date_raw": { "type": "string" },
  "invoice_date_iso": { "type": "string" },
  "po_number":        { "type": "string" },
  "currency":         { "type": "string",
                        "enum": ["EUR", "GBP", "USD", "CHF", "OTHER"] },
  "net_amount":       { "type": "number" },
  "tax_amount":       { "type": "number" },
  "total_amount":     { "type": "number" }
}

The line items are an array of objects:

"line_items": {
  "type": "array",
  "items": {
    "type": "object",
    "properties": {
      "position":    { "type": "integer" },
      "description": { "type": "string" },
      "kind":        { "type": "string",
                       "enum": ["GOODS", "SERVICE", "FREIGHT", "DISCOUNT", "OTHER"] },
      "quantity":    { "type": "number" },
      "unit_price":  { "type": "number" },
      "line_total":  { "type": "number" }
    },
    "required": ["position", "description", "kind", "quantity", "unit_price",
                 "line_total"],
    "additionalProperties": false
  }
}

Two of these fields exist only for lesson 3:

document_type uses enum, which is a closed list of allowed values. It is the one field your PL/SQL can test when the document turns out not to be an invoice. Google removes enum in the conversion below, so treat the field as plain text there and keep the check constraint on your table.
invoice_date_raw keeps the date string exactly as it is printed, next to the date the model parsed. Lesson 3 needs both.

The kind field on a line is the third. It separates freight, a discount and an early-payment discount in SQL, and the arithmetic of lesson 3 needs it.

One more field is not in either block above. extraction_note is a single string for anything the model could not read with confidence. It gives a hedge one bounded place, so that it does not leak into the fields your code reads.

Read final_message as text, then parse it yourself

The answer arrives in final_message, the same place as the paragraph in lesson 1. It arrives as the same type as in lesson 1: a CLOB that now holds JSON. generate_text does not turn it into a JSON object for you. You do that yourself, in one line:

-- wrong: gives you null, and raises nothing at all
l_invoice := l_result.get_object('final_message');

-- right
l_invoice := json_object_t(l_result.get_clob('final_message'));

Get that wrong and there is no error to follow. get_object returns null:

get_object  -> (null)

Read final_message with get_clob, then wrap it in json_object_t. After that, get_string and get_number give you the fields.

Your schema is not what the provider gets

Every provider wants a schema in its own dialect, so UC AI rewrites yours before it sends it. Two of those rewrites change what your schema means, and you cannot see either one from your own code.

You can see them from uc_ai_structured_output, which is the package that does the converting. A direct call costs nothing and needs no model:

declare
  c_small constant varchar2(2000 char) := '{
  "title": "Invoice Header",
  "type": "object",
  "properties": {
    "invoice_number": { "type": "string",
                        "description": "The number the supplier gave this invoice" },
    "po_number":      { "type": "string",
                        "description": "Purchase order number, empty when there is none" },
    "currency":       { "type": "string", "enum": ["EUR", "GBP", "USD"],
                        "description": "Currency of the amounts" }
  },
  "required": ["invoice_number"],
  "additionalProperties": false
}';
  l_converted json_object_t;
  l_schema    json_object_t;
begin
  l_converted := uc_ai_structured_output.to_responses_api_format(json_object_t(c_small));
  l_schema    := l_converted.get_object('schema');

  sys.dbms_output.put_line('Schema name:  ' || l_converted.get_string('name'));
  sys.dbms_output.put_line('Required:     ' || l_schema.get_array('required').to_string);
  sys.dbms_output.put_line('po_number:    '
    || l_schema.get_object('properties').get_object('po_number').to_string);
  sys.dbms_output.put_line('currency:     '
    || l_schema.get_object('properties').get_object('currency').to_string);
end;
/

The input had one required field and a description on all three properties. The output:

Schema name:  Invoice_Header
Required:     ["invoice_number","po_number","currency"]
po_number:    {"type":"string"}
currency:     {"type":"string","enum":["EUR","GBP","USD"]}

Three things changed:

What you wrote	What the provider receives	What it means for you
`"required": ["invoice_number"]`	all three names	A field cannot be optional. Every field comes back with a value.
a `description` on each property	nothing	Write your field rules in the system prompt instead.
`"title": "Invoice Header"`	the name `Invoice_Header`	The title is not thrown away. It becomes the schema’s name.

Anthropic forces the same required list and deletes the same descriptions, and it does not use the title. Google keeps the descriptions and drops every enum instead.

Your schema has no way to say “this document does not have that field”. The model has to put something in every field, so you decide what “nothing” looks like. This course uses an empty string for text and 0 for an amount, and the system prompt says so in one line.

The rules move to the system prompt

Because UC AI deletes the descriptions, every rule lives in the system prompt instead:

- Read every page. A total on page 1 is not the total when a later page adds charges.
- Tax is NOT a line item. Put the total tax in tax_amount and nowhere else.
- net_amount is everything except tax. It is total_amount minus tax_amount, and it
  is the sum of the line items. When the document prints a subtotal that leaves out
  delivery or a discount, that subtotal is NOT net_amount.

ferrotek.pdf prints a subtotal of 2,091.50 that leaves out the 45.00 delivery charge. “The net amount as printed” is not a definition, because the page prints two numbers that both look like one.

The recorded answer for ferrotek.pdf

Recorded answer, as fieldsgpt-5.6-terra2026-08-25

document_type: INVOICE
supplier_name: FerroTek Components Ltd
invoice_number: FT-2026-04417
invoice_date_raw: 14 July 2026
invoice_date_iso: 2026-07-14
po_number: PO-4500198231
currency: GBP
net / tax / total: 2136.5 / 427.3 / 2563.8

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

The script prints the six lines below it, with their sum:

  GOODS           824  Servo gearbox SG-40, ratio 1:25
  GOODS           342  Sealed bearing unit, 40 mm bore
  GOODS         388.5  Hydraulic hose assembly, 1.8 m
  GOODS           193  Drive belt, toothed, 1250 mm
  SERVICE         344  On-site support, senior engineer
  FREIGHT          45  Delivery
  sum of lines: 2136.5

The delivery charge is not a printed line on that invoice. It sits under the table, next to the subtotal. The prompt asked for every charge as a line item. So it became line 6, and the six lines add up to the net amount exactly.

The net amount is 2136.5, and not the 2091.50 printed as the subtotal.

The German invoice fills the same schema

Now run the same schema and the same prompt over nordwind.pdf. The layout is different, the labels are German, the decimals use a comma, and the date is DD.MM.YYYY. Nothing changes in your code, and the script prints this:

supplier:      NORDWIND Industrietechnik GmbH
invoice_no:    NW-2026-1188
date printed:  03.08.2026
date parsed:   2026-08-03
currency:      EUR
net / tax / total: 6450.85 / 1225.66 / 7676.51
  GOODS          3540  Planetengetriebe PG-63, Übersetzung 1:40
  GOODS           258  Wellendichtring 55x72x8, FKM
  GOODS        2310.5  Hydraulikpumpe HP-22, 22 l/min
  GOODS           378  Kupplungsnabe, Bohrung 38 mm
  FREIGHT          96  Versand und Verpackung
  DISCOUNT    -131.65  Skonto 2% bei Zahlung bis 13.08.2026
  sum of lines: 6450.85

The unit price printed as 1.180,00 came back as the JSON number 1180, and the line total printed as 3.540,00 came back as 3540, which is the first line above. The early-payment discount came back as a negative line, because the prompt said a discount is negative. The six lines add up to 6,450.85, which is the net amount again.

Verification

Two checks:

The token cost of the two documents. Both blocks print a Tokens: line, and the two counts are not the same even though the files are within 100 bytes of each other. A PDF does not cost tokens by its size on disk. What a provider counts for a document is not published, so store the number for every run. That is why inv_extractions has a column for it, and lesson 5 builds a report from it.
The sum of the lines equals the net amount, on both documents. Prove it in SQL after lesson 4 posts the rows, or read it off the two blocks above.

Key takeaways

A response schema does not change the type of final_message. It is still a CLOB, and it now holds JSON. get_object on it gives you null and raises nothing, so read it with get_clob and wrap it in json_object_t yourself.
On OpenAI and Anthropic every field is required, whatever your required array says, and UC AI deletes every description. Put the field rules in the system prompt, and decide what an absent value looks like.
l_invoice := json_object_t(l_result.get_clob('final_message'));

Full reference: Structured output covers the schema rules and the differences between providers.

Next lesson: Do not trust what you extractedThe model cannot say a field is missing. Five checks in PL/SQL decide which documents a person must look at.