Oracle Text - DRG-10599:Column not Indexed

Following are the Steps i followed:

Step 1: Create Table

CREATE TABLE Test_Table01
(
Cust_ID NUMBER,
Cust_NAME VARCHAR2(200 BYTE),
CONSTRAINT PK_Cust_ID PRIMARY KEY (Cust_ID) USING INDEX TABLESPACE INDX
)
TABLESPACE USERS;

Step 2: Create CONTEXT Index on SDN_NAME.

CREATE INDEX INDX_Cust_NAME ON Test_Table01
(Cust_NAME)
INDEXTYPE IS CTXSYS.CONTEXT;

Step 3: Insert Data into Test_Table01

Insert into TEST_TABLE01
(CUST_ID, CUST_NAME)
Values
(1, 'MARTIN DIAZ JOHN EDWARD');

Insert into TEST_TABLE01
(CUST_ID, CUST_NAME)
Values
(2, 'NKOMO JOHN');

Insert into TEST_TABLE01
(CUST_ID, CUST_NAME)
Values
(3, 'PINZON CEDIEL JOHN JAIRO');

Insert into TEST_TABLE01
(CUST_ID, CUST_NAME)
Values
(4, 'MENDEZ SALAZAR JOHN JAIRO');

COMMIT;

Step 3:

select * from Test_Table01
where contains(CUST_NAME),'JOHN') > 0;

No Rows Returned.

Step 4:

select * from Test_Table01
where contains(UPPER(CUST_NAME),UPPER('JOHN')) > 0;

ORA-20000: Oracle Text error:
DRG-10599: column is not indexed

-------------------------------------------------

In Step 4, i am getting column not indexed which i have done in Step 2.

answer:

 

CONTEXT indexes need to be explicitly synced by you (and afaik, CTXCAT synchronize automatically, but don't take my word for granted->refer to Oracle Text guide).

A quote from Oracle Text App. Guide:

http://download.oracle.com/docs/cd/B19306_01/text.102/b14217/quicktour.htm#CCAPP0200

Quote:
When you create a CONTEXT index, you need to explicitly synchronize your index to keep it up to date with any inserts, updates, or deletes to the text table.

Oracle Text enables you to do so with the CTX_DDL.SYNC_INDEX procedure.

So after you execute:

EXEC CTX_DDL.SYNC_INDEX('INDX_Cust_NAME', '2M');

I believe your query:

select * from Test_Table01
where contains(CUST_NAME,'JOHN') > 0;

will return the rows containing the word JOHN (don't have db with OracleText installed to try this out).

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/26920263/viewspace-736164/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/26920263/viewspace-736164/