Skip Headers

Oracle® Database Application Developer's Guide - Large Objects
10g Release 1 (10.1)

Part Number B10796-01
Go to Documentation Home
Go to Book List
Book List
Go to Table of Contents
Go to Index
Go to Master Index
Master Index
Go to Feedback page

Go to previous page
Go to next page
View PDF

Working with LOBs

This chapter describes the usage and semantics of LOBs that you need to be familiar with to use LOBs in your application. Various techniques for working with LOBs are covered.

Most of the discussions in this chapter regarding persistent LOBs assume that you are dealing with LOBs in tables that already exist. The task of creating tables with LOB columns is typically performed by your database administrator. See Chapter 4, "LOBs in Tables" of this guide for details on creating tables with LOB columns.

This chapter includes the following sections:

LOB Column States

The techniques you use when accessing a cell in a LOB column differ depending on the state of the given cell. A cell in a LOB Column can be in one of the following states:

Locking a Row Containing a LOB

You can lock a row containing a LOB to prevent other database users from writing to the LOB during a transaction. To lock a row containing a LOB, specify the FOR UPDATE clause when you select the row. While the row is locked, other users cannot lock or update a the LOB, until you end your transaction.

Opening and Closing LOBs

The LOB APIs include operations that enable you to explicitly open and close a LOB instance. You can open and close a persistent LOB instance of any type: BLOB, CLOB, NCLOB, or BFILE. You open a LOB to achieve one or both of the following results:

If you open a LOB, then you must close the LOB at some point later in your session. This is the only requirement for an open LOB. While a LOB instance is open, you can perform as many operations as you want on the LOB--provided the operations are allowed in the given mode.

See Also:

"Opening Persistent LOBs with the OPEN and CLOSE Interfaces" for details on usage of these APIs.

LOB Locator and LOB Value

There are two techniques that you can use to access and modify LOB values:

Using the Data Interface for LOBs

You can perform bind and define operations on CLOB and BLOB columns in C applications using the data interface for LOBs in OCI. Doing so, enables you to insert or select out data in a LOB column without using a LOB locator as follows:

Using the LOB Locator to Access and Modify LOB Values

The value of a LOB instance stored in the database can be accessed through a LOB locator, a reference to the location of the LOB value. Database tables store only locators in CLOB, BLOB, NCLOB and BFILE columns. Note the following with respect to LOB locators and values:

LOB Locators and BFILE Locators

There are some differences between the semantics of locators for LOB types BLOB, CLOB, and NCLOB; and the semantics of locators for the BFILE type that you need to be aware of:

Regardless of where the value of a LOB is stored, a locator is stored in the table row of any initialized LOB column. Note that when the term locator is used without an identifying prefix term, it refers to both LOB locators and BFILE locators. Also, when your select a LOB from a table, the LOB returned is always a temporary LOB. For more information on working with locators for temporary LOBs, see "LOBs Returned from SQL Functions".

Initializing a LOB Column to Contain a Locator

Any LOB instance that is NULL does not have a locator. Before you can pass a LOB instance to any LOB API routine, the instance must contain a locator. For example, you can select a NULL LOB from a row, but you cannot pass the instance to the PL/SQL DBMS_LOB.READ procedure. The following sub-sections describe how to initialize a persistent LOB column and how to initialize a BFILE column.

Initializing a Persistent LOB Column

Before you can start writing data to a persistent LOB using the supported programmatic environment interfaces (PL/SQL, OCI, OCCI, Pro*C/C++, Pro*COBOL, Visual Basic, Java, or OLEDB), the LOB column/attribute must be made non-null, that is, it must contain a locator.

You can accomplish this by initializing the persistent LOB to empty in an INSERT/UPDATE statement using the functions EMPTY_BLOB for BLOBs or EMPTY_CLOB for CLOBs and NCLOBs.


You can use SQL to populate a LOB column with data even if it contains a NULL value.

See Also:

Chapter 4, "LOBs in Tables" for more information on initializing LOB columns.

Running the EMPTY_BLOB() or EMPTY_CLOB() function in and of itself does not raise an exception. However, using a LOB locator that was set to empty to access or manipulate the LOB value in any PL/SQL DBMS_LOB or OCI routine will raise an exception.

Valid places where empty LOB locators may be used include the VALUES clause of an INSERT statement and the SET clause of an UPDATE statement.

The following INSERT statement:

Similarly, the LOB attributes for the ad_header column in print_media can be initialized to NULL or set to empty as shown in the following.

INSERT INTO print_media (product_id, ad_id, ad_header) 
    VALUES (101, 1, adheader_typ('AD FOR ORACLE', sysdate, 
    'Have Grid', EMPTY_BLOB()));


You cannot initialize a LOB object attribute with a literal.

See Also:


Before you can access BFILE values using LOB APIs, the BFILE column or attribute must be made non-null. You can initialize the BFILE column to point to an external operating system file by using the BFILENAME() function.

See Also:

"Accessing BFILEs" for more information on initializing BFILE columns.

Accessing LOBs

You can access a LOB instance using the following techniques:

Accessing a LOB Using SQL

Support for columns that use LOB datatypes is built into many SQL functions. This support enables you to use SQL semantics to access LOB columns in SQL. In most cases, you can use the same SQL semantics on a LOB column that you would use on a VARCHAR2 column.

See Also:

For details on SQL semantics support for LOBs, see Chapter 9, "SQL Semantics and LOBs".

Accessing a LOB Using the Data Interface

You can select a LOB directly into CHAR or RAW buffers using the LONG-to-LOB API in OCI and PL/SQL. In the following PL/SQL example, AD_FINALTEXT is selected into a VARCHAR buffer final_ad.

    final_ad VARCHAR(32767);
    SELECT AD_FINALTEXT INTO final_ad FROM print_media
        WHERE PRODUCT_ID= 2056 and AD_ID= 12001 ; 
    /* more calls to read final_ad */

See Also:

For more details on accessing LOBs using the data interface, see Chapter 13, "Data Interface for Persistent LOBs".

Accessing a LOB Using the Locator Interface

You can access and manipulate a LOB instance by passing the LOB locator to the LOB APIs supplied with the database. An extensive set of LOB APIs is provided with each supported programmatic environment. In OCI, a LOB locator is mapped to a locator pointer which is used to access the LOB value.


In all environments, including OCI, the LOB APIs operate on the LOB value implicitly--there is no need to "dereference" the LOB locator.

See Also:

LOB Restrictions

This section provides details on LOB restrictions.

See Also:

Restrictions on LOB Columns

LOB columns are subject to the following restrictions:

See Also:

Restrictions for LOB Operations

Other general LOB restrictions include the following: