Reputation: 4859
I have a table with a column varchar2(4000), where I got a problem inserting data, such that:
ORA-12899: value too large for column "XXX"."YYY"."ZZZ" (actual: 2132, maximum: 2048)
When I run
select * from user_tab_columns where table_name = 'YYY'
I can see a CHAR_LENGTH column of size 2048, but other than that I have no trace why it would preempt itself?
CHARACTER_SET_NAME is CHAR_CS, but content is mostly base64 encoded.. Any clues how to overcome this problem ?
Regards
Update:
Here's the full user_tab_columns, sorry for the indentation.
TABLE_NAME COLUMN_NAME DATA_TYPE DATA_TYPE_MOD DATA_TYPE_OWNER DATA_LENGTH DATA_PRECISION DATA_SCALE NULLABLE COLUMN_ID DEFAULT_LENGTH DATA_DEFAULT NUM_DISTINCT LOW_VALUE HIGH_VALUE DENSITY NUM_NULLS NUM_BUCKETS LAST_ANALYZED SAMPLE_SIZE CHARACTER_SET_NAME CHAR_COL_DECL_LENGTH GLOBAL_STATS USER_STATS AVG_COL_LEN CHAR_LENGTH CHAR_USED V80_FMT_IMAGE DATA_UPGRADED HISTOGRAM
YYY ZZZ VARCHAR2 <null> <null> 4,000 <null> <null> Y 7 <null> <null> 15 41 42 43 44 45 46 47 4d 49 49 46 75 7a 43 43 42 4b 4f 67 41 77 49 42 41 67 49 45 54 41 4c 4d 68 6a 41 4e 42 67 6b 71 0.06667 662 1 2013-06-03 929 CHAR_CS 4,000 NO NO 1,394 2,048 C NO YES NONE
The 2048 mark comes from the CHAR_LENGTH column, and CHAR_USED is C..
Update: Managed to get the initial DDL
CREATE TABLE "XXX", "YYY"
(
...
"ZZZ" VARCHAR2 (2048 CHAR)
...
)
But I still have no clue as to how to adjust that figure? Would it help with a simple alter table and set the varchar2(3192 CHAR)?
Upvotes: 1
Views: 1217
Reputation: 36922
Your column is limited to both 2048 characters and 4000 bytes. Regardless of your character length semantics, ALL_TAB_COLUMNS.DATA_LENGTH is "Length of the column (in bytes)". AL32UTF8 can use up to 4 bytes per character, so DATA_LENGTH will be the number of characters * 4. Except it will never be larger than the Oracle limit of 4000.
For example:
create table test1(a varchar2(1 char));
create table test2(a varchar2(2 char));
create table test3(a varchar2(1000 char));
create table test4(a varchar2(4000 char));
select table_name, data_length
from all_tab_columns
where table_name like 'TEST_';
TABLE_NAME DATA_LENGTH
---------- -----------
TEST1 4
TEST2 8
TEST3 4000
TEST4 4000
You can fix your problem with alter table xxx.yyy modify zzz varchar2(4000 char);
.
Upvotes: 1