Probelm with Function based indexes on NVARCHAR2 columns

From: Madhu Konda <konda_madhusudhan_at_yahoo.com>
Date: Wed, 27 Jul 2005 00:02:16 -0700 (PDT)
Message-ID: <20050727070216.73378.qmail@web50613.mail.yahoo.com>

Hi All,

I am getting errors while trying to use function based indexes on NVARCHAR data type column. I have table consumer_mst with NAME_1 declared as NVARCHAR2(60).Our database charset is UTF-8 and db version is 8.1.6

I created the following function based index :

SQL>create index consumer_mst_fbidx2 on consumer_mst(UPPER(NAME_1));

SQL > ALTER SESSION SET QUERY_REWRITE_ENABLED=TRUE; Then when I execute the following query I am getting ORA-12704.

SQLSELECT">>SELECT CONS.CONSUMER_CODE,UPPER(COUNTRY_CODE) COUNTRY_CODE,CONS.ERA,

12:23:27   2  CONS.MEMBERSHIP_CODE,CONS.NAME_1,CONS.NAME_2,CONS.NAME_3,
12:23:27   3  nvl(to_char(CONS.BIRTH_YEAR),'0000') BIRTH_YEAR,  
12:23:27   4  nvl(DECODE( LENGTH(BIRTH_DATE),4,SUBSTR(BIRTH_DATE,1,2),3,  
12:23:27   5  SUBSTR(BIRTH_DATE,1,1),BIRTH_DATE) ,'00') MONTH,  
12:23:27   6  nvl(DECODE( LENGTH(BIRTH_DATE),4,SUBSTR(BIRTH_DATE,3,4),3,  SUBSTR(BIRTH_DATE,2,3),BIR

TH_DATE),'00') DAY, CONS.ZIP_CODE,CONS.PERSONAL_CODE, 12:23:27 7 NVL( phone_number_1 , NVL(phone_number_2,phone_number_3)) PHONE_NUMBER_1, NVL(email_ address_1, email_address_2) EMAIL_ADDRESS_1

12:23:27   8   FROM CONSUMER_MST CONS  
12:23:27   9  WHERE CONS.COUNTRY_CODE = 'SG' AND  
12:23:27  10  CONS.DATA_STATUS ='0'  AND 
12:23:27  11  UPPER(CONS.NAME_1) LIKE UPPER(N'tammy%')  
12:23:27  12  ORDER BY CONS.NAME_1;

SELECT CONS.CONSUMER_CODE,UPPER(COUNTRY_CODE) COUNTRY_CODE,CONS.ERA, *
ERROR at line 1:
ORA-12704: character set mismatch

Elapsed: 00:01:03.08

Can you please help me what I am doing wrong and make this work. TIA,
Madhu

Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com

--
http://www.freelists.org/webpage/oracle-l

Received on Wed Jul 27 2005 - 02:08:06 CDT