substring search with CTXCAT index doesn't work even after enabling substring index during index creation

Question

I am trying to create a text index (CTXCAT) on a column of an Oracle DB table. I have set substring search to TRUE when creating the index. But after the index is created, when I do a substring search I don't see any results.

DDL of the table

"
  CREATE TABLE "INSTANCE_REPO"."CUSTOMERS" 
   (    "UUID" VARCHAR2(50), 
    "NAME" VARCHAR2(50), 
    "IDENTIFIER" VARCHAR2(50)
   ) SEGMENT CREATION IMMEDIATE 
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 
 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "USERS" "

Index creation command(s)

begin 
ctx_ddl.create_preference('catsearch_preferences', 'BASIC_WORDLIST'); 
ctx_ddl.set_attribute('catsearch_preferences','SUBSTRING_INDEX', 'TRUE');
end;

CREATE INDEX customer_cat ON CUSTOMERS(NAME) 
  INDEXTYPE IS CTXSYS.CTXCAT 
  PARAMETERS ('WORDLIST catsearch_preferences');

Substring search query (on the inserted row)

INSERT INTO customers(UUID,NAME,IDENTIFIER) VALUES ('500000','helloworld','HELLOWORLD');

Select * from customers where CATSEARCH(NAME, '*ello*', null) > 0

Select * from customers where CATSEARCH(NAME, '%ello%', null) > 0

result screenshot

Answer 1

catsearch queries don't support leading wildcards; to do this you can use a query template to switch to the context grammar:

create table customers (    
  uuid varchar2(50), 
  name varchar2(50), 
  identifier varchar2(50)
) ;

begin 
  ctx_ddl.create_preference('catsearch_preferences', 'BASIC_WORDLIST'); 
  ctx_ddl.set_attribute('catsearch_preferences','SUBSTRING_INDEX', 'TRUE');
end;
/

create index customer_cat on customers(name) 
  indextype is ctxsys.ctxcat 
  parameters ('WORDLIST catsearch_preferences');
  
insert into customers(uuid,name,identifier) 
  values ('500000','helloworld','HELLOWORLD');

select * from customers 
where  catsearch ( 
  name, 
  '*ell*', 
  null
) > 0;

no rows selected

select * from customers 
where  catsearch ( 
  name, 
  'hell*', 
  null
) > 0;

UUID     NAME         IDENTIFIER   
500000   helloworld   HELLOWORLD    

select * from customers 
where  catsearch ( 
  name, 
  '<query>
    <textquery grammar="context">
      %ello%
    </textquery>
   </query>', 
  null
) > 0;

UUID     NAME         IDENTIFIER   
500000   helloworld   HELLOWORLD