oracle - Unable to write the correct XML data from CLOB column -


/* have written below procedure writes clob column data physical location in xml format. file gets written getting truncated or missing correct xml format.*/

create or replace procedure p_generate_xml    c_amount         binary_integer := 32767;   l_buffer         varchar2(32767);   l_chr10          pls_integer;   l_cloblen        pls_integer;   l_fhandler       utl_file.file_type;   l_pos            pls_integer    := 1;  l_clob           clob;  l_message_num number;  l_cnt number;  l_err_msg varchar2(3000);  v_sysdate date;  begin  l_message_num := 1;     c2 in ( select xml_clob , case_id audit_xml_clob case_id = '2006s1000018')     loop     select  count(1) l_cnt audit_xml_clob nvl(dbms_lob.getlength(xml_clob),0) > 0;      if l_cnt > 0         l_pos := 1;         select xml_clob          l_clob          audit_xml_clob case_id = c2.case_id;            l_fhandler := utl_file.fopen('my_dir1', 'test.xml','w',c_amount);  l_cloblen  := dbms_lob.getlength(l_clob);                                             while l_pos < l_cloblen               loop                  l_buffer := dbms_lob.substr(l_clob, c_amount, l_pos);                      exit when l_buffer null;   utl_file.put_line(l_fhandler, l_buffer,true);                 l_pos := l_pos + least(length(l_buffer)+1,c_amount);                 dbms_output.put_line('l_pos:'||l_pos);    utl_file.fflush (l_fhandler);   end loop;    utl_file.fclose(l_fhandler);  l_message_num := l_message_num +1;  end if;     end loop; commit; dbms_output.put_line('4'); exception when others   if utl_file.is_open(l_fhandler)     utl_file.fclose(l_fhandler);   end if;   --raise; l_err_msg:= substr(sqlerrm,1,3000);  dbms_output.put_line(l_err_msg);  end; / 

1) fast way dump clob new file use dbms_xslprocessor.clob2file(...)
2) wrong xml?

begin     c2 in ( select xml_clob , case_id audit_xml_clob case_id = '2006s1000018')     loop       dbms_xslprocessor.clob2file(cl=> c2.xml_clob, flocation=>'my_dir1', fname=>'test.xml', csid=>0);    end loop; end; 

where my_dir1 exists , have privilege write in location.


Comments

Popular posts from this blog

java - Andrioid studio start fail: Fatal error initializing 'null' -

android - Gradle sync Error:Configuration with name 'default' not found -

StringGrid issue in Delphi XE8 firemonkey mobile app -