SQL & PL/SQL

Hi,


Let us assume the database character set is UTF-8, which I believe is the default in recent version of Oracle. In this case, some characters take more than 1 byte to store in the database.

If you define the field as VARCHAR2(11 BYTE), Oracle will allocate 11 bytes for storage, but you may not actually be able to store 11 characters in the field, because some of them take more than one byte to store, e.g. non-English characters.

By defining the field as VARCHAR2(11 CHAR) you tell Oracle to allocate enough space to store 11 characters, no matter how many bytes it takes to store each one. I believe that in Oracle 10g, 3 bytes per character were used.


Also find good links.

http://stackoverflow.com/questions/81448/difference-between-byte-and-char-in-column-datatypes
2379822

Declare your variables as table.column%TYPE.
Even if the type (length) of the database column changes, it will still fit in the variable.

It could be up to 4 bytes depending on the character set and the character being stored.

Also need to consider that if using a multi byte character set, then the limit on varchar2 columns on the database is still 4000 bytes and not 4000 characters, so if multi-byte characters get stored, the number of characters that one can store in that column can be as few as 1000. It can cause confusion when some people think the limit is 4000 characters and then find they have trouble storing that many because of multi-byte characters. (Same principle applies to the 32767 byte limit in PL/SQL varchar2 variables)

All the three options
1) varchar2(100 char)
2) varchar2(100 bytes)
3) varchar2(100)

are equally supported and recommended. Using any of them will result in insertion of 100 characters only.

No it won't. Clearly you haven't read previous replies.

varchar2(100 bytes) will support at most 100 characters, but could be as few as 25 characters if it is populated with multi byte characters (each could take up to 4 bytes).