How do I create an index for text stored in a NCLOB or NVARCHAR2? -
06-04-2008
, 12:18 PM
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_DATA STORE');
CTX_DDL.SET_ATTRIBUTE('MyDataStore','PROCEDURE','A _IDX_PROC_TEST');
CTX_DDL.SET_ATTRIBUTE('MyDataStore','OUTPUT_TYPE', 'NCLOB');
END; |