PL/SQL Script to read CLOB(XML Data) which is more than 32kb size

Here in this article I will be writing how to read a CLOB file using DBMS_LOB which is more than 32kb size. The below example is related to dunning letters where a seeded concurrent program IEX: Bulk XML Delivery Manager generates dunning letters data in XML form and stored it in table IEX_XML_REQUEST_HISTORIES in CLOB format

DECLARE
/**********************************************************
 *PURPOSE: PL/SQL Script to read CLOB(XML Data)           *
 *AUTHOR: Shailender Thallam                              *
 **********************************************************/
   lv_xml_data_txt          CLOB;
   lv_offset_num            NUMBER;
   lv_amount_num            NUMBER             := 20000;
   lv_length_num            NUMBER;
   lv_first_100length_num   NUMBER             := 100;
   lv_out_data_txt          VARCHAR2 (32000);
   lv_file_ref              UTL_FILE.file_type;
BEGIN
   --
   DBMS_OUTPUT.put_line ('');

   FOR rec IN (SELECT xmldata mylob
                 FROM iex_xml_request_histories
                WHERE request_id = p_xml_data_req_id)    --Fetching clob data
   LOOP
------------------------
--Finding length of clob
------------------------
      lv_length_num := DBMS_LOB.getlength (rec.mylob);
      lv_xml_data_txt := rec.mylob;
      lv_offset_num := 1;
-----------------------------------------------------------------
--Logic to remove '' from XML Data
--Removing this as this will repeat in each iteration of the loop
-----------------------------------------------------------------
--Reading only first 100 chars as this tag appears only in the beginning
      DBMS_LOB.READ (lv_xml_data_txt,
                     lv_first_100length_num,
                     lv_offset_num,
                     lv_out_data_txt
                    );

      SELECT REPLACE (lv_out_data_txt, '')
        INTO lv_out_data_txt
        FROM DUAL;

      DBMS_OUTPUT.put_line (lv_out_data_txt);
      --Setting offset to 101 as already 100 chars are read
      lv_offset_num := 101;

      --
      IF lv_length_num < 20000
      THEN
--------------------------------------------------
--Read the data in one go if size is less than 20k
--------------------------------------------------
         DBMS_LOB.READ (lv_xml_data_txt,
                        lv_length_num,
                        lv_offset_num,
                        lv_out_data_txt
                       );
         DBMS_OUTPUT.put_line (lv_out_data_txt);
      ELSE
-----------------------------------------------------------
--Read the data in multiple chunks if size is more than 20k
-----------------------------------------------------------
         DBMS_LOB.READ (lv_xml_data_txt,
                        lv_amount_num,
                        lv_offset_num,
                        lv_out_data_txt
                       );
         DBMS_OUTPUT.put_line (lv_out_data_txt);
      END IF;

-----------------------------------
--Incrementing offset with next 20k
-----------------------------------
      lv_offset_num := lv_offset_num + lv_amount_num;

      WHILE (lv_offset_num < lv_length_num)
      LOOP
----------------------------------------------------------
--loop till entire data is fetched with 20k chunks of data
----------------------------------------------------------
         write_message ('LOG', 'lv_offset_num : ' || TO_CHAR (lv_offset_num));
         lv_out_data_txt := NULL;
         DBMS_LOB.READ (lv_xml_data_txt,
                        lv_amount_num,
                        lv_offset_num,
                        lv_out_data_txt
                       );
         DBMS_OUTPUT.put_line (lv_out_data_txt);
-----------------------------------
--Incrementing offset with next 20k
-----------------------------------
         lv_offset_num := lv_offset_num + lv_amount_num;
      END LOOP;
   END LOOP;

   DBMS_OUTPUT.put_line ('');
--
EXCEPTION
   WHEN OTHERS
   THEN
      --
      DBMS_OUTPUT.put_line ('Error: ' || SQLERRM);
--
END;

Please do comment if you need some more information on implementing this.