oracle sql - to store 4000 characters in a column of my DB table [closed]

Question

In my web application, I am saving some Text message in a COLUMN of a DB table(Oracle). Earlier the VARCHAR2 length(maximum length) is (500 BYTE). Now the client has asked me to increase the maximum length to 4000 characters.
Is it possible to save 4000 char in a column of a DB table or do we need to save it as BLOB.
Which gives better performance?
Can anyone please clarify on this?

Answer 1

Oracle has a limit of 4000 bytes for a VARCHAR2 column and there is no way to workaround that limit.

So you can only store 4000 characters in a VARCHAR2 column if you are using a single-byte characterset.

If you are using UTF8 or any other multi-byte characterset you might not be able to store 4000 characters (as some characters require more than one byte). Defining the column as VARCHAR2(4000 Char) will not overcome that limit.

If you are indeed using a multi-byte characterset you will have to use a CLOB column otherwise you cannot store 4000 characters (as suggested by gTito).

If you are using a single byte characterset then you can get away with a VARCHAR2(4000 Char) column.

Answer 2

Did you check CLOB. For saving large Text oracle recommends CLOB.

A CLOB (Character Large Object) is an Oracle data type that can hold up to 4 GB of data. CLOBs are handy for storing text.