How to convert CLOB into NVARCHAR2 chunks as per the size of the CLOB?

Question

We have a procedure that generates an HTML report and stores the entire value into a CLOB field. It is then sent to a different procedure which sends an email using UTL_SMTP. The email size can vary, usually, it is a huge size.

I am converting the CLOB value into VARCHAR2 chunks and passing the different chunks to the UTL_STMP through multiple variables.

   chunk1            varchar2 (4000);
   chunk2            varchar2 (4000);
   chunk3            varchar2 (4000);
   chunk4            varchar2 (4000);
   chunk5            varchar2 (4000);
   chunk6            varchar2 (4000);
   chunk7            varchar2 (4000);
   chunk8            varchar2 (4000);
   chunk9            varchar2 (4000);
   chunk10           varchar2 (4000);
   chunk11           varchar2 (4000);

Converting the CLOB into different chunks and storing in different variables:

   chunk1 := DBMS_LOB.SUBSTR (EM_TEXT, 4000, 1);
   chunk2 := DBMS_LOB.SUBSTR (EM_TEXT, 4000, 4001);
   chunk3 := DBMS_LOB.SUBSTR (EM_TEXT, 4000, 8001);
   chunk4 := DBMS_LOB.SUBSTR (EM_TEXT, 4000, 12001);
   chunk5 := DBMS_LOB.SUBSTR (EM_TEXT, 4000, 16001);
   chunk6 := DBMS_LOB.SUBSTR (EM_TEXT, 4000, 20001);
   chunk7 := DBMS_LOB.SUBSTR (EM_TEXT, 4000, 24001);
   chunk8 := DBMS_LOB.SUBSTR (EM_TEXT, 4000, 28001);
   chunk9 := DBMS_LOB.SUBSTR (EM_TEXT, 4000, 32001);
   chunk10 := DBMS_LOB.SUBSTR (EM_TEXT, 4000, 36001);
   chunk11 := DBMS_LOB.SUBSTR (EM_TEXT, 4000, 40001);

Then passing the variables to UTL_SMTP:

   UTL_SMTP.WRITE_RAW_DATA (
      M_MAIL_CONN,
      UTL_ENCODE.QUOTED_PRINTABLE_ENCODE (UTL_RAW.CAST_TO_RAW (chunk1))
   );

   UTL_SMTP.WRITE_RAW_DATA (
      M_MAIL_CONN,
      UTL_ENCODE.QUOTED_PRINTABLE_ENCODE (UTL_RAW.CAST_TO_RAW (chunk2))
   );

   UTL_SMTP.WRITE_RAW_DATA (
      M_MAIL_CONN,
      UTL_ENCODE.QUOTED_PRINTABLE_ENCODE (UTL_RAW.CAST_TO_RAW (chunk3))
   );

   UTL_SMTP.WRITE_RAW_DATA (
      M_MAIL_CONN,
      UTL_ENCODE.QUOTED_PRINTABLE_ENCODE (UTL_RAW.CAST_TO_RAW (chunk4))
   );

   UTL_SMTP.WRITE_RAW_DATA (
      M_MAIL_CONN,
      UTL_ENCODE.QUOTED_PRINTABLE_ENCODE (UTL_RAW.CAST_TO_RAW (chunk5))
   );

   UTL_SMTP.WRITE_RAW_DATA (
      M_MAIL_CONN,
      UTL_ENCODE.QUOTED_PRINTABLE_ENCODE (UTL_RAW.CAST_TO_RAW (chunk6))
   );

   UTL_SMTP.WRITE_RAW_DATA (
      M_MAIL_CONN,
      UTL_ENCODE.QUOTED_PRINTABLE_ENCODE (UTL_RAW.CAST_TO_RAW (chunk7))
   );

   UTL_SMTP.WRITE_RAW_DATA (
      M_MAIL_CONN,
      UTL_ENCODE.QUOTED_PRINTABLE_ENCODE (UTL_RAW.CAST_TO_RAW (chunk8))
   );

   UTL_SMTP.WRITE_RAW_DATA (
      M_MAIL_CONN,
      UTL_ENCODE.QUOTED_PRINTABLE_ENCODE (UTL_RAW.CAST_TO_RAW (chunk9))
   );

   UTL_SMTP.WRITE_RAW_DATA (
      M_MAIL_CONN,
      UTL_ENCODE.QUOTED_PRINTABLE_ENCODE (UTL_RAW.CAST_TO_RAW (chunk10))
   );

   UTL_SMTP.WRITE_RAW_DATA (
      M_MAIL_CONN,
      UTL_ENCODE.QUOTED_PRINTABLE_ENCODE (UTL_RAW.CAST_TO_RAW (chunk11))
   );

As of today, 11 variables are sufficient for the data but tomorrow, it may increase. If the variables are more than the CLOB data, the procedure throws an error.

I need to find a way to dynamically distribute the CLOB to different variables and then pass only the variables with data to UTL_SMTP.

Thanks in advance for the tips, suggestions, and examples.

Answer 1

I used the answer from this post with some modification as below and got what I wanted.

DECLARE
   TYPE typ_comment IS TABLE OF varchar2 (4000);
   v_varchar         typ_comment := typ_comment ();
   v_clob            CLOB;
   k                 number;
   j                 number := 4000;

BEGIN
  SELECT   EM_TEXT INTO v_clob FROM DUAL; 

   k := CEIL (DBMS_LOB.getlength (v_clob) / j);
   v_varchar.EXTEND (k);

   FOR i IN 1 .. k
   LOOP
      v_varchar (i) := DBMS_LOB.SUBSTR (v_clob, j, 1 + j * (i - 1));

      UTL_SMTP.WRITE_RAW_DATA (
         M_MAIL_CONN,
         UTL_ENCODE.QUOTED_PRINTABLE_ENCODE (
            UTL_RAW.CAST_TO_RAW (v_varchar (i))
         )
      );
   END LOOP;
END;