Updating a LOB with EMPTY_CLOB() or EMPTY_BLOB()

Figure 10-44 Use Case Diagram: Updating a LOB with EMPTY_CLOB() or EMPTY_BLOB()

Text description of the illustration adl10p14.gif

See: "Use Case Model: Internal Persistent LOBs Operations", for all Internal Persistent LOB operations.

For Binds of More Than 4,000 Bytes

For information on how to UPDATE a LOB when binds of more than 4,000 bytes are involved, see the following sections in Chapter 7, "Modeling and Design":

• Binds Greater than 4,000 Bytes are Now Allowed For LOB INSERTs and UPDATEs

• Binds of More Than 4,000 Bytes ... No HEX to RAW or RAW to HEX Conversion

• Example: PL/SQL - Using Binds of More Than 4,000 Bytes in INSERT and UPDATE

• Example: PL/SQL - Binds of More Than 4,000 Bytes -- Inserts Not Supported Because Hex to Raw/Raw to Hex Conversion is Not Supported

• Example: PL/SQL - 4,000 Byte Result Limit in Binds of More than 4,000 Bytes When Data Includes SQL Operator

• Example: C (OCI) - Binds of More than 4,000 Bytes For INSERT and UPDATE

  Note: Performance improves when you update the LOB with the actual value, instead of using EMPTY_CLOB() or EMPTY_BLOB().

Purpose

This procedure describes how to UPDATE a LOB with EMPTY_CLOB() or EMPTY_BLOB().

Usage Notes

Making a LOB Column Non-Null

Before you write data to an internal LOB, make the LOB column non-null; that is, the LOB column must contain a locator that points to an empty or populated LOB value. You can initialize a BLOB column's value by using the function EMPTY_BLOB() as a default predicate. Similarly, a CLOB or NCLOB column's value can be initialized by using the function EMPTY_CLOB().

You can also initialize a LOB column with a character or raw string less than 4,000 bytes in size. For example:

UPDATE Multimedia_tab 
          SET story = 'This is a One Line Story'
          WHERE clip_id = 2;

You can perform this initialization during CREATE TABLE (see "Creating a Table Containing One or More LOB Columns") or, as in this case, by means of an INSERT.

Syntax

Use the following syntax reference:

• SQL: Oracle9i SQL Reference Chapter 7, "SQL Statements" -- UPDATE

Scenario

The following example shows a series of updates via the EMPTY_CLOB operation to different data types of the first clip:

Examples

The example is provided in SQL and applies to all the programmatic environments:

• SQL: UPDATE a LOB with EMPTY_CLOB() or EMPTY_BLOB()

SQL: UPDATE a LOB with EMPTY_CLOB() or EMPTY_BLOB()

UPDATE Multimedia_tab SET Story = EMPTY_CLOB() WHERE Clip_ID = 1;

UPDATE Multimedia_tab SET FLSub = EMPTY_CLOB() WHERE Clip_ID = 1;

UPDATE multimedia_tab SET Sound = EMPTY_BLOB() WHERE Clip_ID = 1;