Lesson 9	Deleting LOB data using PL/SQL
Objective	Use DBMS_LOB to delete LOB data.

Deleting LOB data using PL/SQL

You can delete the data from a LOB-based object table or you can erase the LOB data. Let’s first look at deleting the records from an object table based on the LOB data type.
You can delete a row that contains an internal LOB column or attribute by using:

The explicit SQL DML command DELETE, or
The SQL DDL command that effectively deletes the row, such as DROP TABLE, TRUNCATE TABLE, or DROP TABLESPACE

In either case, you can delete both the LOB locator and the LOB value. The two distinct rows of a table with a LOB column have their own distinct LOB locators and their own copies of the LOB values irrespective of whether these LOB values are the same. This means that deleting one row has no effect on the data or LOB locator within another row, even if one LOB was originally copied from another row.
Some examples of the DELETE statements include:

DELETE FROM Customer_photo_obj_table
WHERE customer_id = 10;
DROP TABLE Customer_photo_obj_table;
TRUNCATE TABLE Customer_photo_obj_table;

The ERASE()procedure allows you to erase a part of or the entire LOB, depending on the amount of data and offset parameters passed to the procedure. The actual number of bytes or characters erased can differ from the number specified in the AMOUNT parameter if the end of the LOB parameter is reached. Erased characters are replaced with zero-byte fillers ^[1] for BLOBs and spaces for CLOBs.
Let’s try another evaluative simulation to update a LOB value using PL/SQL. When a record is inserted into an object table with an internal LOB data type, the value of the LOB is set with the EMPTY_CLOB() and EMPTY_BLOB() functions. In this evaluative simulation, we will update the LOB value with an actual file using the DBMS_LOB.LOADFROMFILE function:
Erase Oracle LOB
The next lesson wraps up this module.

Erasing Data within Lob - Exercise

Now let’s try erasing the data within a LOB column using PL/SQL. Click on the Exercise button to use the DBMS_LOB.ERASE function to erase a part of the LOB data. This function requires three parameters: the LOB, the number of bytes to be erased, and the starting point for erasing the data.
Erasing Data within Lob - Exercise

[1]Zero-Byte Fillers: While editing or copying on an existing LOB, if the data already exists at the start position of the destination, it is overwritten with the source data. If the start position of the destination is beyond the end of the current data, zero-byte fillers (for BLOBs) or spaces (for CLOBs) are written into the destination LOB from the end of the current data to the beginning of the newly written data from the source.