How do I create an index for text stored in a NCLOB or NVARCHAR2?

From: Ryan <rwagner_at_iconect.com>
Date: Wed, 4 Jun 2008 10:18:55 -0700 (PDT)
Message-ID: <c9c56247-579c-480d-acd0-c1cc2d2a515b@p25g2000hsf.googlegroups.com>


I need to be able to create a oracle text index (domain index) on NCLOB and NVARCHAR2 fields. We need to be able to support searching for chinese and other unicode characters.

I know that if the database is unicode we could just use CLOB and VARCHAR2 but we need to support users who don't have unicode databases. Therefore we need to use NVARCHAR2 and NCLOB's to store the data.

Whereever I create an index on a NCLOB field it gives error: "ORA-29855: Error occurred in the execution of ODCIINDEXCREATE routine.

ORA-20000: Oracle Text Error:
DRG-10509: Invalid Text Column: NCLOBFIELD
ORA-06512: at "CTXSYS.DRUE", line 160
ORA-06512: at "CTXSYS.TEXTINDEXMETHODS", line 364"

Here is an example I've created:
--drop table A_TEST;
create table A_TEST (recNum INT PRIMARY KEY, nClobField NCLOB); insert into A_TEST VALUES (0, '̨y?? JBlendTM ??3?'); insert into A_TEST VALUES (1, 'something ̨y?? sunny weather'); commit;

select * from A_TEST
BEGIN
ctx_ddl.create_preference('WORLDLEXER', 'world_lexer'); END; CREATE INDEX A_TEST_IDX ON A_TEST(nClobField) INDEXTYPE IS CTXSYS.CONTEXT
PARAMETERS ('lexer WORLDLEXER SYNC (MANUAL)');

select * from A_TEST where contains(nClobField, '̨y??') > 0; select * from A_TEST where nClobField = '̨y?? JBlendTM ??3?';

Also

Creating an index procedure with NCLOB's is not allowed. This could cause a problem for us also. Is there a way around this.

example:

CREATE OR REPLACE PROCEDURE A_IDX_PROC_TEST(RID IN ROWID, TLOB IN OUT NCLOB) IS
BEGIN
END; BEGIN

CTX_DDL.CREATE_PREFERENCE('MyDataStore','USER_DATASTORE');
CTX_DDL.SET_ATTRIBUTE('MyDataStore','PROCEDURE','A_IDX_PROC_TEST');
CTX_DDL.SET_ATTRIBUTE('MyDataStore','OUTPUT_TYPE','NCLOB');
END; Received on Wed Jun 04 2008 - 12:18:55 CDT

Original text of this message