Difference between BYTE and CHAR in column datatypes

Question

In Oracle, what is the difference between :

CREATE TABLE CLIENT

(

 NAME VARCHAR2(11 BYTE),

 ID_CLIENT NUMBER

)

and

CREATE TABLE CLIENT

(

 NAME VARCHAR2(11 CHAR), -- or even VARCHAR2(11)

 ID_CLIENT NUMBER

)

Answer 1

Let's assume the database character set is UTF-8, which is the recommended setting in the recent versions of Oracle. 

Some characters take more than 1 byte to store in the database, in the above case.

But, if you define the field as VARCHAR2(11 BYTE), Oracle can use up to 11 bytes for storage, but you might not actually be able to store 11 characters in the field, because some of them will take more than one byte to store, as for example non-English characters.

If you are defining the field as VARCHAR2(11 CHAR), then you can tell Oracle it can use enough space to store 11 characters, no matter how many bytes it takes to store each one. 

Note: A single character can require up to 4 bytes.