DRG-10700: Preference does not exist error while creating the index

mNem

I was getting the following error while trying to create a domain index in a database while it worked fine in another database. I did try recreating the index after dropping it and the issue still persisted until the DBA "reinstalled"  the Oracle Text component (don't know what that means though).

CREATE INDEX whs_base_ft_ix ON whs_base_t(title)

INDEXTYPE IS CTXSYS.CONTEXT PARAMETERS

('storage whs_base_storage datastore whs_base_datastore section group whs_base_section_group sync (on commit)')

Error report -

ORA-29855: error occurred in the execution of ODCIINDEXCREATE routine

ORA-20000: Oracle Text error:

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

ORA-06512: at "CTXSYS.DRUE", line 183

ORA-06512: at "CTXSYS.TEXTINDEXMETHODS", line 320

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.

Version for both DBs are: "Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production Version 18.8.0.0.0"

While searching, came across another post which had a similar error but for another preference. What I am interested in is, how do I query to find out the collection of these preferences from CTXSYS as a currently logged user who is not a dba.

Roger Ford-Oracle

That's an odd one. Looks like one of the default preferences has been set to a preference which no longer exists.

It doesn't tell us which preference (eg. lexer, datastore) is the problem, but you should be able to find out with:

SELECT par_name, par_value FROM ctx_parameters WHERE par_value = 'CTXSYS.NONE_DATATYPE';

You can then fix whichever default is broken using CTXSYS.CTX_ADM.SET_PARAMETER.  If you can post the output of the previous query (assuming there is some) then I can advise the exact call necessary.

Roger

Roger Ford-Oracle

That's an odd one. Looks like one of the default preferences has been set to a preference which no longer exists.

It doesn't tell us which preference (eg. lexer, datastore) is the problem, but you should be able to find out with:

SELECT par_name, par_value FROM ctx_parameters WHERE par_value = 'CTXSYS.NONE_DATATYPE';

You can then fix whichever default is broken using CTXSYS.CTX_ADM.SET_PARAMETER.  If you can post the output of the previous query (assuming there is some) then I can advise the exact call necessary.

Roger

mNem

The query returned no rows as I highlighted the issue disappeared after whatever the DBA did with reinstall.

This is what I get for SELECT par_name, par_value FROM ctx_parameters though.

PAR_NAME                                PAR_VALUE                             
---------------------------------------- ----------------------------------------
AUTO_OPTIMIZE                            ENABLE                                 
AUTO_OPTIMIZE_LOGFILE                                                           
CTX_DOC_KEY_TYPE                        PRIMARY_KEY                           
DEFAULT_CLASSIFIER                      CTXSYS.DEFAULT_CLASSIFIER             
DEFAULT_CLUSTERING                      CTXSYS.DEFAULT_CLUSTERING             
DEFAULT_CTXCAT_INDEX_SET                CTXSYS.EMPTY_INDEX_SET                 
DEFAULT_CTXCAT_LEXER                    CTXSYS.DEFAULT_LEXER                   
DEFAULT_CTXCAT_STOPLIST                  CTXSYS.DEFAULT_STOPLIST               
DEFAULT_CTXCAT_STORAGE                  CTXSYS.DEFAULT_STORAGE                 
DEFAULT_CTXCAT_WORDLIST                  CTXSYS.DEFAULT_WORDLIST               
DEFAULT_CTXRULE_LEXER                    CTXSYS.DEFAULT_LEXER                   
DEFAULT_CTXRULE_STOPLIST                CTXSYS.DEFAULT_STOPLIST               
DEFAULT_CTXRULE_STORAGE                  CTXSYS.DEFAULT_STORAGE                 
DEFAULT_CTXRULE_WORDLIST                CTXSYS.DEFAULT_WORDLIST               
DEFAULT_CTXXPATH_STORAGE                CTXSYS.DEFAULT_STORAGE                 
DEFAULT_DATASTORE                        CTXSYS.DEFAULT_DATASTORE               
DEFAULT_EXTRACT_LEXER                    CTXSYS.DEFAULT_EXTRACT_LEXER           
DEFAULT_FILTER_BINARY                    CTXSYS.AUTO_FILTER                     
DEFAULT_FILTER_FILE                      CTXSYS.AUTO_FILTER                     
DEFAULT_FILTER_TEXT                      CTXSYS.NULL_FILTER                     
DEFAULT_INDEX_MEMORY                    67108864                               
DEFAULT_LEXER                            CTXSYS.DEFAULT_LEXER                   
DEFAULT_SECTION_HTML                    CTXSYS.HTML_SECTION_GROUP             
DEFAULT_SECTION_TEXT                    CTXSYS.NULL_SECTION_GROUP             
DEFAULT_SECTION_XML                      CTXSYS.PATH_SECTION_GROUP             
DEFAULT_SENT_CLASSIFIER                  CTXSYS.DEFAULT_SENT_CLASSIFIER         
DEFAULT_STOPLIST                        CTXSYS.DEFAULT_STOPLIST               
DEFAULT_STORAGE                          CTXSYS.DEFAULT_STORAGE                 
DEFAULT_WORDLIST                        CTXSYS.DEFAULT_WORDLIST               
FUNCTIONAL_CACHE_SIZE                    20971520                               
LOG_DIRECTORY                                                                   
MAIL_FILTER_CONFIG_FILE                  drmailfl.txt                           
MAX_INDEX_MEMORY                        274877906944                          

33 rows selected.

Roger Ford-Oracle

Sorry, I didn't realise the issue had gone away (obviously didn't read carefully enough). So is your question now answered, or do you need further info?

mNem

Yes, it is answered. I thought of sharing the query results (entire result set) in the hope that if you spot something that needs fixing ...

Thanks.

Roger Ford-Oracle

Those results look normal and fine.