DELETE the Row of a Table Containing a LOB

Figure 9-45 Use Case Diagram: DELETE the Row of a Table Containing a LOB

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

Purpose

This procedure describes how to delete the row of a table containing a LOB.

Usage Notes

To delete a row that contains an internal LOB column or attribute use one of the following commands

• SQL DML: DELETE

• SQL DDL that effectively deletes it:
  • DROP TABLE

  • TRUNCATE TABLE

  • DROP TABLESPACE.

In either case you delete the LOB locator and the LOB value as well.

Note: Due to the consistent read mechanism, the old LOB value remains accessible with the value that it had at the time of execution of the statement (such as SELECT) that returned the LOB locator. This is an advanced topic. It is discussed in more detail with regard to "Read-Consistent Locators".

Distinct LOB Locators for Distinct Rows

Of course, two distinct rows of a table with a LOB column have their own distinct LOB locators and distinct copies of the LOB values irrespective of whether the LOB values are the same or different. This means that deleting one row has no effect on the data or LOB locator in another row even if one LOB was originally copied from another row.

Syntax

Use the following syntax reference:

• SQL: Oracle8i SQL Reference , Chapter 7, "SQL Statements" -- DELETE, DROP TABLE, TRUNCATE TABLE

Scenario

In the three examples provided in the following section, all data associated with Clip 10 is deleted.

Examples

The examples are provided in SQL and apply to all six programmatic environments:

• SQL: Delete a LOB

SQL: Delete a LOB

DELETE FROM Multimedia_tab WHERE Clip_ID = 10;

DROP TABLE Multimedia_tab;
 
TRUNCATE TABLE Multimedia_tab;