dbTalk Databases Forums  

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

comp.databases.oracle.misc comp.databases.oracle.misc


Discuss How do I create an index for text stored in a NCLOB or NVARCHAR2? in the comp.databases.oracle.misc forum.



Reply
 
Thread Tools Display Modes
  #1  
Old   
Ryan
 
Posts: n/a

Default 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;

Reply With Quote
Reply




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is On
HTML code is Off



Powered by vBulletin Version 3.5.3
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.