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

declare
lv_weekday_name varchar2(7);
lv_date:=sysdate;
begin
lv_weekday_name := to_char(lv_date,'day','nls_date_language = swedish');
end;

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

DUMP(TO_CHAR(SYSDATE-2,’DAY’,’NLS_DATE_LANGUAGE=SWEDISH’))
——————————————————————————–
Typ=1 Len=7: 109,229,110,100,97,103,32

In my AL32UTF8 environment

DUMP(TO_CHAR(SYSDATE-2,’DAY’,’NLS_DATE_LANGUAGE=SWEDISH’))
——————————————————————————–
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.

Over&Out