CODEWITHSUNDEEP
sqloracle-databaseoracle-sqldeveloper
user8235810
user8235810

Reputation:

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

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?

Upvotes: 1

Views: 4585

Answers (1)

Kaushik Nayak
Kaushik Nayak

Reputation: 31648

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

Upvotes: 5

Related Questions