UPDATE a LOB with EMPTY_CLOB() or EMPTY_BLOB()

Figure 9-42 Use Case Diagram: UPDATE using EMPTY_CLOB() or EMPTY_BLOB()

See: "Use Case Model: Internal Persistent LOBs Basic Operations", for all basic operations of Internal Persistent LOBs.

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 "CREATE 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: Oracle8i 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;