DB Blob and Clob files - Yash-777/oracle-aq-samples GitHub Wiki

-- dev/resources.properties
-- security.certificate=security/PKI/
-- security.privatekey=security/PKI/

CREATE TABLE "CONF_FILES"
  (
     "NAME"        VARCHAR2(255 BYTE) NOT NULL ENABLE,
    "TYPE"        VARCHAR2(255 BYTE) DEFAULT '',
    "MIMETYPE"    VARCHAR2(255 BYTE) NOT NULL ENABLE,
    "BINCONTENT" BLOB,
    "TEXTCONTENT" CLOB,
    "VISIBLE"  CHAR(1 BYTE) DEFAULT 'T',
    "INSERTTIME" DATE DEFAULT sysdate
  );

Insert into CONF_FILES (NAME,TYPE,MIMETYPE,BINCONTENT,VISIBLE,INSERTTIME) values 
  ('Baeldung.p12','security.privatekey','application/octet-stream',empty_blob(),'T',sysdate);
-- ('Baeldung.cer','security.certificate','application/octet-stream',empty_blob(),'T',sysdate);

DECLARE
   p_parameter_name   VARCHAR2 (100 BYTE) := 'Baeldung.p12';
   src_file           BFILE;
   dst_file           BLOB;
   lgh_file           BINARY_INTEGER;
BEGIN
   src_file := BFILENAME ('C:/Yash/SQL_Files', p_parameter_name);
   SELECT BINCONTENT INTO dst_file FROM CONF_FILES WHERE NAME = p_parameter_name FOR UPDATE;
   -- open the file
   DBMS_LOB.fileopen (src_file, DBMS_LOB.file_readonly);
   -- determine length
   lgh_file := DBMS_LOB.getlength (src_file);
   -- read the file
   DBMS_LOB.loadfromfile (dst_file, src_file, lgh_file);
   -- update the blob field
   UPDATE CONF_FILES SET BINCONTENT = dst_file WHERE NAME = p_parameter_name;
   -- close file
   DBMS_LOB.fileclose (src_file);
   COMMIT;
END;