adm_ai_rag_admin_api

Administration of RAG collections: the typed server side of the collection manager.

Everything here is for administrators. Every method starts with adm_access_control_api.assert_admin, so any other caller gets adm_error.e_no_permission before anything is read. Create, update and delete delegate to adm_ai_rag_api, which applies its own validation and its own admin check again.

No method commits. The one thing to know is start_indexing: starting a scheduler job is an implicit commit, so it creates the job in an autonomous transaction and leaves the transaction of the caller alone.

The read methods return JSON text, so that one call gives a screen everything it needs:

select json_value(adm_ai_rag_admin_api.list_collections, '$.collections[0].collection_name')
  from dual;

Functions and Procedures

list_collections

Lists the collections with their indexing status.

Signature:

function list_collections (
  p_search in varchar2 default null
) return clob;

Parameters:

Name	Direction	Type	Description
p_search	in	varchar2 default null	Only collections whose name contains this text (case-insensitive); null lists all

Returns: clob - JSON {collections: [...]}. Each entry has the collection id, name and description, status (ERROR, PROCESSING, PENDING, INCOMPLETE, EMPTY or READY), indexing_state (IDLE, SCHEDULED or RUNNING), the file, chunk and job counts, the vector store and the embedding provider and model.

---

get_collection

Returns one collection: the stored configuration split into the sections of a form, the fields that cannot change after creation, the stage backlog and the last background run.

Signature:

function get_collection (
  p_collection_id in number
) return clob;

Parameters:

Name	Direction	Type	Description
p_collection_id	in	number	ID of the collection

Returns: clob - JSON with collection, config, sections, locked, backlog and last_run

---

list_folders

Lists the folders directly below one folder, for the folder picker of the collection form. Trashed folders are not listed. The children are read on demand, one level at a time, so the call stays cheap for a large folder tree.

Signature:

function list_folders (
  p_parent_path in varchar2 default null,
  p_filter in varchar2 default null
) return clob;

Parameters:

Name	Direction	Type	Description
p_parent_path	in	varchar2 default null	Path of the folder whose children are listed; null or / is the root
p_filter	in	varchar2 default null	Only children whose name contains this text (case-insensitive); null lists all. Use it to reach a folder in a parent with more than 500 children.

Returns: clob - JSON {parent_path, folders: [{path, name, has_children}], truncated}; truncated is true when more than 500 children match and only the first 500 (by path) are listed

---

get_config_options

Returns what a form needs to offer choices: providers and models from UC AI, embedding presets, the defaults the product applies, limits and the allowed values of enumerated fields.

Signature:

function get_config_options return clob;

Returns: clob - JSON with providers, models, embedding_presets, defaults, limits and enums

---

validate_config

Checks a configuration with the same rules create and update apply, and reports every broken rule, not only the first.

Signature:

function validate_config (
  p_config_json in clob
) return clob;

Parameters:

Name	Direction	Type	Description
p_config_json	in	clob	The configuration as JSON text

Returns: clob - JSON {valid: true|false, errors: [{path, message}]}; path is the configuration key the message is about, or null for the configuration as a whole

---

create_collection

Creates a collection.

Signature:

function create_collection (
  p_collection_name in varchar2,
  p_description in varchar2 default null,
  p_config_json in clob
) return number;

Parameters:

Name	Direction	Type	Description
p_collection_name	in	varchar2	Name; lowercase letters, digits, underscore and hyphen
p_description	in	varchar2 default null	Description
p_config_json	in	clob	The configuration as JSON text

Returns: number - ID of the new collection

---

update_collection

Updates a collection. The embedding provider, model and dimensions and the vector store cannot change, because the stored vectors were built with them.

Signature:

procedure update_collection (
  p_collection_id in number,
  p_collection_name in varchar2 default null,
  p_description in varchar2 default null,
  p_config_json in clob default null,
  p_clear_description in boolean default false
);

Parameters:

Name	Direction	Type	Description
p_collection_id	in	number	ID of the collection
p_collection_name	in	varchar2 default null	New name; null keeps it
p_description	in	varchar2 default null	New description; null keeps it
p_config_json	in	clob default null	New configuration; null keeps it
p_clear_description	in	boolean default false	True sets the description to null and ignores p_description

---

delete_collection

Deletes a collection with its vector store.

Signature:

procedure delete_collection (
  p_collection_id in number
);

Parameters:

Name	Direction	Type	Description
p_collection_id	in	number	ID of the collection

---

indexing_state

Whether a background indexing run exists for a collection.

Signature:

function indexing_state (
  p_collection_id in number
) return varchar2;

Parameters:

Name	Direction	Type	Description
p_collection_id	in	number	ID of the collection

Returns: varchar2 - RUNNING, SCHEDULED (created, not yet started) or IDLE

---

start_indexing

Starts indexing of a collection as a background job and returns at once. The job synchronizes the files, extracts text, chunks and embeds, with the ADM context of the calling administrator.

Signature:

function start_indexing (
  p_collection_id in number,
  p_retry_failed in boolean default false
) return varchar2;

Parameters:

Name	Direction	Type	Description
p_collection_id	in	number	ID of the collection
p_retry_failed	in	boolean default false	Reset the failed-attempt counters of the files and the FAILED queue entries first, so that they are tried again

Returns: varchar2 - Name of the scheduler job

---

reset_failed

Makes the failed work of a collection eligible again: clears the attempt counters and errors of its files and sets its FAILED queue entries back to PENDING.

Signature:

function reset_failed (
  p_collection_id in number
) return number;

Parameters:

Name	Direction	Type	Description
p_collection_id	in	number	ID of the collection

Returns: number - Number of files and queue entries that were reset

---

rebuild_collection

Throws away what indexing built for a collection so that the next run builds it again with the current configuration. Use it after a change that only applies to files that are not chunked yet, such as the chunk sizes.

Deletes the chunks of every file of the collection, their vectors (native Oracle store and Qdrant points), the queue entries of its files, and the attempt counters and errors of its files. Keeps the collection, its configuration, the files that belong to it and the extracted text of the document versions, which other collections share. It does not index: start_indexing does that. Until indexing has ended the collection finds fewer passages or none.

Every file is chunked and embedded again, which calls the embedding model for every chunk. Text is read again only for a file that has none, and for text from the built-in filter that no other collection has chunked, when this collection reads text with an LLM or OCR.

Writes an audit row. Does not commit.

Signature:

procedure rebuild_collection (
  p_collection_id in number
);

Parameters:

Name	Direction	Type	Description
p_collection_id	in	number	ID of the collection

---

list_files

Lists the files of a collection, one page at a time.

Signature:

function list_files (
  p_collection_id in number,
  p_status in varchar2 default null,
  p_search in varchar2 default null,
  p_offset in number default 0,
  p_limit in number default null
) return clob;

Parameters:

Name	Direction	Type	Description
p_collection_id	in	number	ID of the collection
p_status	in	varchar2 default null	Only files with this status (ERROR, PROCESSING, PENDING, INCOMPLETE, EMPTY, NOT_INDEXED, INDEXED or INACTIVE); null lists every file that is still part of the collection
p_search	in	varchar2 default null	Only files whose path contains this text (case-insensitive)
p_offset	in	number default 0	Rows to skip
p_limit	in	number default null	Page size; defaults to c_default_page_size, at most c_max_page_size

Returns: clob - JSON {total, offset, limit, files: [...]}

---

get_file

Returns one file of a collection: its document, status, attempts and errors, queue entries and an excerpt of the extracted text.

Signature:

function get_file (
  p_file_id in number,
  p_excerpt_length in number default 2000
) return clob;

Parameters:

Name	Direction	Type	Description
p_file_id	in	number	ID of the collection file
p_excerpt_length	in	number default 2000	Characters of extracted text to return (at most 8000)

Returns: clob - JSON

---

list_chunks

Lists the chunks of a file in text order, with a short preview of each.

Signature:

function list_chunks (
  p_file_id in number,
  p_offset in number default 0,
  p_limit in number default null
) return clob;

Parameters:

Name	Direction	Type	Description
p_file_id	in	number	ID of the collection file
p_offset	in	number default 0	Rows to skip
p_limit	in	number default null	Page size; defaults to c_default_page_size, at most c_max_page_size

Returns: clob - JSON {total, offset, limit, chunks: [...]}

---

get_chunk

Returns one chunk with its full text, its position in the extracted text and its neighbours.

Signature:

function get_chunk (
  p_chunk_id in number
) return clob;

Parameters:

Name	Direction	Type	Description
p_chunk_id	in	number	ID of the chunk

Returns: clob - JSON

---

test_search

Runs a test retrieval against a collection and explains it: the original and the rewritten query, the effective parameters, the time of each stage and the matched chunks with their score, and in hybrid mode their vector and keyword rank. Optionally generates an answer too, which also returns the prompts and the sources and writes a debug log entry.

Needs the embedding provider (and the rewrite and answer providers, if used) to be reachable.

Signature:

function test_search (
  p_collection_id in number,
  p_query in varchar2,
  p_limit in number default null,
  p_score_threshold in number default null,
  p_rewrite_query in boolean default null,
  p_rewrite_provider in varchar2 default null,
  p_rewrite_model in varchar2 default null,
  p_document_id in number default null,
  p_generate_answer in boolean default false,
  p_max_sources in number default null,
  p_surrounding_chunks in number default null,
  p_system_prompt in clob default null,
  p_answer_provider in varchar2 default null,
  p_answer_model in varchar2 default null
) return clob;

Parameters:

Name	Direction	Type	Description
p_collection_id	in	number	ID of the collection
p_query	in	varchar2	The question
p_limit	in	number default null	Maximum number of chunks; null uses the collection default
p_score_threshold	in	number default null	Minimum score; null uses the collection default
p_rewrite_query	in	boolean default null	Rewrite the query with an LLM; null uses the collection setting
p_rewrite_provider	in	varchar2 default null	Override provider for the rewrite
p_rewrite_model	in	varchar2 default null	Override model for the rewrite
p_document_id	in	number default null	Restrict the search to one document (needs the Oracle vector store)
p_generate_answer	in	boolean default false	Also generate an answer
p_max_sources	in	number default null	Maximum number of source documents for the answer
p_surrounding_chunks	in	number default null	Neighbouring chunks per side for the answer sources
p_system_prompt	in	clob default null	System prompt for the answer; null uses the collection setting
p_answer_provider	in	varchar2 default null	Override provider for the answer
p_answer_model	in	varchar2 default null	Override model for the answer

Returns: clob - JSON with query, rewritten_query, parameters, timings, chunks and, with an answer, answer, system_prompt, user_prompt, sources and debug_log_id

---