Generate XML from SQL

   FUNCTION  get_xml_data( p_sql_stmt IN VARCHAR2, p_row_set_tag IN VARCHAR2, p_row_tag IN VARCHAR2 )
   RETURN CLOB
   IS
      l_ctx    dbms_xmlgen.ctxHandle;
      l_xml    CLOB;
 
   BEGIN
 
 
      l_ctx := dbms_xmlgen.newContext(p_sql_stmt) ;
      dbms_xmlgen.setrowsettag(l_ctx, p_row_set_tag );
      dbms_xmlgen.setRowTag(l_ctx, p_row_tag );
      l_xml := dbms_xmlgen.getXML(l_ctx);
      DBMS_XMLGEN.closecontext ( l_ctx )  ;
 
      RETURN l_xml ;
 
   EXCEPTION
      WHEN OTHERS THEN
 
        RETURN NULL ;
     
   END  get_xml_data 

Read CLOB line by line

create table nrr_test_clob (c clob);

truncate table nrr_test_clob

insert INTO nrr_test_clob (c) values (
'<REPORT>
  <HEADER>
   <REPORT_NAME> WMS PICK TASK RELEASE</REPORT_NAME>
     <REQUEST_ID> 236560596</REQUEST_ID>
     <REQDATE> 02-MAR-2018 04:53:2525 PM</REQDATE>
     <REQUSER> RAYKARN</REQUSER>
     <FROM_DATE> 11-JAN-2018 01:00:0000 AM</FROM_DATE>
     <TO_DATE> 19-JAN-2018 09:00:0000 PM</TO_DATE>
     <FACILITY> WPC</FACILITY>
     <ORG> </ORG>
     </HEADER>
     <DATE_SUMMARY>
     <DATE_ROW>
     <COUNT_PREV>2447</COUNT_PREV>
     <COUNT_TODAY>0</COUNT_TODAY>
     <COUNT_TOMORROW>0</COUNT_TOMORROW>
     <COUNT_WEEK>644</COUNT_WEEK>
     <COUNT_MONTH>697</COUNT_MONTH>
     <COUNT_FUTURE>39</COUNT_FUTURE>
     </DATE_ROW>
     </DATE_SUMMARY>
     </REPORT>');
----------------------------------------------------------------------------------------------------------------------

select to_char(regexp_substr(test.c, '.+', 1, level)) pattern
from nrr_test_clob test
connect by level <= regexp_count(test.c, '.+');

----------------------------------------------------------------------------------------------------------------------


DECLARE

   l_clob   clob ;
   l_offset number := 1;
   l_amount number := 32767;
   l_length    number ;
   lc_buffer varchar2(32767);
   i   pls_integer := 1;
   idx pls_integer := 0;


   -- For UNIX type file - replace CHR(13) to NULL
   C_CR char := chr(13); -- carriage return
   --CR char := NULL;
   C_LF char := chr(10); --line feed
   
BEGIN


   SELECT c
   INTO   l_clob
   FROM   nrr_test_clob ;
 
   l_length := dbms_lob.getlength(l_clob);

   dbms_output.put_line('l_length => '||l_length) ;
 
   <<len_lbl1>>
   WHILE ( l_offset < l_length ) LOOP
 
      idx := idx + 1;
     
      dbms_output.put_line(idx||' l_offset => '||l_offset||' l_length => '||l_length) ;
 
      -- If no more newlines are found, read till end of CLOB
      IF (instr(l_clob, c_lf, l_offset) = 0) THEN
       
         l_amount := l_length - l_offset + 1;
   
      ELSE
   
         l_amount := instr(l_clob, c_lf, l_offset) - l_offset;
       
      END IF;
   
      dbms_output.put_line('l_amount => '||l_amount) ;
 
      -- This is to catch empty lines, otherwise we get a NULL error
      IF  ( l_amount = 0 ) THEN
   
         lc_buffer := '';
   
      ELSE
   
         dbms_lob.read(l_clob, l_amount, l_offset, lc_buffer);
   
      END IF;
   
      dbms_output.put_line('Line #'||i||':'||lc_buffer);


      -- This is to catch a newline on the last line with 0 characters behind it
   
   
      IF (instr(l_clob, c_lf, l_offset) = l_length) THEN
   
         lc_buffer := '';
   
         dbms_output.put_line('Line #'||idx||':'||lc_buffer);
   
      END IF;

      l_offset := l_offset + l_amount + 1;
 
   END LOOP len_lbl1 ;
 
 
END ;