An Overview of Oracle Datatypes-Datatypes-1

Oracle provides 23 different SQL datatypes. Briefly, they are as follows:

•\ CHAR: A fixed-length character string that will be blank padded with spaces to its maximum length. A non-null CHAR(10) will always contain 10 bytes of information using the default National Language Support (NLS) settings. We will cover NLS implications in more detail shortly. A CHAR field may store up to 2000 bytes of information.

•\ NCHAR: A fixed-length character string that contains UNICODE formatted data. Unicode is a character-encoding standard developed by the Unicode Consortium with the aim of providing a universal way of encoding characters of any language, regardless of the computer system or platform being used. The NCHAR type allows a database tocontain data in two different character sets: the CHAR type and NCHAR type use the database’s character set and the national character set, respectively. A non-null NCHAR(10) will always contain 10 characters of information (note that it differs from the CHAR type in this respect). An NCHAR field may store up to 2000 bytes of information.

•\ VARCHAR2: Also currently synonymous with VARCHAR. This is a variable-length character string that differs from the CHAR type in that it is not blank padded to its maximum length. A VARCHAR2(10) may contain between 0 and 10 bytes of information using the default NLS settings. A VARCHAR2 may store up to 4000 bytes of information. Starting with Oracle 12c, a VARCHAR2 can be configured to store up to 32,767 bytes of information (see the “Extended Datatypes” section in this chapter for further details).

•\ NVARCHAR2: A variable length character string that contains UNICODE formatted data. An NVARCHAR2(10) may contain between 0 and10 characters of information. An NVARCHAR2 may store up to 4,000 bytes of information. Starting with Oracle 12c, an NVARCHAR2 can be configured to store up to 32,767 bytes of information (see the “Extended Datatypes” section in this chapter for further details).

•\ RAW: A variable-length binary datatype, meaning that no character set conversion will take place on data stored in this datatype. It is considered a string of binary bytes of information that will simply be stored by the database. A RAW may store up to 2000 bytes of information. Starting with Oracle 12c, a RAW can be configured to store up to 32,767 bytes of information (see the “Extended Datatypes” section in this chapter for further details).

•\ NUMBER: This datatype is capable of storing numbers with up to 38 digits of precision. These numbers may vary between 1.0×10(–130) and up to but not including 1.0×10(126). Each number is stored in a variable-length field that varies between 0 bytes (for NULL) and 22 bytes. Oracle NUMBER types are very precise—much more so than normal FLOAT and DOUBLE types found in many programming languages.

•\ BINARY_FLOAT: This is a 32-bit single-precision floating-point number. It can support at least six digits of precision and will consume 5 bytes of storage on disk.

•\ BINARY_DOUBLE: This is a 64-bit double-precision floating-point number. It can support at least 15 digits of precision and will consume 9 bytes of storage on disk.

•\ LONG: This type is capable of storing up to 2GB of character data (2 gigabytes, not characters, as each character may take multiple bytes in a multibyte character set). LONG types have many restrictions (I’ll discuss later) that are provided for backward compatibility, so it is strongly recommended you do not use this type in new applications. When possible, convert from LONG to CLOB types in existing applications.

•\ LONG RAW: The LONG RAW type is capable of storing up to 2GB of binary information. For the same reasons as noted for LONGs, it is recommended you use the BLOB type in all future development and, when possible, in existing applications as well.

•\ DATE: This is a fixed-width 7-byte date/time datatype. It will always contain the seven attributes of the century, the year within the century, the month, the day of the month, the hour, the minute, and the second.

•\ TIMESTAMP: This is a fixed-width 7- or 11-byte date/time datatype (depending on the precision). It differs from the DATE datatype in that it may contain fractional seconds; up to nine digits to the right of the decimal point may be preserved for TIMESTAMPs with fractional seconds.