I'm using Oracle 19c.
I have a table with a VARCHAR2(4000) column for which I'm creating a full-text index using Oracle Text.
The SQL creating the index is as follow:
begin
ctx_ddl.create_index_set('ix_iset');
ctx_ddl.create_preference('ix_pref', 'BASIC_LEXER');
ctx_ddl.set_attribute('ix_pref', 'skipjoins', '-');
ctx_ddl.set_attribute ( 'ix_pref', 'index_stems', 'portuguese');
ctx_ddl.set_attribute ( 'ix_pref', 'index_text', 'YES');
ctx_ddl.set_attribute ( 'ix_pref', 'base_letter', 'YES');
end;
CREATE INDEX ix_tx_column ON MYTABLE(tx_column)
INDEXTYPE IS CTXSYS.CTXCAT
PARAMETERS ('index set ix_iset lexer ix_pref');
Everything works very well, but when there is a word with more than 64 chars, an error is raised:
ORA-29855: error occurred in the execution of ODCIINDEXCREATE routine
ORA-20000: Oracle Text Error:
DRG-50857: oracle error in dreii1fsh
ORA-12899: value too large for column "CAD"."DR$IX_TX_COLUMN$I"."DR$TOKEN" (actual: 65, maximum: 64)
ORA-06512: em "CTXSYS.DRUE", line 186
ORA-06512: em "CTXSYS.CATINDEXMETHODS", line 102
29855. 00000 - "error occurred in the execution of ODCIINDEXCREATE routine"
*Cause: Failed to successfully execute the ODCIIndexCreate routine.
*Action: Check to see if the routine has been coded correctly.
I've looked into the documentation and the only reference to this limitation is this item: 2.14 Token Limitations for Oracle Text Indexes
After some research I realized that probably the Oracle-Text internal functions are truncating the words to 64 CHARS, but the token limit is 64 BYTES.
The text word causing this error have Diacritical Characters within its first 64 chars like ç ã õ é and so on... Each char are 2 bytes in size.
I tried to set NLS_LENGTH_SEMANTICS=BYTE with no success.
As long as I researched, there is no way how to change the size of the index token.
Edit:
The result of SELECT * FROM v$nls_parameters is:
