A datatypes is used to specify the type of data a table column can store whether it's numeric, char, date, float, binary or RAW. Also date types is used in an argument of a procedure or function. These properties cause Oracle to treat values of one datatype differently from values of another datatype.
Oracle SQL Types
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.
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).
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 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 date time to the local time zone set for the database. The process of conversion is known as normalizing the date time. 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 1
28 terabytes.
RAW(length):Variable length binary data of up to length bytes. Maximum length is 2,000 bytes. Super ceded by BLOB type, but supported for backwards.
