Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Why using anything else but VARCHAR2(4000) to store strings in an Oracle database?

I found an Ask Tom article explaining that there is not much difference between CHAR and VARCHAR2 in Oracle. It gave the impression that I should use VARCHAR2(4000 BYTE) for every column where I would like to store strings, even for ISO 639-1 language codes, because it does not make any difference.

I know that it makes sense to use CHAR(2) for ISO 639-1 language codes to enforce a basic data constraint. But this is not a sufficient constraint, because it permits storing 'xy' in the column which is not a valid language code. On the other side I pay for this basic constraint with the limitation that I have to change the database if I want to change my application to use ISO 639-2 language codes, which require 3 characters. So I tend to drop the constraint at all from the database level, because it seems to me that the cost is higher than the benefit.

With that in mind, I am wondering if there is any other significant reason why I should not use VARCHAR2(4000 BYTE) for any string shorter than 4000 bytes that I intend to store in an Oracle database?

like image 959
ceving Avatar asked Dec 13 '12 11:12

ceving


People also ask

How do I save more than 4000 characters in Oracle?

For strings greater than 4000 use a CLOB. you can use CLOB column.

Why do we use VARCHAR2 in Oracle?

The VARCHAR datatype is currently synonymous with the VARCHAR2 datatype. However, in a future version of Oracle, the VARCHAR datatype might store variable-length character strings compared with different comparison semantics. Therefore, use the VARCHAR2 datatype to store variable-length character strings.

What is the difference between VARCHAR2 and VARCHAR in Oracle?

VARCHAR2 is the same as VARCHAR in the oracle database. The main difference is that VARCHAR is ANSI Standard and VARCHAR2 is Oracle standard. The VarChar2 data type is used to store the character values. It is a variable-length data type i.e we can change the size of the character variable at execution time.

What is the maximum size for VARCHAR2 Oracle?

Maximum size is 4000 bytes or characters, and minimum is 1 byte or 1 character. You must specify size for VARCHAR2 .


1 Answers

Tom Kyte also rebuts your proposition here: https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1145132537055

like image 188
David Aldridge Avatar answered Sep 22 '22 06:09

David Aldridge