Garayed.com  

Go Back   Garayed.com > Oracle
FAQ Members List Calendar Search Today's Posts Mark Forums Read


Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old 06-04-2008, 05:18 PM
Ryan
 
Posts: n/a
Default How do I create an index for text stored in a NCLOB or NVARCHAR2?

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;
Digg this Post!Add Post to del.icio.usBookmark Post in TechnoratiFurl this Post!
Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
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
Trackbacks are On
Pingbacks are On
Refbacks are On



All times are GMT. The time now is 03:21 AM.




LinkBacks Enabled by vBSEO 3.0.0 © 2007, Crawlability, Inc.