Building RAG Entirely Inside Oracle Database 26ai — No Vector DB, No App Layer, Just PL/SQL

Building Retrieval-Augmented Generation (RAG)

Entirely Inside Oracle Database — with PL/SQL

How I grounded a local LLM (Ollama + Qwen2.5) in Oracle 26ai's native Vector Search — no external vector database, no separate application layer.

Munish Kumar Karna

Senior Database Solution Architect | Oracle DBA | Oracle RAC, ASM, Data Guard, GoldenGate, OCI

---

Why This Matters

Retrieval-Augmented Generation (RAG) is usually built with a dedicated vector database, an orchestration framework, and an application server sitting between the data and the LLM. With Oracle Database 26ai, that entire pipeline can live inside the database itself.

In this walkthrough, I demonstrate a complete, self-contained RAG pipeline built purely in PL/SQL: importing an ONNX embedding model, running a local LLM through Ollama, chunking and embedding a real PDF manual, and using Oracle's native VECTOR data type and DBMS_VECTOR package to ground the model's answers in that document — all from a single SQL session.

Prerequisites

Two components need to be in place before the pipeline can run:

• An imported ONNX embedding model — ALL_MINILM_L12_V2 — used by Oracle to generate vector embeddings natively.
• Ollama installed locally with the qwen2.5:7b model pulled and served, so Oracle can call it as an LLM provider.

A step-by-step guide to importing an ONNX model into Oracle Database is available in this earlier post: Importing an ONNX LLM model into Oracle Database →

Verifying the ALL_MINILM_L12_V2 embedding model is registered in the database:

Confirming Ollama is running locally with the qwen2.5:7b model loaded:

The Problem: An LLM With No Knowledge of “Oracle 26ai”

Before building any retrieval layer, I asked the LLM directly — with no context supplied — about a very specific, current topic:

SET LONG 100000
SET SERVEROUTPUT ON
 
SELECT DBMS_VECTOR.UTL_TO_GENERATE_TEXT(
  'How Oracle RAC maintains HA in Oracle 26ai',
  JSON('{
    "provider": "ollama",
    "host": "local",
    "url": "http://localhost:11434/api/generate",
    "model": "qwen2.5:7b",
    "transfer_timeout": 800
  }')
) AS qwen_output
FROM DUAL;

As expected, the model pushed back — it had no idea “Oracle 26ai” was a real release, and its answer, while generally correct about Oracle RAC, was generic and hedged rather than grounded in any authoritative source:

“It seems there might be a typo or misunderstanding regarding ‘Oracle 26AI.’ There is no version of Oracle Database called ‘26,’ and AI isn’t typically part of the version naming convention...”

This confirmed the gap that RAG is designed to close: the model's parametric knowledge stops at its training cutoff, and it has no way to consult a current, authoritative document unless we give it one. So I set out to implement RAG directly in Oracle PL/SQL.

Implementing RAG in Oracle PL/SQL

STEP 1Create the Source Table for the PDF

A staging table to hold the raw PDF file as a BLOB:

CREATE TABLE pdf_documents (
  id        NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  file_name VARCHAR2(255),
  pdf_blob  BLOB
);

STEP 2Load the PDF Into the Staging Table

Using a directory object on the database server, the Oracle Clusterware Administration and Deployment Guide is loaded into the BLOB column:

-- 1. Create a directory pointing to the folder containing the PDF
CREATE OR REPLACE DIRECTORY pdf_dir AS '/home/oracle/books';

-- 2. Load the file into the BLOB column
DECLARE
  l_bfile BFILE;
  l_blob  BLOB;
BEGIN
  INSERT INTO pdf_documents (file_name, pdf_blob)
  VALUES ('clusterware-administration-and-deployment-guide.pdf', EMPTY_BLOB())
  RETURNING pdf_blob INTO l_blob;
 
  l_bfile := BFILENAME('PDF_DIR', 'clusterware-administration-and-deployment-guide.pdf');
  DBMS_LOB.FILEOPEN(l_bfile, DBMS_LOB.FILE_READONLY);
  DBMS_LOB.LOADFROMFILE(l_blob, l_bfile, DBMS_LOB.GETLENGTH(l_bfile));
  DBMS_LOB.FILECLOSE(l_bfile);
  COMMIT;
END;
/

STEP 3Create the Target Chunk & Vector Table

This table stores the segmented text chunks alongside the vector embeddings generated from the model:

CREATE TABLE vector_book_chunks (
  doc_id     NUMBER,
  chunk_id   NUMBER,
  book_name  VARCHAR2(50),
  chunk_data VARCHAR2(4000),
  embedding  VECTOR(384, FLOAT32)
) TABLESPACE users;

STEP 4Extract, Chunk, Embed, and Populate

A single INSERT statement parses the PDF text, splits it into overlapping chunks, computes 384-dimensional embeddings via ALL_MINILM_L12_V2, and loads everything into the target table:

INSERT INTO system.vector_book_chunks (doc_id, chunk_id, book_name, chunk_data, embedding)
SELECT
  p.id,
  JSON_VALUE(c.column_value, '$.chunk_id' RETURNING NUMBER),
  'Clusterware Administration and Deployment Guide',
  JSON_VALUE(c.column_value, '$.chunk_data'),
  VECTOR_EMBEDDING(SYS.ALL_MINILM_L12_V2 USING JSON_VALUE(c.column_value, '$.chunk_data') AS data)
FROM system.pdf_documents p,
     DBMS_VECTOR.UTL_TO_CHUNKS(
       TO_CLOB(p.pdf_blob),
       JSON('{"by":"words", "max":"300", "overlap":"50", "split":"recursively"}')
     ) c
WHERE p.file_name = 'clusterware-administration-and-deployment-guide.pdf';
 
COMMIT;

STEP 5Retrieve, Augment, and Generate

With the vector store populated, this PL/SQL block runs the full RAG loop against the same question asked earlier: it embeds the prompt, retrieves the three most relevant chunks by cosine distance, builds a grounded context, and passes it to the LLM through DBMS_VECTOR.UTL_TO_GENERATE_TEXT:

SET SERVEROUTPUT ON SIZE UNLIMITED;
DECLARE
  l_prompt        VARCHAR2(4000) := 'How Oracle RAC maintains HA in Oracle 26ai?';
  l_context       CLOB := '';
  l_json_payload  CLOB;
  l_response_text CLOB;
BEGIN
  UTL_HTTP.set_transfer_timeout(1200);
 
  -- 1. Retrieve top matching chunks for the prompt
  FOR r IN (
    SELECT chunk_data
    FROM system.vector_book_chunks,
         (SELECT VECTOR_EMBEDDING(SYS.ALL_MINILM_L12_V2 USING l_prompt AS data) AS q_vec FROM DUAL) q
    WHERE book_name = 'Clusterware Administration and Deployment Guide'
    ORDER BY VECTOR_DISTANCE(embedding, q.q_vec, COSINE) ASC
    FETCH FIRST 3 ROWS ONLY
  ) LOOP
    l_context := l_context || CHR(10) || r.chunk_data;
  END LOOP;
 
  -- 2. Build the JSON provider configuration
  SELECT JSON_OBJECT(
           'provider' VALUE 'ollama',
           'host' VALUE 'local',
           'url' VALUE 'http://localhost:11434/api/generate',
           'model' VALUE 'qwen2.5:7b',
           'transfer_timeout' VALUE 1200,
           'stream' VALUE FALSE
           RETURNING CLOB
         ) INTO l_json_payload FROM DUAL;
 
  -- 3. Generate the grounded answer
  l_response_text := DBMS_VECTOR.UTL_TO_GENERATE_TEXT(
    'Using the following context, answer the question accurately.' || CHR(10) ||
    'Context:' || CHR(10) || l_context || CHR(10) ||
    'Question:' || CHR(10) || l_prompt,
    JSON(l_json_payload)
  );
 
  SYS.DBMS_OUTPUT.PUT_LINE('LLM Response:');
  SYS.DBMS_OUTPUT.PUT_LINE(l_response_text);
EXCEPTION
  WHEN OTHERS THEN
    SYS.DBMS_OUTPUT.PUT_LINE('Error Stack: ' || DBMS_UTILITY.format_error_stack);
    RAISE;
END;
/

Execution output:

The Result

After implementing RAG, the same question produced a materially different answer. Rather than hedging on an unfamiliar term, the model drew directly on the retrieved chunks from the Clusterware Administration and Deployment Guide — referencing Clusterware components, RAC processes, and platform-specific configuration topics — and grounded its response in language pulled from the source documentation.

“Oracle 26AI” was no longer unknown to the LLM.

Scaling It Up: Adding a Vector Index

The walkthrough above uses a full scan over the embedding column, which is fine for a single PDF's worth of chunks. As the corpus grows, an HNSW vector index brings similarity search down from a linear scan to an approximate nearest-neighbor lookup:

CREATE VECTOR INDEX vector_book_chunks_hnsw_idx
  ON vector_book_chunks (embedding)
  ORGANIZATION INMEMORY NEIGHBOR GRAPH
  DISTANCE COSINE
  WITH TARGET ACCURACY 95
  PARAMETERS (type HNSW, neighbors 40, efConstruction 500)
  TABLESPACE users;

With the index in place, the same retrieval query in Step 5 now resolves through an approximate nearest-neighbor graph lookup instead of a full scan of the embedding column — the query logic stays identical, only the execution path underneath changes.

Key Takeaways

• Oracle Database 26ai can host an end-to-end RAG pipeline — embedding, chunking, vector storage, similarity search, and generation — natively in PL/SQL.
• VECTOR_EMBEDDING and DBMS_VECTOR.UTL_TO_CHUNKS handle chunking and embedding generation directly against a BLOB, with no external ETL step.
• VECTOR_DISTANCE with the VECTOR data type performs similarity search in-database, right alongside the relational data.
• DBMS_VECTOR.UTL_TO_GENERATE_TEXT can call any compatible LLM provider — local (Ollama) or cloud — directly from SQL/PL-SQL, keeping the entire RAG loop inside the database boundary.
• A native HNSW/IVF vector index — CREATE VECTOR INDEX — lets the same retrieval query scale from a full scan to an approximate nearest-neighbor lookup as the document corpus grows.

---

This was a hands-on proof of concept run end-to-end in a single PL/SQL session — happy to share more detail on the setup or discuss real-world use cases for in-database RAG.

#OracleDatabase #Oracle26ai #RAG #VectorSearch #PLSQL #OracleDBA #AI #GenerativeAI #Ollama