PeopleSoft RAG Without a Vector Database: Building Native Semantic Retrieval Inside Oracle AI Database
Upload a PDF on a PeopleSoft page, store it in a PeopleTools record, index it into vectors, and retrieve answers to plain-English questions — with every stage of the pipeline running inside the PeopleSoft database. No external search service, no middleware, no document ever touching a filesystem.
Every PeopleSoft AI architecture I've seen — including ones I've built — follows the same pattern: PeopleSoft on one side, an external AI stack on the other (Azure AI Search, a standalone vector database, a middleware layer), and an integration bridge between them. It works, but it carries two permanent taxes: your document content leaves the database, and you must replicate PeopleSoft's row-level security in a foreign system and keep it in sync forever.
Oracle 23ai changes the equation. With AI Vector Search, the native VECTOR datatype, and an ONNX model runtime inside the database, the entire RAG pipeline can live where your PeopleSoft data already lives. I built this end to end on an HCM PUM image — this post walks through the complete flow, all the objects, and the landmines.
The architecture in one picture
┌────────────── PIA (browser) ──────────────┐
│ VA_RAG page: [Upload Document] [Ask] │
└───────────────┬───────────────┬───────────┘
│ AddAttachment │ CreateSQL
▼ ▼
┌────────────────── PeopleSoft Database (Oracle 23ai) ─────────────────┐
│ │
│ 1. STORE PS_VA_RAG_ATT (attachment rows, record:// storage) │
│ 2. ASSEMBLE PS_VA_RAG_DOC (header + reassembled BLOB) │
│ 3. INDEX PS_VA_RAG_CHUNK (text chunks + VECTOR(384) + HNSW) │
│ │
│ DBMS_VECTOR_CHAIN: UTL_TO_TEXT → UTL_TO_CHUNKS │
│ MINILM_MODEL (ONNX, in-database): VECTOR_EMBEDDING() │
│ Retrieval: VECTOR_DISTANCE(...) ORDER BY ... FETCH APPROX FIRST k │
└──────────────────────────────────────────────────────────────────────┘
Four stages: upload (a normal PeopleSoft attachment), store (PeopleTools chunked attachment rows plus a document header), index (parse → chunk → embed, one PL/SQL call), retrieve (one SQL statement, rendered as ranked cards on the same page).
The key property: documents never exist on a filesystem. They travel browser → web server → app server → attachment record, the same path as every PeopleSoft attachment you've ever managed, and everything downstream is SQL and PL/SQL against tables.
What it looks like working
I indexed a Leave Guide PDF and asked, on the PeopleSoft page:
how many days off do I get when I have a baby
Top results: the Paid Parental Leave section (12 weeks, eligibility rules), XX County Family and Medical Leave (18 weeks bonding leave), and the protected-leave overview — ranked by cosine distance, with zero keyword overlap between the question and the text. "Days off when I have a baby" found "paid parental leave" and "bonding with a new child" on meaning alone. That's the semantic search payoff, and it happened entirely inside the database.
Prerequisites
- Oracle 23ai under your PeopleSoft database. PeopleTools 8.62 certifies 23ai, but PUM images still ship on 19c — I upgraded the image database in place with AutoUpgrade (19.26 → 23.26, about 2h20m on a VirtualBox VM; that adventure is a post of its own).
COMPATIBLE≥ 23.4 (required for the VECTOR datatype) andvector_memory_sizeset (I used 512M) for the HNSW in-memory index.- The CONTEXT component of Oracle Text installed in your PDB — see Landmine #1, because on a PUM image it probably isn't.
Step 0 — Load the embedding model (one-time setup)
The database ships vector infrastructure but no embedding model. Oracle publishes a pre-converted, "augmented" ONNX build of all-MiniLM-L12-v2 — augmented meaning the tokenizer is baked in, which is what makes it work with the in-database runtime (a raw Hugging Face export will not). Download it (search "Oracle pre-built ONNX embedding model" on blogs.oracle.com/machinelearning for the current link — the pre-authenticated URLs rotate), place it on the DB server, and load it:
-- as SYSDBA in the PDB.
-- This is the ONLY Oracle directory in the whole design, used once,
-- for the model file. Documents never touch the filesystem.
CREATE OR REPLACE DIRECTORY ONNX_DIR AS '/opt/oracle/psft/db/oracle-server/models';
GRANT READ ON DIRECTORY ONNX_DIR TO SYSADM;
GRANT EXECUTE ON DBMS_VECTOR TO SYSADM;
GRANT EXECUTE ON CTXSYS.DBMS_VECTOR_CHAIN TO SYSADM; -- after Oracle Text is installed
GRANT CREATE MINING MODEL TO SYSADM;
-- as SYSADM:
EXEC DBMS_VECTOR.LOAD_ONNX_MODEL('ONNX_DIR','all_MiniLM_L12_v2.onnx','MINILM_MODEL');
-- the moment it becomes real:
SELECT VECTOR_EMBEDDING(MINILM_MODEL USING 'annual leave policy' AS data) FROM dual;
Step 1 — UPLOAD: a normal PeopleSoft attachment
Three PeopleTools objects:
Record VA_RAG_ATT — the attachment storage table. New record in App Designer, drag in the delivered subrecord FILE_ATTDET_SBR (ATTACHSYSFILENAME, FILE_SEQ, VERSION, FILE_SIZE, LASTUPDDTTM, FILE_DATA), keys ATTACHSYSFILENAME + FILE_SEQ, Build → Create Table.
URL definition — PeopleTools > Utilities > Administration > URLs: identifier VA_RAG_ATT_URL, URL record://VA_RAG_ATT. This is what tells AddAttachment to store into the database record rather than an FTP/HTTP repository.
Page + component — a work record (VA_RAG_WRK) carrying: ATTACHADD (Char 1, push button), QUESTION (Char 254), ASKBTN (Char 1, push button), ANSWER (Long Character). All fields at level 0 on the page; ANSWER gets Rich Text Enabled + Display Only (it will render our HTML result cards). Component search record INSTALLATION, registered to a menu and permission list.
Upload button FieldChange:
Local number &ret;
Local string &sysFile, &userFile, &docId, &status, &err;
/* next numeric doc id */
SQLExec("SELECT LPAD(NVL(MAX(TO_NUMBER(DOC_ID)),0)+1,8,'0') FROM PS_VA_RAG_DOC WHERE REGEXP_LIKE(DOC_ID,'^[0-9]+$')", &docId);
If None(&docId) Then
&docId = "00000001";
End-If;
&sysFile = &docId | "_"; /* unique storage prefix — see Landmine #4 */
&ret = AddAttachment(URL.VA_RAG_ATT_URL, &sysFile, "", &userFile, 0);
If &ret = %Attachment_Success Then
/* resolve the actual stored name (tools appends the user filename) */
SQLExec("SELECT ATTACHSYSFILENAME FROM PS_VA_RAG_ATT WHERE ATTACHSYSFILENAME LIKE :1 || '%' AND FILE_SEQ = 1", &docId | "_", &sysFile);
SQLExec("INSERT INTO PS_VA_RAG_DOC (DOC_ID, OPRID, ATTACHSYSFILENAME, ATTACHUSERFILE, DEPTID, DOC_STATUS, UPLOAD_DTTM, ERROR_MSG) VALUES (:1,:2,:3,:4,' ','N',SYSTIMESTAMP,' ')", &docId, %OperatorId, &sysFile, &userFile);
SQLExec("BEGIN VA_RAG.INGEST_FROM_ATT(:1); END;", &docId);
SQLExec("SELECT DOC_STATUS, ERROR_MSG FROM PS_VA_RAG_DOC WHERE DOC_ID=:1", &docId, &status, &err);
If &status = "I" Then
MessageBox(0, "", 0, 0, "Indexed: " | &userFile);
Else
MessageBox(0, "", 0, 0, "Indexing failed: " | &err);
End-If;
End-If;
The user browses, picks a PDF, and PeopleTools streams it over HTTPS into the database.
Step 2 — STORE: chunked attachment rows + a document header
Two tables hold the document:
PS_VA_RAG_ATT (built by App Designer above) — where AddAttachment lands the file. Important detail: PeopleTools does not store one BLOB per file. It splits the file into pieces — one row per FILE_SEQ, sized by the "Maximum Attachment Chunk Size" in PeopleTools Options (default ~28KB). My 500KB leave guide became 18 rows.
PS_VA_RAG_DOC — the document header, created directly in SQL (see Landmine #6 on why the vector-era objects stay out of App Designer):
CREATE TABLE PS_VA_RAG_DOC (
DOC_ID VARCHAR2(20) NOT NULL,
OPRID VARCHAR2(30) DEFAULT ' ' NOT NULL,
ATTACHSYSFILENAME VARCHAR2(128) DEFAULT ' ' NOT NULL,
ATTACHUSERFILE VARCHAR2(128) DEFAULT ' ' NOT NULL,
DEPTID VARCHAR2(10) DEFAULT ' ' NOT NULL,
DOC_STATUS VARCHAR2(1) DEFAULT 'N' NOT NULL, -- N/P/I/E
DOC_BLOB BLOB,
UPLOAD_DTTM TIMESTAMP DEFAULT SYSTIMESTAMP,
ERROR_MSG VARCHAR2(1000) DEFAULT ' ',
CONSTRAINT PS_VA_RAG_DOC_PK PRIMARY KEY (DOC_ID)
);
DOC_STATUS gives you an operational lifecycle: New → Parsing → Indexed, or Error with the message captured — which paid for itself repeatedly during the build.
Step 3 — INDEX: reassemble → parse → chunk → embed
The chunk table and its vector index:
CREATE TABLE PS_VA_RAG_CHUNK (
DOC_ID VARCHAR2(20) NOT NULL,
CHUNK_SEQ NUMBER NOT NULL,
CHUNK_TEXT VARCHAR2(4000 CHAR),
EMBEDDING VECTOR(384, FLOAT32),
CONSTRAINT PS_VA_RAG_CHUNK_PK PRIMARY KEY (DOC_ID, CHUNK_SEQ),
CONSTRAINT PS_VA_RAG_CHUNK_FK FOREIGN KEY (DOC_ID)
REFERENCES PS_VA_RAG_DOC (DOC_ID) ON DELETE CASCADE
);
CREATE VECTOR INDEX VA_RAG_CHUNK_HNSW ON PS_VA_RAG_CHUNK (EMBEDDING)
ORGANIZATION INMEMORY NEIGHBOR GRAPH DISTANCE COSINE
WITH TARGET ACCURACY 95;
And the ingestion procedures — the heart of the whole build:
PROCEDURE INGEST_FROM_ATT (p_doc_id IN VARCHAR2) IS
v_sysfile VARCHAR2(128);
v_doc BLOB;
BEGIN
SELECT ATTACHSYSFILENAME INTO v_sysfile
FROM PS_VA_RAG_DOC WHERE DOC_ID = p_doc_id;
-- reassemble PeopleTools' chunked attachment rows into one BLOB
DBMS_LOB.CREATETEMPORARY(v_doc, TRUE);
FOR r IN (SELECT FILE_DATA FROM PS_VA_RAG_ATT
WHERE ATTACHSYSFILENAME = v_sysfile
ORDER BY FILE_SEQ) LOOP
DBMS_LOB.APPEND(v_doc, r.FILE_DATA);
END LOOP;
UPDATE PS_VA_RAG_DOC SET DOC_BLOB = v_doc WHERE DOC_ID = p_doc_id;
COMMIT;
INGEST_DOC(p_doc_id);
END INGEST_FROM_ATT;
PROCEDURE INGEST_DOC (p_doc_id IN VARCHAR2) IS
v_doc BLOB;
v_text CLOB;
v_err VARCHAR2(1000);
v_params JSON := JSON('{"by":"words","max":"300","overlap":"40",
"split":"sentence","normalize":"all"}');
BEGIN
UPDATE PS_VA_RAG_DOC SET DOC_STATUS='P', ERROR_MSG=' ' WHERE DOC_ID=p_doc_id;
SELECT DOC_BLOB INTO v_doc FROM PS_VA_RAG_DOC WHERE DOC_ID=p_doc_id;
-- PDF/DOCX/HTML -> plain text (Oracle Text filters under the hood)
v_text := DBMS_VECTOR_CHAIN.UTL_TO_TEXT(v_doc);
IF v_text IS NULL OR LENGTH(v_text) < 20 THEN
RAISE_APPLICATION_ERROR(-20001,'Parser returned no text (scanned/image PDF?)');
END IF;
DELETE FROM PS_VA_RAG_CHUNK WHERE DOC_ID=p_doc_id;
-- chunk AND embed in a single INSERT...SELECT
INSERT INTO PS_VA_RAG_CHUNK (DOC_ID, CHUNK_SEQ, CHUNK_TEXT, EMBEDDING)
SELECT p_doc_id,
JSON_VALUE(c.column_value,'$.chunk_id' RETURNING NUMBER),
JSON_VALUE(c.column_value,'$.chunk_data' RETURNING VARCHAR2(4000)),
VECTOR_EMBEDDING(MINILM_MODEL
USING JSON_VALUE(c.column_value,'$.chunk_data'
RETURNING VARCHAR2(4000)) AS data)
FROM TABLE(DBMS_VECTOR_CHAIN.UTL_TO_CHUNKS(v_text, v_params)) c;
UPDATE PS_VA_RAG_DOC SET DOC_STATUS='I' WHERE DOC_ID=p_doc_id;
COMMIT;
EXCEPTION WHEN OTHERS THEN
v_err := SUBSTR(SQLERRM,1,1000);
ROLLBACK;
UPDATE PS_VA_RAG_DOC SET DOC_STATUS='E', ERROR_MSG=v_err WHERE DOC_ID=p_doc_id;
COMMIT;
RAISE;
END INGEST_DOC;
Read that INSERT...SELECT again: parse, chunk, and embed is one SQL statement. UTL_TO_CHUNKS is a table function streaming JSON chunk descriptors; VECTOR_EMBEDDING runs the ONNX transformer per row. My leave guide became 34 chunks with 34 embeddings in a few seconds, synchronously, while the user watched the page. PeopleCode's total involvement: SQLExec("BEGIN VA_RAG.INGEST_FROM_ATT(:1); END;", &docId);
Step 4 — RETRIEVE: one SQL statement, ranked cards on the page
The Ask button (VA_RAG_WRK.ASKBTN FieldChange) embeds the question inline and ranks by cosine distance:
Local SQL &sql;
Local string &docName, &chunk, &html, &q, &esc;
Local number &seq, &dist;
Local integer &rank;
&q = VA_RAG_WRK.QUESTION.Value;
&sql = CreateSQL(
"SELECT d.ATTACHUSERFILE, c.CHUNK_SEQ, SUBSTR(c.CHUNK_TEXT,1,1200), " |
"ROUND(VECTOR_DISTANCE(c.EMBEDDING, VECTOR_EMBEDDING(MINILM_MODEL USING :1 AS data), COSINE),3) " |
"FROM PS_VA_RAG_CHUNK c JOIN PS_VA_RAG_DOC d ON d.DOC_ID = c.DOC_ID " |
"WHERE d.DOC_STATUS = 'I' " |
"ORDER BY 4 FETCH APPROX FIRST 5 ROWS ONLY", &q);
&html = "<div style='font-family:Segoe UI,Arial,sans-serif;'>";
&html = &html | "<h3>Results for: <span style='color:#1a5276;'>" | &q | "</span></h3>";
&rank = 0;
While &sql.Fetch(&docName, &seq, &chunk, &dist)
&rank = &rank + 1;
&esc = Substitute(Substitute(Substitute(&chunk, "&", "&"), "<", "<"), ">", ">");
&html = &html | "<div style='border:1px solid #d5d8dc; border-left:4px solid #2e86c1; border-radius:4px; padding:8px 12px; margin:8px 0; background:#fbfcfc;'>";
&html = &html | "<div style='font-size:11px; color:#566573;'><b>#" | &rank | "</b> | " | &docName | " | chunk " | &seq | " | <span style='background:#eaf2f8; padding:1px 6px; border-radius:8px;'>distance " | &dist | "</span></div>";
&html = &html | "<div style='font-size:13px; color:#1c2833;'>" | &esc | "</div></div>";
End-While;
&sql.Close();
&html = &html | "</div>";
VA_RAG_WRK.ANSWER.Value = &html;
Points worth noticing:
- PeopleCode never touches a vector. It binds a string and fetches strings and numbers.
VECTOR_EMBEDDING()produces the query vector andVECTOR_DISTANCE()consumes it, entirely inside the SQL. The tools layer is blissfully unaware the VECTOR datatype exists — which matters, because App Designer has no such field type and never will at current releases. FETCH APPROX FIRST kengages the HNSW index for approximate nearest-neighbor search — the difference between a graph walk and a full scan as the corpus grows.- The HTML-escape line is not cosmetic. Parsed PDF text contains
<and&; unescaped, it breaks rendering — and it's an injection surface once other users' documents are searchable. - The rich-text ANSWER field renders the styled cards: rank, source document, chunk number, a distance badge, and the text. It looks like a product, not a debug dump.
The landmines (read this section twice)
These cost me real hours and none are documented in a PeopleSoft context:
1. PUM image PDBs ship without Oracle Text — and DBMS_VECTOR_CHAIN doesn't exist without it. The upgrade log shows Oracle Text UPGRADED for CDB$ROOT, but the application PDB never had the CONTEXT component installed (PeopleSoft dropped its Oracle Text dependency when Search Framework moved to Elasticsearch). DBMS_VECTOR_CHAIN is owned by CTXSYS and is created by the Oracle Text installation itself. If you get ORA-04042 trying to grant it, this is why. Fix: @?/ctx/admin/catctx.sql <pwd> SYSAUX TEMP NOLOCK in the PDB, then utlrp, then GRANT EXECUTE ON CTXSYS.DBMS_VECTOR_CHAIN TO SYSADM;
2. Guaranteed restore points block the COMPATIBLE advance. Vector features need COMPATIBLE ≥ 23.4, and leftover AutoUpgrade restore points throw ORA-38880 at mount — after the spfile already says 23, so the database won't even mount for you to drop them. Escape the chicken-and-egg from the NOMOUNT instance: set COMPATIBLE back to 19 in the spfile, mount, drop the restore points, then advance for real. The advance is one-way — snapshot the VM first.
3. The model load needs tablespace headroom. LOAD_ONNX_MODEL writes the ~130MB model into the loading schema's default tablespace — for a PeopleSoft schema that's PSDEFAULT, which on a PUM image is nearly full. ORA-01652 mid-load means grow PSDEFAULT. Consider a dedicated tablespace for the doc/chunk tables while you're there; document BLOBs and vectors add up.
4. AddAttachment's "system filename" parameter is a prefix, not a name. Preset it and PeopleTools appends the user's filename to it (at least in my tools release). If your reassembly join expects an exact match, you'll chase empty BLOBs — my first PIA upload produced a 0-byte reassembly for exactly this reason. Treat it as a unique prefix (I use the doc ID) and resolve the actual stored name with a LIKE lookup after the upload. Bonus: unique prefixes mean re-uploading the same file never collides on VERSION.
5. UTL_TO_TEXT is text extraction, not OCR. Scanned or image-based PDFs return empty text. Guard for it in the pipeline (my package raises ORA-20001 with a clear message into DOC_STATUS='E') and set expectations: image documents need OCR upstream, which is outside the in-database story.
6. Keep the vector objects out of App Designer. App Designer has no VECTOR field type, and an alter-by-recreate build would silently drop a database-added vector column and its index. My approach: the attachment record (VA_RAG_ATT) and the page objects are proper PeopleTools objects; the doc/chunk tables are database-level DDL managed alongside the ONNX model and grants — which all live outside PeopleTools anyway. (An alternative for PS-metadata purists: text columns in a PS record, vectors in a DB-only shadow table joined on the keys.)
Honest limitations
MiniLM — the class of model practical to load in-database today — sits noticeably below the large API embedding models on nuanced language. It handled HR policy text well in my testing; validate against your corpus. You're spending database CPU on AI workload, which your DBAs will rightly scrutinize. Naive chunking mangles tables inside PDFs (retrieval still worked via surrounding prose, but table-aware chunking is a future refinement). And the adoption gate is real: production PeopleSoft databases are overwhelmingly on 19c today, so treat this as R&D and architecture positioning until your 23ai upgrade lands.
Where this goes next
Three extensions, each a follow-up post:
- Security-trimmed retrieval — the architectural centerpiece, and the real reason to prefer in-database RAG for HR content. Because retrieval is just SQL, the WHERE clause can join to PeopleSoft SJT security tables so chunks a user cannot see are eliminated before vector ranking runs — enforced by the same tables that protect the transactional data. External RAG stacks can only approximate this by replicating security into the index (fragile) or filtering after retrieval (leaky).
- Generation — the page currently returns ranked source chunks; adding an LLM synthesis step (Integration Broker REST to an approved endpoint, top-k chunks as context) turns search results into cited answers.
- REST exposure — a JSON_OBJECT/JSON_ARRAYAGG wrapper already returns the full result set as one CLOB, making an IB Provider service operation a five-line handler with %OperatorId injected server-side — retrieval-as-an-API for other assistants and copilots.
No comments:
Post a Comment