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:

1. The explicit SQL DML command DELETE, or
2. 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:

To insert Oracle LOB data using SQL*Loader in an Oracle "cloud-enabled" RDBMS 19c environment, the process is similar to the legacy instructions but with some modern adaptations for cloud-enabled features and enhancements available in Oracle 19c.
Here's the updated process:
Updated Instructions for Oracle 19c (Cloud-Enabled RDBMS):

1. Connect to the Database:
   • Use SQL*Plus or another SQL client, connect to the Oracle 19c cloud-enabled database with the appropriate credentials. For example:
     

     SQLPLUS PETSTORE/GREATPETS@MYDB
     

   • Approve the entries as needed.
2. Declare Variables:
   • In your PL/SQL block, declare two variables:
     • A destination variable for the LOB data (of type BLOB or CLOB).
     • A source variable that points to the file in the file system, which should be of type BFILE.
     • Ensure that the directory object in Oracle has been created and points to the appropriate path in the file system that holds the BLOB data.

     DECLARE
     	dest_lob BLOB;
     	src_file BFILE := BFILENAME('LOB_DIR', 'file_name');
     BEGIN
     

3. Select LOB Data for Update:
   • In the procedural section, select the LOB data for updating from the relevant table. Use a FOR UPDATE clause to lock the row for update.

     SELECT photo INTO dest_lob
     FROM PRODUCT_PHOTO_OBJ_TABLE
     WHERE PRODUCT_ID = 10
     FOR UPDATE;
     

4. Open the BFILE:
   • Use the DBMS_LOB.FILEOPEN procedure to open the BFILE.

     DBMS_LOB.FILEOPEN(src_file, DBMS_LOB.FILE_READONLY);
     

5. Get the Length of the Source File:
   • Use DBMS_LOB.GETLENGTH to get the size of the file and output it.

     DBMS_OUTPUT.PUT_LINE('Source file length: ' || DBMS_LOB.GETLENGTH(src_file));
     

6. Load Data into LOB Column:
   • Use the DBMS_LOB.LOADFROMFILE procedure to load data from the source file into the destination LOB.

     DBMS_LOB.LOADFROMFILE(dest_lob, src_file, DBMS_LOB.GETLENGTH(src_file));
     

7. Close the BFILE:
   • After loading the data, close the BFILE using DBMS_LOB.FILECLOSE.

     DBMS_LOB.FILECLOSE(src_file);
     

8. Commit the Changes:
   • Commit the changes to save the LOB data to the database.

     COMMIT;
     END;
     /
     
     DBMS_OUTPUT.PUT_LINE('Destination LOB length: ' || DBMS_LOB.GETLENGTH(dest_lob));
     

9. Handling in Cloud-Enabled Environment:
   • In Oracle 19c (cloud-enabled), ensure that any required directory objects are correctly configured, and the database has permissions to access the filesystem where LOB data resides.
   • For cloud deployments, consider using Oracle's Autonomous Database features for handling data files directly from Object Storage.
10. Exit SQL*Plus:
    • After reviewing the result of the PL/SQL block's execution, exit SQL*Plus or your preferred client.

Additional Considerations in Oracle 19c:

• Cloud Storage Integration: You can also consider using Oracle's cloud storage capabilities (like Oracle Object Storage) to handle larger LOB files more efficiently.
• Improved Security: Ensure that any files or directories used for BLOB storage in the cloud have the correct security configurations, particularly for access permissions and encryption if needed.

This modern approach takes advantage of Oracle 19c's cloud features, especially when handling file system access and integration with cloud storage services.

Now let us 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.