DBA Tips Archive for Oracle


[an error occurred while processing this directive]

No Title

[an error occurred while processing this directive]

by Michael New, MichaelNew@earthlink.net, Gradation LLC



Oracle has for many years supported the notion of storing large objects (unstructured data) within the database by using the LONG and LONG RAW datatypes. Significant changes, although, where made in Oracle8 with the introduction of the Large Object (LOB) datatypes. Oracle introduced LOBs to support the growing demand for storing large, unstructured data, such as video, audio, photo images, etc within the database. The LOB datatypes allow you to store up to 4 Gigabytes of data. Although the LONG and LONG RAW datatype still exists in current versions of Oracle, it is highly recommended to make use of Oracle's new LOB types for storing unstructured data as LONG and LONG RAW will be de-supported in future releases.

This document provides a brief introduction into Oracle LOBs. I will attempt to explain what LOBs are, how and why to use them, along with ways in which to manipulate LOB data. Detailed examples will not be provided in this document, but rather exist in the Oracle LOB Examples Collection in the LOBs section of my Oracle DBA Tips section.


Oracle LOBs offer many more advantages to the developer/DBA than using LONG or LONG RAW. Just recognizing the key differences between the data types should indicate why you would use a LOB instead of a LONG or LONG RAW. These key differences are:

About LOBs

The data stored within a LOB is called the LOB's value. As far as Oracle is concerned, a LOB's value is unstructured and cannot be queried against. LOBs can be stored along with other row data or separate from row data (depending on the size of the LOB value). Regardless of how the data is stored, every LOB was a LOB Locator associated with it which can be viewed as a handle or pointer to the actual location of the LOB value. Oracle8 introduced two new functions with SQL DML: EMPTY_CLOB(), and EMPTY_BLOB. These two functions allow initialization of NULL or non-NULL LOB columns to a status of empty.

LOB Types

There are essentially two categories of LOBs defined in Oracle - Internal LOBs and External LOBs.

Internal LOBs

Internal LOBs are stored within the database, as columns in a table and participate in the transaction model of the server. There are three types of internal LOBs:

External LOBs

External LOBs are stored outside of the database as operating system files. Only a reference to the actual OS file is stored in the database. External LOBs do not participate in transactions. The only external LOB data type in Oracle 8i/9i is called a BFILE.

Difference Between Internal and External LOBs

It is important to understand the differences between internal and external LOBs. The following list are some of the more important differences:

  1. Types of internal LOBs include CLOB, NCLOB and BLOB. The only external LOB is a BFILE.

  2. Internal LOBs are stored in the database as columns in a table while external LOBs are stored outside the database in operating system files.

  3. Internal LOBs take part in the transaction model of the server. In the event of a database failure, internal LOBs can be recovered and changes made to them can be committed or even rolled back. External LOBs do not participate in transactions. The BFILE type allows only read access to the operating system files. All changes to external LOBs must be done outside of the database through the underlying OS.

  4. Internal LOBs use copy semantics. That is when you INSERT or UPDATE a LOB with a LOB from another row in a table, the LOB locator as well as the LOB value are copied to the row. External LOBs on the other hand use reference semantics. That is only the BFILE location is copied and not the actual operating system file.

  5. Each internal LOB column has a distinct LOB locator for each row and a distinct copy of the LOB value. Each BFILE column has its own BFILE locator for each row. However you could have two rows in the table that contain BFILE locators pointing to the same operating system file.

LOB Locator

Associated with every LOB is a lob locator. A lob locator is a pointer to the actual location of the LOB value. The locator associated with internal LOBs is called a LOB locator while the locator associated with external LOBs is called a BFILE locator. When storing data in a LOB column, you are also storing a LOB locator with it. This LOB locator is what is returned to you when you select the LOB column. The actual LOB value can then be retrieved using this locator.

A LOB column can be initialized to either a NULL value or made empty. The basic different has to do with the lob locator. If a LOB column is set to NULL, the LOB has no locator or value stored. The value of the LOB will be NULL. An empty LOB, however, has a lob locator as well as a lob value of length 0 stored in the column. Before attempting to starting writing data to a LOB column using the various programming interfaces (PL/SQL, OCI, Java, etc.), you will need to make it non-null by populating it with a lob locator. To perform this, use the built-in functions EMPTY_CLOB() for CLOBs and BCLOBs, EMPTY_BLOB() for BLOBs. For BFILEs, use the BFILENAME() method to initialize a BFILE column to point to an OS file. Keep in mind that when you query a LOB column, only the lob locator is returned to you. This lob locator is used by the various programming interfaces to manipulate the lob value.

Manipulating LOBs

Probably one of the more popular ways in which to manipulate a LOB is through PL/SQL. PL/SQL provides a means to manipulate LOBs via the package DBMS_LOB. The DBMS_LOB package provides procedures and functions which allow manipulation of specific parts as well as complete internal LOBs along with read-only operations on BFILEs. An important concept in working with and manipulating LOBs is that all DBMS_LOB routines work based on LOB locators.

In-line LOBs

Internal LOBs (those created as part of a table like CLOB, NCLOB, and BLOB) can be stored in either in-line or out of line mode. The default is in-line mode. The mode can only be specified at the time of table creation.

Using in-line mode, if the LOB (data + locator) < 4000 bytes, then the LOB is stored in-line. If the LOB (data + locator) > 4000 bytes, then it is stored out-of-line. It is possible to disable in-line storage of LOBs smaller than 4K by using the STORE AS (disable storage in row) clause at creation time. In this case, only the locator is stored in the row and the data is stored in the LOB segment.

Restrictions on LOBs

About the Author

Jeffrey Hunter is an Oracle Certified Professional, Java Development Certified Professional, Author, and an Oracle ACE. Jeff currently works as a Senior Database Administrator for The DBA Zone, Inc. located in Pittsburgh, Pennsylvania. His work includes advanced performance tuning, Java and PL/SQL programming, developing high availability solutions, capacity planning, database security, and physical / logical database design in a UNIX / Linux server environment. Jeff's other interests include mathematical encryption theory, tutoring advanced mathematics, programming language processors (compilers and interpreters) in Java and C, LDAP, writing web-based database administration tools, and of course Linux. He has been a Sr. Database Administrator and Software Engineer for over 20 years and maintains his own website site at: http://www.iDevelopment.info. Jeff graduated from Stanislaus State University in Turlock, California, with a Bachelor's degree in Computer Science and Mathematics.

Copyright (c) 1998-2018 Jeffrey M. Hunter. All rights reserved.

All articles, scripts and material located at the Internet address of http://www.idevelopment.info is the copyright of Jeffrey M. Hunter and is protected under copyright laws of the United States. This document may not be hosted on any other site without my express, prior, written permission. Application to host any of the material elsewhere can be made by contacting me at jhunter@idevelopment.info.

I have made every effort and taken great care in making sure that the material included on my web site is technically accurate, but I disclaim any and all responsibility for any loss, damage or destruction of data or any other property which may arise from relying on it. I will in no case be liable for any monetary damages arising from such loss, damage or destruction.

Last modified on
Thursday, 08-Mar-2012 21:16:19 EST
Page Count: 75577