In a BLOB/CLOB type column, each BLOB/CLOB type value is stored, and if binary or character string data is input, you must explicitly change the types by using each BIT_TO_BLOB( )/CHAR_TO_CLOB( ) function.
If a value is input in a LOB column by using an INSERT statement, a file is created in an external storage internally and the relevant data is stored; the relevant file path (Locator) is stored in an actual column value.
If a record containing a LOB column uses a DELETE statement, a file to which the relevant LOB column refers will be deleted simultaneously. If a LOB column value is changed using an UPDATE statement, the column value will be changed following the operation below, according to whether a new value is NULL or not.
-- inserting data after explicit type conversion into CLOB type column
INSERT INTO doc_t (doc_id, content) VALUES ('doc-1', CHAR_TO_CLOB('This is a Dog'));
INSERT INTO doc_t (doc_id, content) VALUES ('doc-2', CHAR_TO_CLOB('This is a Cat'));
-- inserting data after explicit type conversion into BLOB type column
INSERT INTO image_t VALUES ('image-0', 'doc-0', BIT_TO_BLOB(X'000001'));
INSERT INTO image_t VALUES ('image-1', 'doc-1', BIT_TO_BLOB(X'000010'));
INSERT INTO image_t VALUES ('image-2', 'doc-2', BIT_TO_BLOB(X'000100'));
-- inserting data from a sub-query result
INSERT INTO image_t SELECT 'image-1010', 'doc-1010', image FROM image_t WHERE image_id = 'image-0';
-- updating CLOB column value to NULL
UPDATE doc_t SET content = NULL WHERE doc_id = 'doc-1';
-- updating CLOB column value
UPDATE doc_t SET content = CHAR_TO_CLOB('This is a Dog') WHERE doc_id = 'doc-1';
-- updating BLOB column value
UPDATE image_t SET image = (SELECT image FROM image_t WHERE image_id = 'image-0') WHERE image_id = 'image-1';
-- deleting BLOB column value and its referencing files
DELETE FROM image_t WHERE image_id = 'image-1010';