-- Oracle Table With CLOB and BLOB Columns CREATE TABLE cms_config_detail (cms_id NUMBER NOT NULL, content_type VARCHAR2(50) NOT NULL, content_clob CLOB, content_blob BLOB, created_date DATE DEFAULT SYSDATE NOT NULL ); -- PL/SQL Code To Load BLOB Data using DBMS_LOB write and writeappend procedure. The -- code shown is just for reference (CLOB to be used instead, since the data is -- character datatype). Since BLOB is for binary datatype storage, appending binary -- datatype may not be a good option. -- The UTL_RAW.cast_to_raw() is needed to load the BLOB data. DECLARE g_blob BLOB; PROCEDURE insert_blob(p_cid IN cms_config_detail.cms_id%TYPE, p_type IN cms_config_detail.content_type%TYPE, p_text IN VARCHAR2) AS BEGIN INSERT INTO cms_config_detail(cms_id, content_type, content_blob) VALUES(p_cid, p_type, empty_blob()) RETURNING content_blob INTO g_blob; DBMS_LOB.write(g_blob, LENGTH(p_text), 1, UTL_RAW.cast_to_raw(p_text)); END; PROCEDURE append_blob(p_text IN VARCHAR2) as BEGIN DBMS_LOB.writeappend(g_blob, LENGTH(p_text), UTL_RAW.cast_to_raw(p_text)); END; BEGIN insert_blob(10,'BLOB','<html><head><title>Technology</title></head>'); append_blob(' <body>Content</body></html>'); COMMIT; END; / -- Query to review data SELECT cms_id, content_type, UTL_RAW.cast_to_varchar2(content_blob) content_blob FROM cms_config_detail WHERE cms_id = 10;
55789