Lesson : 7 Data types in Oracle

 

Data types in Oracle Database

Data Type	Description
VARCHAR2(size [BYTE | CHAR])	Variable-length character string having maximum length size bytes or characters. Maximum size is 4000 bytes or characters.
NVARCHAR2(size)	Variable-length Unicode character string having maximum length size characters. Maximum size is  4000 characters.
NUMBER [ (p [, s]) ]	Number having precision p and scale s. The precision p can range from 1 to 38. The scale s can range from -84 to 127.
FLOAT [(p)]	A subtype of the NUMBER data type having precision p. A FLOAT value is represented internally as NUMBER. The precision p can range from 1 to 126 binary digits.
LONG	Character data of variable length up to 2 GB.
DATE	Valid date range from January 1, 4712 BC, to December 31, 9999 AD. The size is fixed at 7 bytes. This data type contains the datetime fields YEAR, MONTH, DAY, HOUR, MINUTE, and SECOND.
BINARY_FLOAT	32-bit floating point number. This data type requires 4 bytes.
BINARY_DOUBLE	64-bit floating point number. This data type requires 8 bytes.
TIMESTAMP [(fractional_seconds_precision)]	Year, month, and day values of date, as well as hour, minute, and second values of time, where fractional_seconds_precision is the number of digits in the fractional part of the SECOND datetime field. Accepted values of fractional_seconds_precision are 0 to 9. The default is 6.
TIMESTAMP [(fractional_seconds)] WITH TIME ZONE	All values of TIMESTAMP as well as time zone displacement value, where fractional_seconds_precision is the number of digits in the fractional part of the SECOND datetime field. Accepted values are 0 to 9.  This data type contains the datetime fields YEAR, MONTH, DAY, HOUR, MINUTE, SECOND, TIMEZONE_HOUR, and TIMEZONE_MINUTE. It has fractional seconds and an explicit time zone.
TIMESTAMP [(fractional_seconds)] WITH LOCAL TIME ZONE	Same as TIMESTAMP WITH TIME ZONE, But here, Data is normalized to the database time zone when it is stored in the database. When the data is retrieved, users see the data in the session time zone.
INTERVAL YEAR [(year_precision)] TO MONTH	Stores a period of time in years and months, where year_precision is the number of digits in the YEAR datetime field. Accepted values are 0 to 9. The default is 2. The size is fixed at 5 bytes.
INTERVAL DAY [(day_precision)] TO SECOND [(fractional_seconds)]	Stores a period of time in days, hours, minutes, and seconds, where day_precision is the maximum number of digits in the DAY datetime field. Accepted values are 0 to 9. The default is 2. fractional_seconds_precision is the number of digits in the fractional part of the SECOND field. Accepted values are 0 to 9. The default is 6. The size is fixed at 11 bytes.
RAW(size)	Raw binary data of length size bytes. Maximum size is 2000 bytes.
LONG RAW	Raw binary data of variable length up to 2 gigabytes.
ROWID	Base 64 string representing the unique address of a row in its table. This data type is primarily for values returned by the ROWID pseudocolumn.
UROWID [(size)]	Base 64 string representing the logical address of a row of an index-organized table. The optional size is the size of a column of type UROWID. The maximum size and default is 4000 bytes.
CHAR [(size [BYTE | CHAR])]	Fixed-length character data of length size bytes or characters. Maximum size is 2000 bytes or characters.
NCHAR[(size)]	Fixed-length character data of length size characters. Maximum size 2000 bytes.
CLOB	A character large object containing single-byte or multibyte characters. Maximum size is 4 GB.
NCLOB	A character large object containing Unicode characters. Maximum size is 4 GB.
BLOB	A binary large object. Maximum size is 4 GB.
BFILE	Contains a locator to a large binary file stored outside the database. Enables byte stream I/O access to external LOBs residing on the database server. Maximum size is 4 gigabytes.