字符串缓冲区到Oracle (utl，xml)

I have procedure to create xml file and save it on disc directory:

create or replace procedure test_write_xml_data_to_file 
(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);
begin

    v_file:= UTL_FILE.FOPEN(p_directory,p_file_name,'w',v_amount);

    UTL_FILE.PUT_LINE(v_file, '');

    select
      extract(
        XMLELEMENT("my_list",
          XMLAGG(
            XMLELEMENT("myName", col1 ||''|| col2) order by col1)),'/my_list') as xml_test
    into v_xml_data
    from tableABC;

    v_char_buffer :=v_xml_data.GETSTRINGVAL();

    UTL_FILE.put(v_file,v_char_buffer);
    UTL_FILE.fflush(v_file);
    UTL_FILE.fclose(v_file);

end test_write_xml_data_to_file;


When I execute it I got error:
execute test_write_xml_data_to_file('DMP_XML','test_xml.xml')
Error report -
ORA-19011: Character string buffer too small
ORA-06512: at "SYS.XMLTYPE", line 169
ORA-06512: at "FPWH.TEST_WRITE_XML_DATA_TO_FILE", line 31
ORA-06512: at line 1
19011. 00000 -  "Character string buffer too small" 
*Cause:    The string result asked for is too big to return back
*Action:   Get the result as a lob instead


How to change my procedure to correct?
Thank you in advance
复制

SQL> set serverout on
SQL> declare
  2    p_directory varchar2(10) := 'TEMP';
  3    p_file_name varchar2(10) := 'xml.out';
  4
  5    v_file UTL_FILE.FILE_TYPE;
  6    v_amount INTEGER := 32767;
  7    v_xml_data XMLType;
  8    v_char_buffer varchar2(32767);
  9
 10    l_lob clob;
 11    l_idx pls_integer;
 12    start_pos pls_integer := 1;
 13  begin
 14     dbms_lob.createtemporary(l_lob,true);
 15      v_file:= UTL_FILE.FOPEN(p_directory,p_file_name,'w',v_amount);
 16
 17      UTL_FILE.PUT_LINE(v_file, '');
 18
 19      select
 20        extract(
 21          XMLELEMENT("my_list",
 22            XMLAGG(
 23              XMLELEMENT("myName", owner ||''|| object_name) order by object_id)),'/my_list') as xml_test
 24      into v_xml_data
 25      from dba_objects
 26      where rownum <= 10;
 27
 28      l_lob :=v_xml_data.GETclobVAL();
 29      loop
 30        l_idx := instr(l_lob,'');
 31        if l_idx > 0 then
 32           dbms_output.put_line(substr(l_lob,start_pos,l_idx+8));
 33           --UTL_FILE.put_line(v_file,substr(l_lob,start_pos,l_idx+8));
 34           l_lob := substr(l_lob,l_idx+9);
 35        else
 36           dbms_output.put_line(substr(l_lob,start_pos));
 37           --UTL_FILE.put_line(v_file,substr(l_lob,start_pos));
 38           exit;
 39        end if;
 40      end loop;
 41
 42      UTL_FILE.fflush(v_file);
 43      UTL_FILE.fclose(v_file);
 44     dbms_lob.freetemporary(l_lob);
 45
 46  end;
 47  /
SYSI_OBJ#
SYSC_FILE#_BLOCK#
SYSTS$
SYSICOL$
SYSUSER$
SYSCDEF$
SYSI_TAB1
SYSI_OBJ2
SYSI_OBJ5
SYSI_IND1


PL/SQL procedure successfully completed.

复制