Oracle text catsearch multiple in one predicate

Question

I have a table with 2 column of type varchar2.

I have create 2 indeces

CREATE INDEX DOC_IDX_1 
ON DOCUMENTO 
( column1
) 
INDEX TYPE IS CTXSYS.CTXCAT
; 
CREATE INDEX DOC_IDX_2 
ON DOCUMENTO
( column2
) 
INDEX TYPE IS CTXSYS.CTXCAT
; 

when I execute the query

select * 
from   table1 
where  CATSEARCH(column1, 'AAA', NULL) > 0
AND    CATSEARCH(column2 , 'BBBb', NULL) > 0

I get the following error

ORA-20000: Oracle Text error:
DRG-10849: catsearch non supporta il richiamo funzionale
DRG-10599: la colonna non è indicizzata
20000. 00000 -  "%s"
*Cause:    The stored procedure 'raise_application_error'
       was called which causes this error to be generated.
*Action:   Correct the problem as described in the error message or contact
       the application administrator or DBA for more information.

The English translation is:

$ oerr drg 10849
10849,0, "catsearch does not support functional invocation"
// *Cause: the query plan for your query is invoking a catsearch operator
//         functionally rather than as an index rowsource.  The catsearch
//         operator does not support functional invocation
// *Action: add optimizer hints so that all catsearch operators are
//          performed through index scans
// *Mnemonic: QE_CATSEARCH_FUNCINV
//

And

10599,0, "column is not indexed"
// *Cause: The column specified is not indexed.
// *Action: Create index on the column before performing this operation
// *Mnemonic: DL_COLUMN_NOT_INDEXED

How can I fix the problem?

Thanks

Sergio

Answer 1

You're doing it wrong, first of all, in the ctxsys schema, you should set the preferences in this way:

begin
 ctx_ddl.create_preference('my_documents', 'MULTI_COLUMN_DATASTORE');
 ctx_ddl.set_attribute('my_documents', 'COLUMNS', 'column1, column2');
 ctx_ddl.create_preference('my_documents_lexer', 'BASIC_LEXER');  /* I strongly recommend you this preference*/
 ctx_ddl.set_attribute('my_documents_lexer', 'BASE_LETTER', 'YES');
end;
/

Once you run the script above, then you're able to create the index:

create index DOC_IDX on DOCUMENTO(column1) indextype is ctxsys.context
parameters ('DATASTORE ctxsys.my_documents  section group CTXSYS.AUTO_SECTION_GROUP LEXER ctxsys.my_documents_lexer SYNC(on commit)');

Your query might works this way:

select * 
from documento
where contains(column1,'AAA WITHIN column1 AND BBBb WITHIN column2')>0

Regards

Daniel