ORACLE发送邮件

--===============================================================================

    --发送邮件的主过程---------------------------------------------------------------

    --xuyinghuai 20150603------------------------------------------------------------

    Procedure send_mail_ (p_From     Varchar2, --邮件发送人

                          p_Fromuser Varchar2, --发件人昵称

                          p_Touser   Varchar2, --接受人昵称

                          p_To       Varchar2, --邮件接收人

                          p_Cc       Varchar2, --邮件抄送人

                          p_Subject  Varchar2, --邮件标题

                          p_Message  Varchar2, --邮件内容

                          p_User     Varchar2, --邮件验证用户

                          p_Mailhost VARCHAR2, --邮件的服务地址

                          p_Psd      Varchar2 --邮件验证密码

                         ) IS

        v_Conn     Utl_Smtp.Connection; --到邮件服务器的连接

        v_Msg      Varchar2(32700);     --邮件内容

        Psrc       Varchar2(4000);

        Psrc1      Varchar2(4000);

        v_Touser1  VARCHAR2(4000);

        v_Touser   VARCHAR2(500);

        v_To       Varchar2(500);

        i          Number := 1;

        j          Number := 1;

        m          Number := 1;

        n          Number := 1;

        str_error  VARCHAR2(20000);

    BEGIN

        v_Touser1 := '';

        v_Conn    := Utl_Smtp.Open_Connection(p_Mailhost, 25);

        Utl_Smtp.Ehlo(v_Conn, p_Mailhost);      -- 是用 ehlo() 而不是 helo() 函数否则会报：ORA-29279: SMTP 永久性错误: 503 5.5.2 Send hello first.

        Utl_Smtp.Command(v_Conn, 'AUTH LOGIN'); -- smtp服务器登录校验

        Utl_Smtp.Command(v_Conn, Utl_Raw.Cast_To_Varchar2(Utl_Encode.Base64_Encode(Utl_Raw.Cast_To_Raw(p_User))));

        Utl_Smtp.Command(v_Conn, Utl_Raw.Cast_To_Varchar2(Utl_Encode.Base64_Encode(Utl_Raw.Cast_To_Raw(p_Psd ))));

        Utl_Smtp.Mail   (v_Conn, '<' || p_From || '>'); --设置发件人

        -- 设置收件人----------------

        Psrc  := Rtrim(Ltrim(p_To    , ';'), ';') || ';';

        Psrc1 := Rtrim(Ltrim(p_Touser, ';'), ';') || ';';

 

        IF Nvl(Instr(Psrc, ';', j), 0)>0 THEN

            LOOP

                i := Nvl(Instr(Psrc , ';', j), 0);

                m := Nvl(Instr(Psrc1, ';', n), 0);

                If i > 0 THEN

                    v_To     := Trim(Substr(Psrc , j, i - j));

                    v_Touser := nvl(Trim(Substr(Psrc1, n, m - n)),'1');

                    Utl_Smtp.Rcpt(v_Conn, v_To);            --设置收件人

                    IF v_Touser = '1' THEN

                        v_Touser := trim(SUBSTR(v_To,1,Instr(v_To,'@')-1));

                    END IF;

                    IF j = 1 THEN

                        v_Touser1 := v_Touser1 || v_Touser || '<' || v_To  || '>';

                    ELSE

                        v_Touser1 := v_Touser1 || ';'|| v_Touser || '<' || v_To  || '>';

                    END IF;

                    j  := i + 1;

                    n  := m + 1;

                END IF;

                Exit When i = 0;

            END LOOP;

        ELSE

            Utl_Smtp.Rcpt(v_Conn, p_To);            --设置收件人

            v_Touser1 := v_Touser1 || p_Touser || '<' || p_To  || '>';

        END IF;

        -- 设置抄送人----------------

        i := 1;

        j := 1;

        IF nvl(p_Cc,'aa')<>'aa' THEN

            Psrc := Rtrim(Ltrim(p_Cc, ';'), ';')|| ';';

            IF Nvl(Instr(Psrc, ';', j), 0)>0 THEN

                LOOP

                    i := Nvl(Instr(Psrc, ';', j), 0);

                    If i > 0 THEN

                        v_To := Trim(Substr(Psrc, j, i - j));

                        j  := i + 1;

                        Utl_Smtp.Rcpt(v_Conn, v_To);            --设置抄送人

                    END IF;

                    Exit When i = 0;

                END LOOP;

            ELSE

                IF nvl(p_Cc,'1') <> '1' THEN

                    Utl_Smtp.Rcpt(v_Conn, p_Cc);            --设置抄送人

                END IF;

            END IF;

        END IF;

        -- 创建要发送的邮件内容注意报头信息和邮件正文之间要空一行

        v_Msg := 'Date: ' || To_Char(Sysdate, 'dd mon yy hh24:mi:ss') || Utl_Tcp.Crlf ||

                 'From: ' || p_Fromuser || '<' || p_From || '>'       || Utl_Tcp.Crlf ||

                 'To:   ' || v_Touser1                                || Utl_Tcp.Crlf ||

                 'Cc:   ' || p_Cc                                     || Utl_Tcp.Crlf ||

                 'Subject: ' || p_Subject                             || Utl_Tcp.Crlf ||

                 Utl_Tcp.Crlf -- 这前面是报头信息

                 || p_Message; -- 这个是邮件正文

        Utl_Smtp.Open_Data(v_Conn); --打开流

        Utl_Smtp.Write_Raw_Data(v_Conn,Utl_Raw.Cast_To_Raw(Convert('Content-Type:text/html;charset=utf-8' ||

                                                      v_Msg, 'ZHS16GBK'))); --这样写标题和内容都能用中文

        Utl_Smtp.Close_Data(v_Conn); --关闭流

        Utl_Smtp.Quit(v_Conn); --关闭连接

    Exception

        When Others THEN

        str_error := Dbms_Utility.Format_Error_Stack || Dbms_Utility.Format_Call_Stack;

 

 

        Dbms_Output.Put_Line(str_error);

    END;