| Type | Description |
| CHAR[(length [BYTE | CHAR])] | Fixed-length character data of length bytes or characters and padded with trailing spaces. Maximum length is 2,000 bytes. |
| VARCHAR2(length [BYTE | CHAR]) | 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 | Stores a single precision 32-bit floating-point number. Operations involving BINARY_FLOAT are typically performed faster than on NUMBERs. BINARY_FLOAT requires 5 bytes of storage space. |
| BINARY_DOUBLE | Stores a double precision 64-bit floating-point number. Operations involving BINARY_DOUBLE are typically performed faster than on NUMBERs. 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. |
| 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. |
Quote from:
Oracle PL/SQL For Dummies (Paperback)
by Michael Rosenblum (Author), Paul Dorsey (Author)
# Paperback: 414 pages
# Publisher: For Dummies (June 13, 2006)
# Language: English
# ISBN-10: 0764599577
# ISBN-13: 978-0764599576
| 10.1.Introduction | ||||
| 10.1.1. | Oracle SQL Types | |||
| 10.1.2. | Change datatype of an existing column | |||