SQL & PL/SQL

Hi,

I have an requirement with the "CATSEARCH" which does a text search with the wild cards

The steps to produce are as follows

1. The text data(search conditions) are stored in a temparory table.
2. The select statement uses the "CATSEARCH" in the "Where" clause of select statement for searching, based on the column(where the search string is stored in a table).
3. An index is created on the search column with the indextype as "CTXCAT".


Here are the Technical details:

-- Index set that was created for other columns on the same temp table to improve the perfomance

Begin
ctx_ddl.create_index_set ('QOTSEARCH');
ctx_ddl.add_index ('QOTSEARCH', 'col1');
ctx_ddl.add_index ('QOTSEARCH', 'col2');
ctx_ddl.add_index ('QOTSEARCH', 'col3');
ctx_ddl.add_index ('QOTSEARCH', 'col4');
End;

-- Index Type that was created on the search column (existing indextype)

Create Index hdr_cat_idx On temp_tab (search_text) --col used for storing the search condition
Indextype Is ctxsys.ctxcat
Parameters('INDEX SET QOTSEARCH
memory 50M'
)
Parallel 5;


Requirement:

I need to modify the existing select statement which returns the data when the search condition contains any special characters say for ex:"/".
But the limitation is, the same search condition(col) is already indexed with indextype "CTXCAT" as mentioned above
in the technical section which I cant use to create any more indexes(CTXCAT) on the same column.

thought of using the below code to achieve the above requirement


BEGIN
CTX_DDL.CREATE_PREFERENCE ('QOTSEARCH', 'BASIC_LEXER');
CTX_DDL.SET_ATTRIBUTE ('QOTSEARCH', 'SKIPJOINS' , '/');
END;




Create Index cat_spchar_idx On temp_tab (col1)
Indextype Is ctxsys.ctxcat
Parameters('LEXER QOTSEARCH')

unfortunately I cant use this bcoz I cannot create another DOMAIN INDEX on the same col with same indextype or

Is there any other way that I can add one more parameter to the existing Indextype on the same column.


Please share your comments on this,looking forward for your comments


Cheers