Happy three friends: VARCHAR2, UNICODE and CYRILLIC.

Many of DBA's know that database migration from single-byte character set (like CL8MSWIN1251, WE8MSWIN1252 etc) to multi-byte character set (AL32UTF8) is a comprehensive task.
In this article I'll talk about 2 very important quations, that I think must be resolved during migration procedure:
 1. Considerations about cyrillic VARCHAR2 data migrations (or any other non latin symbols) from single-byte database to multibyte database.
 2. How works parameter NLS_LENGTH_SEMANTICS in both cases.
The best way to demonstrate how Oracle implicates this - test cases. 
An example assumes that database operates in 2 languages - english and russian.
We want to migrate from 10g CL8MSWIN1251(single-byte) to 11g AL32UTF8(multibyte).
/* examples done in PL\SQL, so I've got an PL/SQL errors like ORA-01461,ORA-06512. In SQLPlus, Datapump, SQL*Loader U will see another errors like "ORA-12899: value too large for column " and so on. */



10g test with CL8MSWIN1251:
SQL> select * from nls_database_parameters where parameter in ('NLS_RDBMS_VERSION','NLS_CHARACTERSET','NLS_NCHAR_CHARACTERSET','NLS_LANGUAGE','NLS_TERRITORY','NLS_LENGTH_SEMANTICS');

PARAMETER                      VALUE
------------------------------ ----------------------------------------
NLS_LANGUAGE                   AMERICAN
NLS_TERRITORY                  AMERICA
NLS_CHARACTERSET               CL8MSWIN1251
NLS_LENGTH_SEMANTICS           BYTE
NLS_NCHAR_CHARACTERSET         AL16UTF16
NLS_RDBMS_VERSION              10.2.0.4.0

 6 rows selected


SQL> create table TEST (b varchar2(4000));

Table created

SQL> declare
  2    a varchar(4000) := 'я'; /* я - russian unique letter */
  3  begin
  4    dbms_output.enable(4000);
  5    while length(a) < 4000 loop
  6      a := a || 'я';
  7    end loop;
  8    insert into test (b) values (a);
  9    commit;
 10    dbms_output.put_line(length(a) ||'..inserted');
 11  end;
 12  /

PL/SQL procedure successfully completed

So, default NLS_LENGTH_SEMANTICS is BYTE. Lets change it.

SQL> alter session set NLS_LENGTH_SEMANTICS='CHAR';

Session altered

SQL> declare
  2    a varchar(4000) := 'я';
  3  begin
  4    dbms_output.enable(4000);
  5    while length(a) < 4000 loop
  6      a := a || 'я';
  7    end loop;
  8    insert into test (b) values (a);
  9    commit;
 10    dbms_output.put_line(length(a) ||'..inserted');
 11  end;
 12  /

PL/SQL procedure successfully completed


SQL> select length(b) from test;

 LENGTH(B)
----------
      4000
      4000

As we see, since CL8MSWIN1251 encodes any cyrillic symbol in 1 byte - we have no limits to insert 4000 chars( = bytes) into single-byte database, despite of NLS_LENGTH_SEMANTICS = CHAR or BYTE.



11g test with AL32UTF8:

SQL> select * from nls_database_parameters where parameter in ('NLS_RDBMS_VERSION','NLS_CHARACTERSET','NLS_NCHAR_CHARACTERSET','NLS_LANGUAGE','NLS_TERRITORY','NLS_LENGTH_SEMANTICS');

PARAMETER                      VALUE
------------------------------ --------------------------------------------------------------------------------
NLS_LANGUAGE                   AMERICAN
NLS_TERRITORY                  AMERICA
NLS_CHARACTERSET               AL32UTF8
NLS_LENGTH_SEMANTICS           BYTE
NLS_NCHAR_CHARACTERSET         AL16UTF16
NLS_RDBMS_VERSION              11.2.0.3.0

6 rows selected

To see how  NLS_LENGTH_SEMANTICS works:


SQL> create table TEST (b varchar2(4000));

Table created


SQL> declare
  2    a varchar(4000) := 'я';
  3  begin
  4    dbms_output.enable(4000);
  5    while length(a) < 4000 loop
  6      a := a || 'я';
  7    end loop;
  8    insert into test (b) values (a);
  9    commit;
 10    dbms_output.put_line(length(a) || '..inserted');
 11  end;
 12  /


ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at line 7

Variable 'a' - exceeds 4000 byte limit. Fortunately for VARCHAR2 in PL\SQL (in contrast with SQL) - the hard limit is 32767 byte. Also lets change commit frequency to see how many insert are done.


SQL> declare
  2    a varchar(8000) := 'я';
  3  begin
  4    dbms_output.enable(4000);
  5    while length(a) < 4000 loop
  6      insert into test (b) values (a);
  7      commit;
  8      a := a || 'я';
  9       end loop;
 10    dbms_output.put_line(length(a) || '..inserted');
 11  end;
 12  /


ORA-01461: can bind a LONG value only for insert into a LONG column
ORA-06512: at line 9

SQL> select max(LENGTH(b)) from test;

MAX(LENGTH(B))

--------------

          2000

Now we see that only 2000 symbols inserted. In UNICODE, cyrillic letters encoded by 2 bytes,  that is 2000*2 = 4000byte limit. Now what if we change table column to VARCHAR2 CHAR..? (or change NLS_LENGTH_SEMANTICS to CHAR) ?

SQL> drop table test;

Table dropped

SQL> create table test(b varchar2(4000 CHAR));

Table created

It is not necessary but still:

SQL> alter session set NLS_LENGTH_SEMANTICS='CHAR';

Session altered

SQL> declare

  2    a varchar(8000) := 'я';

  3  begin

  4    dbms_output.enable(4000);

  5    while length(a) < 4000 loop

  6      a := a || 'я';

  7    end loop;

  8    insert into test (b) values (a);

  9    commit;

 10    dbms_output.put_line(length(a) || '..inserted');

 11  end;

 12  /

ORA-01461: can bind a LONG value only for insert into a LONG column

ORA-06512: at line 9

Wow, we still can't insert 4000 symbols? wtf? Lets look how many inserts:

SQL> declare

  2    a varchar(8000) := 'я';

  3  begin

  4    dbms_output.enable(4000);

  5    while length(a) < 4000 loop

  6      insert into test (b) values (a);

  7      commit;

  8      a := a || 'я';

  9       end loop;

 10    dbms_output.put_line(length(a) || '..inserted');

 11  end;

 12  /

SQL> select max(LENGTH(b)) from test;

MAX(LENGTH(B))

--------------

          2000

Still 2000 only. What about non cyrillic symbols?

SQL> truncate table test;

Table truncated

SQL> declare

  2    a varchar(8000) := 'z';

  3  begin

  4    dbms_output.enable(4000);

  5    while length(a) < 4000 loop

  6      a := a || 'z';

  7    end loop;

  8    insert into test (b) values (a);

  9    commit;

 10    dbms_output.put_line(length(a) || '..inserted');

 11  end;

 12  /

PL/SQL procedure successfully completed

SQL> select max(LENGTH(b)) from test;

MAX(LENGTH(B))

--------------

          4000

Obviously, latin symbols encodes in 1 byte, and cyrillic in 2 
bytes. I remind, that session and column semantic is still CHAR...so wtf? The answer is in the: 

Examples and limits of BYTE and CHAR semantics usage (NLS_LENGTH_SEMANTICS) [ID 144808.1]

where we read:

You may have defined the column as 4000 CHAR, but the "limit behind the scene" is 4000 BYTES 

Nice. I'm happy that behind this scene we didn't saw some wall