Short articles can be embedded whole. Manuals, FAQs, and release notes cannot: an embedding model reads only the first few hundred tokens of a text, and a search should return the passage that answers a question, not a whole manual. Documents need two extra steps before they can be searched by meaning: extracting their text, and splitting it into chunks.
This guide shows how to store PDF, Word, and HTML files in Oracle AI Database 26ai, extract their text with UTL_TO_TEXT, split it with UTL_TO_CHUNKS or VECTOR_CHUNKS, decide on overlap, and fill a chunk table with embeddings in one statement.
Code for This Guide
The examples are files 01 to 06 in the examples/ch09 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.
The eight sample documents are in setup/atlas/documents: three PDF manuals, two Word FAQs, and three HTML release notes. Copy them to the server folder of the directory object ATLAS_FILES, and load the embedding model ALL_MINILM_L12_V2 as shown in how to load an ONNX embedding model into Oracle Database. The schema needs the CTXAPP role, whose document filters do the text extraction.
Example (database in a Docker container named db26ai):
docker cp documents/. db26ai:/opt/oracle/atlas_files/
Store Documents in a BLOB Column
Store documents in the database rather than reading them from files each time. The database then backs them up, secures them, and keeps them consistent with their chunks. APEX stores uploaded files the same way.
Example:
create table atlas_documents (
doc_id number generated always as identity constraint atlas_documents_pk primary key,
file_name varchar2(200) not null constraint atlas_documents_file_uk unique,
doc_type varchar2(20) not null,
title varchar2(200) not null,
product_id number constraint atlas_documents_product_fk references products,
content blob not null,
loaded_on date default sysdate not null
);
insert into atlas_documents (file_name, doc_type, title, product_id, content)
with files (file_name, doc_type, title, product_id) as (
values ('atlas-crm-admin-guide.pdf', 'Manual', 'Atlas CRM 8.4 Administrator Guide', 1),
('atlas-billing-user-guide.pdf', 'Manual', 'Atlas Billing 5.2 User Guide', 2),
('atlas-mobile-guide.pdf', 'Manual', 'Atlas Mobile 3.9 Guide', 3),
('atlas-connect-api-faq.docx', 'FAQ', 'Atlas Connect API FAQ', 5),
('atlas-sync-faq.docx', 'FAQ', 'Atlas Sync FAQ', 6),
('atlas-crm-8.4.1-release-notes.html', 'Release notes', 'Atlas CRM 8.4.1', 1),
('atlas-mobile-3.9.1-release-notes.html', 'Release notes',
'Atlas Mobile 3.9.1', 3),
('atlas-analytics-6.1-release-notes.html', 'Release notes',
'Atlas Analytics 6.1', 4))
select file_name, doc_type, title, product_id, to_blob(bfilename('ATLAS_FILES', file_name))
from files;
commit;
select doc_id, file_name, doc_type, dbms_lob.getlength(content) as bytes
from atlas_documents
order by doc_id;Output:
Table ATLAS_DOCUMENTS created.
8 rows inserted.
Commit complete.
DOC_ID FILE_NAME DOC_TYPE BYTES
_________ _________________________________________ ________________ ________
1 atlas-crm-admin-guide.pdf Manual 49587
2 atlas-billing-user-guide.pdf Manual 44732
3 atlas-mobile-guide.pdf Manual 41506
4 atlas-connect-api-faq.docx FAQ 5911
5 atlas-sync-faq.docx FAQ 5167
6 atlas-crm-8.4.1-release-notes.html Release notes 985
7 atlas-mobile-3.9.1-release-notes.html Release notes 779
8 atlas-analytics-6.1-release-notes.html Release notes 883
8 rows selected.Extract the Text with UTL_TO_TEXT
DBMS_VECTOR_CHAIN.UTL_TO_TEXT extracts the plain text of a document. It recognizes the format by its content, including PDF, Word, Excel, PowerPoint, HTML, and RTF, using the document filters of Oracle Text.
Syntax:
dbms_vector_chain.utl_to_text(data { clob | blob } [, params json]) return clobExample:
select doc_id, doc_type,
length(dbms_vector_chain.utl_to_text(content)) as characters
from atlas_documents
order by doc_id;
select dbms_vector_chain.utl_to_text(content) as text
from atlas_documents
where file_name = 'atlas-mobile-3.9.1-release-notes.html';Output:
DOC_ID DOC_TYPE CHARACTERS
_________ ________________ _____________
1 Manual 6476
2 Manual 4599
3 Manual 2336
4 FAQ 2303
5 FAQ 1690
6 Release notes 739
7 Release notes 539
8 Release notes 634
8 rows selected.
TEXT
__________________________________________________________________________________________________
Atlas Mobile 3.9.1 Release Notes
Atlas Mobile 3.9.1 Release Notes
Released
Atlas Mobile 3.9.1 was released on 2 September 2026 in the App Store and Google Play. Updates can
take up to 48 hours to reach every device.
Fixed
The app no longer crashes on startup when the offline data is larger than 2 GB.
Invoices created offline no longer lose their line discounts when they are uploaded.
Improved
Offline downloads are about 40 percent smaller, because attachments are now downloaded only when
you open them.A 49 KB PDF holds 6,476 characters of text; the rest is fonts, layout, and structure. The HTML release note lost its tags and style sheet, and its title appears twice because the file has it as both its title and its first heading. Extraction keeps the words and paragraph breaks and drops everything else: tables become lines of text, and images disappear unless they contain text the filter can read.
Split Text with UTL_TO_CHUNKS
UTL_TO_CHUNKS splits a text into chunks and returns a collection of JSON objects, one per chunk, with its number, its position (chunk_offset and chunk_length, in characters), and its text (chunk_data).
Syntax:
dbms_vector_chain.utl_to_chunks(data clob, params json) return sys.vector_array_t
{ "by" : "words" | "chars" | "vocabulary",
"max" : maximum_size,
"overlap" : size,
"split" : "sentence" | "recursively" | "newline" | "blankline" | "space" | "none",
"normalize" : "all" | "none" }| Parameter | Sets |
|---|---|
| by | What the size counts: words, characters, or a model's vocabulary tokens |
| max | The largest chunk, at least 10 words |
| split | Where a chunk may end: at a sentence, a line, a space, or anywhere |
| overlap | How much of each chunk's end is repeated at the start of the next |
| normalize | Cleans up extra spaces and line breaks |
This example splits a PDF guide into chunks of at most 100 words that end at sentences.
Example:
select json_value(c.column_value, '$.chunk_id') as id,
json_value(c.column_value, '$.chunk_offset') as offset,
json_value(c.column_value, '$.chunk_length') as length,
regexp_replace(substr(json_value(c.column_value, '$.chunk_data'), 1, 60), '\s+', ' ')
|| '...' as chunk_start
from atlas_documents d,
dbms_vector_chain.utl_to_chunks(
dbms_vector_chain.utl_to_text(d.content),
json('{"by": "words", "max": 100, "split": "sentence", "normalize": "all"}')) c
where d.file_name = 'atlas-mobile-guide.pdf';Output:
ID OFFSET LENGTH CHUNK_START _____ _________ _________ __________________________________________________________________ 1 4 467 Atlas Mobile 3.9 Guide Atlas Mobile 3.9 Guide Getting star... 2 476 486 The app then asks for it every time it opens, and after 5 mi... 3 963 497 If the same record was changed on the web in the meantime, t... 4 1461 417 Choose the notifications under Settings > Notifications. Not... 5 1882 447 Known problems Version 3.9.0 can crash when it starts if th...
Five chunks of about 80 words each: a chunk ends at the last full sentence that fits. A chunk can begin with a heading such as "Known problems", which then travels with the paragraph after it.
Split Text with VECTOR_CHUNKS
VECTOR_CHUNKS is the same chunker as a SQL table function, with its settings written as keywords. It returns rows with CHUNK_OFFSET, CHUNK_LENGTH, and CHUNK_TEXT.
Syntax:
vector_chunks( text
[ by { words | chars | vocabulary vocabulary_name } ]
[ max size ] [ overlap size ]
[ split [ by ] { sentence | recursively | newline | blankline | space | none } ]
[ normalize { all | none } ] )Example:
select c.chunk_offset, c.chunk_length,
regexp_replace(substr(c.chunk_text, 1, 60), '\s+', ' ') || '...' as chunk_start
from (select dbms_vector_chain.utl_to_text(content) as text
from atlas_documents
where file_name = 'atlas-mobile-guide.pdf') d,
vector_chunks(d.text by words max 100 split by sentence normalize all) c;Output:
CHUNK_OFFSET CHUNK_LENGTH CHUNK_START
_______________ _______________ __________________________________________________________________
4 467 Atlas Mobile 3.9 Guide Atlas Mobile 3.9 Guide Getting star...
476 486 The app then asks for it every time it opens, and after 5 mi...
963 497 If the same record was changed on the web in the meantime, t...
1461 417 Choose the notifications under Settings > Notifications. Not...
1882 447 Known problems Version 3.9.0 can crash when it starts if th...The same five chunks. VECTOR_CHUNKS reads better in a query, but its settings must be literals. The JSON of UTL_TO_CHUNKS can be built at run time, for example to try several chunk sizes in one statement.
Decide on Overlap
A sentence that answers a question can be split between two chunks, so that neither holds the whole answer. Overlap repeats the last words of each chunk at the start of the next. This example asks for 20 words of overlap, splitting by sentence and not.
Example:
-- the first two chunks of the guide, with an overlap of 20 words, split by sentence and not
with d as (select dbms_vector_chain.utl_to_text(content) as text
from atlas_documents
where file_name = 'atlas-mobile-guide.pdf')
select 'sentence' as split, c.chunk_offset, c.chunk_length,
regexp_replace(substr(c.chunk_text, 1, 40), '\s+', ' ') || '...' as chunk_start
from d, vector_chunks(d.text by words max 100 overlap 20 split by sentence) c
where c.chunk_offset < 900
union all
select 'none', c.chunk_offset, c.chunk_length,
regexp_replace(substr(c.chunk_text, 1, 40), '\s+', ' ') || '...'
from d, vector_chunks(d.text by words max 100 overlap 20 split by none) c
where c.chunk_offset < 900;Output:
SPLIT CHUNK_OFFSET CHUNK_LENGTH CHUNK_START ___________ _______________ _______________ ______________________________________________ sentence 4 467 Atlas Mobile 3.9 Guide Atlas Mobile 3.9... sentence 476 486 The app then asks for it every time it o... none 4 490 Atlas Mobile 3.9 Guide Atlas Mobile 3.9... none 395 503 with Face ID, Touch ID, or the fingerpri... none 796 555 takes about 200 MB for an average accoun...
Split by sentence, the second chunk starts at 476, after the first one ends: chunks that end at sentences do not overlap, and need not, since no sentence is cut. Split by none, the second chunk starts at 395, inside the first, and both start and end mid-sentence. For prose, split at sentences. Keep overlap for text without clear sentences, such as logs or tables extracted as text.
Fill a Chunk Table in One Statement
A chunk is stored like an article: its text, where it came from, and its embedding. One INSERT extracts, chunks, and embeds the whole library.
Example:
create table doc_chunks (
doc_id number not null constraint doc_chunks_doc_fk
references atlas_documents on delete cascade,
chunk_id number not null,
chunk_offset number not null,
chunk_length number not null,
chunk_text varchar2(4000) not null,
embedding vector(384, float32),
constraint doc_chunks_pk primary key (doc_id, chunk_id)
);
set timing on
insert into doc_chunks (doc_id, chunk_id, chunk_offset, chunk_length, chunk_text, embedding)
select d.doc_id,
row_number() over (partition by d.doc_id order by c.chunk_offset),
c.chunk_offset, c.chunk_length, c.chunk_text,
vector_embedding(all_minilm_l12_v2 using c.chunk_text as data)
from atlas_documents d,
vector_chunks(dbms_vector_chain.utl_to_text(d.content)
by words max 100 split by sentence normalize all) c;
set timing off
commit;
select d.title, count(*) as chunks, round(avg(c.chunk_length)) as avg_characters
from atlas_documents d join doc_chunks c on c.doc_id = d.doc_id
group by d.doc_id, d.title
order by d.doc_id;Output:
Table DOC_CHUNKS created. 44 rows inserted. Elapsed: 00:00:02.178 Commit complete. TITLE CHUNKS AVG_CHARACTERS ____________________________________ _________ _________________ Atlas CRM 8.4 Administrator Guide 14 459 Atlas Billing 5.2 User Guide 10 456 Atlas Mobile 3.9 Guide 5 463 Atlas Connect API FAQ 5 457 Atlas Sync FAQ 4 418 Atlas CRM 8.4.1 2 365 Atlas Mobile 3.9.1 2 265 Atlas Analytics 6.1 2 312 8 rows selected.
Forty-four chunks in about two seconds. ON DELETE CASCADE deletes a document's chunks with the document, and the stored offsets let an application show where in the document a chunk came from.
Conclusion
To prepare documents for vector search in Oracle, store them in a BLOB column, extract their text with DBMS_VECTOR_CHAIN.UTL_TO_TEXT, and split it with UTL_TO_CHUNKS or VECTOR_CHUNKS, by words and at sentence boundaries. Use overlap only when the split can cut sentences. A single INSERT with VECTOR_CHUNKS and VECTOR_EMBEDDING then fills a chunk table, with each chunk's document, position, text, and embedding, ready to search.
