Oracle - getting 'value too large' error while inserting data

Question

I am trying to insert northwind sample database in oracle 11g. While it worked mostly, for some insert queries i got below error -

ORA-12899: value too large for column "SYSTEM"."CUSTOMERS"."CITY" (actual: 16, maximum: 15)

The data looked like -

Insert into CUSTOMERS
   (CUSTOMER_ID, CUSTOMER_CODE, COMPANY_NAME, CONTACT_NAME, CONTACT_TITLE, ADDRESS, CITY, REGION, POSTAL_CODE, COUNTRY, PHONE, FAX)
 Values
   (35, 'HILAA', 'HILARIÃ“N-Abastos', 'Carlos HernÃ¡ndez', 'Sales Representative', 
    'Carrera 22 con Ave. Carlos Soublette #8-35', 'San CristÃ³bal', 'TÃ¡chira', '5022', 'Venezuela', 
    '(5) 555-1340', '(5) 555-1948');

Note the special Ã in city value. When i replaced that special character with normal A, it got inserted successfully.

Insert into CUSTOMERS
   (CUSTOMER_ID, CUSTOMER_CODE, COMPANY_NAME, CONTACT_NAME, CONTACT_TITLE, ADDRESS, CITY, REGION, POSTAL_CODE, COUNTRY, PHONE, FAX)
 Values
   (35, 'HILAA', 'HILARIÃ“N-Abastos', 'Carlos HernÃ¡ndez', 'Sales Representative', 
    'Carrera 22 con Ave. Carlos Soublette #8-35', 'San CristA³bal', 'TÃ¡chira', '5022', 'Venezuela', 
    '(5) 555-1340', '(5) 555-1948');

I didnt reduced the number of characters. When length of data was not changed, why oracle gives such an error?

You have encoding problems, the data you're showing is corrupt (UTF-8 interpreted as ASCII it looks like). — Mat, Jun 10 '18 at 07:38
Create a new user for your CUSTOMERS table, you should not be touching the SYSTEM or SYS schemas. — thatjeffsmith, Jun 10 '18 at 10:26

Kaushik Nayak · Accepted Answer · 2018-06-10T08:16:33.903

I didn't reduced the number of characters. When length of data was not changed, why oracle gives such an error?

Because number of characters not always = no of bytes . When the database character set is UTF-8 ( generally true ),some characters take more than 1 byte to store in the database.

To verify this, you may use functions LENGTH, which gives character length and LENGTHB which gives you bytes length.

SELECT
    length('A'),
    length('Ã'),
    lengthb('A'),
    lengthb('Ã')
FROM
    dual;

LENGTH('A') LENGTH('Ã') LENGTHB('A') LENGTHB('Ã')
----------- ----------- ------------ ------------
          1           1            1            2

So, If have defined the column as VARCHAR2( n BYTE) and not VARCHAR2( n CHAR), then this error will be raised, when you try to insert a n character string, which takes more than 1 byte per character, like Ã

As an example consider this

create table test_byte( c VARCHAR2(1 byte) );
INSERT INTO test_byte(c) VALUES('Ã');

ORA-12899: value too large for column "HR"."TEST_BYTE"."C" (actual: 2,
maximum: 1)

However, if I specify the column as 1 CHAR, the insert works.

create table test_byte2( c VARCHAR2(1 char) );
INSERT INTO test_byte2(c) VALUES('Ã');
1 row inserted.

Refer this question for further details : Difference between BYTE and CHAR in column datatypes

Oracle - getting 'value too large' error while inserting data

1 Answers1