Appendix: Oracle Data Types

This appendix contains two tables that document the data types available in Oracle SQL and may be used to define columns in a table, along with the additional types supported by Oracle PL/SQL.

Oracle SQL Types

Table A-1 shows the Oracle SQL types.

Table A-1: Oracle SQL Types

Type	Description
CHAR[( length [BYTE CHAR])] [1]	Fixed-length character data of length bytes or characters and padded with trailing spaces. Maximum length is 2,000 bytes.
VARCHAR2( length [BYTE CHAR]) [1]	Variable-length character data of up to length bytes or characters. Maximum length is 4,000 bytes.
NCHAR[( length )]	Fixed-length Unicode character data of length characters. Number of bytes stored is 2 * length for AL16UTF16 encoding and 3 * length for UTF8. Maximum length is 2,000 bytes.
NVARCHAR2( length )	Variable-length Unicode character data of length characters. Number of bytes stored is 2 * length for AL16UTF16 encoding and 3 * length for UTF8 encoding. Maximum length is 4,000 bytes.
BINARY_FLOAT	New for Oracle Database 10 g . Stores a single precision 32-bit floating-point number. Operations involving BINARY_FLOAT are typically performed faster than on NUMBER s. BINARY_FLOAT requires 5 bytes of storage space.
BINARY_DOUBLE	New for Oracle Database 10 g . Stores a double precision 64-bit floating-point number. Operations involving BINARY_DOUBLE are typically performed faster than on NUMBER s. BINARY_DOUBLE requires 9 bytes of storage space.
NUMBER( precision, scale ) and NUMERIC( precision, scale )	Variable-length number; precision is the maximum number of digits (in front of and behind a decimal point, if used) that may be used for the number. The maximum precision supported is 38; scale is the maximum number of digits to the right of a decimal point (if used). If neither precision nor scale is specified, then a number with up to a precision and scale of 38 digits may be supplied (meaning you can supply a number with up to 38 digits, and any of those 38 digits may be in front of or behind the decimal point).
DEC and DECIMAL	Subtype of NUMBER . A fixed-point decimal number with up to 38 digits of decimal precision.
DOUBLE PRECISION and FLOAT	Subtype of NUMBER . A floating-point number with up to 38 digits of precision.
REAL	Subtype of NUMBER . A floating-point number with up to 18 digits of precision.
INT, INTEGER, and SMALLINT	Subtype of NUMBER . An integer with up to 38 digits of decimal precision.
DATE	Date and time with the century, all four digits of year, month, day, hour (in 24- hour format), minute, and second. May be used to store a date and time between January 1, 4712 B.C. and December 31, 4712 A.D. Default format is specified by the NLS_DATE_FORMAT parameter (for example: DD-MON-RR ).
INTERVAL YEAR[( years_precision )] TO MONTH	Time interval measured in years and months; years_precision specifies the precision for the years, which may be an integer from 0 to 9 (default is 2). Can be used to represent a positive or negative time interval.
INTERVAL DAY[( days_precision )] TO SECOND[( seconds_precision )]	Time interval measured in days and seconds; days_precision specifies the precision for the days, which is an integer from 0 to 9 (default is 2); seconds_precision specifies the precision for the fractional part of the seconds, which is an integer from 0 to 9 (default is 6). Can be used to represent a positive or negative time interval.
TIMESTAMP[( seconds_precision )]	Date and time with the century, all four digits of year, month, day, hour (in 24-hour format), minute, and second; seconds_precision specifies the number of digits for the fractional part of the seconds, which can be an integer from 0 to 9 (default is 6). Default format is specified by the NLS_TIMESTAMP_FORMAT parameter.
TIMESTAMP[( seconds_precision )] WITH TIME ZONE	Extends TIMESTAMP to store a time zone. The time zone can be an offset from UTC, such as ˜-5:0' , or a region name , such as ˜US/Pacific' . Default format is specified by the NLS_TIMESTAMP_TZ_FORMAT parameter.
TIMESTAMP[( seconds_precision )] WITH LOCAL TIME ZONE	Extends TIMESTAMP to convert a supplied datetime to the local time zone set for the database. The process of conversion is known as normalizing the datetime. Default format is specified by the NLS_TIMESTAMP_FORMAT parameter.
CLOB	Variable length single-byte character data of up to 128 terabytes.
NCLOB	Variable length Unicode national character set data of up to 128 terabytes.
BLOB	Variable length binary data of up to 128 terabytes.
BFILE	Pointer to an external file.
LONG	Variable length character data of up to 2 gigabytes. Superceded by CLOB and NCLOB types, but supported for backwards compatibility.
RAW( length )	Variable length binary data of up to length bytes. Maximum length is 2,000 bytes. Superceded by BLOB type, but supported for backwards compatibility.
LONG RAW	Variable length binary data of up to 2 gigabytes. Superceded by BLOB type but supported for backwards compatibility.
ROWID	Hexadecimal string used to represent a row address.
UROWID[( length )]	Hexadecimal string representing the logical address of a row of an index-organized table; length specifies the number of bytes. Maximum length is 4,000 bytes (also default).
REF object_type	Reference to an object type. Similar to a pointer in C.
VARRAY	Variable length array. This is a composite type and stores an ordered set of elements.
NESTED TABLE	Nested table. This is a composite type and stores an unordered set of elements.
XMLType	Stores XML data.
User defined object type	You can define your own object type and create objects of that type.
[1] The BYTE and CHAR keywords only work with Oracle9 i and above. If neither BYTE nor CHAR is specified, the default is BYTE . [1] The BYTE and CHAR keywords only work with Oracle9 i and above. If neither BYTE nor CHAR is specified, the default is BYTE .