Oracle large object (LOB) support Oracle Tips by Burleson Consulting

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 locations.

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 Datatypes

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 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 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 gigabytes.

* 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 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.

Need an Oracle Health Check? Do you have bad performance after an upgrade?   Need to certify that your database follows best practices? BC Oracle performance gurus can quickly certify every aspect of your Oracle database and provide a complete verification that your database is fully optimized.