PL/SQL DBMS_LOB.ERASE

The DBMS_LOB.ERASE function in Oracle is part of the DBMS_LOB package, which provides a set of procedures and functions for managing large objects (LOBs) in the database. LOBs refer to the BLOB, CLOB, and NCLOB data types, which are designed to store large amounts of data, such as text, images, and multimedia content.

The ERASE function specifically is used to erase a portion of the LOB value, starting from a specified offset. This can be particularly useful when you need to remove data from a LOB without affecting the rest of its content.

Syntax

DBMS_LOB.ERASE(
   lob_loc    IN OUT NOCOPY CLOB | BLOB,
   amount     IN OUT INTEGER,
   offset     IN INTEGER);

lob_loc: This is the LOB locator that points to the LOB to be erased. It must be a LOB that’s already been opened for writing. This parameter is passed as IN OUT NOCOPY to avoid unnecessary copying of the LOB value, for performance reasons.

amount: This specifies the number of characters (for CLOB or NCLOB) or bytes (for BLOB) to erase. The actual number of characters or bytes erased is returned in this parameter, so it is also an IN OUT parameter.

offset: This specifies the starting point from which to begin erasing. The first position in a LOB is 1.

How It Works

When you call DBMS_LOB.ERASE, the function erases the specified portion of the LOB starting from the offset up to the amount specified. After the operation, the erased portion of the LOB is filled with spaces (for CLOB and NCLOB) or zero-bytes (for BLOB), effectively removing the data from that portion of the LOB.

It’s important to note that DBMS_LOB.ERASE modifies the LOB in place and does not reduce the size of the LOB. If you need to adjust the LOB’s size after erasing content, you would use other functions like DBMS_LOB.TRIM.

Example Usage

DECLARE
   my_lob CLOB;
   amount INTEGER := 10;
BEGIN
   SELECT my_clob INTO my_lob FROM my_table WHERE id = 1 FOR UPDATE;
   DBMS_LOB.OPEN(my_lob, DBMS_LOB.LOB_READWRITE);
   DBMS_LOB.ERASE(my_lob, amount, 1);
   DBMS_LOB.CLOSE(my_lob);
   UPDATE my_table SET my_clob = my_lob WHERE id = 1;
   COMMIT;
END;

In this example, a CLOB column from a row in my_table is selected for erasing. The first 10 characters of the CLOB are erased (replaced with spaces), and the CLOB is then updated in the table.

Considerations

Ensure the LOB is opened for read-write operations before calling ERASE.
Remember that ERASE does not reduce the physical size of the LOB. Use TRIM for that purpose.
Always include proper error handling, especially for operations that modify data.
Using DBMS_LOB.ERASE can be very powerful for managing LOB content directly within the database, providing flexibility in how large data objects are manipulated and stored.