CREATE OR REPLACE PROCEDURE SEND_MAIL (
                                       p_to        IN VARCHAR2,
                                       p_from      IN VARCHAR2,
                                       p_from_name IN VARCHAR2,
                                       p_reply_to  IN VARCHAR2,
                                       p_subject   IN VARCHAR2,
                                       p_body      IN VARCHAR2 DEFAULT NULL,
                                       p_body_html IN VARCHAR2 DEFAULT NULL,
                                       p_attach_name IN VARCHAR2 DEFAULT NULL,
                                       p_attach_mime IN VARCHAR2 DEFAULT NULL,
                                       p_attach_blob IN BLOB DEFAULT NULL)
AS
  l_mail_conn   UTL_SMTP.connection;
  l_boundary1   varchar2(50);
  l_boundary2   varchar2(50);
  l_step        pls_integer  :=9000;
  l_smtp_host   varchar2(50) := 'Ваш почтарь';
  l_smtp_port   number := 25;
  l_attach_mime varchar2(255); 

function encodeQ(subj_str varchar2) return varchar2
AS
begin
  return replace(UTL_ENCODE.MIMEHEADER_ENCODE (subj_str, 'utf8', UTL_ENCODE.QUOTED_PRINTABLE),UTL_TCP.crlf,''); -- replace важно!
end;

function get_boundary return varchar2 is
begin
 return upper(MD5HASH(current_timestamp));
end;

BEGIN
  l_boundary1 := '--=:'||get_boundary||':';
  l_boundary2 := '--=:'||get_boundary||':';
  
  l_mail_conn := UTL_SMTP.open_connection(l_smtp_host, l_smtp_port);
  UTL_SMTP.helo(l_mail_conn, l_smtp_host);
  UTL_SMTP.mail(l_mail_conn, p_from);
  UTL_SMTP.rcpt(l_mail_conn, p_to);
  UTL_SMTP.open_data(l_mail_conn);
  
  UTL_SMTP.write_data(l_mail_conn, 'MIME-Version: 1.0' || UTL_TCP.crlf);  
  UTL_SMTP.write_data(l_mail_conn, 'To: ' || p_to || UTL_TCP.crlf);
  UTL_SMTP.write_data(l_mail_conn, 'From: ' || encodeQ(p_from_name) || '<' || p_from ||'>' || UTL_TCP.crlf);
  UTL_SMTP.write_data(l_mail_conn, 'Reply-To: ' || nvl(p_reply_to, p_from) || UTL_TCP.crlf);
  if p_attach_name is null then
    UTL_SMTP.write_data(l_mail_conn, 'Content-Type: multipart/alternative; boundary="' || l_boundary1 || '"' || UTL_TCP.crlf);
  else
    UTL_SMTP.write_data(l_mail_conn, 'Content-Type: multipart/mixed; boundary="' || l_boundary1 || '"' || UTL_TCP.crlf);
  end if;
  UTL_SMTP.write_data(l_mail_conn, 'Content-Transfer-Encoding: 8bit'|| UTL_TCP.crlf);
  UTL_SMTP.write_data(l_mail_conn, 'Date: ' || to_char(current_timestamp,'Dy, DD Mon YYYY HH24:MI:SS TZHTZM', 'NLS_DATE_LANGUAGE=AMERICAN') || UTL_TCP.crlf);
  UTL_SMTP.write_data(l_mail_conn, 'Subject: ' || encodeQ(nvl(p_subject,'Тема письма не указана.')) || UTL_TCP.crlf);

  -- для почтарей комментируем этот кусок, а для mail-tester оставляем
  UTL_SMTP.write_data(l_mail_conn, 'List-Unsubscribe: <mailto:unsubscribe@вашдомен.com?subject=Unsubscribe>'|| UTL_TCP.crlf ); 
  UTL_SMTP.write_data(l_mail_conn, 'List-Subscribe: <mailto:subscribe@вашдомен.com?subject=Subscribe>'|| UTL_TCP.crlf );
  --------------------------------------------------------------------
  
  UTL_SMTP.write_data(l_mail_conn, 'List-Owner: <mailto:abuse@вашдомен.com?subject=Abuse>'|| UTL_TCP.crlf );
  UTL_SMTP.write_data(l_mail_conn, 'List-Help: <mailto:help@вашдомен.com?subject=Help>'|| UTL_TCP.crlf || UTL_TCP.crlf);
  
  UTL_SMTP.write_data(l_mail_conn, '--' || l_boundary1 || UTL_TCP.crlf);
  UTL_SMTP.write_data(l_mail_conn, 'Content-Type: multipart/alternative; boundary="' || l_boundary2 || '"' || UTL_TCP.crlf || UTL_TCP.crlf);
   
  if p_body is not null then
    UTL_SMTP.write_data(l_mail_conn, '--' || l_boundary2 || UTL_TCP.crlf);
    UTL_SMTP.write_data(l_mail_conn, 'Content-Type: text/plain; charset="utf-8"' || UTL_TCP.crlf || UTL_TCP.crlf);
    UTL_SMTP.write_raw_data(l_mail_conn, UTL_RAW.cast_to_raw(p_body)); -- write_raw_data важно
    UTL_SMTP.write_data(l_mail_conn, UTL_TCP.crlf);
  end if;

  if p_body_html is not null then
    UTL_SMTP.write_data(l_mail_conn, '--' || l_boundary2 || UTL_TCP.crlf);
    UTL_SMTP.write_data(l_mail_conn, 'Content-Type: text/html; charset="utf-8"' || UTL_TCP.crlf || UTL_TCP.crlf);
    UTL_SMTP.write_raw_data(l_mail_conn, UTL_RAW.cast_to_raw(p_body_html)); -- write_raw_data важно
    UTL_SMTP.write_data(l_mail_conn, UTL_TCP.crlf );
  end if;
  
  UTL_SMTP.write_data(l_mail_conn, '--' || l_boundary2 || '--' || UTL_TCP.crlf || UTL_TCP.crlf);
  
  if p_attach_name is not null then
    UTL_SMTP.write_data(l_mail_conn, '--' || l_boundary1 || UTL_TCP.crlf);
    
    if instr(p_attach_name,'.pdf')>0 then l_attach_mime := 'application/pdf';  
    elsif instr(p_attach_name,'.docx')>0 then l_attach_mime := 'application/vnd.openxmlformats-officedocument.wordprocessingml.document';
    else l_attach_mime := p_attach_mime;
    end if;
    
    UTL_SMTP.write_data(l_mail_conn, 'Content-Type: ' || l_attach_mime || '; name="' || p_attach_name || '"' || UTL_TCP.crlf);
    UTL_SMTP.write_data(l_mail_conn, 'Content-Disposition: attachment; filename="' || p_attach_name || '"' || UTL_TCP.crlf);
    UTL_SMTP.write_data(l_mail_conn, 'Content-Transfer-Encoding: base64' || UTL_TCP.crlf || UTL_TCP.crlf);

    for i in 0 .. trunc((DBMS_LOB.getlength(p_attach_blob) - 1 )/l_step) 
    loop
      UTL_SMTP.write_data(l_mail_conn, UTL_RAW.cast_to_varchar2(UTL_ENCODE.base64_encode(DBMS_LOB.substr(p_attach_blob, l_step, i * l_step + 1))) || UTL_TCP.crlf); -- || UTL_TCP.crlf важно 
    end loop; 

    UTL_SMTP.write_data(l_mail_conn, UTL_TCP.crlf || UTL_TCP.crlf);
  end if;

  UTL_SMTP.write_data(l_mail_conn, '--' || l_boundary1 || '--' || UTL_TCP.crlf);
  UTL_SMTP.close_data(l_mail_conn);

  UTL_SMTP.quit(l_mail_conn);
  
  exception 
    when others then
            UTL_SMTP.close_data(l_mail_conn);
            UTL_SMTP.quit(l_mail_conn);          
END;

CREATE OR REPLACE FUNCTION MD5HASH (str IN VARCHAR2)
	RETURN VARCHAR2
	IS v_checksum VARCHAR2(32);
 
	BEGIN
		if str is null then 
      v_checksum:='';
    else 
      v_checksum := lower( rawtohex( utl_raw.cast_to_raw( sys.dbms_obfuscation_toolkit.md5(input_string => str) ) ) );
    end if;
    
		RETURN v_checksum;
		EXCEPTION
			WHEN NO_DATA_FOUND THEN
      return '';
			NULL;
      
		WHEN OTHERS THEN
			-- Consider logging the error and then re-raise
      return '';
			RAISE;
	END md5hash;

   LOOP
      BEGIN
        dbms_lob.read(message, v_amt, ps, vBuf);
        ps := ps + v_amt;
        utl_smtp.write_raw_data (v_Mail_Conn,utl_encode.base64_encode ( utl_raw.cast_to_raw(convert(vBuf,'UTF8'))));
      EXCEPTION
        WHEN no_data_found THEN
            EXIT;
      END;
    END LOOP;