Limit size of VARCHAR2 field in Oracle


I have a column with type VARCHAR2 (4000 CHAR) and I am testing the size limit of the VARCHAR2 field in Oracle. I thought there was a limit of 4000 bytes in the field, even specifying the value 4000 in characters, because that error already happened at work and the problem was solved.

When trying to insert a string containing 2000 characters á , an error occurred saying that the value was too large for the field. This happened because the character occupied 2 bytes in the database . However, today the same test was performed and this time the database entered the value correctly.

This occurred on only one of the machines where I work, which has the same configuration as the others.

What can be happening?

Is there any configuration in Oracle that might be allowing you to insert a string with more than 4000 bytes?

asked by anonymous 08.07.2016 / 20:26

2 answers


VARCHAR2 stores alphanumeric characters of variable length, between 1 and 4000 bytes or characters. The default size of this column is defined in bytes, in your case, this may be the problem .

When you create a VARCHAR2 column, you can specify whether the data is counted as bytes or characters. There is a large difference between these two options, for example, if you use the encode UTF-8, 1 single character can be stored in up to 3 bytes, so the storage limit can drop considerably if there are too many accented words / letters in these cases in this case it would be better to specify the column storage in character.

More information at: link

17.10.2016 / 00:48

Just a note: If you have Oracle 12c, you can extend this limit to 32767 bytes. For this, it is necessary to change the parameter MAX_STRING_SIZE = EXTENDED. In this link you have more information: link

31.01.2017 / 10:48