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

By | December 18, 2016 | 215 views | Category: PL/SQL Scripts

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.

WOW! Did you like this post? We'll send more interesting posts like PL/SQL Script to read CLOB(XML Data) which is more than 32kb size to you!
Enter your Email Address: