Oracle Built-in Data Types

Oracle Built-in Data Types

The Oracle server provides several built-in data types to store scalar data, collections, and relationships.

Scalar data types:

Character data: Character data can be stored as either fixed-length or variable-length strings in the database.

Fixed-length character data types, such as CHAR and NCHAR, are stored with padded blanks. NCHAR is a Globalization Support data type that enables the storage of either fixed-width or variable-width character sets. The maximum size is determined by the number of bytes required to store one character, with an upper limit of 2,000 bytes per row. The default is one character or one byte, depending on the character set.

Variable-length character data types use only the number of bytes needed to store the actual column value, and can vary in size for each row, up to 4,000 bytes. VARCHAR2 and NVARCHAR2 are examples of variable-length character data types.

Numeric data type: Numbers in an Oracle database are always stored as variable-length data. They can store up to 38 significant digits. Numeric data types require:

•One byte for the exponent

•One byte for every two significant digits in the mantissa

•One byte for negative numbers if the number of significant digits is less than 38 bytes

DATE data type: The Oracle server stores dates in fixed-length fields of seven bytes. An Oracle DATE always includes the time.

TIMESTAMP data type: This data type stores the date and time including fractional seconds up to nine decimal places. TIMESTAMP WITH TIME ZONE and TIMESTAMP WITH LOCAL TIME ZONE can use time zones to factor items such as daylight savings time. TIMESTAMP and TIMESTAMP WITH LOCAL TIME ZONE can be used in primary keys, TIMESTAMP WITH TIME ZONE cannot.

RAW data type: This data type enables the storage of small binary data. The Oracle server does not perform character set conversion when RAW data is transmitted across machines in a network or if RAW data is moved from one database to another using Oracle utilities. The number of bytes needed to store the actual column value, and can vary in size for each row, up to 2,000 bytes.

LONG, LONG RAW, and Large Object (LOBs) data types:

Oracle provides six data types for storing LOBs:

•CLOB and LONG for large fixed-width character data

•NCLOB for large fixed-width national character set data

•BLOB and LONG RAW for storing unstructured data

•BFILE for storing unstructured data in operating system files

LONG and LONG RAW data types were previously used for unstructured data, such as binary images, documents, or geographical information, and are primarily provided for backward compatibility. These data types are superseded by the LOB data types. LOB data types are distinct from LONG and LONG RAW, and they are not interchangeable. LOBs will not support the LONG application programming interface (API), and vice versa

It is beneficial to discuss LOB functionality in comparison to the older types (LONG and LONG RAW). Below, LONGs refers to LONG and LONG RAW, and LOBs refer to all LOB data types.

LOBs store a locator in the table and the data elsewhere, unless the size is less than the maximum size for a VARCHAR2 data type, which is 4,000 bytes; LONGs store all data in-line. In addition, LOBs allow data to be stored in a separate segment and tablespace, or in a host file.

LOBs support object type attributes (except NCLOBs) and replication; LONGs do not.

LONGs are primarily stored as chained row pieces, with a row piece in one block pointing to the next row piece stored in another block. Therefore, they need to be accessed sequentially. In contrast, LOBs support random piece-wise access to the data through a file-like interface.

ROWID and UROWID data types:

ROWID is a data type that can be queried along with other columns in a table. It has the following characteristics:

•ROWID is a unique identifier for each row in the database.

•ROWID is not stored explicitly as a column value.

•Although the ROWID does not directly give the physical address of a row, it can be used to locate the row.

•ROWID provides the fastest means of accessing a row in a table.

•ROWIDs are stored in indexes to specify rows with a given set of key values.

With release Oracle8.1, the Oracle server provides a single datatype called the universal ROWID or UROWID. It supports ROWIDs of foreign tables (non-Oracle tables) and can store all kinds of ROWIDs. For example: A UROWID datatype is required to store a ROWID for rows stored in an index-organized table (IOT). The value of the parameter COMPATIBLE must be set to Oracle8.1 or higher to use UROWID.

 

Collection data types:

Two types of collection data types are available to store data that is repetitive for a given row in a table. Prior to Oracle8i, the Objects option was needed to define and use collections. A brief discussion of these types follows.

Varying arrays (VARRAY): Varying arrays are useful to store lists that contain a small number of elements, such as phone numbers for a customer.

VARRAYs have the following characteristics:

•An array is an ordered set of data elements.

•All elements of a given array are of the same data type.

•Each element has an index, which is a number corresponding to the position of the element in the array.

•The number of elements in an array determines the size of the array.

•The Oracle server allows arrays to be of variable size, which is why they are called VARRAYs, but the maximum size must be specified when declaring the array type.

Nested tables: Nested tables provide a means of defining a table as a column within a table. They can be used to store sets that may have a large number of records such as number of items in an order.

Nested tables generally have the following characteristics:

•A nested table is an unordered set of records or rows.

•The rows in a nested table have the same structure.

•Rows in a nested table are stored separate from the parent table with a pointer from the corresponding row in the parent table.

•Storage characteristics for the nested table can be defined by the database administrator.

•There is no predetermined maximum size for a nested table.

Relationship data types (REFs):

Relationship types are used as pointers within the database. The use of these types requires the Objects option. As an example, each item that is ordered could point to or reference a row in the PRODUCTS table, without having to store the product code.

Oracle user-defined data types:

The Oracle server allows a user to define abstract data types and use them within the application.