Re: V10.2.0.1 exp/imp ORA-00910: specified length too long for its datatype
- From: "Jared Still" <jkstill@xxxxxxxxx>
- To: gints.plivna@xxxxxxxxx
- Date: Wed, 19 Mar 2008 09:25:21 -0700
On Wed, Mar 19, 2008 at 9:01 AM, Gints Plivna <gints.plivna@xxxxxxxxx>
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@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))
*
ERROR at line 1:
ORA-00910: specified length too long for its datatype
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
Other related posts:
