Handling large String values in database VARCHAR2 columns

Question

I have defined an VARCHAR2(2000 CHAR) field in the database. Often, we run into issues where the client input is more than 2000 characters and we end up missing out on the record. I am not sure if setting the field(s) to maximum characters would be the best way to solve this.

What would be the best way to handle such arbitrary large text input ?

I think you may find [this](http://dba.stackexchange.com/questions/4479/is-there-any-point-in-undersizing-varchar-columns) interesting. — Ben, Feb 29 '12 at 23:43

score 2 · Accepted Answer · answered Feb 29 '12 at 15:33

2

Use a CLOB (Character Large Object) column. These can store up to 4gb of character data.

answered Feb 29 '12 at 15:33

Andy

8,870
1
31
39

score 1 · Answer 2 · answered Feb 29 '12 at 15:33

1

CLOB. It's slower, can't be a primary key, some other limitations apply; but it can hold up to 4Gb.

answered Feb 29 '12 at 15:33

Amadan

191,408
23
240
301

1

Hopefully, @darkie is not trying to make varchar2(4000) as primary key. If he does, he will face lot of performance issues when the data will grow. – Femme Fatale Mar 06 '12 at 20:26

score 1 · Answer 3 · answered Feb 29 '12 at 15:35

1

If you're confident the client input will never exceed 4000 characters then you can increase the field size to that. Otherwise, you'll need to use a CLOB.

answered Feb 29 '12 at 15:35

Alex Poole

183,384
11
179
318

Handling large String values in database VARCHAR2 columns

3 Answers3