Sample for persisting CLOB with PL/SQL

mainScript.sql

DECLARE
  documentId DOCUMENT.ID%TYPE;
  documentName DOCUMENT.NAME%TYPE := 'python cheatsheet';
  documentFileName VARCHAR2(1024 CHAR) := 'python.html';

  documentFile BFILE;
  documentClob CLOB;
  
  sourceOffset INTEGER := 1;
  destinationOffset INTEGER := 1;
  languageContext INTEGER := DBMS_LOB.default_lang_ctx;
  warning INTEGER;
BEGIN
  SELECT DOCUMENT_ID_SEQ.NEXTVAL
    INTO documentId
    FROM DUAL;

  -- Insert record
  INSERT INTO DOCUMENT (ID, NAME, CONTENT) VALUES (documentId, documentName, EMPTY_CLOB());
  
  -- Initialize CLOB and BFILE
  documentFile := BFILENAME('BFILE_DATA', documentFileName);
  SELECT CONTENT
    INTO documentClob
    FROM DOCUMENT
  WHERE ID = documentId;
  
  DBMS_LOB.OPEN(documentClob, DBMS_LOB.LOB_READWRITE);
  DBMS_LOB.OPEN(documentFile);
  
  DBMS_LOB.LOADCLOBFROMFILE(documentClob, documentFile, DBMS_LOB.LOBMAXSIZE, sourceOffset, destinationOffset, NLS_CHARSET_ID('UTF8'), languageContext, warning);
  
  DBMS_LOB.CLOSE(documentClob);
  DBMS_LOB.CLOSE(documentFile);
  
  IF warning = 0
  THEN
    COMMIT;
  ELSE
    DBMS_OUTPUT.PUT_LINE('Warning!!');
    ROLLBACK;
  END IF;
EXCEPTION WHEN OTHERS THEN ROLLBACK;
END;

prepareScript.sql

CREATE TABLE DOCUMENT (
  ID NUMBER,
  NAME VARCHAR2(50),
  CONTENT CLOB,
  CONSTRAINT DOCUMENT_PK PRIMARY KEY (ID));
  
CREATE SEQUENCE DOCUMENT_ID_SEQ
    MINVALUE 1
    MAXVALUE 999999999999999999999999999
    START WITH 1
    INCREMENT BY 1
    CACHE 20;
  
CREATE DIRECTORY BFILE_DATA AS 'C:\DOCUMENT_DIRECTORY\';
GRANT READ ON DIRECTORY BFILE_DATA TO alice;