오라클 이메일 보내기(한글깨짐보완)

CREATE OR REPLACE PROCEDURE SP_SEND_EMAIL(
    P_fromemail  IN  VARCHAR2 ,  
    P_toemail    IN  VARCHAR2 , 
    P_subject    IN  VARCHAR2 , 
    P_body       IN  VARCHAR2 )  

IS
  
    l_boundary      VARCHAR2(50) := '----=*#abc1234321cba#*='; 

    v_From      VARCHAR2(80) := P_fromemail||'@xxx.com';
    v_Recipient VARCHAR2(80) := P_toemail||'@xxx.com';
    v_Subject   VARCHAR2(80) := P_subject;
    v_Mail_Host VARCHAR2(30) := 'xx.co.kr';
    v_Mail_Conn utl_smtp.Connection;
    crlf        VARCHAR2(2)  := chr(13)||chr(10);

BEGIN
 
    utl_tcp.close_all_connections; 


    v_Mail_Conn := utl_smtp.Open_Connection(v_Mail_Host,25);

    utl_smtp.Helo(v_Mail_Conn, v_Mail_Host);
    utl_smtp.Mail(v_Mail_Conn, v_From);              -- 보내는사람
    utl_smtp.Rcpt(v_Mail_Conn, v_Recipient);         -- 받는사람

 
    utl_smtp.Open_data(v_Mail_Conn);
 


    UTL_SMTP.Write_raw_data(v_Mail_Conn, UTL_RAW.Cast_to_raw('Date: ' || TO_CHAR(SYSDATE, 'DD-MON-YYYY HH24:MI:SS') || crlf));
    UTL_SMTP.Write_raw_data(v_Mail_Conn, UTL_RAW.Cast_to_raw('To: ' || ' <' || v_Recipient || '>' || crlf));
    UTL_SMTP.Write_raw_data(v_Mail_Conn, UTL_RAW.Cast_to_raw('From: ' || ' <' || v_From || '>' || crlf));
    UTL_SMTP.Write_raw_data(v_Mail_Conn, UTL_RAW.Cast_to_raw('Subject: ' || P_subject || crlf));
    UTL_SMTP.Write_raw_data(v_Mail_Conn, UTL_RAW.Cast_to_raw('Reply-To: ' || ' <' || v_From || '>' || crlf));
    UTL_SMTP.Write_raw_data(v_Mail_Conn, UTL_RAW.Cast_to_raw('MIME-Version: 1.0' || crlf));
    UTL_SMTP.Write_raw_data(v_Mail_Conn, UTL_RAW.Cast_to_raw('Content-Type: multipart/alternative; boundary="' || l_boundary || '"' || crlf || crlf));

 

    IF P_body IS NOT NULL THEN
        UTL_SMTP.Write_raw_data(v_Mail_Conn, UTL_RAW.Cast_to_raw('--' || l_boundary || crlf));
        UTL_SMTP.Write_raw_data(v_Mail_Conn, UTL_RAW.Cast_to_raw('Content-Type: text/html; charset="utf-8"' || crlf || crlf));

        UTL_SMTP.Write_raw_data(v_Mail_Conn, UTL_RAW.Cast_to_raw(P_body));
        UTL_SMTP.Write_raw_data(v_Mail_Conn, UTL_RAW.Cast_to_raw(crlf || crlf));
    END IF;

    UTL_SMTP.Write_raw_data(v_Mail_Conn, UTL_RAW.Cast_to_raw('--' || l_boundary || '--' || crlf));


    UTL_SMTP.Close_data(v_Mail_Conn);
    UTL_SMTP.Quit(v_Mail_Conn); 

EXCEPTION
    WHEN UTL_SMTP.Transient_error OR UTL_SMTP.Permanent_error THEN 
              DBMS_OUTPUT.Put_line(SQLERRM);
              UTL_SMTP.Quit(v_Mail_Conn);
    WHEN OTHERS THEN 
              DBMS_OUTPUT.Put_line(SQLERRM);
              UTL_SMTP.Quit(v_Mail_Conn);

END SP_SEND_EMAIL;

 

 

 

 

[ 테스트 ] 

declare  
  i integer; 
  
begin
  
   SP_SEND_EMAIL('fromemail넣기', 'toemail넣기', '테스트', '이메일 테스트 발송 입니다') ;
   
end;