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 is now availble. If you haven't you downloaded already: Why?

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