Monday, January 30, 2012

DRG-10700: preference does not exist: CTXSYS.DEFAULT_WORDLIST

After data migration from 9i to 11g, was facing DEFAULT_WORDLIST text index creation issue.  After some research, it appeared that default_worldlist preference needs to be configured on ctxsys same as it was on old database.

Problem:
ERROR at line 1:
ORA-29855: error occurred in the execution of ODCIINDEXCREATE routine
ORA-20000: Oracle Text error:
DRG-10700: preference does not exist: CTXSYS.DEFAULT_WORDLIST
ORA-06512: at "CTXSYS.DRUE", line 160
ORA-06512: at "CTXSYS.TEXTINDEXMETHODS", line 366

SOLUTION:
Login as CTXSYS:
BEGIN
CTX_DDL.create_preference('CTXSYS.DEFAULT_WORDLIST', 'BASIC_WORDLIST');
END;

update dr$parameter
set par_value = 'CTXSYS.EMPTY_STOPLIST'
where par_value = 'CTXSYS.DEFAULT_STOPLIST';

BEGIN
CTX_DDL.create_preference('CTXSYS.DEFAULT_LEXER', 'BASIC_LEXER');
END;

2 comments:

Ricardo Martin said...

We had a similar error when upgrading the ESRI software ArcSDE, from version 9.3 to 10.2.2 on Oracle database 11.2.0.4:

EXT_ERROR1 = ORA-29855: ocorreu um erro na execução da rotina ODCIINDEXCREATE
ORA-20000: Erro do Oracle Text:
DRG-10700: a preferência não existe: CTXSYS.DEFAULT_LEXER
ORA-06512: em "CTXSYS.DRUE", line 160
ORA-06512: em "CTXSYS.TEXTINDEXMETHODS", line 366

To solve we had to apply all the above steps.

Very grateful to make the solution available.

Ricardo Martin said...

We had a similar error when upgrading the ESRI software ArcSDE, from version 9.3 to 10.2.2 on Oracle database 11.2.0.4:

EXT_ERROR1 = ORA-29855: ocorreu um erro na execução da rotina ODCIINDEXCREATE
ORA-20000: Erro do Oracle Text:
DRG-10700: a preferência não existe: CTXSYS.DEFAULT_LEXER
ORA-06512: em "CTXSYS.DRUE", line 160
ORA-06512: em "CTXSYS.TEXTINDEXMETHODS", line 366

To solve we had to apply all the above steps.

Very grateful to make the solution available.