We have upgraded DB from 10g to 11g. 10g DB character set was UTF8 while 11g DB character set is AL32UTF8. We have some data with Greek characters which works
fine with 10g however not working with 11g.
In 10g, when trying to store 14 character string(with 1 Greek character) into variable like v_string varchar2(14) was working fine. However, with 11g it is giving error
ORA-06502: PL/SQL: numeric or value error: character string buffer too small.
I have searched and found multilingual character depends on NLS_LENGTH_SEMANTICS parameter
In Oracle 10g
parameter like : NLS_CHARACTERSET=UTF8, NLS_LENGTH_SEMANTICS=BYTE
in Oracle 11g database,
Parameter like : NLS_CHARACTERSET=AL32UTF8, NLS_LENGTH_SEMANTICS=BYTE
Please advice to solve this issue.
Thanks in advance.
The difference is probably because the variable v_string is defined as v_string varchar2(14 char) in 10g but as v_string varchar2(14 byte) in 11g. It may have happened if you created the relevant procedure with different NLS_LENGTH_SEMANTICS parameter in the creating session. The database-level NLS_LENGTH_SEMANTICS is irrelevant here. The instance-level parameter is a default for sessions (and should be kept as BYTE). The really relevant parameter value is the one set in the session that created the procedure.
To avoid misunderstanding, add the char keyword after the variable length in the procedure source code. This way the code will not depend on NLS_LENGTH_SEMANTICS.
What tool do you use to insert the data? Another possible problem is misconfiguration of clients for the new database. If you enter Greek characters encoded in Unicode UTF-8 through an OCI-based program but you set NLS_LANG to (for example) .EL8MSWIN1253, then unnecessary conversion will happen, which will misinterpret each byte of the 2-byte Greek character codes and convert them to random 2-byte UTF-8 characters. This will double the string length.