InterMedia 9i: ORA-03113: end-of-file on communication channel

From: Giovanni Azua <bravegag_at_hotmail.com>
Date: Mon, 21 Oct 2002 13:56:00 +0200
Message-ID: <ap0q1f$ql79u$1@ID-114658.news.dfncis.de>

Hello all,

I am using Oracle 9.0.1 Text (InterMedia), created several text indexes in a multi-language table like this:

begin

   ctx_ddl.create_preference('my_wordlist', 'basic_wordlist');
   ctx_ddl.set_attribute('my_wordlist'    , 'stemmer'        , 'auto');
   ctx_ddl.set_attribute('my_wordlist'    , 'prefix_index'     , 'yes');
   ctx_ddl.set_attribute('my_wordlist'    , 'prefix_min_length', 3);
   ctx_ddl.set_attribute('my_wordlist'    , 'prefix_max_length', 6);
   ctx_ddl.set_attribute('my_wordlist'    , 'substring_index'  , 'yes');
   ctx_ddl.set_attribute('my_wordlist'    , 'fuzzy_match'      , 'auto');
   ctx_ddl.set_attribute('my_wordlist'    , 'fuzzy_score'      , 0);
   ctx_ddl.set_attribute('my_wordlist'    , 'fuzzy_numresults' , 5000);

   ctx_ddl.create_preference('english_lexer' , 'basic_lexer');
   ctx_ddl.set_attribute('english_lexer'     , 'mixed_case'       , 'no');
   ctx_ddl.set_attribute('english_lexer'     , 'index_text'       , 'yes');
-- ctx_ddl.set_attribute('english_lexer'     , 'index_stems'      , 1); --
english
   ctx_ddl.set_attribute('english_lexer'     , 'theme_language'   ,

'english');

   ctx_ddl.create_preference('german_lexer'  , 'basic_lexer');
   ctx_ddl.set_attribute('german_lexer'      , 'mixed_case'        , 'no');
   ctx_ddl.set_attribute('german_lexer'      , 'index_text'        , 'yes');
-- ctx_ddl.set_attribute('german_lexer'      , 'index_stems'       , 5); --
german
   ctx_ddl.set_attribute('german_lexer'      , 'alternate_spelling',


'german');

   ctx_ddl.set_attribute('german_lexer'      , 'composite'         ,

'german');

   ctx_ddl.create_preference('french_lexer'  , 'basic_lexer');
   ctx_ddl.set_attribute('french_lexer'      , 'mixed_case'        , 'no');
   ctx_ddl.set_attribute('french_lexer'      , 'index_text'        , 'yes');
-- ctx_ddl.set_attribute('french_lexer'      , 'index_stems'       , 4); --

french

   ctx_ddl.create_preference('italian_lexer' , 'basic_lexer');
   ctx_ddl.set_attribute('italian_lexer'     , 'mixed_case'        , 'no');
   ctx_ddl.set_attribute('italian_lexer'     , 'index_text'        , 'yes');
-- ctx_ddl.set_attribute('italian_lexer'     , 'index_stems'       , 6); --

italian

   ctx_ddl.create_preference('spanish_lexer' , 'basic_lexer');
   ctx_ddl.set_attribute('spanish_lexer'     , 'mixed_case'        , 'no');
   ctx_ddl.set_attribute('spanish_lexer'     , 'index_text'        , 'yes');
-- ctx_ddl.set_attribute('spanish_lexer'     , 'index_stems'       , 7); --

spanish

   ctx_ddl.create_preference('global_lexer' , 'multi_lexer');    ctx_ddl.add_sub_lexer('global_lexer' , 'default' ,
'german_lexer');

   ctx_ddl.add_sub_lexer('global_lexer' , 'english' ,
'english_lexer' , 'eng');

   ctx_ddl.add_sub_lexer('global_lexer' , 'french' ,
'french_lexer' , 'fre');

   ctx_ddl.add_sub_lexer('global_lexer' , 'italian' ,
'italian_lexer' , 'ita');

   ctx_ddl.add_sub_lexer('global_lexer' , 'spanish' ,
'spanish_lexer' , 'spa');

end;
/

REM Creating textual indexes;

create index person_firstname_indx on person(first_name) indextype is ctxsys.context
parameters ('

               lexer global_lexer language column LANGUAGE_ISO_CODE
               wordlist my_wordlist
            ')

/

create index person_lastname_indx on person(last_name) indextype is ctxsys.context
parameters ('

               lexer global_lexer language column LANGUAGE_ISO_CODE
               wordlist my_wordlist
            ')

/

create index person_streetname_indx on person(street_name) indextype is ctxsys.context
parameters ('

               lexer global_lexer language column LANGUAGE_ISO_CODE
               wordlist my_wordlist
            ')

/

When I try to execute a very simple query like this one bellow I get a complete shutdown of the connection ORA-03113: end-of-file on communication channel

SELECT *
FROM FOGVW_SEARCHCUSTOMERS
WHERE (CONTAINS(LAST_NAME, 'fuzzy(?Az,65,100,weight)', 1) > 0  OR CONTAINS(LAST_NAME, '!Az') > 0

    OR CONTAINS(FIRST_NAME, 'fuzzy(?Giovann,65,100,weight)', 2) > 0

 OR CONTAINS(FIRST_NAME, '!Giovann') > 0
 OR CONTAINS(STREET_NAME, 'fuzzy(?Flurstras,65,100,weight)', 3) > 0 )
 OR CONTAINS(STREET_NAME, '!Flurstras') > 0

AND ZIPCODE='8048' ORDER BY (.5*SCORE(1) + .3*SCORE(2) + .2*SCORE(3)) DESC Is there any specific DB parameter I should tune? like shared_pool or make_the_text_capabilities_work_properly_pool :-) What the problem might be?
I also found out that for complex queries the documentation strongly suggest on using CTXCAT index type but this one doesn't almost have any feature at all
so I only have my problems solved with the CONTEXT index type.

TIA,
Best regards,
Giovanni Received on Mon Oct 21 2002 - 06:56:00 CDT