Oracle DBMS_LOB - Write, Writeappend BLOB
-- 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;
/

BLOB Data Write Append

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

BLOB Data Analysis Query

  Refer - CLOB Write, Writeappend

  CLOB Processing (PL/SQL block)

Oracle registered trademark of Oracle Corporation.

Last Revised On: February 14th, 2013

  374