ADD_MDATA

Use this procedure to change the metadata of a document that has been specified as an MDATA section.

After this call, MDATA queries involving the named MDATA value will find documents with the given MDATA value.

There are two versions of CTX_DDL.ADD_MDATA: one for adding a single metadata value to a single rowid, and another for handing multiple values, multiple rowids, or both.

CTX_DDL.ADD_MDATA is transactional; it takes effect immediately in the calling session, can be seen only in the calling session, can be reversed with a ROLLBACK command, and must be committed to take permanent effect.

Syntax

This is the syntax for adding a single value to a single rowid:

CTX_DDL.ADD_MDATA(
     idx_name           IN VARCHAR2,
     section_name       IN VARCHAR2,
     mdata_value        IN VARCHAR2,
     mdata_rowid        IN VARCHAR2,
     [part_name]        IN VARCHAR2]
);

idx_name

Name of the text index that contains the named rowid.

section_name

Name of the MDATA section.

mdata_value

The metadata value to add to the document.

mdata_rowid

The rowid to which to add the metadata value.

[part_name]

Name of the index partition, if any. Must be provided for local partitioned indexes and must be NULL for global, nonpartitioned indexes.

This is the syntax for handling multiple values, multiple rowids, or both. This version is more efficient for large numbers of new values or rowids.

CTX_DDL.ADD_MDATA(
     idx_name           IN VARCHAR2,
     section_name       IN VARCHAR2,
     mdata_values       SYS.ODCIVARCHAR2LIST,
     mdata_rowids       SYS.ODCIRIDLIST,
     [part_name]        IN VARCHAR2]
);

idx_name

Name of the text index that contains the named rowids.

section_name

Name of the MDATA section.

mdata_values

List of metadata values. If a metadata value contains a comma, the comma must be escaped with a backslash.

mdata_rowids

The rowids to which to add the metadata values.

[part_name]

Name of the index partition, if any. Must be provided for local partitioned indexes and must be NULL for global, nonpartitioned indexes.

Restrictions and Limitations

Use CTX_DDL.REMOVE_MDATA to remove metadata values from already-indexed documents. Only the owner of the index is allowed to call ADD_MDATA and REMOVE_MDATA.
The CTX_DDL.ADD_MDATA queries can be slower when using a PDB lockdown profile.
If a rowid is not yet indexed, CTX_DDL.ADD_MDATA completes without error, but an error is logged in CTX_USER_INDEX_ERRORS.
These updates are updates directly on the index itself, not on the actual contents stored in the base table. Therefore, they will not exist when the Text index is rebuilt.
CTX_DDL.ADD_MDATA is not supported for documents with Oracle Text search index as stage_itab is ON by default for Oracle Text search index.

Examples

This example updates a single value:

select rowid from mytab where contains(text, 'MDATA(sec, value')>0;
No rows returned
exec ctx_ddl.add_mdata('my_index', 'sec', 'value', 'ABC');
select rowid from mytab where contains(text, 'MDATA(sec, value')>0;
ROWID
-----
ABC

This example updates multiple values:

begin
ctx_ddl.add_mdata('my_index', 'sec',
     sys.odcivarchar2list('value1','value2','value3'),
     sys.odciridlist('ABC','DEF'));
end;

This is equivalent to:

begin
ctx_ddl.add_mdata('my_index', 'sec', 'value1', 'ABC');
ctx_ddl.add_mdata('my_index', 'sec', 'value1', 'DEF');
ctx_ddl.add_mdata('my_index', 'sec', 'value2', 'ABC');
ctx_ddl.add_mdata('my_index', 'sec', 'value2', 'DEF');
ctx_ddl.add_mdata('my_index', 'sec', 'value3', 'ABC');
ctx_ddl.add_mdata('my_index', 'sec', 'value3', 'DEF');
end;

Related Topics