Lesson 8	Updating LOB data using PL/SQL
Objective	Use DBMS_LOB to update LOB data

Updating LOB data (using PL/SQL)

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.