How to Build RAG with PL/SQL in Oracle Database

Ask a language model about your company's refund policy and it writes what help desks usually say, plausibly and wrongly. Retrieval-augmented generation (RAG) fixes that: find the knowledge that answers the question, put it in the prompt, and make the model answer only from it, with citations.

This guide builds RAG entirely in Oracle AI Database 26ai with two PL/SQL functions: RETRIEVE, which finds the nearest sources and leaves out anything off the subject, and ASK, which builds the prompt, calls Gemini, cites sources, admits when the answer is not there, and logs every answer.

Code for This Guide

The examples are files 01 to 06 in the examples/ch12 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.

Put All Knowledge Behind One View

An assistant answers from several sources: here 24 knowledge base articles and 44 chunks of PDF and Word documents. A view puts them behind one name, so no caller has to search and merge them separately. This example first gives the document chunks Gemini embeddings too, in one batch call, then creates the view.

Example:

-- the document chunks get Gemini embeddings too, as the articles did in
alter table doc_chunks add (gemini_embedding vector(3072, float32));

declare
  l_chunks   sys.vector_array_t;
  l_results  sys.vector_array_t;
  l_params   json;
begin
  select params into l_params from embedding_models where name = 'GEMINI';

  select json_object('chunk_id' value doc_id * 1000 + chunk_id,
                     'chunk_data' value chunk_text returning clob)
  bulk   collect into l_chunks
  from   doc_chunks;

  l_results := dbms_vector_chain.utl_to_embeddings(l_chunks, l_params);

  forall i in 1 .. l_results.count
    update doc_chunks
    set    gemini_embedding = to_vector(json_value(l_results(i), '$.embed_vector'
                                                   returning clob))
    where  doc_id * 1000 + chunk_id = json_value(l_results(i), '$.embed_id');
  commit;
end;
/

-- everything the assistant may answer from: articles and document chunks
create or replace view knowledge as
select 'Article ' || a.article_id as source, a.body as text,
       a.embedding, a.gemini_embedding
from   kb_articles a
union all
select d.title || ', part ' || c.chunk_id, to_clob(c.chunk_text),
       c.embedding, c.gemini_embedding
from   doc_chunks c join atlas_documents d on d.doc_id = c.doc_id;

select count(*) as sources,
       count(case when gemini_embedding is not null then 1 end) as with_gemini
from   knowledge;

Output:

Table DOC_CHUNKS altered.

PL/SQL procedure successfully completed.

View KNOWLEDGE created.

   SOURCES    WITH_GEMINI
__________ ______________
        68             68

Sixty-eight sources, each with both embeddings. The SOURCE column is what the assistant cites, such as "Article KB-201" or "Atlas CRM 8.4 Administrator Guide, part 4". Adding a new kind of knowledge, such as resolved tickets, is one more branch of the UNION ALL.

Retrieve the Sources

RETRIEVE embeds the question with the chosen model, finds the nearest sources, and returns them as a JSON array numbered by distance. It returns at most four sources, and none farther than a cutoff: 0.8 for the in-database model and 0.45 for Gemini, distances measured beforehand to separate the subject of the knowledge base from everything else.

Example:

create or replace function retrieve (
  p_question      in varchar2,
  p_model         in varchar2    default 'MINILM',   -- or 'GEMINI'
  p_k             in pls_integer default 4,          -- at most this many sources
  p_max_distance  in number      default null        -- farther sources are off the subject
) return json
is
  l_vector    vector := embed(p_question, p_model);
  l_max       number := coalesce(p_max_distance,
                                 case p_model when 'GEMINI' then 0.45 else 0.8 end);
  l_sources   json;
begin
  select json_arrayagg(json_object('n' value rownum, 'source' value source,
                                   'distance' value round(distance, 3),
                                   'text' value text returning clob)
                       order by distance returning json)
  into   l_sources
  from  (select k.source, k.text,
                case p_model
                  when 'GEMINI' then vector_distance(k.gemini_embedding, l_vector, cosine)
                  else vector_distance(k.embedding, l_vector, cosine)
                end as distance
         from   knowledge k
         order  by distance
         fetch  first p_k rows only)
  where  distance <= l_max;
  return l_sources;
end;
/

select s.n, s.source, s.distance
from   json_table(retrieve('Can I get my money back for a duplicate charge?'), '$[*]'
         columns (n number path '$.n', source varchar2(60) path '$.source',
                  distance number path '$.distance')) s;

Output:

Function RETRIEVE compiled

   N SOURCE                                     DISTANCE
____ _______________________________________ ___________
   1 Article KB-201                                0.319
   2 Atlas Billing 5.2 User Guide, part 6          0.448
   3 Atlas Billing 5.2 User Guide, part 8          0.635
   4 Atlas Billing 5.2 User Guide, part 3          0.647

The article on duplicate charges comes first, followed by parts of the billing guide. More sources make it likelier that the answer is among them, but make the prompt longer, slower, and more expensive; four short sources suit this knowledge base. JSON keeps RETRIEVE easy to use everywhere: JSON_TABLE turns it into rows, PL/SQL reads it directly, and APEX can pass it to JavaScript. How to measure cutoffs is shown in how to build semantic search in Oracle Database.

Answer from the Sources

The prompt of a RAG call has two parts:

ASK retrieves the sources. If there are none, it answers that it can only help with the product, without calling the model at all. Otherwise it builds the prompt, calls GENERATE at temperature 0 with thinking off, and logs the question, answer, and sources in RAG_LOG, in an autonomous transaction so that ASK can be called from a query.

Example:

create table rag_log (
  asked_at  timestamp default systimestamp not null,
  question  varchar2(4000) not null,
  answer    clob,
  sources   json
);

create or replace function ask (
  p_question  in varchar2,
  p_model     in varchar2 default 'MINILM',   -- the embedding model that finds the sources
  p_llm       in varchar2 default 'GEMINI'    -- the language model that answers
) return clob
is
  c_instructions constant varchar2(1000) :=
    '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, in the language of the question.';
  l_sources  json := retrieve(p_question, p_model);
  l_prompt   clob;
  l_answer   clob;

  procedure log_answer is
    pragma autonomous_transaction;
  begin
    insert into rag_log (question, answer, sources)
    values (p_question, l_answer, l_sources);
    commit;
  end;
begin
  if l_sources is null then
    -- nothing in the knowledge base is near the question: don't call the model
    l_answer := 'I can only answer questions about Atlas products and services.';
  else
    -- the sources, numbered, then the question
    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'));

    l_answer := generate(l_prompt, p_llm, json_object(
      'systemInstruction' value json_object('parts' value json_array(
                                  json_object('text' value c_instructions))),
      'generationConfig'  value json('{"temperature": 0,
                                       "thinkingConfig": {"thinkingBudget": 0}}')
      returning json));
  end if;

  log_answer;
  return l_answer;
end;
/

select ask('Can I get my money back for a duplicate charge?') as answer from dual;

Output:

Table RAG_LOG created.

Function ASK compiled

ANSWER
__________________________________________________________________________________________________
Yes, Atlas detects most duplicate charges within 24 hours and refunds them automatically, and
Support also refunds the duplicate as soon as it is reported [1], [2]. The refund returns the
money to your original payment method [2]. Refunds to cards appear on your card statement within 5
to 10 business days, while refunds of direct debits take up to 3 business days [1], [2].

Every statement is now the company's own policy: the automatic refund within 24 hours, the refund on request, 5 to 10 business days for cards, 3 for direct debits. Each is cited, [1] for the article and [2] for the billing guide, so a customer or agent can check it. Answering from given text needs neither creativity nor reasoning, hence temperature 0 and no thinking.

Look at the Prompt

What the model sees decides what it answers. GENERATE logged the prompt, so it can be read back.

Example:

-- the prompt of the last call to Gemini: what the model saw
select substr(prompt, 1, 900) || '...' as prompt
from   llm_calls
order  by call_id desc
fetch  first 1 row only;

Output:

PROMPT
__________________________________________________________________________________________________
Sources:
[1] Article KB-201: A duplicate charge usually happens when the payment gateway times out and the
charge is retried. 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. Never pay the same invoice
manually after an automatic charge has failed without checking the invoice status first.

[2] Atlas Billing 5.2 User Guide, part 6: A payment can also fail because the payment gateway does
not answer in time. In that case the charge

is retried at once, and in rare cases both charges succeed: the customer is charged twice. Atlas
detects

most duplicate charges within 24 hours and refunds them automatically.

Refunds and credit notes

A refund returns money to the customer's payment method. Refunds to cards appear on the card

statement within 5 to 10 business days, depending on the bank; refunds ...

The sources are numbered and named so the model can cite them. The PDF text still carries line breaks from the page layout: harmless to the model, but worth cleaning up if an application displays the sources.

Handle Questions the Sources Cannot Answer

A RAG assistant meets three kinds of question: those its sources answer, those on its subject that the sources do not answer, and those off its subject.

Example:

-- a question only a manual answers, one the sources don't answer, and one off the subject
select ask('How many custom roles can an Enterprise account have?') as answer from dual;

select ask('Can I pay my invoice in Bitcoin?') as answer from dual;

select ask('What is the capital of France?') as answer from dual;

Output:

ANSWER
________________________________________________________________________
Enterprise accounts can create up to 20 custom roles per account [1].

ANSWER
_____________________________________________________
I could not find this in the Atlas knowledge base.

ANSWER
_________________________________________________________________
I can only answer questions about Atlas products and services.

"Say exactly" makes the refusal easy to detect, so an application can count unanswered questions, show a "contact support" button, or collect them as topics for new articles.

Answer in Other Languages

The small in-database embedding model does not understand Spanish, while Gemini's embedding model does. This example asks the same question in Spanish with each.

Example:

-- a question in Spanish: sources found by the English model, and by Gemini's
select ask('¿Puedo recuperar el dinero de un cobro duplicado?') as minilm_answer
from   dual;

select ask('¿Puedo recuperar el dinero de un cobro duplicado?', 'GEMINI') as gemini_answer
from   dual;

Output:

MINILM_ANSWER
_________________________________________________________________
I can only answer questions about Atlas products and services.

GEMINI_ANSWER
__________________________________________________________________________________________________
Sí, puedes recuperar el dinero. Atlas detecta la mayoría de los cobros duplicados en 24 horas y
los reembolsa automáticamente, o bien el equipo de soporte reembolsa el duplicado tan pronto como
se reporta [1], [2]. Los reembolsos se devuelven al método de pago del cliente y aparecen en el
extracto de la tarjeta en un plazo de 5 a 10 días hábiles, o en hasta 3 días hábiles si se trata
de adeudo directo [1], [2].

With the in-database model, no source came near enough and the assistant declined. With Gemini embeddings, the Spanish question found the English sources, and the answer came back in Spanish with the facts of those sources. The knowledge base stays in one language; questions and answers can be in any.

Conclusion

RAG in Oracle is a view over every source, a RETRIEVE function that returns the nearest sources as JSON and drops anything beyond a measured cutoff, and an ASK function that sends numbered sources plus the question to the model with instructions to answer only from them, cite them, and say an exact sentence when the answer is missing. Answer off-topic questions without calling the model, log every answer with its sources, and use a multilingual embedding model to serve questions in any language.