Oracle large object (LOB) support
Oracle Database Tips by Donald BurlesonConsulting
Oracle has introduced many new multi-media datatypes,
or LOB datatypes if you prefer, that make handling picture, video,
audio or large character data sets much easier. In earlier releases
before Oracle8 all multi-media types were placed into LONG RAW
datatypes. With all of the limitations placed on LONG RAW by Oracle
it was usually easier to keep the large object type data external to
the database and just use Oracle as an indexing system to the file
With Oracle8 this has all changed. Now the
developer has several new LOB datatypes as well as procedures,
cartridges and other developmental tools to help them cope with LOB
types. Going out for the LOB has never been easier.
Internal LOB Multi-Media
Oracle has two basic types of LOB datatypes, these
are internal and external. Internal types are BLOB (Binary Large
Object), CLOB (Character (single - byte) large object) and NCLOB
(National Character (multi-byte) large object) and the external
datatype (actually a pointer construct to an external file location
a "locator"), BFILE.
All of the internal LOB datatypes can be either
stored inline (up to a maximum of 4000 bytes) or in an external LOB
storage area (up to 4 gigabytes or the maximum datafile size on your
platform) that is specified by a LOB storage declaration.
Differences Between LOB and LONG RAW
* Multiple LOBs are allowed in a single row.
* LOBs can be attributes of a user-defined
* The LOB locator is stored in the table column,
either with or without the actual LOB value; BLOB, NCLOB, and CLOB
values can be stored in separate tablespaces and BFILE data is
stored in an external file on the server.
* When you access a LOB column, it is the locator
which is returned.
* A LOB can be up to four gigabytes in size. BFILE
maximum size is operating system dependent, but cannot exceed four
* LOBs permit efficient, random, piece-wise access
to and manipulation of data.
* With the exception of NCLOB, you can define one
or more LOB attributes in an object.
* You can declare LOB bind variables.
* You can select LOB columns and LOB attributes.
* You can insert a new row or update an existing
row that contains one or more LOB columns and/or an object with one
or more LOB attributes. (You can set the internal LOB value to NULL,
empty, or replace the entire LOB with data. You can set the BFILE to
NULL or so that it points to a different file.)
* You can update a LOB row/column intersection or
a LOB attribute with another LOB row/column intersection or LOB
* You can delete a row containing a LOB column or
LOB attribute and thereby also delete the LOB value. Note that for
BFILEs, the actual operating system file is not deleted.