Storing and Updating Columns

Description

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.

Example

-- 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';