Free text hides details that belong in columns or in the hands of the next person: a product version, what was already done on a ticket, a message in another language. A language model can pull them out, but only if the prompt asks for the right form, gives the model every fact it needs, and does not presume an answer.
This guide shows four extraction tasks with Gemini from Oracle AI Database 26ai: asking for the form of the answer, pulling a handover summary out of a ticket's conversation, extracting a field from 30 tickets in one call and checking it against a regular expression, and translating while keeping values intact.
Code for This Guide
The examples are files 01, 02, 09, and 10 in the examples/ch11 folder of the Oracle AI code repository on GitHub, each with its output.
These examples come from AI Applications with Oracle Database 26ai and APEX 26.1, a book of 237 tested examples of AI in Oracle Database and APEX.
They use the help desk tickets and comments of the sample schema from setup/atlas, and call Gemini through the GENERATE function from how to make LLM calls reliable in PL/SQL. Most calls turn thinking off with thinkingBudget 0, since these tasks need no reasoning.
Ask for the Form of the Answer
Models format answers in Markdown for chat windows. An application that shows plain text, in a report column, an email, or an APEX text item, must ask for plain text.
Example:
-- the same request, as is and with the form of the answer specified (first 500 characters)
select substr(generate(
'How does a customer reset a forgotten password in a web application?',
p_options => json('{"generationConfig":
{"thinkingConfig": {"thinkingBudget": 0}}}')), 1, 500)
as default_answer
from dual;
select substr(generate(
'How does a customer reset a forgotten password in a web application? '
|| 'Answer in plain text, without Markdown, in at most three sentences.',
p_options => json('{"generationConfig":
{"thinkingConfig": {"thinkingBudget": 0}}}')), 1, 500)
as plain_answer
from dual;Output:
DEFAULT_ANSWER __________________________________________________________________________________________________ From a customer's perspective, resetting a forgotten password in a modern web application is designed to be a simple, secure, self-service process. Here is the step-by-step breakdown of how it works: --- ### Step-by-Step User Flow #### 1. Click "Forgot Password" * On the login page, the customer clicks the **"Forgot Password?"** or **"Trouble signing in?"** link typically located near the password field. #### 2. Enter Identifying Information * The customer is taken to a recovery page where PLAIN_ANSWER __________________________________________________________________________________________________ A customer begins by clicking the "Forgot Password" link on the login page and entering their registered email address or username. The web application then sends a secure, time-sensitive reset link or verification code to that email address. Finally, the customer clicks the link or enters the code to access a secure page where they can enter and save their new password.
The first answer is an article with headings, steps, bold text, and rules, about 70 lines in full. The second is three sentences of plain text. Every prompt in an application should state the form: plain text or Markdown, the length, the language, and, when code reads the answer, a JSON schema.
Pull a Summary out of a Conversation
A ticket is more than its description: its comments say what was done. This example builds a prompt from a ticket and its comments in order, and asks for a summary for the agent who takes it over.
Example:
-- ticket 15 and its conversation, summarized for the agent who takes it over
select generate(
'Summarize this support ticket for the agent who takes it over: the problem, what '
|| 'was done, and what is still open. Plain text, at most three sentences.'
|| chr(10) || 'Customer: ' || t.description || chr(10)
|| (select listagg(c.author_type || ': ' || c.body, chr(10))
within group (order by c.created_at)
from ticket_comments c
where c.ticket_id = t.ticket_id),
p_options => json('{"generationConfig":
{"thinkingConfig": {"thinkingBudget": 0}}}')) as handover
from tickets t
where t.ticket_id = 15;Output:
HANDOVER __________________________________________________________________________________________________ The customer was accidentally billed twice for the same invoice due to a payment gateway timeout retry. The agent refunded the duplicate charge and provided knowledge base article KB-201, noting that the refund should appear in 5 to 10 business days. The ticket remains open to confirm that the customer successfully receives the refund within the expected timeframe.
The first two sentences are right: the duplicate charge, its cause, the refund, the article. The third is invented. The ticket is resolved and nothing is waiting, but the prompt did not include the status, and it asked "what is still open", so the model found something.
Two fixes: give the model every fact it needs, here the ticket's status, and give it permission to answer "nothing", for example "or say that nothing is open". A prompt that presumes an answer gets one.
Extract a Field from Many Rows
This example asks the model, in one call, for the product version each of 30 tickets mentions, with a JSON schema that allows null for none. It then compares the answers with a regular expression that finds numbers such as 8.4 or 8.4.1.
Example:
-- the product version each customer mentions: by the model, and by a regular expression
with batch as (
select ticket_id, dbms_lob.substr(description, 4000) as description
from tickets
where ticket_id between 1 and 30),
extracted as (
select j.ticket_id, j.version
from json_table(
generate('For each ticket, extract the product version the customer says '
|| 'they use, such as 5.2 or 8.4.1, or null if the ticket mentions '
|| 'none. '
|| 'Tickets: '
|| (select json_arrayagg(json_object('ticket_id' value ticket_id,
'text' value description
returning clob) returning clob)
from batch),
'GEMINI',
json('{"generationConfig": {"temperature": 0,
"thinkingConfig": {"thinkingBudget": 0},
"responseMimeType": "application/json",
"responseSchema": {"type": "ARRAY", "items": {"type": "OBJECT",
"properties": {"ticket_id": {"type": "INTEGER"},
"version": {"type": "STRING", "nullable": true}},
"required": ["ticket_id", "version"]}}}}')),
'$[*]' columns (ticket_id number path '$.ticket_id',
version varchar2(20) path '$.version')) j)
select count(*) as tickets,
count(regexp_substr(b.description, '\d+\.\d+(\.\d+)?')) as with_version,
count(case when e.version = regexp_substr(b.description, '\d+\.\d+(\.\d+)?')
then 1 end) as model_agrees,
count(case when e.version is null
and regexp_substr(b.description, '\d+\.\d+(\.\d+)?') is null
then 1 end) as both_none
from batch b left join extracted e on e.ticket_id = b.ticket_id;Output:
TICKETS WITH_VERSION MODEL_AGREES BOTH_NONE
__________ _______________ _______________ ____________
30 12 12 18Twelve tickets mention a version, and the model agreed with the regular expression on all 30. Both work, and here the regular expression is the better choice: instant, free, and never varying.
| Use | When the detail is |
|---|---|
| A regular expression | A regular pattern: version numbers, invoice numbers, error codes, emails |
| A language model | Written irregularly or needing understanding: "the version before the latest", "since the September update", who is affected and since when |
The JSON schema technique itself, responseMimeType and responseSchema, is covered in how to call an LLM from PL/SQL with UTL_TO_GENERATE_TEXT.
Translate While Keeping Values
A help desk with customers in many countries needs translation both ways. This example translates a Spanish message for the agent and the agent's reply back, with the cheaper Lite model.
Example:
-- a customer writes in Spanish: translate for the agent, and translate the reply back
select generate('Translate this customer message to English. Answer with the translation '
|| 'only: Hola, desde ayer la aplicación del móvil se cierra al abrirla. '
|| 'Usamos la versión 3.9.0. ¿Hay alguna solución?', 'GEMINI_LITE')
as for_the_agent
from dual;
select generate('Translate this support reply to Spanish, keeping the version numbers. '
|| 'Answer with the translation only: Version 3.9.1 fixes the crash. Until '
|| 'it reaches your app store, sign out and sign in again.', 'GEMINI_LITE')
as for_the_customer
from dual;Output:
FOR_THE_AGENT __________________________________________________________________________________________________ Hello, since yesterday the mobile app crashes when opening it. We are using version 3.9.0. Is there a solution? FOR_THE_CUSTOMER __________________________________________________________________________________________________ La versión 3.9.1 soluciona el bloqueo. Hasta que llegue a tu tienda de aplicaciones, cierra sesión y vuelve a iniciar sesión.
"Answer with the translation only" matters: without it, models add explanations, alternatives, and notes on tone. "Keeping the version numbers" protects what must not change; product names, codes, and amounts deserve the same instruction.
Conclusion
Extracting data from text with an LLM works when the prompt states the form of the answer, includes every fact the model needs, and allows "none" as an answer. Ask for a JSON schema with nullable fields when code reads the result, send many rows in one call, and check the model against a regular expression where the pattern is regular; prefer the expression when it agrees. When translating, ask for the translation only and name the values that must not change.
