Datatypes - Expert Oracle Database Architecture - page 509

•

INTERVAL YEAR TO MONTH : This is a fixed-width 5-byte datatype that stores a duration of time,
in this case as a number of years and months. You may use intervals in date arithmetic to add
or subtract a period of time from a DATE or the TIMESTAMP types.

•

INTERVAL DAY TO SECOND : This is a fixed-width 11-byte datatype that stores a duration of time,
in this case as a number of days and hours, minutes, and seconds, optionally with up to
9 digits of fractional seconds.

•

BLOB : This datatype permits for the storage of up to 4GB of data in Oracle9 i and before or
(4 gigabytes - 1) * (database block size) bytes of data in Oracle 10 g and above. BLOB s contain
“binary” information that is not subject to character set conversion. This would be an
appropriate type in which to store a spreadsheet, a word processing document, image files,
and the like.

•

CLOB : This datatype permits for the storage of up to 4GB of data in Oracle9 i and before or
(4 gigabytes -1) * (database block size) bytes of data in Oracle 10 g and above. CLOB s contain
information that is subject to character set conversion. This would be an appropriate type in
which to store large plain-text information. Note that I said large plain-text information; this
datatype would not be appropriate if your plain text data is 4,000 bytes or less—for that you
would want to use the VARCHAR2 datatype.

•

NCLOB : This datatype permits for the storage of up to 4GB of data in Oracle9 i and before or
(4 gigabytes - 1) * (database block size) bytes of data in Oracle 10 g and above. NCLOB s store
information encoded in the national character set of the database and are subject to character
set conversions just as CLOB s are.

•

BFILE : This datatype permits you to store an Oracle directory object (a pointer to an operating
system directory) and a file name in a database column and to read this file. This effectively
allows you to access operating system files available on the database server in a read-only
fashion, as if they were stored in the database table itself.

•

ROWID : A ROWID is effectively a 10-byte address of a row in a database. Sufficient information is
encoded in the ROWID to locate the row on disk, as well as identify the object the ROWID points
to (the table and so on).

•

UROWID : A UROWID is a universal ROWID and is used for tables—such as IOTs and tables accessed
via gateways to heterogeneous databases—that do not have fixed ROWID s. The UROWID is a
representation of the primary key value of the row and hence will vary in size depending on
the object to which it points.

Many types are apparently missing from the preceding list, such as INT , INTEGER , SMALLINT , FLOAT , REAL , and
others. These types are actually implemented on top of one of the base types in the preceding list—that is, they are
synonyms for the native Oracle type. Additionally, datatypes such as XMLType , SYS.ANYTYPE , and SDO_GEOMETRY are not
listed because we will not cover them in this topic. They are complex object types comprising a collection of attributes
along with the methods (functions) that operate on those attributes. They are made up of the basic datatypes listed
previously and are not truly datatypes in the conventional sense, but rather an implementation, a set of functionality,
that you may make use of in your applications.
Now, let's take a closer look at these basic datatypes.