Introduction to Oracle LOBs

Introduction

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.

DO NOT use LONG and LONG RAW

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:

• When using LOBs, you can create one or more LOB columns per table whereas you are restricted to only one LONG or LONG RAW column in a table.

• A LOB column can contain a length of up to 4 Gigabytes of data as compared to a 2 Gigabyte limit on LONG.

• LOBs provide procedures that allow for random access to its data while with a LONG, you are forced to perform a sequential read of the data from beginning to end.

• When you query from a LOB column, Oracle only returns the LOB locator - not the entire value of the LOB. LONGs, although, return the entire value contained within the LONG column.

• When inserting into a LOB column, the actual value of the LOB is stored in a separate segment (with the exception of in-line LOBS, explained a bit later), and only the LOB locator is stored in the row. This makes the process of storing unstructured data more efficient from a storage as well as query perspective. With LONG or LONG RAW columns, the entire contents of the data is stored in-line with the rest of the table row data.

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 orpointer 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 ofempty.

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:

• CLOB - A character LOB. Used to store single-byte character data.
• BLOB - A binary LOB. Used to store binary, raw data.
• NCLOB - A LOB used to store multi-byte character data that corresponds

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.

• BFILE - Short for Binary File. The BFILE LOB holds references to large binary data stored as physical files in the OS outside the database.

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 itnon-null by populating it with a lob locator. To perform this, use the built-in functionsEMPTY_CLOB() for CLOBs and BCLOBs, EMPTY_BLOB() for BLOBs. For BFILEs, use theBFILENAME() 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 packageDBMS_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 theSTORE 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

• Cannot be a part of a clustered table.

• Cannot be used in the following part of a SQL statement:

  • GROUP BY
  • ORDER BY
  • SELECT DISTINCT
  • joins
  • aggregate functions

• Cannot be analyzed using the ANALYZE command.

• Cannot be included in a partiioned index-organized table.

• Cannot use them in VARRAYs.

• Cannot use NCLOBs as object attributes.