Deleting the Row of a Table Containing a LOB

Figure 10-47 Use Case Diagram: Deleting the Row of a Table Containing a LOB

Text description of the illustration adl10p21.gif

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

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: Oracle9i 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 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;