Oracle | DBMS

Oracle DBMS_SEARCH Version 26ai

General Information

Library Note

Morgan's Library Page Header

The best Oracle News for FY2026

Oracle Database 26ai will be available on generic Linux platforms in January and soon on AIX and Windows

Purpose Subprograms for creating, managing, dropping and index searches for index-range ubiquitous document searches

AUTHID CURRENT_USER

Constants

Name	Data Type	Value
Type Constants
JSON_TYPE	VARCHAR2(4);	'JSON'
OSON_TYPE	VARCHAR2(4);	'OSON'
Score Constants
MAX_SCORE	VARCHAR2(3);	'MAX'
MI_SCORE	VARCHAR2(3);	'MIN'
AVG_SCORE	VARCHAR2(3);	'AVG'
Mode Constants
CHUNK_MODE	VARCHAR2(10);	'CHUNK'
DOCUMENT	VARCHAR2(17);	'DOCUMENT'
INDEX_MODE	VARCHAR2(5);	'INDEX'
QUERY_MODE	VARCHAR2(5);	'QUERY'

Data Types TYPE message_list IS TABLE OF VARCHAR2(4000);

Dependencies

ALL_CONSTRAINTS	DBS_STANDARD
ALL_CONS_COLUMNS	DBMS_UTILITY
ALL_DEPENDENCIES	DRIDML
ALL_EXTERNAL_TABLES	DRIG
ALL_INDEXES	DRIUTL
ALL_JSON_COLUMNS	DRUE
ALL_JSON_DUALITY_VIEW_TABS	DRVUTL
ALL_JSON_DUALITY_VIEW_TAB_COLS	DRVXMD
ALL_TABLES	DRVXTAB
ALL_TAB_COLS	DR_DEF
ALL_VIEWS	DUAL
CTX_DDL	JSON_ELEMENT_T
CTZ_QUERY	JSON_KEY_LIST
DBMS_AQ	JSON_OBJECT_T
DBMS_ASSERT	PLITBLM
DBMS_SEARCH_DEF	UTL_RAW
DBMS_SQL

Documented Partial: Oracle Text Reference

Exceptions

Error Code	Reason
DRG=10502	index "<schema_name>"."<index_name>" does not exist

First Available 23ai

Security Model Owned by CTXSYS with EXECUTE granted to PUBLIC

Source {ORACLE_HOME}/ctx/admin/dbmssearch.pkh
{ORACLE_HOME}/ctx/admin/dbmssearch.pkb

Subprograms

ADD_SOURCE	FIND	QUEUEMESSAGE
CREATE_INDEX	GET_DOCUMENT	QUEUEQUERYMESSAGE
DO_SYNC_INDEX_DML	GET_DOCUMENT_OSON	REMOVE_SOURCE
DROP_INDEX	GET_MESSAGE_LIST	SYNC_INDEX

ADD_SOURCE (new 23ai)

Add a table for view to a ubiquitous search index dbms_search.add_source( index_name IN VARCHAR2, source_name IN VARCHAR2);

exec ctxsys.dbms_search.add_source('UWUBIQIDX', 'UWSRCTAB');

           *

      ORA-20000: Oracle Text error:

      DRG-10502: index "SYS"."UWUBIQIDX" does not exist

CREATE_INDEX (new 23ai)

Create a ubiquitous search index dbms_search.create_index( index_name IN VARCHAR2, tablespace_name IN VARCHAR2 DEFAULT NULL, datatype IN VARCHAR2 DEFAULT NULL);

exec ctxsys.dbms_search.create_index('UWUBIQIDX', 'UWDATA', 'OSON');

           *

      ORA-20000: Oracle Text error:

      DRG-50857:oracle error in dbms_search_def.CreateSequence

DO SYNC_INDEX_DML (new 23ai)

Undocumented dbms_search.do_sync_index_dml( index_name IN VARCHAR2, partname IN VARCHAR2, source_name IN VARCHAR2, key_str IN VARCHAR2, c_source_owner IN VARCHAR2, c_source_name IN VARCHAR2, src_type IN VARCHAR2, message_json IN VARCHAR2);

TBD

DROP INDEX (new 23ai)

Drop a ubiquitous search index dbms_search.drop_index(index_name IN VARCHAR2);

exec ctxsys.dbms_search.drop_index('UWUBIQIDX');

           *

      ORA-20000: Oracle Text error:

      DRG-10502: index "SYS"."UWUBIQIDX" does not exist

FIND (new 23ai)

Returns a hit-list, and facets an aggregation of JSON documents, based on the search condition

Overload 1 dbms_search.find( index_name IN VARCHAR2, search_qbe IN JSON) RETURN JSON;

SELECT dbms_search.find('ADMIN.IDX_PROPERTY_RCPOST_FULL_PURE_COLUMN',

        JSON('{"$query": 

                { "$and" : [

                 
      { "ADMIN.PROPERTY_RCPOST_FULL_PURE_COLUMN.SCOPELEVELHMA" :

                    { "$contains" : "%ABC%" }

                  },        ]

                },

               "$search" : { "start" : 1, "end" : 10},

        "$facet" : [{ "$uniqueCount" : "ADMIN.PROPERTY_RCPOST_FULL_PURE_COLUMN.BIGESTLEVELHMA"

                   }]

              }')

            );

Overload 2 dbms_search.find( index_name IN VARCHAR2, search_qbe IN BLOB) RETURN BLOB;

TBD

GET_DOCUMENT (new 23ai)

Returns a virtual indexed JSON document corresponding to the source metadata

Overload 1 dbms_search.get_document( index_name IN VARCHAR2, document_metadata IN JSON result_mode IN VARCHAR2 DEFAULT QUERY_MODE) RETURN JSON;

TBD

Overload 2 dbms_search.get_document( index_name IN VARCHAR2, document_metadata IN BLOB, result_mode IN VARCHAR2 DEFAULT QUERY_MODE) RETURN BLOB;

TBD

Overload 3 dbms_search.get_document( c_source_powner IN VARCHAR2, c_source_name IN VARCHAR2, table_rowid IN ROWID) RETURN JSON;

TBD

GET_DOCUMENT_OSON (new 23ai)

Undocumented dbms_search.get_document_oson( c_source_owner IN VARCHAR2, c_source_name IN VARCHAR2, table_rowid IN ROWID) RETURN BLOB;

TBD

GET_MESSAGE_LIST (new 23ai)

Undocumented dbms_search.get_message_list( sql_stmt IN VARCHAR2, rid IN ROWID, msg_list OUT message_list);

TBD

QUEUEMESSAGE (new 23ai)

Undocumented dbms_search.queueMessage( queue_name IN VARCHAR2, message IN RAW);

TBD

QUEUEQUERYMESSAGE (new 23ai)

Undocumented dbms_search.queueQueryMessage( query_text IN VARCHAR2, rid IN ROWID, queue_name IN VARCHAR2);

TBD

REMOVE_SOURCE (new 23ai)

Remove a table for view to a ubiquitous search index dbms_search.remove_source( index_name IN VARCHAR2, source_name IN VARCHAR2);

exec ctxsys.dbms_search.remove_source('UWUBIQIDX', 'UWSRCTAB');

           *

      ORA-20000: Oracle Text error:

      DRG-10502: index "SYS"."UWUBIQIDX" does not exist

SYNC_INDEX (new 23ai)

Undocumented dbms_search.sync_index( index_name IN VARCHAR2, source_name IN VARCHAR2, source_id IN NUMBER);

TBD