oracle varchar max lower than specified

Question

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)?

Why do you think it's `varchar2(4000)` - does the data type and data length from `user_tab_columns` support that? Can you post the table creation command (preferably from `dbms_metadata.get_ddl` in case it wasn't built how you think)? — Alex Poole, Jun 04 '13 at 10:21
See Update above. Don't know how to get to the dbms_metadata.get_ddl, so can only assume it was the original sql, which was a basic: ZZZ VARCHAR2(4000) — Niels Bech Nielsen, Jun 04 '13 at 10:39
from nls_database_parameters.... NLS_CHARACTERSET=AL32UTF8, NLS_NCHAR_CHARACTERSET=UTF8, NLS_LENGTH_SEMANTICS=BYTE, NLS_NCHAR_CONV_EXCP=FALSE — Niels Bech Nielsen, Jun 04 '13 at 11:31
Weird problem. Use this to get the table definition: `select dbms_metadata.get_ddl('TABLE', 'YYY', 'XXX') from dual;` — Jon Heller, Jun 05 '13 at 05:27
That might be caused by the internal conversion of the incoming data... Are you sure both charsets are the same? — i100, Jun 06 '13 at 08:37

score 1 · Accepted Answer · answered Jun 06 '13 at 18:08

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);.

oracle varchar max lower than specified

1 Answers1