Probelm with Function based indexes on NVARCHAR2 columns

Links

Frequent Oracle Errors

•	TNS:could not resolve the connect identifier specified
•	Backtrace message unwound by exceptions
•	invalid identifier
•	PL/SQL compilation error
•	internal error
•	missing expression
•	table or view does not exist
•	end-of-file on communication channel
•	TNS:listener unknown in connect descriptor
•	insufficient privileges
•	PL/SQL: numeric or value error string
•	TNS:protocol adapter error
•	ORACLE not available
•	target host or object does not exist
•	invalid number
•	unable to allocate string bytes of shared memory
•	resource busy and acquire with NOWAIT specified
•	error occurred at recursive SQL level string
•	ORACLE initialization or shutdown in progress
•	archiver error. Connect internal only, until freed
•	snapshot too old
•	unable to extend temp segment by string in tablespace
•	Credential retrieval failed
•	missing or invalid option
•	invalid username/password; logon denied
•	unable to create INITIAL extent for segment
•	out of process memory when trying to allocate string bytes
•	shared memory realm does not exist
•	cannot insert NULL
•	TNS:unable to connect to destination
•	remote database not found'>ora-02019
•	exception encountered: core dump
•	inconsistent datatypes
•	no data found
•	TNS:operation timed out
•	PL/SQL: could not find program
•	existing state of packages has been discarded
•	maximum number of processes exceeded
•	error signaled in parallel query server
•	ORACLE instance terminated. Disconnection forced
•	TNS:packet writer failure
•	see ORA-12699
•	missing right parenthesis
•	name is already used by an existing object
•	cannot identify/lock data file
•	invalid file operation
•	quoted string not properly terminated

Probelm with Function based indexes on NVARCHAR2 columns

Probelm with Function based indexes on NVARCHAR2 columns

2005-07-27 - By Madhu Konda

Reply: 1 2 3

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 (See ORA-12704.ora-code.com).

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 (See ORA-12704.ora-code.com): 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
<DIV>Hi All,<BR>   I am getting errors while trying to use function
based indexes on NVARCHAR data type column.  I have table consumer_mst<BR>
   with NAME_1  declared as NVARCHAR2(60).Our database charset is
UTF-8 and db version is 8.1.6<BR>   <BR>   I created the
following function based index :<BR>   <BR>   SQL>create
index consumer_mst_fbidx2 on consumer_mst(UPPER(NAME_1));<BR>   <BR>
   SQL > ALTER SESSION SET QUERY_REWRITE_ENABLED=TRUE;</DIV>
<DIV><BR>Then when I execute the following query I am getting ORA-12704 (See ORA-12704.ora-code.com).<BR>
   <BR>  SQL<A href="mailto:IGCSPROD@>SELECT">&gt
;SELECT</A> CONS.CONSUMER_CODE,UPPER(COUNTRY_CODE) COUNTRY_CODE,CONS.ERA, 
<BR>12:23:27   2  CONS.MEMBERSHIP_CODE,CONS.NAME_1,CONS.NAME_2
,CONS.NAME_3,<BR>12:23:27   3  nvl(to_char(CONS.BIRTH_YEAR),'0000
') BIRTH_YEAR,  <BR>12:23:27   4  nvl(DECODE( LENGTH(BIRTH
_DATE),4,SUBSTR(BIRTH_DATE,1,2),3,  <BR>12:23:27   5  SUBSTR
(BIRTH_DATE,1,1),BIRTH_DATE) ,'00') MONTH,  <BR>12:23:27   6&nbsp
; nvl(DECODE( LENGTH(BIRTH_DATE),4,SUBSTR(BIRTH_DATE,3,4),3,  SUBSTR(BIRTH
_DATE,2,3),BIR<BR>TH_DATE),'00') DAY,  CONS.ZIP_CODE,CONS.PERSONAL_CODE,
  <BR>12:23:27   7  NVL( phone_number_1 , NVL(phone_number_2
,phone_number_3))  PHONE_NUMBER_1,  NVL(email_<BR>address_1, email
_address_2) EMAIL_ADDRESS_1<BR>12:23:27  
8   FROM CONSUMER_MST CONS  <BR>12:23:27   9 
WHERE CONS.COUNTRY_CODE = 'SG' AND  <BR>12:23:27  10  CONS.DATA
_STATUS ='0'  AND <BR>12:23:27  11  UPPER(CONS.NAME_1) LIKE UPPER
(N'tammy%')  <BR>12:23:27  12  ORDER BY CONS.NAME_1;</DIV>
<DIV>SELECT CONS.CONSUMER_CODE,UPPER(COUNTRY_CODE) COUNTRY_CODE,CONS.ERA,<BR>*
<BR>ERROR at line 1:<BR>ORA-12704 (See ORA-12704.ora-code.com): character set mismatch</DIV>
<DIV><BR>Elapsed: 00:01:03.08</DIV>
<DIV><BR>Can you please help me what I am doing wrong and make this work.</DIV>
<DIV>TIA,<BR>Madhu<BR></DIV><p>__ ____ ____ ____ ____ ____ ____ ____ ____ _____
__<br>Do You Yahoo!?<br>Tired of spam? Yahoo! Mail has the best spam protection
around <br>http://mail.yahoo.com