CODEWITHSUNDEEP
oracle-databaseoracle10gvarchar2
Niels Bech Nielsen
Niels Bech Nielsen

Reputation: 4859

oracle varchar max lower than specified

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

Answers (1)

Jon Heller
Jon Heller

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

Related Questions