Term: TYPE

Definition:
Each column value and constant has a datatype which is associated with a defined storage format, constraints, and a valid range of values. When creating tables you need to specify a datatype for each of its columns.

Basic Oracle Data Types
char(size)	Maximum size of 2000 bytes. Fixed-length string size, space padded.
nchar(size)	Maximum size of 2000 bytes. Fixed-length NLS string size, space padded.
nvarchar2(size)	Maximum size of 4000 bytes. Variable-length NLS string size.
varchar2(size)	Maximum size of 4000 bytes. Variable-length string size.
long	Maximum size of 2GB. Variable-length string size.
raw	Maximum size of 2000 bytes. Variable-length binary string.
long raw	Maximum size of 2GB. Variable-length binary string.

Character Datatypes
CHAR Datatype

The CHAR datatype stores fixed-length character strings. When a table is created with a CHAR column, you must specify a string length (in bytes or characters) from 1 to 2000 bytes for the column width. The default is 1 byte. Oracle manages the data so that:

When you insert or update a row in the table, the value for the CHAR column has the fixed length.
If you give a shorter value, then the value is blank-padded to the fixed length.

VARCHAR2 and VARCHAR Datatypes
The VARCHAR2 datatype stores variable-length character strings. When a table is created with a VARCHAR2 column, you specify a maximum string length (in bytes or characters) from 1 to 4000 bytes for the column. For each row, data is stored in the column as a variable-length field unless a value exceeds the column's maximum length, in which case an error is returned. Using VARCHAR2 conserves storage space used by the table.

NCHAR and NVARCHAR2 Datatypes
NCHAR and NVARCHAR2 are Unicode datatypes that store Unicode character data. The character set of NCHAR and NVARCHAR2 datatypes can only be either AL16UTF16 or UTF8 and is specified when the database is created as the national character set. Note that both AL16UTF16 and UTF8 are Unicode encoding.

The NCHAR datatype stores fixed-length character strings that correspond to the national character set.
The NVARCHAR2 datatype stores variable length character strings.

When you create a table with an NCHAR or NVARCHAR2 column, the maximum size specified is always in character length semantics. Character length semantics is the default (and only) length semantics for NCHAR or NVARCHAR2.

For example, if national character set is UTF8, then the following statement defines the maximum byte length of 90 bytes (not 30):

CREATE TABLE table_1 (col_1 NCHAR(30));

This statement creates a column with maximum character length of 30. The maximum byte length, however, is the multiple of the maximum character length and the maximum number of bytes for each character.

NCHAR
The maximum length of an NCHAR column is 2000 bytes (2000 characters). The actual data is subject to the maximum byte limit of 2000. The two size constraints must be satisfied simultaneously at run time.

NVARCHAR2
The maximum length of an NVARCHAR2 column is 4000 bytes (4000 characters). The actual data is subject to the maximum byte limit of 4000. Like NCHAR, the two size constraints must be satisfied simultaneously at run time.

LOB Character Datatypes
The LOB datatypes for character data are CLOB and NCLOB. They can store up to 8 terabytes of character data (CLOB) or national character set data (NCLOB).

LONG
The LONG datatype is deprecated, and Oracle recommends that you do not create tables with LONG columns. Use LOB columns (CLOB, NCLOB) instead. LONG columns are supported only for backward compatibility and support for the LONG type may change or be dropped in later versions.

Numeric Datatypes
Numeric datatypes store positive and negative fixed and floating-point numbers, zero, infinity. They also store values that are the undefined result of an operation (such as "not a number" or NAN).

NUMBER Datatype
The NUMBER datatype stores fixed and floating-point numbers. Numbers of nearly any size can be stored and are guaranteed to be portable among different systems operating Oracle, with 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

Floating-Point Numbers
Oracle provides two numeric datatypes exclusively for floating-point numbers: BINARY_FLOAT and BINARY_DOUBLE, both of which use binary precision. Note that BINARY_FLOAT and BINARY_DOUBLE are datatypes which store approximate representations of decimal values, rather than exact representations. For example, the value '0.1' cannot be exactly represented by either BINARY_DOUBLE or BINARY_FLOAT.

BINARY_FLOAT Datatype
BINARY_FLOAT is a 32-bit, single-precision floating-point number datatype. Each BINARY_FLOAT value requires 5 bytes, including a length byte.

BINARY_DOUBLE Datatype
BINARY_DOUBLE is a 64-bit, double-precision floating-point number datatype. Each BINARY_DOUBLE value requires 9 bytes, including a length byte.

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

The standard Oracle date format is DD-MON-YY, for example: '21-MAY-98'. You can change the default format with the parameter NLS_DATE_FORMAT or during a user session with the ALTER SESSION statement. For the time protion of a date, Oracle stores the time in 24-hour format as 'HH:MI:SS'. The default time in a date field is 00:00:00 A.M.

Date arithmetic takes into account the anomalies of various calendars, including the Julian and Gregorian calendars.

LOB Datatypes
The LOB datatypes BLOB, CLOB, NCLOB, and BFILE allow storage of large blocks of unstructured data (such as text, graphic images, video clips, and sound waveforms) in binary or character format.

LOB datatypes differ from LONG and LONG RAW datatypes in several ways:

A table can contain multiple LOB columns but only one LONG column.
A table containing one or more LOB columns can be partitioned, but a table containing a LONG column cannot be partitioned.
The maximum size of a LOB is 8 terabytes, and the maximum size of a LONG is only 2 gigabytes.
LOBs support random access to data, but LONGs support only sequential access.
LOB datatypes (except NCLOB) can be attributes of a user-defined object type but LONG datatypes cannot.
Temporary LOBs that act like local variables can be used to perform transformations on LOB data. Temporary internal LOBs (BLOBs, CLOBs, and NCLOBs) are created in a temporary tablespace and are independent of tables. For LONG datatypes, however, no temporary structures are available.
Tables with LOB columns can be replicated, but tables with LONG columns cannot.

BLOB Datatype
The BLOB datatype stores unstructured binary data in the database. BLOBs can store up to 8 terabytes of binary data.

BLOBs participate fully in transactions. Changes made to a BLOB value by the DBMS_LOB package, PL/SQL, or the OCI can be committed or rolled back. However, BLOB locators cannot span transactions or sessions.

CLOB and NCLOB Datatypes
The CLOB and NCLOB datatypes store up to 8 terabytes of character data in the database. CLOBs store database character set data, and NCLOBs store Unicode national character set data. Storing varying-width LOB data in a fixed-width Unicode character set internally enables Oracle to provide efficient character-based random access on CLOBs and NCLOBs.

CLOBs and NCLOBs participate fully in transactions. Changes made to a CLOB or NCLOB value by the DBMS_LOB package, PL/SQL, or the OCI can be committed or rolled back. However, CLOB and NCLOB locators cannot span transactions or sessions. You cannot create an object type with NCLOB attributes, but you can specify NCLOB parameters in a method for an object type.

BFILE Datatype
The BFILE datatype stores unstructured binary data in flat files outside the database. A BFILE column or attribute stores a file locator that points to an external file containing the data. BFILEs can store up to 8 terabytes of data.

BFILEs are read only and cannot be modified. They support only random (not sequential) reads, and they do not participate in transactions. The operating system (Linux, Windows, etc) must maintain the file integrity, security, and durability for BFILEs. It is the database administrator's responsibility to make sure that the file exists and that Oracle processes have read permissions on the file.

RAW and LONG RAW Datatypes
Note: The LONG RAW datatype is deprecated, and is provided only for backward compatibility with existing applications. Support for the LONG type may change or be dropped in later versions. Use LOB columns (CLOB, NCLOB) instead.

ROWID and UROWID Datatypes
Oracle uses a ROWID datatype to store the address (rowid) of every row in the database.

Physical rowids store the addresses of rows in ordinary tables (excluding index-organized tables), clustered tables, table partitions and subpartitions, indexes, and index partitions and subpartitions.
Logical rowids store the addresses of rows in index-organized tables.

A single datatype called the universal rowid, or UROWID, supports both logical and physical rowids, as well as rowids of foreign tables such as non-Oracle tables accessed through a gateway. A column of the UROWID datatype can store all kinds of rowids.