Oracle Database Discussions

Hi Experts,

We are on Oracle 11.2.0.2 on Solaris 10 and I have implemented Oracle Text as in the example below:

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

I already implemented this and now facing this issue that the queries are not working as expected - can someone suggest what can be done for this issue?

Thanks

Edited by: orausern on Jan 16, 2013 10:59 AM

Edited by: orausern on Jan 16, 2013 11:00 AM

Edited by: orausern on Jan 16, 2013 11:07 AM