This discussion is archived
5 Replies Latest reply: Nov 28, 2013 4:18 AM by Adam_S RSS

Oracle Text Issue XE

Adam_S Newbie
Currently Being Moderated

I have an XE Database 11.2 on Windows, with Oracle text 11.2.0.2.0, all object verified, which has one imported Apex Application that was exported form Oracle 10 without issue.

I used data pump to populate the underlying application tables, which has also been successful, but need to recreate the text indexes in the target 11.2 instance.

I used ctx_report.create_index_script to create the 4 scripts I needed, no issue.

 

When running the first script on the target instance it all executed OK except for the  "create index" section, and I cant seem to get around the issue.

The part of the script that fails is

 

create index "SCHEMA_OWNER"."A_INDEX1"

on "SCHEMA_OWNER"."A"

("A1")

indextype is ctxsys.context

parameters('

datastore       "A_INDEX1_DST"

filter              "A_INDEX1_FIL"

section group   "A_INDEX1_SGP"

lexer           "A_INDEX1_LEX"

wordlist        "A_INDEX1_WDL"

stoplist        "A_INDEX1_SPL"

storage         "A_INDEX1_STO"

')                                                                           

/

 

Which returns the following errors

 

ERROR at line 1:

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

ORA-20000: Oracle Text error:

DRG-50857: oracle error in drvxtab.create_index_tables

ORA-00905: missing keyword

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

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

 

Can anyone help point me in the right direction, going slightly mad.

  • 1. Re: Oracle Text Issue XE
    clcarter Expert
    Currently Being Moderated

    Might be quite a few differences in 11g, the ora-905 "missing keyword" sounds like something may not be right with the index create parameters.

     

    Not a text expert here, or even a novice. The 11gR2 documentation Indexing with Oracle Text might help, or someone in the Text forum can probably offer much better help.

  • 2. Re: Oracle Text Issue XE
    Adam_S Newbie
    Currently Being Moderated

    Thanks clcarter, it's not my core area either. never really used XE, always Enterprise and Standard. I will post on the Text Forum and see what comes up.  I'm worried that I have hit some limitation on XE.

     

    Thanks for replying

     


  • 3. Re: Oracle Text Issue XE
    kevinUCB Explorer
    Currently Being Moderated

    I have seen that type of error mainly when there's an error in the storage clause (A_INDEX1_STO in your case), when the storage clause refers to things that don't exist in the new database instance.

    Can you post the complete index script, with all preferences?

  • 4. Re: Oracle Text Issue XE
    Adam_S Newbie
    Currently Being Moderated

    Thanks for the help, it's much appreciated.

     

    I have truncated the stoplist, but otherwise here is the complete script as created by ctx_report.create_index_script.

     

    = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = =

     

    begin                                                                          
      ctx_ddl.create_preference('"A_INDEX1_DST"','DIRECT_DATASTORE');     
    end;                                                                           
    /                                                                              
                                                                                   
    begin                                                                          
      ctx_ddl.create_preference('"A_INDEX1_FIL"','AUTO_FILTER');          
    end;                                                                           
    /                                                                              
                                                                                   
    begin                                                                          
      ctx_ddl.create_section_group('"A_INDEX1_SGP"','HTML_SECTION_GROUP');
    end;                                                                           
    /                                                                              
                                                                                   
    begin                                                                          
      ctx_ddl.create_preference('"A_INDEX1_LEX"','BASIC_LEXER');          
    end;                                                                           
    /                                                                              
                                                                                   
    begin                                                                          
      ctx_ddl.create_preference('"A_INDEX1_WDL"','BASIC_WORDLIST');       
      ctx_ddl.set_attribute('"A_INDEX1_WDL"','STEMMER','ENGLISH');        
      ctx_ddl.set_attribute('"A_INDEX1_WDL"','FUZZY_MATCH','GENERIC');    
    end;                                                                           
    /                                                                              
                                                                                   
    begin                                                                          
      ctx_ddl.create_stoplist('"A_INDEX1_SPL"','BASIC_STOPLIST');         
      ctx_ddl.add_stopword('"A_INDEX1_SPL"','Mr');                        
      ctx_ddl.add_stopword('"A_INDEX1_SPL"','Mrs');                       
      ctx_ddl.add_stopword('"A_INDEX1_SPL"','Ms');

      ....

      ....

      ....

      ctx_ddl.add_stopword('"A_INDEX1_SPL"','might');                     
      ctx_ddl.add_stopword('"A_INDEX1_SPL"','yours');                     
    end;                                                                           
    /                                                                              
                                                                                   
    begin                                                                          
      ctx_ddl.create_preference('"A_INDEX1_STO"','BASIC_STORAGE');        
      ctx_ddl.set_attribute('"A_INDEX1_STO"','R_TABLE_CLAUSE','lob (data) store as (cache)');                                                             
      ctx_ddl.set_attribute('"A_INDEX1_STO"','I_INDEX_CLAUSE','compress 2')
    ;                                                                              
    end;                                                                           
    /                                                                              
                                                                                   
                                                                                   
    begin                                                                          
      ctx_output.start_log('A_INDEX1_LOG');                               
    end;                                                                           
    /                                                                              
                                                                                   
    create index "SCHEMA_OWNER"."A_INDEX1"                                 
      on "SCHEMA_OWNER"."A"                                                
          ("A1")                                                            
      indextype is ctxsys.context                                                  
      parameters('                                                                 
        datastore       "A_INDEX1_DST"                                    
        filter          "A_INDEX1_FIL"                                    
        section group   "A_INDEX1_SGP"                                    
        lexer           "A_INDEX1_LEX"                                    
        wordlist        "A_INDEX1_WDL"                                    
        stoplist        "A_INDEX1_SPL"                                    
        storage         "A_INDEX1_STO"                                    
      ')                                                                           
    /                                                                              
                                                                                   
    begin                                                                          
      ctx_output.end_log;                                                          
    end;                                                                           
    /                                                                             

     

    = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = =

     

    Hope this helps

  • 5. Re: Oracle Text Issue XE
    Adam_S Newbie
    Currently Being Moderated

    I have now managed to fix this, and kevinUBC was right, the error was in the storage clause.  The problem was caused by the line break in 3rd line of the storage clause at the semi colon ^^.

     

     

    begin                                                                   
      ctx_ddl.create_preference('"A_INDEX1_STO"','BASIC_STORAGE'); 
      ctx_ddl.set_attribute('"A_INDEX1_STO"','R_TABLE_CLAUSE','lob (data) store as (cache)');                                                      

      ctx_ddl.set_attribute('"A_INDEX1_STO"','I_INDEX_CLAUSE','compress 2')

    ;                                                                       

    end;

     

    I dropped the storage clause with ctx_ddl.drop_preference and recreated it with the semi colon in the correct place, the index then created without issue

     

    Thanks everyone

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points