Trying to migrate our MCD from Oracle Database 10g to Oracle Database 11.2 or 12
A/ I have just installed Oracle 11.2.0 on a Linux Red Hat 6.5 – 64 bits plateform.
During installation i have selected the WE8ISO8859P15 characterset and i’m face to an important problem about length of data storing into the database
B/ An exemple to illustrate the type of this problem
Please remember that i use the WE8ISO8859P15 characterset (european with € symbol).
This character set is supposed to be a Single Byte one, as I read in the oracle documentation.
1-Table creation
> CREATE TABLE TT (C Varchar2(10));
2-Inserting a row with sqlplus
> Insert into TT values ('éééééééééé');
ORA-12899: Value too large for column (real 20 : maximum authorized 10 )
3- Using the length function
Select length (‘é’) from dual ;
Oracle V11 Return : 2
Oracle V12 Return : 2
Oracle V10 Return : 1 – As whe are used to.
I tried to modify length_semantics from byte to char in the same sql session whitout any improvement.
By evidence, despite the single byte character set WE8ISO8859 installed, Oracle needs 20 bytes to store the 10 characters (‘éééééééééé’). On an Oracle database 10g, only 10 bytes where used by oracle
QUESTIONS :
- 1- Is there any parameter to manage for the instance (11 or 12), to get one byte for one character ?
- 2- Is the characters set WE8ISO8859P15 able to solve that or is there anyone else ?
- 3- If no solution is possible managing Oracle for that point, what kind of solution exists ? Enlarging (double sizing) all the columns ?
C/ Please note that i tried to re install the database 11 with differents charactersets like (WE8ISO8859P9, WE8MSWIN1252) but without any improvement. The French national characters like ‘é’ persisting in using 2 bytes.
D/ More informations:
SQL> select * from v$nls_parameters;
PARAMETER VALUE
---------------------------------------------------------------- ----------------------------------------------------------------
NLS_LANGUAGE FRENCH
NLS_TERRITORY FRANCE
NLS_CURRENCY �
NLS_ISO_CURRENCY FRANCE
NLS_NUMERIC_CHARACTERS ,
NLS_CALENDAR GREGORIAN
NLS_DATE_FORMAT DD/MM/RR
NLS_DATE_LANGUAGE FRENCH
NLS_CHARACTERSET WE8ISO8859P15
NLS_SORT FRENCH
NLS_TIME_FORMAT HH24:MI:SSXFF
NLS_TIMESTAMP_FORMAT DD/MM/RR HH24:MI:SSXFF
NLS_TIME_TZ_FORMAT HH24:MI:SSXFF TZR
NLS_TIMESTAMP_TZ_FORMAT DD/MM/RR HH24:MI:SSXFF TZR
NLS_DUAL_CURRENCY �
NLS_NCHAR_CHARACTERSET AL16UTF16
NLS_COMP BINARY
NLS_LENGTH_SEMANTICS CHAR
NLS_NCHAR_CONV_EXCP FALSE
19 ligne(s) s�lectionn�e(s).