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

오라클을 이용한 이메일 보내기

 

create or replace package PA_SEND_EMAIL is


  gn_err_no         number;
  gs_err_msg        varchar2(200);

 PROCEDURE  sp_send_email( P_fromemail  IN  VARCHAR2,  
                           P_toemail    IN  VARCHAR2, 
                           P_subject    IN  VARCHAR2, 
                           P_body       IN  VARCHAR2);




 FUNCTION sf_send_email( parm_gbn        char )
                         return          varchar2 ;
 PRAGMA RESTRICT_REFERENCES (sf_send_email,   WNDS); 
 
 
end PA_SEND_EMAIL;
/

 

 

create or replace package body PA_SEND_EMAIL is

  
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||'@sxxx.com'; 
    v_Mail_Host VARCHAR2(30) := 'xx.co.kr';
    v_Mail_Conn utl_smtp.Connection;
    crlf        VARCHAR2(2)  := chr(13)||chr(10);

BEGIN
    gn_err_no  := 0;
    gs_err_msg := null;
    
    
      

    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
          gn_err_no  := -1;
          gs_err_msg := SUBSTR('메일발송오류(1)'||SQLERRM,1,100); 
           
          UTL_SMTP.Quit(v_Mail_Conn); 
     WHEN OTHERS THEN
          gn_err_no  := -1;
          gs_err_msg := SUBSTR('메일발송오류(2)'||SQLERRM,1,100); 
           
          UTL_SMTP.Quit(v_Mail_Conn);  
       
END sp_send_email;




FUNCTION sf_send_email(   parm_gbn        char )
                          return          varchar2 
IS

begin
    if parm_gbn = '1' then
       return to_char(gn_err_no) ;
    else
       return gs_err_msg ;
    end if;
END sf_send_email;

end PA_SEND_EMAIL;
/

 

 

 

 

[ 테스트 ]

   PA_SEND_EMAIL.SP_SEND_EMAIL(from_email  , to_email, '테스트', '테스트 발송입니다');