Oracle AI Database 26ai can send a prompt to a large language model and get the answer back as a CLOB, from plain SQL or PL/SQL. From there the answer is data: you can store it, join it, and show it in APEX like any other column.
This guide covers DBMS_VECTOR_CHAIN.UTL_TO_GENERATE_TEXT with Google Gemini: a first call, a model table and a GENERATE function, system instructions, temperature, output limits and thinking, JSON output that follows a schema, and summaries with UTL_TO_SUMMARY.
Code for This Guide
The examples are files 01 to 08 in the examples/ch10 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 call Gemini through a database credential named GEMINI_CRED, set up in how to call Gemini from Oracle Database with a stored credential. A language model writes a new answer on each call, so yours will say the same things in other words.
A First Call
UTL_TO_GENERATE_TEXT sends a prompt and returns the answer. The parameters name the provider, the credential, the API address, and the model; for Gemini the model name ends with the method :generateContent. Any other keys in the parameters are passed to the provider with the prompt, which is how you use the provider's own settings.
Syntax:
dbms_vector_chain.utl_to_generate_text(prompt clob, params json) return clob
Example:
set timing on
select dbms_vector_chain.utl_to_generate_text(
'In one sentence: why do customers contact a help desk?',
json('{"provider": "googleai",
"credential_name": "GEMINI_CRED",
"url": "https://generativelanguage.googleapis.com/v1beta/models/",
"model": "gemini-flash-latest:generateContent"}')) as answer
from dual;
set timing offOutput:
ANSWER __________________________________________________________________________________________________ Customers contact a help desk to resolve problems, troubleshoot technical issues, and receive guidance or information about a product or service. Elapsed: 00:00:03.563
About three seconds for one sentence. A model writes its answer token by token, and newer models also think before they write, so a call takes from one to many seconds. Design every feature around that.
Keep Models in a Table
Keep each model's parameters in a table and call models by name. The THINKING column records whether a model accepts thinking settings: the Lite model does not, and rejects a request that contains them.
Example:
create table llm_models (
name varchar2(30) constraint llm_models_pk primary key,
params json not null,
thinking varchar2(3) default 'Yes' not null -- accepts thinking settings
);
insert into llm_models (name, params, thinking) values
('GEMINI',
json('{"provider": "googleai",
"credential_name": "GEMINI_CRED",
"url": "https://generativelanguage.googleapis.com/v1beta/models/",
"model": "gemini-flash-latest:generateContent"}'), 'Yes'),
('GEMINI_LITE',
json('{"provider": "googleai",
"credential_name": "GEMINI_CRED",
"url": "https://generativelanguage.googleapis.com/v1beta/models/",
"model": "gemini-flash-lite-latest:generateContent"}'), 'No');
commit;
select name, json_value(params, '$.model') as model, thinking from llm_models;Output:
Table LLM_MODELS created. 2 rows inserted. Commit complete. NAME MODEL THINKING ______________ ___________________________________________ ___________ GEMINI gemini-flash-latest:generateContent Yes GEMINI_LITE gemini-flash-lite-latest:generateContent No
One GENERATE Function for Every Call
GENERATE reads a model's parameters, merges any options into them with JSON_MERGEPATCH, and calls UTL_TO_GENERATE_TEXT.
Example:
create or replace function generate (
p_prompt in clob,
p_model in varchar2 default 'GEMINI',
p_options in json default null -- provider options, merged into the parameters
) return clob
is
l_params json;
begin
select case when p_options is null then params
else json_mergepatch(params, p_options returning json) end
into l_params
from llm_models
where name = p_model;
return dbms_vector_chain.utl_to_generate_text(p_prompt, l_params);
end;
/
set timing on
select generate('Name the largest planet of the solar system. Answer with one word.')
as gemini
from dual;
select generate('Name the largest planet of the solar system. Answer with one word.',
'GEMINI_LITE') as gemini_lite
from dual;
set timing offOutput:
Function GENERATE compiled GEMINI __________ Jupiter Elapsed: 00:00:02.142 GEMINI_LITE ______________ Jupiter Elapsed: 00:00:01.477
The same answer, and the Lite model took about two thirds of the time. For simple tasks, a word, a category, a rewritten line, the smaller model is often enough at a fraction of the price.
Every setting below is a value of P_OPTIONS. Section names such as systemInstruction and generationConfig are Gemini's; other providers name the same ideas differently.
Set the Role with a System Instruction
A system instruction applies to the whole conversation: who the model is, what it may do, and how it must answer. Keeping it apart from the user's question keeps the question clean, and models weigh it more than an instruction mixed into the prompt.
Example:
select generate(
'Can I get my money back for a duplicate charge?',
p_options => json('{"systemInstruction": {"parts": [{"text": "'
|| 'You are the support assistant of Atlas Software. '
|| 'Answer in at most two sentences, in plain text, without Markdown. '
|| 'If you do not know, say so."}]}}')) as answer
from dual;Output:
ANSWER __________________________________________________________________________________________________ Yes, you can receive a full refund for any duplicate charge. Please contact our billing support team with your transaction details so we can process the refund for you.
The form is right: two sentences, no Markdown. The content is not. The help desk's own policy is to refund duplicate charges as soon as they are reported, without asking for receipts, but the model has never seen that policy and wrote what a help desk plausibly says. "If you do not know, say so" did not help, because the model did not know that it did not know. Only facts in the prompt fix this, which is what retrieval-augmented generation does.
Control Randomness with Temperature
Temperature controls how much randomness the model uses when choosing each word: 0 makes it choose the most likely word every time, and higher values allow less likely ones. Gemini's maximum is 2.
Example:
-- the same prompt, twice with temperature 0 and twice with temperature 2
with runs (temperature) as (values (0), (0), (2), (2))
select temperature,
generate('Suggest a subject line for a ticket about a duplicate credit card charge. '
|| 'Answer with the subject line only.',
p_options => json_object('generationConfig' value
json_object('temperature' value temperature) returning json))
as subject_line
from runs;Output:
TEMPERATURE SUBJECT_LINE
______________ ________________________________________________
0 Billing Issue: Duplicate Credit Card Charge
0 Billing Issue: Duplicate Credit Card Charge
2 Duplicate Credit Card Charge - Refund Request
2 Billing Issue: Duplicate Credit Card ChargeAt 0 the two answers are identical; at 2 they differ. Use a low temperature for tasks with one right answer, such as classifying, extracting, or answering from facts, and the default or higher for drafting. Temperature 0 makes answers repeatable in practice but not guaranteed, since the provider can change the model behind the name.
Limit Output and Control Thinking
maxOutputTokens limits the length, and so the cost, of an answer. This example limits a one-word answer to 20 tokens, then does the same with thinking turned off.
Example:
select generate('Name one color. Answer with one word.',
p_options => json('{"generationConfig": {"maxOutputTokens": 20}}'))
as answer
from dual;
select generate('Name one color. Answer with one word.',
p_options => json('{"generationConfig": {"maxOutputTokens": 20,
"thinkingConfig": {"thinkingBudget": 0}}}')) as answer
from dual;Output:
Error starting at line : 1
In command -
select generate('Name one color. Answer with one word.',
p_options => json('{"generationConfig": {"maxOutputTokens": 20}}'))
as answer
from dual
Error at Command Line : 1 Column : 8
Error report -
SQL Error: ORA-20000: Oracle Text error:
DRG-50857: oracle error in dbms_vector_chain.utl_to_generate_text
ORA-20002: The provider returned an error: {
"candidates": [
{
"content": {},
"finishReason": "MAX_TOKENS",
"index": 0
}
],
"usageMetadata": {
"promptTokenCount": 9,
"totalTokenCount": 25,
"promptTokensDetails": [
{
"modality": "TEXT",
"tokenCount": 9
}
],
"thoughtsTokenCount": 16,
"serviceTier": "standard"
},
"modelVersion": "gemini-3.8-flash",
"responseId": "z2y_avbgKPWHjuMP6Ymf4Qg"
}
ORA-06512: at "CTXSYS.DRUE", line 192
ORA-06512: at "CTXSYS.DBMS_VECTOR_CHAIN", line 1012
ORA-06512: at "ATLAS.GENERATE", line 14
ORA-06512: at line 1
ANSWER
_________
BlueThe first call failed with finishReason MAX_TOKENS and no text. Gemini Flash is a thinking model: before answering, it reasons in tokens you do not see, here 16 of them (thoughtsTokenCount), and they count against the limit. With thinkingBudget 0, the model answers at once.
Thinking makes answers to hard questions better and slower. Turn it off or keep it low for simple tasks, and leave it on for questions that need reasoning, such as generating SQL. The settings differ between models: this one rejects a thinkingLevel of minimal, for instance, while thinkingBudget 0 works. Test them with the model you use.
Get JSON That Follows a Schema
An answer is text that code must parse. With responseMimeType set to application/json and a responseSchema, Gemini answers with JSON that has exactly the fields you list, with the types you give and values from the lists you allow.
Example:
-- the model must answer with JSON that follows a schema
select generate(
'Classify this support ticket: "Since the update, nobody on our team can sign in. '
|| 'We get invalid credentials even after a password reset."',
p_options => json('{"generationConfig": {
"responseMimeType": "application/json",
"responseSchema": {
"type": "OBJECT",
"properties": {
"category": {"type": "STRING",
"enum": ["Account", "Billing", "Bug", "Question",
"Feature Request"]},
"priority": {"type": "STRING", "enum": ["Low", "Normal", "High", "Urgent"]},
"summary": {"type": "STRING"}},
"required": ["category", "priority", "summary"]}}}')) as answer
from dual;Output:
ANSWER
__________________________________________________________________________________________________
{"category":"Bug","priority":"Urgent","summary":"Entire team unable to log in due to invalid
credentials error after recent update"}The answer is valid JSON, and category and priority come from their lists: the model cannot answer "Login problem" or "Very high". SQL reads it with JSON_VALUE and stores it in ordinary columns. Whether "Bug" is the right category is a separate question, answered only by measuring the model against people.
Summarize with UTL_TO_SUMMARY
UTL_TO_SUMMARY summarizes a text with a language model. It takes the same parameters as UTL_TO_GENERATE_TEXT and writes the prompt for you.
Syntax:
dbms_vector_chain.utl_to_summary(data clob, params json) return clob
This example summarizes a PDF guide stored in the database, with a system instruction added to the model's parameters.
Example:
select dbms_vector_chain.utl_to_summary(
(select dbms_vector_chain.utl_to_text(content)
from atlas_documents
where file_name = 'atlas-mobile-guide.pdf'),
(select json_mergepatch(params,
'{"systemInstruction": {"parts": [{"text":
"Summarize in three plain-text sentences, without Markdown."}]}}'
returning json)
from llm_models
where name = 'GEMINI')) as summary
from dual;Output:
SUMMARY __________________________________________________________________________________________________ Atlas Mobile 3.9 brings Atlas CRM and Billing to iOS and Android devices, offering features like biometric login and customizable notifications. The app includes an offline mode that stores the last ninety days of records on the device and automatically syncs changes within seven days of reconnecting. Additionally, users can manage data and battery usage through sync settings while applying suggested workarounds for device-specific notification and crash issues.
Three sentences covering devices, offline mode, sync, and known problems. Without the system instruction, the summary comes as a Markdown list, the model's habit from chat windows. Extracting text from stored PDFs is covered in how to chunk documents for vector search in Oracle.
Conclusion
DBMS_VECTOR_CHAIN.UTL_TO_GENERATE_TEXT calls a language model from SQL and PL/SQL and passes any extra parameters to the provider. Keep models in a table behind one GENERATE function, set the model's role with a system instruction, use temperature 0 for tasks with one right answer, turn thinking off for simple tasks so it does not eat the token limit, and ask for JSON with a schema when code must read the answer. A system instruction shapes the form of an answer, but only facts in the prompt make it right.
