 |
|
Oracle Tips by Burleson |
Large Object Datatypes
LOBs are similar to LONG and LONG RAW types,
but differ in the following ways:
* Multiple LOBs are allowed in a single row.
* LOBs can be attributes of a user-defined
datatype (object).
* 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
* BFILE data is stored in an external file
on the server.
* When you access a LOB column, it is the
locator that is returned.
* A LOB can be up to 4 GB in size. BFILE
maximum size is operating system-dependent, but cannot exceed 4 GB.
* LOBs permit efficient, random, piecewise
access to, and manipulation of, data.
* You can define one or more LOB datatype
columns in a table.
* 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 or 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 attribute.
* 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.
The LOB datatypes themselves are defined as
follows:
BLOB. Binary large object, usually used to
store graphics, video, or audio data. Maximum length of 4 GB under
Oracle8, 8i, and 9i.
CLOB. Character large object, usually used
to store single-byte character objects such as large text files.
Maximum length of 4 GB under Oracle8, 8i, and 9i.
NCLOB. National character large object,
usually used to store multibyte character data. Maximum length of 4
GB under Oracle8, 8i, and 9i.
BFILE. Binary external file locator. This
probably varies in size depending on the value of the directory and
filename placed into it. In empirical testing, with a directory
specification of ‘GIF_FILES’, which contained the directory value
‘e:\Oracle3\Ortest1\Bfiles’ and an average name length of 10 for the
actual external files, this column showed a length of 40.
See Code Depot
www.oracle-script.com |
