PL/SQL supports a wide range of scalar data types for defining variables and constants. Unlike composite data types, scalar data types have no accessible components. These data types fall into one of the following categories:
* Boolean
* Date/time
* Character
* Number
Now, take a closer look at the data types in each category.
Boolean
The BOOLEAN data type, which takes no parameters, is used to store a binary value, TRUE or FALSE. This data type can also store the non-value NULL. You cannot insert or retrieve data from an Oracle database using this data type.
Date/Time
The data type DATE, which takes no parameters, is used to store date values. These DATE values include time when stored in a database column. Dates can range from 1/1/4712 B.C. to 12/31/4712 A.D. Defaults for the DATE data type are as follows:
* Date: first day of current month
* Time: midnight
Character
Character data types include CHAR, VARCHAR2, LONG, RAW, and LONG RAW. CHAR is for fixed-length character data, and VARCHAR2 stores variable-length character data. LONG stores variable-length character strings; RAW and LONG RAW store binary data or byte strings. The CHAR, VARCHAR2, and RAW data types take an optional parameter for specifying length.
datatype(max_len)
This length parameter, max_len, must be an integer literal, not a constant or variable.
* Boolean
* Date/time
* Character
* Number
Now, take a closer look at the data types in each category.
Boolean
The BOOLEAN data type, which takes no parameters, is used to store a binary value, TRUE or FALSE. This data type can also store the non-value NULL. You cannot insert or retrieve data from an Oracle database using this data type.
Date/Time
The data type DATE, which takes no parameters, is used to store date values. These DATE values include time when stored in a database column. Dates can range from 1/1/4712 B.C. to 12/31/4712 A.D. Defaults for the DATE data type are as follows:
* Date: first day of current month
* Time: midnight
Character
Character data types include CHAR, VARCHAR2, LONG, RAW, and LONG RAW. CHAR is for fixed-length character data, and VARCHAR2 stores variable-length character data. LONG stores variable-length character strings; RAW and LONG RAW store binary data or byte strings. The CHAR, VARCHAR2, and RAW data types take an optional parameter for specifying length.
datatype(max_len)
This length parameter, max_len, must be an integer literal, not a constant or variable.
Data Type | Maximum Length | Maximum Database Column Width |
| CHAR | 32767 | 255 |
| VARCHAR2 | 32767 | 2000 |
| LONG | 32760 | 2147483647 |
| RAW | 32767 | 255 |
| LONG RAW | 32760 | 2147483647 |
From this table, you can see the constraint on inserting CHAR, VARCHAR2, and RAW data into database columns of the same type. The limit is the column width. However, you can insert LONG and LONG RAW data of any length into similar columns because the column width is much greater.
Number
There are two data types in the number data type category: BINARY_INTEGER and NUMBER. BINARY_INTEGER stores signed integers with a range of -231 to 231-1. The most common use for this data type is an index for PL/SQL tables.
Storage for fixed or floating-point numbers of any size is available using the NUMBER data type. For floating-point numbers, you can specify precision and scale in the following format:
NUMBER(10,2)
A variable declared in this manner has a maximum of ten digits, and rounding occurs to two decimal places. The precision default is the maximum integer supported by your system, and 0 is the default for scale. The range for precision is 1 to 38 whereas the scale range is -84 to 127.
Number
There are two data types in the number data type category: BINARY_INTEGER and NUMBER. BINARY_INTEGER stores signed integers with a range of -231 to 231-1. The most common use for this data type is an index for PL/SQL tables.
Storage for fixed or floating-point numbers of any size is available using the NUMBER data type. For floating-point numbers, you can specify precision and scale in the following format:
NUMBER(10,2)
A variable declared in this manner has a maximum of ten digits, and rounding occurs to two decimal places. The precision default is the maximum integer supported by your system, and 0 is the default for scale. The range for precision is 1 to 38 whereas the scale range is -84 to 127.