Character Data Types:
• CHAR Data Type
• VARCHAR2 Data Type
• VARCHAR Data Type
• NUMBER Data Type
CHAR Data Type
The CHAR data type specifies a fixed-length character string in the database character set.
You specify the database character set when you create your database.
When you create a table with a CHAR column, you specify the column length as size optionally followed by a length qualifier. The qualifier BYTE denotes byte length semantics while the qualifier CHAR denotes character length semantics.
Oracle ensures that all values stored in a CHAR column have the length specified by size in the selected length semantics.
If you insert a value that is shorter than the column length, then Oracle blank-pads the value to column length.
If you try to insert a value that is too long for the column, then Oracle returns an error.
You can omit size from the column definition. The default value is 1.
The maximum value of size is 2000, which means 2000 bytes or characters (code points), depending on the selected length semantics. However, independently, the absolute maximum length of any character value that can be stored into a CHAR column is 2000 bytes.
For example, even if you define the column length to be 2000 characters, Oracle returns an error if you try to insert a 2000-character value in which one or more code points are wider than 1 byte. The value of size in characters is a length constraint, not guaranteed capacity. If you want a CHAR column to be always able to store size characters in any database character set, use a value of size that is less than or equal to 500.
To ensure proper data conversion between databases and clients with different character sets, you must ensure that CHAR data consists of well-formed strings.
VARCHAR2 Data Type
The VARCHAR2 data type specifies a variable-length character string in the database character set.
You specify the database character set when you create your database.
When you create a table with a VARCHAR2 column, you must specify the column length as size optionally followed by a length qualifier. The qualifier BYTE denotes byte length semantics while the qualifier CHAR denotes character length semantics.
In the byte length semantics, size is the maximum number of bytes that can be stored in the column. In the character length semantics, size is the maximum number of code points in the database character set that can be stored in the column.
A code point may have from 1 to 4 bytes depending on the database character set and the particular character encoded by the code point.
Oracle stores a character value in a VARCHAR2 column exactly as you specify it, without any blank-padding, provided the value does not exceed the length of the column. If you try to insert a value that exceeds the specified length, then Oracle returns an error.
The minimum value of size is 1. The maximum value is:
• 32767 bytes if MAX_STRING_SIZE = EXTENDED
• 4000 bytes if MAX_STRING_SIZE = STANDARD
VARCHAR Data Type
Do not use the VARCHAR data type.
Use the VARCHAR2 data type instead. Although the VARCHAR data type is currently synonymous with VARCHAR2, the VARCHAR data type is scheduled to be redefined as a separate data type used for variable-length character
strings compared with different comparison semantics.
NUMBER Data Type
The NUMBER data type stores zero as well as positive and negative fixed numbers with absolute values from 1.0 x 10-130 to but not including 1.0 x 10126.
If you specify an arithmetic expression whose value has an absolute value greater than or equal to 1.0 x 10126, then Oracle returns an error. Each NUMBER value requires from 1 to 22 bytes.
Specify a fixed-point number using the following form:
NUMBER(p,s)
where:
• p is the precision, or the maximum number of significant decimal digits, where the most significant digit is the left-most nonzero digit, and the least significant digit is the rightmost known digit. Oracle guarantees the portability of numbers with precision of up to 20 base-100 digits, which is equivalent to 39 or 40 decimal digits depending on the position of the decimal point.
• s is the scale, or the number of digits from the decimal point to the least significant digit. The scale can range from -84 to 127.
It is good practice to specify the scale and precision of a fixed-point number column for extra integrity checking on input. Specifying scale and precision does not force all values to a fixed length. If a value exceeds the precision, then Oracle returns an error.
If a value exceeds the scale, then Oracle rounds it.
Specify an integer using the following form:
NUMBER(p)
This represents a fixed-point number with precision p and scale 0 and is equivalent to NUMBER(p,0).
