Call now: 252-767-6166  
Oracle Training Oracle Support Development Oracle Apps

 E-mail Us
 Oracle Articles
New Oracle Articles

 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog

 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Oracle Support

 SQL Tuning

 Oracle UNIX
 Oracle Linux
 Remote s
 Remote plans
 Application Server

 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S


 Consulting Staff
 Consulting Prices
 Help Wanted!


 Oracle Posters
 Oracle Books

 Oracle Scripts

Don Burleson Blog 









Oracle large object (LOB) support

Oracle Database Tips by Donald Burleson

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.



Oracle Training at Sea
oracle dba poster

Follow us on Twitter 
Oracle performance tuning software 
Oracle Linux poster


Burleson is the American Team

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum.

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:  

and include the URL for the page.


Burleson Consulting

The Oracle of Database Support

Oracle Performance Tuning

Remote DBA Services


Copyright © 1996 -  2020

All rights reserved by Burleson

Oracle ® is the registered trademark of Oracle Corporation.