Search code examples
oracle-databasefull-text-search

Cannot create multi-column text index in Oracle


I'm trying to create a multi-column full-text index in Oracle but can't get it working. Here's what I have so far:

CREATE TABLE DOCS (
    DOCS_ID NUMBER(32,0) NOT NULL,
    COLA NVARCHAR2(100),
    COLB NVARCHAR2(100),
    COLC NVARCHAR2(100)
);

CALL ctx_ddl.create_preference('DOCS_MC_DATASTORE', 'MULTI_COLUMN_DATASTORE');
CALL ctx_ddl.set_attribute('DOCS_MC_DATASTORE', 'COLUMNS', 'COLA, COLB, COLC');
CREATE INDEX DOCSINDEX ON DOCS(COLA) INDEXTYPE IS ctxsys.context parameters ('DATASTORE DOCS_MC_DATASTORE sync(on commit)');

This return the following errors:

SQL Error [29855] [99999]: ORA-29855: error occurred in the execution of ODCIINDEXCREATE routine
ORA-20000: Oracle Text error:
DRG-11135: feature not generally available
ORA-06512: at "CTXSYS.DRUE", line 186
ORA-06512: at "CTXSYS.TEXTINDEXMETHODS", line 338

Update

Ignore the multi-column issue, can't even get the index to work:

CREATE INDEX DOCSINDEX ON DOCS(COLA) INDEXTYPE IS ctxsys.context;

Reports the exact same error. This is Oracle Express 21. Do I need to add something to the install?


Solution

  • In case anyone finds this. Can't use text indexing with Unicode NVARCHAR/NCHAR/NCLOB.