Error when importing Oracle Text: missing pref

DannyS-Oracle

Hi every one,

I am trying to separate my Apex dev and prod environments. Upon importing my exported application and supporting objects file into the new workspace, I got error related to Oracle Text preference:

ORA-29855: error occurred in the execution of ODCIINDEXCREATE routineORA-20000: Oracle Text error:DRG-10700: preference does not exist: MDT_MULTI

Preference MDT_MULTI is created to tell my index that I am using a multi column data store. How to create the DDL statements for preference and lexer? I tried to manually write some statements inside my DDL script, but I still got the same error ...

Gaz in Oz

Creating Preferences

Roger Ford-Oracle

Without a good knowledge of how Apex handles exports and imports, it's a bit tricky to answer this question.

Does Apex create the table and the index?  If so, then if it doesn't create the necessary preferences, I would say that's a bug.

You can export the full index definition from the original database using ctx_report.  For example, if I have an index called 'fooindex' I could run the following in SQL*Plus:

set linesize 255

set pagesize 0

set trimspool on

set long 500000

set longchunksize 500000

spool index_script.sql

select ctx_report.create_index_script('fooindex') from dual;

spool off

That will create a file in the current directory with a full set of statements necessary to recreate the index. Note that the preference in the script won't be called MDT_MULTI, it will probably be called FOOINDEX_DST. That's because the original preference names are not stored in the index - only the preference settings.

You should then be able to export the export .sql file to add the necessary statements to recreate the preference.

Let us know if that doesn't work and we'll try to figure out what else you can do.

Roger