xmloracle11gclobxmltypeutl-file

Clob data don't want to save (Oracle, XML, Clob, PL/SQL, UTL_FILE)


I want to take data from table (xmltype) and save to the file on disc.

set serveroutput on;
declare
     p_directory varchar2(10) := 'DMP_XML';
     p_fileName varchar2(10) := 'test.xml';
     file_handle UTL_FILE.FILE_TYPE;
     clob_part VARCHAR2(1024);
     clob_length NUMBER;
     offset NUMBER := 1;     
     clob_in clob;
     v_xml_data xmltype;

   begin
      file_handle := UTL_FILE.FOPEN(p_directory, p_fileName, 'W');
      UTL_FILE.PUT_LINE(file_handle, '<?xml version="1."?>');

       select
         extract(
           XMLELEMENT("TAG",
             XMLAGG(
               XMLELEMENT("Col1", Col1 ||''|| Col2) order by col1)),'/"') as xml_test
       into v_xml_data
       from table1;      

      clob_in := v_xml_data.GETclobVAL();
      clob_length := LENGTH(clob_in);

      LOOP
        EXIT WHEN offset >= clob_length;
        clob_part := DBMS_LOB.SUBSTR (clob_in, 1024, offset);
        --DBMS_OUTPUT.PUT_LINE(clob_part);
        UTL_FILE.PUT(file_handle, clob_part);
        offset := offset + 1024;
      END LOOP;


      UTL_FILE.FFLUSH(file_handle);
      UTL_FILE.fclose(file_handle);
   end;

I have tested that first line is added to my test.xml file. Also, I see that clob_part has string values (when uncoment DBMS_OUTPUT).

But I got error as below: enter image description here

and I'm not able to save file with data.


Solution

  • below resolve the case:

        LOOP
         EXIT WHEN offset >= clob_length;
          clob_part := DBMS_LOB.SUBSTR (clob_in, 1022, offset);
          UTL_FILE.PUT_LINE(file_handle, clob_part);
          offset := offset + 1022;
        END LOOP;
    

    Other solution:

    create or replace procedure write_xml_clob_to_file_xml (p_directory varchar2, p_file_name varchar2) as
    
         v_file UTL_FILE.FILE_TYPE;
         v_amount INTEGER := 32767;
         v_xml_data XMLType;
         v_char_buffer varchar2(32767);
    
         l_lob clob;
         l_idx pls_integer;
         start_pos pls_integer := 1;
    
    begin
       dbms_lob.createtemporary(l_lob,true);
       v_file:= UTL_FILE.FOPEN(p_directory,p_file_name,'w',v_amount);
    
       UTL_FILE.PUT_LINE(v_file, '<?xml version="1."?>');
    
      select
             extract(
               XMLELEMENT("TAG",
                 XMLAGG(
                   XMLELEMENT("MYTAG", Col1 ||''|| Col2) order by col1)),'/TAG') as xml_test
           into v_xml_data
           from table1;  
    
      l_lob :=v_xml_data.GETclobVAL();
    
      loop l_idx := instr(l_lob,'</MYTAG>');
             if l_idx > 0 then
                 UTL_FILE.put_line(v_file,substr(l_lob,start_pos,l_idx+12));           
               l_lob := substr(l_lob,l_idx+13);
             else
                UTL_FILE.put_line(v_file,substr(l_lob,start_pos));
               exit;
             end if;
          end loop; 
    
      UTL_FILE.fflush(v_file);
      UTL_FILE.fclose(v_file);
      dbms_lob.freetemporary(l_lob);
    
    end write_xml_clob_to_file_xml;