SQL New Data Types

Oracle supports many fundamental datatypes that you can use when creating a relational database table and its columns.And the following sections describe the most commonly used Oracle datatypes.

CHAR (size) - Stores fixed-length character strings up to 2,000 bytes

VARCHAR2 (size) - Stores variable-length character strings up to 4,000 bytes

NUMBER (precision, scale) - Stores any type of number

DATE - Stores dates and times

CLOB - Stores single-byte character large objects up to four gigabytes

BLOB - Stores binary large objects up to four gigabytes

Enhancement of Existing Datatypes and Some New Datatypes

Oracle8 has increased the maximum size of some existing Oracle datatypes

• The capacity of a RAW column has been increased from 255 bytes to 2,000 bytes.

• The capacity of a VARCHAR2 column has been increased from 2,000 characters to 4,000 characters. In addition, a single table can now have up to 1,000 columns.

Two new datatypes, NCHAR and NCHAR VARYING, can be used with Oracle8. These datatypes can be used for national character sets that require more than one byte per character; this is needed for many' Asian languages.

Large Objects, or LOBs

Oracle8 provides much more support in the area of large objects. in fact, a new category of datatype's, called LOB, or Large Object, is introduced with Oracle8.

LOBs can be one of four datatypes

A CLOB column stores character objects such as a documents.

A BLOB column stores large binary objects such as graphics, video clips, or sound files.

A BFILE column stores file pointers to LOBs managed by file systems external to the database. For example, a BFILE column might be a list of filename references for photos stored on a CD-ROM.

NCLOBs, which store character data using a multibyte national character set (primarily designed to support Asian languages)

There are several advantages to using LOBs. First, a LOB can accommodate 4GB or twice the capacity of the LONG RAW Column. Second, a table can contain more than one LOB column, whereas it can contain only a single LONG or LONG RAW column. Also, the data stored in a LOB column is kept in a storage area separate from the table that contains the LOB column, resulting in better overall performance. Let's look at some examples of how these datatypes can be used.

Oracle's National Language Support

Character Datatypes

Oracle National Language Support (NLS) features allow databases to store and manipulate character data in many languages, Some languages have character sets that require several bytes for each character. The special Oracle datatypes NCHAR, NVARCHAR2, and CLOB datatypes, respectively.

The following section explains several other important LOB characteristics in contrast to some older Oracle large object datatypes.

Contrasting LOBS with Older Oracle Large Object Datatypes

For backward compatibility, Oracle8 continues to support older Oracle datatypes designed for large objects, such as LONG and LONG RAW.

However, Oracle8's newer LOB dlatatypes have several advantages over the older Oracle large dlatatypes

A table can have multiple CLOB, BLOB, and BFILE columns. In contrast, a table can have only one LONG or LONG RAW column.

A table stores only small locators (pointers) for the LOBs in a column rather than the actual large objects themselves. In contrast, a table stores data for a LONG column within the table itself.

A LOB column can have storage characteristics independent from those of the encompassing table, making it easier to address the large disk requirements typically associated with LOBs. For example, it's possible to separate the storage of primary table data and related LOBs to different physical locations (for example, disk drives). In contrast, a table physically stores the data for a LONG column in the same storage area that contains all other table data.

Domain Name Search