oracle pl/sql发送邮件多个收件人问题

--pl/sql发送邮件多个收件人问题
--
--测试使用pl/sql发送html报表，在网上找了个post_html_mail的procedure，现在测试单个用户可接受，可多用户，但要保证输入的用户正确
--
--附上post_html_mail

create or replace procedure kol_Post_html_mail(
          p_to                       in   varchar2,
          p_subject                  in   varchar2,
          p_text                     in   varchar2   default   null,
          p_html                     in   varchar2  default   null)
  is
          p_smtp_hostname   varchar2(20):='192.168.38.247'; --SMTP server
          p_smtp_portnum     varchar2(2):='25';--port
          p_from               varchar2(100):='sysAdmin@kolinker.com';   --from
          l_boundary             varchar2(255)   default   'a1B2C3d4e3f2g1';
          l_connection         utl_smtp.connection;
          l_body_html           clob   :=   empty_clob;     --This   LOB   will   be   the   email   MESSage
          l_offset                 number;
          l_ammount               number;
          l_temp                     varchar2(32767)   default   null;
          
          l_adresses varchar2(1000) := p_to;
          l_adress   varchar2(50);
  begin
          l_connection   :=   utl_smtp.open_connection(   p_smtp_hostname,   p_smtp_portnum   );
          utl_smtp.helo(   l_connection,p_smtp_hostname);
          utl_smtp.mail(   l_connection,   p_from   );  
          
          -- Send more than one person
          if (instr(l_adresses, ';') = 0) then
            l_adress := l_adresses;
            utl_smtp.rcpt(   l_connection,   l_adress   );
          end if;
          
          while instr(l_adresses, ';') > 0 loop
            select substr(l_adresses, 1, instr(l_adresses, ';')-1) into l_adress from dual;
            select substr(l_adresses, instr(l_adresses, ';')+1) into l_adresses from dual;
            
            utl_smtp.rcpt(   l_connection,   l_adress   );
            
            if instr(l_adresses, ';') = 0 then
                l_adress := l_adresses;
                utl_smtp.rcpt(   l_connection,   l_adress   );
            end if;
            
          end loop;  
          
          --utl_smtp.rcpt(   l_connection,   p_to   );

          l_temp   :=   l_temp   ||   'MIME-Version:   1.0'   ||     chr(13)   ||   chr(10);
          l_temp   :=   l_temp   ||   'To:   '   ||   p_to   ||   chr(13)   ||   chr(10);
          l_temp   :=   l_temp   ||   'From:   '   ||   p_from   ||   chr(13)   ||   chr(10);
          l_temp   :=   l_temp   ||   'Subject:   '   ||   p_subject   ||   chr(13)   ||   chr(10);
          l_temp   :=   l_temp   ||   'Reply-To:   '   ||   p_from   ||     chr(13)   ||   chr(10);
          l_temp   :=   l_temp   ||   'Content-Type:   multipart/alternative;   boundary='   ||
                                                    chr(34)   ||   l_boundary   ||     chr(34)   ||   chr(13)   ||
                                                    chr(10);
                                                    --chr(34)   is   "
          ----------------------------------------------------
          --   Write   the   headers
          dbms_lob.createtemporary(   l_body_html,   false,   10   );
          dbms_lob.write(l_body_html,length(l_temp),1,l_temp);


          ----------------------------------------------------
          --   Write   the   text   boundary
          l_offset   :=   dbms_lob.getlength(l_body_html)   +   1;
          l_temp       :=   '--'   ||   l_boundary   ||   chr(13)||chr(10);
          l_temp       :=   l_temp   ||   'content-type:   text/plain;   Charset=UTF-8'   ||
                                      chr(13)   ||   chr(10)   ||   chr(13)   ||   chr(10);
          dbms_lob.write(l_body_html,length(l_temp),l_offset,l_temp);

          ----------------------------------------------------
          --   Write   the   plain   text   portion   of   the   email
          l_offset   :=   dbms_lob.getlength(l_body_html)   +   1;
          dbms_lob.write(l_body_html,length(p_text),l_offset,p_text);

          ----------------------------------------------------
          --   Write   the   HTML   boundary
          l_temp       :=   chr(13)||chr(10)||chr(13)||chr(10)||'--'   ||   l_boundary   ||
                                          chr(13)   ||   chr(10);
          l_temp       :=   l_temp   ||   'content-type:   text/html;'   ||
                                        chr(13)   ||   chr(10)   ||   chr(13)   ||   chr(10);
          l_offset   :=   dbms_lob.getlength(l_body_html)   +   1;
          dbms_lob.write(l_body_html,length(l_temp),l_offset,l_temp);

          ----------------------------------------------------
          --   Write   the   HTML   portion   of   the   message
          l_offset   :=   dbms_lob.getlength(l_body_html)   +   1;
          dbms_lob.write(l_body_html,length(p_html),l_offset,p_html);

          ----------------------------------------------------
          --   Write   the   final   html   boundary
          l_temp       :=   chr(13)   ||   chr(10)   ||   '--'   ||     l_boundary   ||   '--'   ||   chr(13);
          l_offset   :=   dbms_lob.getlength(l_body_html)   +   1;
          dbms_lob.write(l_body_html,length(l_temp),l_offset,l_temp);


          ----------------------------------------------------
          --   Send   the   email   in   1900   byte   chunks   to   UTL_SMTP
          l_offset     :=   1;
          l_ammount   :=   1900;
          utl_smtp.open_data(l_connection);
          while   l_offset   <   dbms_lob.getlength(l_body_html)   loop
                    utl_smtp.write_raw_data(l_connection,
                    UTL_RAW.CAST_TO_RAW(dbms_lob.substr(l_body_html,l_ammount,l_offset)));
                  l_offset     :=   l_offset   +   l_ammount   ;
                  l_ammount   :=   least(1900,dbms_lob.getlength(l_body_html)   -   l_ammount);
          end   loop;
          utl_smtp.close_data(l_connection);
          utl_smtp.quit(   l_connection   );
          dbms_lob.freetemporary(l_body_html);
  end;