DATABASE DATATYPES

DATABASE DATATYPES

Each column value and constant in a SQL statement has a datatype, which is associated with a specific storage format, constraints, and a valid range of values. When you create a table, you must specify a datatype for each of its columns.

1. Character Datatypes

• CHAR Datatype
• VARCHAR2 and VARCHAR Datatypes
• NCHAR and NVARCHAR2 Datatypes
• LONG Datatype

2. NUMBER Datatype

3. DATE Datatype 

CHARACTER DATATYPES

The character datatypes store character (alphanumeric) data in strings, with byte values corresponding to the character encoding scheme (generally called a character set or code page).

The database's character set is established when you create the database, and never changes. Examples of character sets are 7-bit ASCII (American Standard Code for Information Interchange), EBCDIC (Extended Binary Coded Decimal Interchange Code), Code Page 500, and Japan Extended UNIX. Oracle supports both single-byte and multibyte encoding schemes.

NUMBER DATATYPES

The NUMBER datatype stores fixed and floating-point numbers. Numbers of virtually any magnitude can be stored and are guaranteed portable among different systems operating Oracle, up to 38 digits of precision. The following numbers can be stored in a NUMBER column:

¨      positive numbers in the range 1 x 10^-130 to 9.99..9 x 10¹²⁵ (with up to 38 significant digits)

¨      negative numbers from -1 x 10^-130 to 9.99..99 x 10¹²⁵ (with up to 38 significant digits)

¨      zero

¨      positive and negative infinity (generated only by importing from an Oracle Version 5 database)

DATE DATATYPE

The DATE datatype stores point-in-time values (dates and times) in a table. The DATE datatype stores the year (including the century), the month, the day, the hours, the minutes, and the seconds (after midnight).

Oracle can store dates in the Julian era, ranging from January 1, 4712 BCE through December 31, 4712 CE (Common Era). Unless BCE ('BC' in the format mask) is specifically used, CE date entries are the default.

Oracle uses its own internal format to store dates. Date data is stored in fixed-length fields of seven bytes each, corresponding to century, year, month, day, hour, minute, and second.

For input and output of dates, the standard Oracle default date format is DD-MON-YY, as below:

'13-NOV-92'

Table below summarizes the characteristics of each Oracle Data type.

Table Summary of Oracle Built-In Datatypes

Datatype	Description	Column Length and Default
CHAR (size)	Fixed-length character data of length size bytes.	Fixed for every row in the table (with trailing blanks); maximum size is 2000 bytes per row, default size is 1 byte per row. Consider the character set (one-byte or multibyte) before setting size.
VARCHAR2 (size)	Variable-length character data. A maximum size must be specified.	Variable for each row, up to 4000 bytes per row. Consider the character set (one-byte or multibyte) before setting size.
NCHAR(size)	Fixed-length character data of length size characters or bytes, depending on the national character set.	Fixed for every row in the table (with trailing blanks). Column size is the number of characters for a fixed-width national character set or the number of bytes for a varying-width national character set. Maximum size is determined by the number of bytes required to store one character, with an upper limit of 2000 bytes per row. Default is 1 character or 1 byte, depending on the character set.
NVARCHAR2 (size)	Variable-length character data of length size characters or bytes, depending on national character set. A maximum size must be specified.	Variable for each row. Column size is the number of characters for a fixed-width national character set or the number of bytes for a varying-width national character set. Maximum size is determined by the number of bytes required to store one character, with an upper limit of 4000 bytes per row. Default is 1 character or 1 byte, depending on the character set.
LONG	Variable-length character data.	Variable for each row in the table, up to 231 - 1 bytes, or 2 gigabytes, per row.
NUMBER (p, s)	Variable-length numeric data. Maximum precision p and/or scale s is 38.	Variable for each row. The maximum space required for a given column is 21 bytes per row.
DATE	Fixed-length date and time data, ranging from January 1, 4712 BCE to December 31, 9999 CE ("A.D.")	Fixed at 7 bytes for each row in the table. Default format is a string (such as DD-MON-YY) specified by NLS_DATE_FORMAT parameter.
RAW (size)	Variable-length raw binary data. A maximum size must be specified.	Variable for each row in the table, up to 2000 bytes per row.
LONG RAW	Variable-length raw binary data.	Variable for each row in the table, up to 231 - 1 bytes, or 2 gigabytes, per row.
BLOB	Binary data.	Up to 232 - 1 bytes, or 4 gigabytes.
CLOB	Single-byte character data.	Up to 232 - 1 bytes, or 4 gigabytes.
NCLOB	Single-byte or fixed- or variable-width multibyte national character set (NCHAR) data.	Up to 232 - 1 bytes, or 4 gigabytes.
BFILE	Binary data stored in an external file.	Up to 232 - 1 bytes, or 4 gigabytes.
ROWID	Binary data representing a physical row address.	Fixed at 10 bytes (extended rowid) or 6 bytes (restricted rowid) for each row in the table.
UROWID	Binary data representing any type of row address: physical, logical, or foreign.	Up to 4000 bytes (but for a logical rowid, only 3950 bytes can be used for the primary key). The default is 4000 bytes.