oracle sp2-0784,AL32UTF8/UTF8 Database CharacterSet Implications-2

ORA-29275

but ORA-600 [kole_t2u], [34]. See Note 734474.1 ORA-600 [kole_t2u], [34] - description, bugs, and

reasons

SP2-0784 is a pure client side error/warning returned by sqlplus,

it means the same as ORA-29275.

Note that those errors cannot be "turned

off" and nor should they be. They error indicate a serious problem

with your setup which needs to be

resolved.

Any character datatype like CHAR, VARCHAR2 and CLOB expect the data

to be in the encoding defined by the NLS_CHARACTERSET. Storing data

in an encoding that is not the NLS_CHARACTERSET is not supported.

Any data using a encoding different from the NLS_CHARACTERSET

should be considered BINARY and a BINARY datatypes like RAW or BLOB

should be used to store and process (!) this. Most of the time this

is seen with "encrypted" (passwords etc) data stored in a VARCHAR2.

See also point B.10) in this note.

This is also true when using the "convert" function by the way, any

conversion from the NLS_CHARACTERSET to a other characterset should

be considered as binary data as the result is not in the

NLS_CHARACTERSET. When using the convert function you might even

see errors like ORA-12703: this character set conversion is not

supported. Note that this is expected behavior and that the convert function should not be used in normal

application logic.

There is only one solution and that is to use CHARACTER datatypes

for what they are designed for, store data in the NLS_CHARACTERSET.

If you want to write out files in an other characterset then

AL32UT8F then use UTL_FILE, see point B.8)

Known Oracle Bugs who can give ORA-29275:

Bug 6268409 ORA-29275 ERROR WHEN QUERYING THE SQL_REDO/UNDO

COLUNMS IN V$LOGMNR_CONTENTS

Fixed in 10.2.0.5 , 11.1.0.7 and up

Bug 5915741 ORA-29275 selecting from V$SESSION with Multibyte

DB

Fixed in 10.2.0.5 , 11.1.0.6 and up

B.5) Going to

AL32UTF8 from another characterset.

To change a database NLS_CHARACTERSET to AL32UTF8 we suggest to

follow Note 260192.1 Changing the NLS_CHARACTERSET to AL32UTF8/UTF8

(Unicode)

Please note that it's strongly recommend to follow that note when

going to AL32UTF8 in any case, even when using (full)

exp/imp.

Using Csalter / Alter Database Character Set when going to

(AL32)UTF8 can be considerable faster then full exp/imp seen you do

not need to export all "US7ASCII" data (as all 0-9, A-Z and a-z

character data will stay the same).

Please see the following note for an Oracle Applications database:

Note 124721.1 Migrating an Applications Installation to a New

Character Set.

This is the only way supported by Oracle applications. If you have

any doubt log an Oracle Applications SR for assistance.

B.6) ORA-01401 /

ORA-12899 while importing data in an AL32UTF8 database.

If import give errors like

IMP-00019: row rejected due to ORACLE error 1401

IMP-00003: ORACLE error 1401 encountered

ORA-01401: inserted value too large for column

or from 10g onwards:

ORA-02374: conversion error loading table "TEST"."NTEST"

ORA-12899: value too large for column COMMENT (actual: 6028,

maximum: 4000)

then this indicates that the columns cannot handle the "grow in

bytes" of the data. This is solved by enlarging the column or using

CHAR semantics. Please see point B.1) of this note.

Note that with CHAR semantics on the column

the ORA-12899 error gives the amount of characters for

"actual" value, hence one can see an at first sight contradicting

error like ORA-12899: value too large for column COMMENT (actual:

2456, maximum: 4000)

One known issue is documented in Note 779526.1 CSSCAN does not detect data truncation for CHAR

datatype - ORA-12899 when importing

B.6) Object and

user names using non-US7ASCII characters.

We strongly suggest to never use

non-US7ASCII names for a database name and a database link name.

See also the limitations listed in the documentation set Restrictions on Character Sets Used to Express

Names . This means that for any type of names that has "no"

under the "Variable Width" column you NEED to use only US7ASCII

characters (a-z,A-Z, 1-0) in an AL32UTF8 database. Using

non-US7ASCII for these names is not supported.

This has to be corrected before going to AL32UTF8. In general

avoiding to use non-US7ASCII characters for database objects

whenever possible is a very good idea.

Names can be from 1 to 30 bytes long with these exceptions:

* Names of databases are limited to 8 bytes.

* Names of database links can be as long as 128 bytes.

* Identifiers of database objects are max 30 bytes long.

http://download-uk.oracle.com/docs/cd/B19306_01/server.102/b14200/sql_elements008.htm#i27570

This select

SQL>

select object_name from dba_objects where object_name <>

convert(object_name,'US7ASCII');

will return all objects having a non-US7ASCII name.

Using CHAR semantics is not supported in the SYS schema and that's

where the database object and user name is stored. If there are

column names, schema objects or comments with non-US7ASCII names

that take more then 30 bytes in AL32UTF8 there is no alternative

besides renaming the affected objects or user to use a name that

will occupy maximum 30 bytes.

* An username can be max 30 bytes long

http://download-uk.oracle.com/docs/cd/B19306_01/server.102/b14200/statements_8003.htm#SQLRF01503

"..his name can contain only characters from your database

character set and must follow the rules described in the section

"Schema Object Naming Rules". Oracle recommends that the user name

contain at least one single-byte character regardless of whether

the database character set also contains multibyte

characters."

This select

SQL>

select username from dba_users where username <>

convert(username,'US7ASCII');

will return all users having a non-US7ASCII name.

B.6) The password

of an user can only contain single byte data in 10g and below.

http://download-uk.oracle.com/docs/cd/B19306_01/server.102/b14200/statements_8003.htm#SQLRF01503

Passwords can contain only single-byte characters from the database

character set regardless of whether the character set also contains

multibyte characters. This means that in an AL32UTF8 database the

user password can only contain US7ASCII characters as this are the

only single byte characters in AL32UTF8.

This may provoke a problem, if you migrate from (for example) a

CL8MSWIN1251 database then your users can use Cyrillic in their

passwords seen in CL8MSWIN1251 Cyrillic letters are one single

byte, in AL32UTF8 they're not.

Note that seen passwords are stored in a

hashed way this will not be seen in the Csscan

result. You will need to reset for those clients the password to an

US7ASCII string.

This restriction is lifted in 11g, there multibyte characters can

be used as password string. Please note that they need to be

updated in 11g before they use the new 11g hashing system. Please

see Note 429465.1 11g R1 New Feature Case Sensitive Passwords and

Strong User Authentication

B.7) When using

DBMS_LOB.LOADFROMFILE

When using DBMS_LOB.LOADFROMFILE then please read

Note 267356.1 Character set conversion when using

DBMS_LOB.

B.8) When using

UTL_FILE

When using UTL_FILE then please read Note 227531.1 Character set conversion when using

UTL_FILE.

B.9) When using

sqlldr or external tables

When using Sqlldr or external tables make sure to define the

correct characterset of the file in the control file. The

characterset of the database has in no direct relation with the

encoding used in the file, in other words, it's not because the

database is using an AL32UTF8 characterset that using AL32UTF8 as

NLS_LANG or as characterset in the control file is always correct.

You need to specify the encoding of the file

sqlldr is loading.

Please read Note 227330.1 Character Sets & Conversion -

Frequently Asked Questions

18. What is the best way to load non-US7ASCII characters using

SQL*Loader or External Tables?

B.10) Make sure you

do not store "binary" (Encrypted) data in character datatypes

(CHAR,VARCHAR2,CLOB).

If binary data is stored/handled as a CHAR, VARCHAR2 or CLOB

datatype then data loss is expected, especially

when using an AL32UTF8 database (even without using exp/imp). Or

errors like ORA-29275 or ORA-600 [kole_t2u], [34] may appear.

This is not a bug, handling "binary" data using

character datatypes is simply not supported. There

is no "workaround" besides implementing a real solution.

This typically most visible on custom application "password hash"

functions. Any encrypted/hash result data should be considered

"binary" data as its endresult usually will

contain codes (or code sequences) who are not

valid in the current NLS_CHARACTERSET. Any character datatype like

CHAR, VARCHAR2 and CLOB expect the data to be in the encoding

defined by the NLS_CHARACTERSET. Storing data in an encoding that

is not the NLS_CHARACTERSET is not supported. Any data using a

encoding different from the NLS_CHARACTERSET should be considered

BINARY and a BINARY datatypes like RAW or BLOB should be used to

store and process (!) this.

The only solution is to use a or a binary datatype (RAW, LONG RAW,

BLOB) for the encrypted data or convert the 'raw' encrypted data

from a binary datatype to hex notation (rawtohex) or BASE64

(utl_encode.base64_encode) encoding before storing

/ using it in an character datatype, making it suitable to store it

in a character datatype.

For more info on storing encrypted/hashed data please see:

Note 270919.1 Transferring Encrypted Data from one Database to

Another

Note 580346.1 Store Encrypted Data into a VARCHAR2 Column Using

DBMS_CRYPTO

Note 760247.1 How to Use Correctly DBMS_CRYPTO.HASH With Clob

and Raw Input?

Please note that if the data is coming as

hashed/ecrypted/whatever form from the clients side the data needs

to be treated as RAW *all* the way from the application layer

onwards. If the data is handled as a character datatype then the

data needs to be converted on the client/application

side to HEX/BASE64.

Here is a simple example of a serverside generated "binary" string

(a MD5 hash function of a text input string) converted to a HEX

string, which allows the endresult to be stored in and used with

CHAR, VARCHAR2 or CLOB datatypes.

create or replace FUNCTION MD5_sample(l_string VARCHAR2) RETURN VARCHAR2

AS

l_MD5_stringHEX VARCHAR2(50 BYTE);

l_MD5_stringR RAW(50);

l_stringR RAW(4000);

BEGIN

-- using dbms_crypto is the best way.

l_stringR := utl_raw.cast_to_raw(l_string);

l_MD5_stringR := dbms_crypto.hash(typ => dbms_crypto.HASH_MD5, src => l_stringR);

-- dbms_crypto is not known in versions lower then 10.2

-- in that case you can also use dbms_obfuscation_toolkit, but do this only if this

-- is really needed

-- l_MD5_stringR := utl_raw.cast_to_raw(dbms_obfuscation_toolkit.md5( input_string => l_string ));

l_MD5_stringHEX := rawtohex(l_MD5_stringR);

RETURN l_MD5_stringHEX;

END;

/

Again, if for example this MD5 sum was made by the application code

then the result needs to be converted to

HEX/BASE64 notation before it can be submitted to the Oracle client

as an character datatype.