CREATE TABLE TEST_USER
(
  FIRST_NAME  VARCHAR2(64 CHAR)                 NOT NULL,
  LAST_NAME   VARCHAR2(64 CHAR)                 NOT NULL
);
 
 
CREATE INDEX TEST_USER_IDX3 ON TEST_USER
(FIRST_NAME)
INDEXTYPE IS CTXSYS.CTXCAT
PARAMETERS('LEXER cust_lexer');
 
 
CREATE INDEX TEST_USER_IDX4 ON TEST_USER
(LAST_NAME)
INDEXTYPE IS CTXSYS.CTXCAT
PARAMETERS('LEXER cust_lexer');
 
Dont worry about the cust_lexer, it is for diacritical search and it is not relevant to this question so I am not copying the code for the preference I created etc.
Now I have a row of data in the table with first_name column as Supervisor. If I run the below sql, it gives output:
 
SELECT *
  FROM test_user
 WHERE catsearch (first_name, 'Supervisor', NULL) > 0;
 
FIRST_NAME                     LAST_NAME
------------------------------ ------------------------------
Supervisor                     upervisor
 
--even the below sql with wild card (*) at the end works fine...
SQL> SELECT *
  2    FROM test_user
  3   WHERE catsearch (first_name, 'Super*', NULL) > 0;
 
FIRST_NAME                     LAST_NAME
------------------------------ ------------------------------
Supervisor                     upervisor
 
 
However the below sql queries doesn't give any output, though they should return the same row as above!
 
SQL> SELECT *
  2    FROM test_user
  3   WHERE catsearch (first_name, '*visor', NULL) > 0;
 
no rows selected
 
 
SQL> SELECT *
  2    FROM test_user
  3   WHERE catsearch (first_name, '*vis*', NULL) > 0;
 
no rows selected

ENWEBTT > @rebuild_context_indexes.sql

INSTANCE_N 'ALTERINDEX'||OWNER||'.'||INDEX_NAME||'REBUILD;'
---------- -------------------------------------------------------------------
NWEBT      alter index ALFC.IDX_NFL_ABS_CONT_TITLE rebuild;
NWEBT      alter index CONTENT.TITLE_TEXT_IDX rebuild;
NWEBT      alter index SITE.PERSONS_TEXT_INDEX rebuild;
NWEBT      alter index XDB.XDB$ACL_XIDX rebuild;
NWEBT      alter index XDB.XDBHI_IDX rebuild;

5 rows selected.

ENWEBTT > list
  1   select INSTANCE_NAME,'alter index '||owner||'.'||index_name||' rebuild;'
  2  from dba_indexes,V$INSTANCE where index_type like '%DOMAIN%'
  3* order by owner
ENWEBTT > alter index ALFC.IDX_NFL_ABS_CONT_TITLE rebuild;

Index altered.

ENWEBTT > alter index CONTENT.TITLE_TEXT_IDX rebuild;

Index altered.

ENWEBTT > alter index SITE.PERSONS_TEXT_INDEX rebuild;

Index altered.

select * from test_user
where catsearch(first_name,
'<query> 
  <textquery grammar="context">
     %pervisor
  </textquery>
</query>','')>0

FIRST_NAME                     LAST_NAME
------------------------------ ------------------------------
Supervisor                     upervisor

SQL> select * from test_user
  2  where catsearch(first_name,
  3  '<query>
  4    <textquery grammar="context">
  5       %pervi%
  6    </textquery>
  7  </query>','')>0;

FIRST_NAME                     LAST_NAME
------------------------------ ------------------------------
Supervisor                     upervisor

This means I have to use this syntax to get around it!!!