If CHAR or BYTE is not specified then the value defined by the NLS_LENGTH_SEMANTICS parameter is used as the default. We can check the current value of this parameter as follows.CREATE TABLE t(x VARCHAR2(10),
y VARCHAR2(20 BYTE),
z VARCHAR2(30 CHAR));Table created.
In the above example, table t was created with columns x and y with maximum lengths of 10 and 20 bytes respectively and column z with a maximum length of 30 characters.SELECT value
WHERE parameter = 'NLS_LENGTH_SEMANTICS';VALUE
BYTE1 rows selected
But what does this actually mean?
Character SetDuring database creation, the character set to be used by CHAR, VARCHAR2 and CLOB datatypes is specified. These are some of the values available for selection.
US7ASCII ASCII 7-bit American (SB)
WE8ISO8859P1 ISO 8859-1 West European 8-bit (SB)
EE8ISO8859P2 ISO 8859-2 East European 8-bit (SB)
JA16VMS JVMS 16-bit Japanese (MB)
KO16KSCCS KSCCS 16-bit Korean (MB)
AL16UTF16 Unicode 3.2 UTF-16 Universal character set (MB)
AL32UTF8 Unicode 3.2 UTF-8 Universal character set (MB)
The character set for a database can be determined with the following SQL.
SB = Single Byte
MB = Multi Byte
WHERE parameter = 'NLS_CHARACTERSET';VALUE
WE8ISO8859P11 rows selected
When using a single byte character set such as WE8ISO8859P1, a character is always held in a single byte so specifying the BYTE or CHAR qualifier has no effect on storage allocation. However, with a multi-byte character set such as EE8ISO8859P2, a single character may require up to 4 bytes of storage. In this case it may be useful to specify the length of the column in characters and let Oracle figure out how many bytes to allocate.
Consideration must be given to the maximum number of bytes that can be allocated. When using BYTE semantics the maximum is 2000 for CHAR datatypes, and 4000 for VARCHAR2. However, when using the CHAR length qualifier the maximum size that can be specified may be reduced in order to accommodate the character set.
National Character SetThe Unicode or national character set is also specified when creating the database. For example.
This setting is used by the Unicode datatypes NCHAR, NVARCHAR2 and NCLOB and the size of the column is always specified in characters.
Use the following SQL to determine the national character set details for a database.
WHERE parameter = 'NLS_NCHAR_CHARACTERSET';VALUE
UTF81 rows selected