Re: V10.2.0.1 exp/imp ORA-00910: specified length too long for its datatype

On Wed, Mar 19, 2008 at 9:01 AM, Gints Plivna <gints.plivna_at_gmail.com> wrote:

> The limit to remember - 4000 bytes maximum for any varchar2 in oracle
> table is enforced. So in your target database national caharcterset is
> AL16UTF16 which means every character takes 2 bytes. So you cannot
> create column with 3000 characters because the minimum bytes needed is
> 2*3000 = 6000 which is greater than 4000.
>

That doesn't appear to be quite correct.

Testing on 10.2.0.3 with NLS_NCHAR_CHARACTERSET = AL16UTF16

Create a table nlstest with varchar2(4000), insert 4000 character column and check the length.
09:09:16 ordevdb01.radisys.com - js001292_at_dv11 SQL> create table nlstest ( a varchar2(4000 char));

Table created.
SQL> insert into nlstest values(rpad('X',4000,'X'));

1 row created.

dv11 SQL> commit;

Commit complete.

SQL> select length(a) from nlstest;

LENGTH(A)

      4000

1 row selected.

SQL> select lengthb(a) from nlstest;

LENGTHB(A)

      4000

1 row selected.

The VARCHAR2 column of 4000 characters is stored in 4000 bytes.

This changes when using NVARCHAR2, as it uses multibyte storage.

SQL> create table nlstest2 (a nvarchar2(2001)); create table nlstest2 (a nvarchar2(2001))

                                  *

SQL> create table nlstest2 (a nvarchar2(2000));

Table created.

dv11 SQL> insert into nlstest2 values(rpad('X',2000,'X'));

1 row created.

dv11 SQL> select length(a) from nlstest2;

LENGTH(A)

      2000

1 row selected.

dv11 SQL> select lengthb(a) from nlstest2;

LENGTHB(A)

      4000

1 row selected.

The NVARCHAR2 is using multibyte semantics, while the VARCHAR2 is not.

-- 
Jared Still
Certifiable Oracle DBA and Part Time Perl Evangelist

--
http://www.freelists.org/webpage/oracle-l