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
Post a Comment