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
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 | 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 ('<XX_DUNNING_LETTER>'); 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 '<?xml version="1.0"?>' 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, '<?xml version="1.0"?>') 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 ('</XX_DUNNING_LETTER>'); -- EXCEPTION WHEN OTHERS THEN -- DBMS_OUTPUT.put_line ('Error: ' || SQLERRM); -- END; |
Please do comment if you need some more information on implementing this.