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

Shailender Thallam

9 years ago

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 ');
--
EXCEPTION
   WHEN OTHERS
   THEN
      --
      DBMS_OUTPUT.put_line ('Error: ' || SQLERRM);
--
END;

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

For Further Reading