Sending prompts to a cloud provider is fine for many organizations and impossible for some. Data may not leave the building, by law or by contract; the network may not reach the internet; per-call costs may be unwelcome at high volume; and a provider can retire a model at any time. A local model runs on hardware you control, and Ollama, an open-source program, downloads and serves open models such as Llama, Gemma, and Mistral.
This guide serves a local model with Ollama, gives Oracle AI Database 26ai network access to it, calls it with DBMS_VECTOR_CHAIN's ollama provider, uses its own settings, runs RAG entirely locally, and measures the small model against Gemini.
Code for This Guide
The examples are in the examples/ch26 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 reuse the LLM_MODELS table and GENERATE function from how to call an LLM from PL/SQL with UTL_TO_GENERATE_TEXT, and the RETRIEVE function from how to build RAG with PL/SQL in Oracle Database. The database runs in a Docker container, so it reaches the host computer as host.docker.internal.
Install Ollama and Serve a Model
Ollama runs on macOS, Linux, and Windows. Install it from ollama.com, or on macOS with Homebrew.
Example (macOS):
brew install ollama
Download a model. llama3.2:3b, Meta's Llama 3.2 with 3 billion parameters, is 2 GB and fast on a laptop.
Example:
ollama pull llama3.2:3b
Start the server so it accepts connections from the database container, not only from the computer itself. It listens on port 11434.
Example:
OLLAMA_HOST=0.0.0.0:11434 ollama serve
On a server, the database reaches Ollama by host name; a machine with a GPU runs larger models at useful speeds.
Allow the Database to Reach It
As for any provider, the database needs an access control entry for the host and port it calls.
Example (run as SYS in the pluggable database):
-- ATLAS may call the Ollama server on the host computer, port 11434
begin
dbms_network_acl_admin.append_host_ace(
host => 'host.docker.internal',
lower_port => 11434,
upper_port => 11434,
ace => xs$ace_type(privilege_list => xs$name_list('http'),
principal_name => 'ATLAS',
principal_type => xs_acl.ptype_db));
end;
/Output:
PL/SQL procedure successfully completed.
This is plain HTTP, which is fine for Ollama on the same computer or network. Across a network, put it behind HTTPS.
Call the Local Model
UTL_TO_GENERATE_TEXT supports Ollama with the provider ollama. No credential is needed.
Syntax:
{ "provider" : "ollama",
"host" : "local",
"url" : "http://host:11434/api/generate",
"model" : "model_name" }Example:
-- the first call loads the model into memory; the second finds it there
set timing on
select dbms_vector_chain.utl_to_generate_text(
'In one sentence: why do customers contact a help desk?',
json('{"provider": "ollama",
"host": "local",
"url": "http://host.docker.internal:11434/api/generate",
"model": "llama3.2:3b"}')) as answer
from dual;
select dbms_vector_chain.utl_to_generate_text(
'In one sentence: why do customers contact a help desk?',
json('{"provider": "ollama",
"host": "local",
"url": "http://host.docker.internal:11434/api/generate",
"model": "llama3.2:3b"}')) as answer
from dual;
set timing offOutput:
ANSWER __________________________________________________________________________________________________ Customers contact a help desk to resolve issues, answer questions, or receive assistance with technical problems, products, or services, often due to frustration or a lack of understanding about a product, service, or process. Elapsed: 00:00:01.240 ANSWER __________________________________________________________________________________________________ Customers contact a help desk to resolve technical issues, answer questions, or seek assistance with products or services they are using, often due to frustration or difficulty with a specific problem. Elapsed: 00:00:00.829
About a second each, a good sentence each time, and nothing left the computer. The first call after Ollama has been idle takes longer: it loads the model into memory on demand and unloads it after five idle minutes.
Add It to the Model Table
With models kept in a table, the local model is one more name for GENERATE, which also logs its calls.
Example:
insert into llm_models (name, params, thinking) values
('LLAMA_LOCAL',
json('{"provider": "ollama",
"host": "local",
"url": "http://host.docker.internal:11434/api/generate",
"model": "llama3.2:3b"}'), 'No');
commit;
select generate('Name the largest planet of the solar system. Answer with one word.',
'LLAMA_LOCAL') as answer
from dual;Output:
1 row inserted. Commit complete. ANSWER ___________ Jupiter.
Use Ollama's Own Settings
Options are provider-specific. Ollama calls the system instruction system, keeps temperature and similar settings in options, and constrains the answer with format, which takes a JSON schema.
| Setting | Gemini | Ollama |
|---|---|---|
| System instruction | systemInstruction | system |
| Temperature | generationConfig.temperature | options.temperature |
| JSON schema | generationConfig.responseSchema | format |
Example:
-- Ollama's names for the settings: system, options, and format
select generate('Can I get my money back for a duplicate charge?', 'LLAMA_LOCAL',
json_object('system' value 'You are the support assistant of Atlas Software. '
|| 'Answer in at most two sentences, in plain text.',
'options' value json('{"temperature": 0}') returning json)) as answer
from dual;
select generate('Classify this support ticket: '
|| '"Since the update, nobody on our team can sign in."', 'LLAMA_LOCAL',
json('{"options": {"temperature": 0},
"format": {"type": "object", "required": ["category"],
"properties": {"category": {"type": "string",
"enum": ["Account", "Billing", "Bug", "Question",
"Feature Request"]}}}}')) as answer
from dual;Output:
ANSWER
__________________________________________________________________________________________________
I can assist you with a refund request, but I'll need to check on the specific details of your
account and the duplicate charge in question. Please contact our support team directly so we can
review your case and provide a more accurate response.
ANSWER
______________________
{
"category": "Bug"
}Both settings work: the answer follows the role, and the classification is JSON with a value from the list. The first answer also shows that without the knowledge base, any model, local or not, writes what a help desk plausibly says.
Run RAG Entirely Locally
ASK_LOCAL is a RAG function with the local model: the same sources from RETRIEVE, the same instructions given as Ollama's system prompt, and temperature 0.
Example:
-- RAG with the local model: the instructions go in Ollama's system prompt
create or replace function ask_local (p_question in varchar2) return clob
is
l_sources json := retrieve(p_question);
l_prompt clob;
begin
if l_sources is null then
return 'I can only answer questions about Atlas products and services.';
end if;
select 'Sources:' || chr(10)
|| listagg('[' || n || '] ' || source || ': ' || text, chr(10) || chr(10))
within group (order by n)
|| chr(10) || chr(10) || 'Question: ' || p_question
into l_prompt
from json_table(l_sources, '$[*]' columns (n number path '$.n',
source varchar2(100) path '$.source',
text varchar2(4000) path '$.text'));
return generate(l_prompt, 'LLAMA_LOCAL', json_object(
'system' value 'You are the support assistant of Atlas Software. '
|| 'Answer the question using only the numbered sources. '
|| 'Cite the sources you used, like [1] or [2]. '
|| 'If the sources do not contain the answer, say exactly: '
|| 'I could not find this in the Atlas knowledge base. '
|| 'Answer in plain text, in at most four sentences.',
'options' value json('{"temperature": 0}') returning json));
end;
/
select ask_local('Can I get my money back for a duplicate charge?') as answer from dual;Output:
Function ASK_LOCAL compiled ANSWER __________________________________________________________________________________________________ Yes, you can get your money back for a duplicate charge. According to [1], a duplicate charge usually happens when the payment gateway times out and the charge is retried, and support refunds the duplicate as soon as it is reported. Refunds appear on card statements within 5 to 10 business days, depending on the bank.
Answered from the article, with its citation: the knowledge does the work, and a small model is enough to put it into words. Retrieval uses an embedding model inside the database, so the whole assistant, embedding, search, and generation, runs without any external service.
Measure the Small Model
The first test asks 14 questions that only the documents answer, checking each answer for its key fact and a citation.
Example:
-- the test, with the local model: does the answer contain the key fact?
set timing on
with keys (question_id, key_fact) as (
values (1, '20'), (2, '7 days'), (3, '20 MB'), (4, '180 days'), (5, '3 days'),
(6, '3 business days'), (7, 'ten months'), (8, '11'), (9, '15 minutes'),
(10, '100'), (11, '30 June 2027'), (12, '5 GB'), (13, '14 September 2026'),
(14, '40'))
select count(*) as questions,
count(case when instr(a.answer, k.key_fact) > 0 then 1 end) as with_key_fact,
count(case when regexp_like(a.answer, '\[\d') then 1 end) as with_citation
from doc_questions q
join keys k on k.question_id = q.question_id
cross apply (select ask_local(q.question) as answer from dual) a;
set timing offOutput:
QUESTIONS WITH_KEY_FACT WITH_CITATION
____________ ________________ ________________
14 14 10
Elapsed: 00:00:31.141All 14 answers contain the key fact, as with Gemini, in about two seconds each. Ten cite their source, against all 14 with Gemini: the small model follows instructions less consistently.
The second test classifies 100 tickets one by one and compares agreement with the agents.
Example:
-- the 100 test tickets, classified one by one by the local model
set timing on
select count(*) as tickets,
count(case when json_value(generate(
'Classify this support ticket of Atlas Software. Categories: Account '
|| '(sign-in, users, security), Billing (invoices, payments, plans, tax), '
|| 'Bug (something does not work), Question (how to do something), '
|| 'Feature Request (something that does not exist). Ticket: '
|| t.subject || '. ' || t.description,
'LLAMA_LOCAL',
json('{"options": {"temperature": 0},
"format": {"type": "object", "required": ["category"],
"properties": {"category": {"type": "string",
"enum": ["Account", "Billing", "Bug", "Question",
"Feature Request"]}}}}')), '$.category') = t.category
then 1 end) as local_right,
count(case when t.ai_category = t.category then 1 end) as gemini_right
from tickets t
where mod(t.ticket_id, 4) = 0;
set timing offOutput:
TICKETS LOCAL_RIGHT GEMINI_RIGHT
__________ ______________ _______________
100 70 86
Elapsed: 00:00:56.401Seventy right, against 86 for Gemini. Classifying means weighing what a ticket means against definitions, and that is where model size shows.
| Task | llama3.2:3b, local | Gemini Flash |
|---|---|---|
| Answer from sources (key fact) | 14 of 14 | 14 of 14 |
| Cite the sources | 10 of 14 | 14 of 14 |
| Classify tickets like the agents | 70 of 100 | 86 of 100 |
| Data leaves the database server | No | Yes |
| Cost per call | Electricity | Per token |
A larger local model narrows the gap at the price of memory and speed: pull it, add a row to LLM_MODELS, and run the same tests. APEX 26.1 also supports Ollama as a Generative AI service provider, so APEX assistants can use the same local model.
Conclusion
To run a local LLM for Oracle Database, serve a model with Ollama on port 11434, give the schema an ACL for that host and port, and call it with DBMS_VECTOR_CHAIN's ollama provider, using Ollama's system, options, and format settings. With RAG and an in-database embedding model, nothing leaves your hardware. A 3-billion-parameter model answered document questions as well as Gemini but cited and classified less reliably, so measure before you choose.
