Updating LOB data (using PL/SQL)

In Oracle 19c, you can use the `DBMS_LOB` package to update LOB (Large Object) data within database tables. The package provides various procedures to manipulate CLOBs, BLOBs, and NCLOBs efficiently.
Steps to Update LOB Data Using `DBMS_LOB`

1. Use `DBMS_LOB.WRITE` – This method allows you to update a BLOB or CLOB by writing data at a specific position.
2. Use `DBMS_LOB.WRITEAPPEND` – This appends data to an existing LOB.
3. Use `DBMS_LOB.ERASE` – This erases a portion of a LOB.
4. Use `DBMS_LOB.COPY` – This copies data from one LOB to another.

Example 1: Updating a `CLOB` Column Using `DBMS_LOB.WRITE`:
Assume you have a table:

CREATE TABLE documents (
    doc_id NUMBER PRIMARY KEY,
    doc_name VARCHAR2(100),
    doc_content CLOB
);

Insert Initial Data:

INSERT INTO documents (doc_id, doc_name, doc_content)
VALUES (1, 'Sample Doc', EMPTY_CLOB());
COMMIT;

Update LOB Data Using `DBMS_LOB.WRITE`

DECLARE
    v_clob  CLOB;
    v_offset NUMBER := 1;  -- Start writing from the beginning
    v_data   CLOB := 'Updated content for the document.';
BEGIN
    -- Select the LOB Locator
    SELECT doc_content INTO v_clob 
    FROM documents 
    WHERE doc_id = 1 
    FOR UPDATE;  -- Lock the row to modify the LOB
    
    -- Write new data
    DBMS_LOB.WRITE(v_clob, LENGTH(v_data), v_offset, v_data);
    
    COMMIT;
END;
/

This replaces the content in the `doc_content` column for `doc_id=1`.:
Example 2: Appending Data Using `DBMS_LOB.WRITEAPPEND`:

DECLARE
    v_clob  CLOB;
    v_data  CLOB := ' Additional content.';
BEGIN
    -- Select the LOB Locator
    SELECT doc_content INTO v_clob
    FROM documents
    WHERE doc_id = 1
    FOR UPDATE;
    
    -- Append new data to existing LOB content
    DBMS_LOB.WRITEAPPEND(v_clob, LENGTH(v_data), v_data);
    
    COMMIT;
END;
/

This appends `Additional content.` to the existing LOB data.:
Example 3: Using `DBMS_LOB.ERASE` to Remove LOB Data:

DECLARE
    v_clob CLOB;
BEGIN
    -- Select the LOB Locator
    SELECT doc_content INTO v_clob 
    FROM documents 
    WHERE doc_id = 1 
    FOR UPDATE;
    
    -- Erase the first 10 characters
    DBMS_LOB.ERASE(v_clob, 10, 1);
    
    COMMIT;
END;
/

This erases the first 10 characters of the LOB data.:
Example 4: Copying Data from One LOB to Another:

DECLARE
    v_src CLOB;
    v_dest CLOB;
BEGIN
    -- Select Source and Destination LOBs
    SELECT doc_content INTO v_src FROM documents WHERE doc_id = 1;
    SELECT doc_content INTO v_dest FROM documents WHERE doc_id = 2 FOR UPDATE;

    -- Copy content from source to destination
    DBMS_LOB.COPY(v_dest, v_src, DBMS_LOB.GETLENGTH(v_src), 1, 1);

    COMMIT;
END;
/

This copies the entire LOB from `doc_id=1` to `doc_id=2`.:
Key Considerations:

• Always use `FOR UPDATE` when selecting LOBs to modify.
• Use `EMPTY_CLOB()` or `EMPTY_BLOB()` to initialize LOB columns.
• Use `DBMS_LOB.GETLENGTH(lob_var)` to get the current LOB length.
• Use `DBMS_LOB.SUBSTR(lob_var, length, offset)` to extract LOB portions.

You cannot use SQL to update the LOB columns. Instead, you can update the data within a LOB column using the DBMS_LOB package. For example, the following UPDATE statement updates the date column within an object table, which has a LOB column:

UPDATE  customer_photo_obj_table
SET created_date = to_date(‘01/12/2001’, ‘MM/DD/YYYY’)
WHERE  product_id = 901;

Let uslook at an example of an UPDATE statement that updates DEMO_LOB_TABLE. The following CREATE statement creates this table:

CREATE demo_lob_table AS 
(
demo_id  number(10),
clob_column CLOB,
blob_column BLOB
);

This table has both CLOB and BLOB columns. The following PL/SQL statement inserts and updates a record within this table:

DECLARE
v_clob_loc CLOB;
v_blob_loc BLOB;
BEGIN
INSERT into DEMO_OBJ_TABLE (demo_id, clob_column)
VALUES (121, ‘abcdefghik’);
UPDATE DEMO_OBJ_TABLE
SET   blob_column = HEXTORAW(‘00FF00FF00FF’)
WHERE  demo_id = 121;
COMMIT;
END;

In this example, after the data into the CLOB column is inserted, the BLOB data is updated using the HEXTORAW conversion. Then the data is committed to the database.
In the next lesson, we will look at deleting the LOB data using DBMS_LOB.