Send mail

PROCEDURE SEND_MAILc ( mail_from IN VARCHAR2
                                      , mail_to IN VARCHAR2
                                      , mail_subject IN VARCHAR2
                                      , mail_body IN clob
                                      , mail_smtp IN VARCHAR2
                                      , mail_user IN VARCHAR2
                                      , mail_user_pwd IN VARCHAR2)
is
    conn utl_smtp.connection;
    vMail_Current Varchar2(512);
    vMail         Varchar2(512);
    mail_date varchar2(20);
    mail_body2 clob;

BEGIN
     DBMS_APPLICATION_INFO.set_client_info('ADM UTIL SEND_MAILc');


    mail_body2 := mail_body;
   
    conn := utl_smtp.open_connection(mail_smtp,25);
    utl_smtp.helo(conn,mail_smtp );
    SELECT TO_CHAR( SYSDATE, 'dd Mon yy hh24:mi:ss' )  INTO mail_date FROM dual;


    /*** Autenticação do SMTP ***/
    IF LENGTH((trim(MAIL_USER))) > 0 THEN
       utl_smtp.command(conn, 'AUTH LOGIN');
       utl_smtp.command(conn, utl_raw.cast_to_varchar2(utl_encode.base64_encode(utl_raw.cast_to_raw((MAIL_USER)))));
       utl_smtp.command(conn, utl_raw.cast_to_varchar2(utl_encode.base64_encode(utl_raw.cast_to_raw((MAIL_USER_PWD)))));
    END IF;
    utl_smtp.mail(conn,mail_from);

    vMail:= mail_to;
    while INSTR(vMail,';') > 0
    loop
       vMail_Current := substr(vMail,1,INSTR(vMail,';')-1);
       utl_smtp.rcpt(conn,vMail_Current);
       vMail := substr(vMail,INSTR(vMail,';')+1);
    end loop;
    utl_smtp.rcpt(conn,vMail);

    utl_smtp.open_data (conn);
    utl_smtp.write_data(conn,'From: '    ||mail_from   ||utl_tcp.CRLF);
    utl_smtp.write_data(conn,'To: '      ||mail_to     ||utl_tcp.CRLF);
    utl_smtp.write_data(conn,'Subject: ' ||mail_subject||' Data: '||mail_date||utl_tcp.CRLF);
    utl_smtp.write_data(conn,'Content-Type:              Text/Html;'||utl_tcp.CRLF);
    utl_smtp.write_data(conn,utl_tcp.CRLF   );
       
    declare       
          vStart number := 1;
          vLength number := 3999; -- What ever size to split the CLOB into     
    begin     
          -- If the Body of the message is too large break up inserting into segments
          if length(mail_body2) > vLength then
          -- Build message in segments
            loop
       
              if vStart + vLength <= length(mail_body2) + 1 then
              utl_smtp.write_data(conn , substr(mail_body2, vStart, vLength));
              end if;
     
              vStart := vStart + vLength;
              exit when vStart + vLength > length(mail_body2);
         
            end loop;
       
            utl_smtp.write_data(conn , substr(mail_body2, vStart, length(mail_body2) - vStart + 1));
         
          else
            utl_smtp.write_data(conn , mail_body2);
          end if;     
    end;   
   
    utl_smtp.write_data(conn,utl_tcp.CRLF);
    utl_smtp.close_data(conn);
    utl_smtp.quit      (conn);


EXCEPTION
  WHEN utl_smtp.transient_error OR utl_smtp.permanent_error THEN
    BEGIN
      utl_smtp.quit(conn);

    EXCEPTION
      WHEN utl_smtp.transient_error OR utl_smtp.permanent_error THEN
        NULL; -- When the SMTP server is down or unavailable, we don't have
              -- a connection to the server. The quit call will raise an
              -- exception that we can ignore.

    END;
    raise_application_error(-20000,'Falha ao enviar o e-mail: ' || sqlerrm);

END SEND_MAILc;