Home>

I have tried various CTXCAT index searches of Oracle Text, but no data is caught in the search results.
In the sample, a CTXCAT index was created on the "HOGE_KEY_WORD" column of "HOGE_TABLE".
Can you tell me if there are not enough settings or if you can't consider if the SELECT statement is a problem?
The settings are as follows.

ORACLE version: 11.2.0.1 64bit

[Settings]
・ Table
CREATE TABLE HOGE_TABLE (
HOGE_ID VARCHAR (9),
HOGE_NAME VARCHAR (30),
HOGE_KEY_WORD VARCHAR (200)
);

・ HOGE_TABLE data
INSERT INTO HOGE_TABLE VALUES ('00001&apos ;,'test name 1&apos ;,'test name 1 INSERT INTO HOGE_TABLE VALUES ('00002&apos ;,'Test name 2&apos ;,'Test name 2');
INSERT INTO HOGE_TABLE VALUES ('00003&apos ;,'test name 3&apos ;,'test name 3 tesutomeisho san');
COMMIT;

・ Index set
execute CTX_DDL.CREATE_INDEX_SET ('HOGE_ISET');
execute CTX_DDL.ADD_INDEX ('HOGE_ISET','HOGE_NAME');

・ Index
CREATE INDEX HOGE_KEY_WORD_IDX ON HOGE_TABLE (HOGE_KEY_WORD)
INDEXTYPE IS CTXSYS.CTXCAT
PARAMETERS ('INDEX SET HOGE_ISET');

[SELECT statement]
I executed the following SQL to search for a row that contains the keyword "quote" in the HOGE_KEY_WORD, but
Search results will be 0.

SELECT * FROM HOGE_TABLE WHERE CATSEARCH (HOGE_KEY_WORD,'Test&&;poss;'ORDER BY HOGE_NAME')>0;

  • Answer # 1

    If you want to search Japanese, the following settings are required.

    [Settings]
    ・ Preferences
    execute ctx_ddl.create_preference ('TEST_LEXER','JAPANESE_VGRAM_LEXER');

    ・ Index
    CREATE INDEX HOGE_KEY_WORD_IDX ON HOGE_TABLE (HOGE_KEY_WORD)
    INDEXTYPE IS CTXSYS.CTXCAT
    PARAMETERS ('LEXER TEST_LEXER');

    [SELECT statement]
    SELECT * FROM HOGE_TABLE WHERE CATSEARCH (HOGE_KEY_WORD,'Tesa&apos ;, NULL)>0;

    Additionally, it seems to be an error in the following cases.

    -When the search keyword exceeds 256 bytes
    ORA-29902: An error occurred while executing the ODClndexStart () routine
    0RA-20000: Oracle Text error:
    DRG-50943: query token too long in line 1 (example 1)

    -When the search keyword is an empty string
    ORA-29902: An error occurred while executing the ODClndexStart () routine
    0RA-20000: Oracle Text error:
    DRG-50943: text query parsing syntax error on line 1 (example 1)

    -When the search keyword is one Japanese character
    ORA-29902: An error occurred while executing the ODClndexStart () routine
    0RA-20000: Oracle Text error:
    DRG-50943: same-key index is required to run catsearch with this type of query

  • Answer # 2

    Assuming the definition is modified with VARCHAR → VARCHAR2

    If Oracle12.1.0

    SELECT * FROM HOGE_TABLE WHERE CATSEARCH (HOGE_KEY_WORD, 'Tesuto', 'ORDER BY HOGE_NAME')>0;
    HOGE_ HOGE_NAME HOGE_KEY_WORD
    ----- ------------ --------------------------------
    00001 Test name 1 Test name 1
    00002 Test name 2 Test name 2
    00003 Test Name 3 Test Name 3 Tesuto Meisho-san


    And return the result.
    If your support contract is valid, ask Oracle Support for a patch in this case.
    Also, Oracle11g will not provide a patch even if a bug occurs, so why not migrate to Oracle12.1 or later? If the support contract is valid, the upgrade of the Oracle license is free.