Probelm with Function based indexes on NVARCHAR2 columns

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

Other related posts: