Right now I’m working on a database migration to the Unicode character set AL32UTF8.I noticed in the alert log that one of the jobs produced the following error
- ORA-06502: PL/SQL: numeric or value error: character string buffer too small
- ORA-06512: at “SCHEMA.PACKAGE”, line 99
So it seems that somewhere in that package, a variable is assigned a value that is too large.
By looking at the code
lv_weekday_name := to_char(lv_date,'day','nls_date_language = swedish');
This code is running fine in production which uses WE8ISO8859P1 instead of AL32UTF8. So what is happening here? I will run the statement in my different environment.
select to_char(sysdate,'day','nls_date_language = swedish') from dual;
The result in both environments is Onsdag(Wednesday). But this error occurred two days ago according to the alert log. This means Monday(Måndag) would be a more suitable test case.
So I run the same query again checking Monday(Måndag) instead but I’m also adding the vsize function to get the actual size in bytes.
select vsize(to_char(sysdate-2,'day','nls_date_language = swedish')) from dual
Which on my production environment returns 7, but in my new AL32UTF8 environment it returns 8. This means that it takes 8 bytes to store the letter “å” in my AL32UTF8 environment.
Let’s use the dump function and see the internal representation of the data.
select dump(to_char(sysdate-2,'day','nls_date_language = swedish')) from dual
In production the dump function returns
Typ=1 Len=7: 109,229,110,100,97,103,32
In my AL32UTF8 environment
Typ=1 Len=8: 109,195,165,110,100,97,103,32
Now I can clearly see that the character set storage representation of the letter “å” is totally different compared to my WE8ISO8859P1 system.
This all comes down to the change of NLS_LENGTH_SEMANTICS which has been set to CHAR instead of BYTE during the migration. To store the letter “å” it takes 1 byte, but by moving to a multi-character supported database it takes 2 bytes.
So the solution would be to use %TYPE or define the variable as VARCHAR2(7 CHAR)
vsize and dump are excellent function to investigate size and representation of data.
*Oracle strongly recommends that you do NOT set the NLS_LENGTH_SEMANTICS parameter to CHAR in the instance or server parameter file.