Hi Tom,
We are using two databases with two different character sets those are
11.2.0.1.0 - 64bit = AR8MSWIN1256
12.1.0.2.0 - 64bit = AL32UTF8
Trying to insert a data from the 11.2 version to the 12.1 through a DBLINK but it gives 'ORA-12899: value too large' error for many records, whereas when checking the length of the field gives only 12 characters, but while inserting it says '(actual: 13, maximum: 12)' for the item "OR_ITEMS_TEMP"."ITEMTYPE";
We have tried inserting the data directly Ex:script is given below
-------Create Table--------------
create table or_items_temp (id number, bu varchar2(12), brand varchar2(12), family varchar2(12), catgory varchar2(12), subcatgory varchar2(12),
grp varchar2(12), sgrp varchar2(12), saletype varchar2(12), itmodel varchar2(12), itemtype varchar2(12), uom varchar2(12),
rsp number(15,3), currency varchar2(12), itmobapp varchar2(12), status number(2))
-----End Create Table------------
-------Row details--------------
insert into or_items_temp(id, bu, brand, family, catgory, subcatgory, grp, sgrp, saletype, itmodel, itemtype, uom, rsp, currency, itmobapp,status)
VALUES(1, 'BU80', 'MR LIGHT', 'ELECTRONICS', 'LIGHTING', 'REC. LIGHT', 'COMBO', 'TORCH+TORCH', '01.TRADING', 'MR 670-2 CO', '24+24 EMERG', 'PCS', 104, 'QAR', NULL, 2);
-------End Row details--------------
but when taking the length of the item
select length('24+24 EMERG') from dual; it gives 12
We have changed the "OR_ITEMS_TEMP"."ITEMTYPE" length to 14 and inserted the values, it went through successfully. There after we again took the length of the field that too gives the same length as 12.
Why such a behavior? how can we correct this? kindly help
Best Regards,
Sreeju
This problem is likely due to the fact that characters can be different lengths in bytes in different character sets.
For example, if you compare the byte size of A umlaut in AL32UTF8 and AR8MSWIN1256, you see:
select lengthb(CONVERT('Ä', 'AL32UTF8')) bytes from dual;
BYTES
2
select lengthb(CONVERT('Ä', 'AR8MSWIN1256')) bytes from dual;
BYTES
1 So why's that a problem?
You can declare column lengths using character or byte semantics. This determines whether the length is in bytes or characters. You can't insert a single two-byte character into a 1 byte column:
create table t (
str_bytes varchar2(1 byte),
str_chars varchar2(1 char)
);
insert into t values (CONVERT('Ä', 'AL32UTF8'), CONVERT('Ä', 'AL32UTF8'));
ORA-12899: value too large for column "CHRIS"."T"."STR_BYTES" (actual: 2, maximum: 1)
insert into t values (CONVERT('Ä', 'AR8MSWIN1256'), CONVERT('Ä', 'AR8MSWIN1256'));
1 row inserted.
select * from t;
STR_BYTES STR_CHARS
? ? So I'm guessing that:
- "OR_ITEMS_TEMP"."ITEMTYPE" was a varchar2(12 byte) on the target
- You have some characters in this column that are a single byte in AR8MSWIN1256, but two in AL32UTF8
You can check the char (C) or byte (B) setting in user_tab_cols:
select column_name, char_used
from user_tab_cols
where table_name = 'T';
COLUMN_NAME CHAR_USED
STR_BYTES B
STR_CHARS C
You can fix this by modifying the length to be in characters instead of bytes:
alter table t modify ( str_bytes varchar2( 1 char ) );
insert into t ( str_bytes ) values (CONVERT('Ä', 'AL32UTF8'));
1 row inserted.
select * from t;
STR_BYTES STR_CHARS
? ?
�