![]() |
| |||||||
![]() |
| | LinkBack | Thread Tools | Search this Thread | Display Modes |
| |||
| 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; |
![]() |
| Thread Tools | Search this Thread |
| Display Modes | |
| |