通过Oracle成功发送邮件

LINUX AS3+Oracle 9.2||10.20.1从Oracle成功发送邮件:

    示例如下：

    具体的测试环境:LINUX AS3 , Oracle 9.0.2.4

    SQL> select * from v$version;

    BANNER

    ----------------------------------------------------------------

    Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production

    PL/SQL Release 9.2.0.4.0 - Production

    CORE 9.2.0.3.0 Production

    TNS for Linux: Version 9.2.0.4.0 - Production

    NLSRTL Version 9.2.0.4.0 - Production

    SQL> select * from v$version;

    Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod

    PL/SQL Release 10.2.0.1.0 - Production

    CORE 10.2.0.1.0 Production

    TNS for Linux: Version 10.2.0.1.0 - Production

    NLSRTL Version 10.2.0.1.0 - Production

    MAIL服务器为WIN2003,WINMAIL

    1.保证ORACLE服务器到MAIL服务器网络畅通,25端口打开

    2.创建发送邮件的procedure如下:

    ---------------------------------------

    CREATE OR REPLACE PROCEDURE SEND_MAIL

    (as_sender in varchar2, --邮件发送者

    as_recp in varchar2, --邮件接收者

    as_subject in varchar2, --邮件标题

    as_msg_body in varchar2) --邮件内容

    IS

    ls_mailhost varchar2(30) := "mail server"; -- address or IP

    lc_mail_conn utl_smtp.connection;

    ls_subject varchar2(100);

    ls_msg_body varchar2(20000);

    ls_username varchar2(256) := "usercode";

    ls_password varchar2(256) := "password";

    BEGIN

    lc_mail_conn := utl_smtp.open_connection(ls_mailhost, 25);

    utl_smtp.helo(lc_mail_conn, ls_mailhost);

    utl_smtp.command(lc_mail_conn, "AUTH LOGIN");

    utl_smtp.command(lc_mail_conn, demo_base64.encode(utl_raw.cast_to_raw(ls_username)));

    utl_smtp.command(lc_mail_conn, demo_base64.encode(utl_raw.cast_to_raw(ls_password)));

    ls_subject := "Subject: [" || upper(sys_context("userenv", "db_name")) || "] - " || as_subject;

    ls_msg_body := as_msg_body;

    utl_smtp.mail(lc_mail_conn, "<"||as_sender||">"); --这里的"<" 一定要写,不然会出现permanent error

    utl_smtp.rcpt(lc_mail_conn, "<"||as_recp||">");--这里的"<" 一定要写,不然会出现permanent error

    utl_smtp.open_data(lc_mail_conn);

    ls_msg_body := "From: " || as_sender || chr(13) || chr(10) || "To: " || as_recp || chr(13) || chr(10) || ls_subject ||

    chr(13) || chr(10) || chr(13) || chr(10) || ls_msg_body;

    utl_smtp.write_raw_data(lc_mail_conn, utl_raw.cast_to_raw(ls_msg_body)); --这样写subject可以支持中文但body内容不支持中文;

    -- utl_smtp.write_data(lc_mail_conn, ls_msg_body); --这样写subject不支持中文

    utl_smtp.close_data(lc_mail_conn);

    utl_smtp.quit(lc_mail_conn);

    EXCEPTION

    WHEN UTL_SMTP.INVALID_OPERATION THEN

    dbms_output.put_line("invalid operation");

    WHEN UTL_SMTP.TRANSIENT_ERROR THEN

    dbms_output.put_line("transient error");

    WHEN UTL_SMTP.PERMANENT_ERROR THEN

    dbms_output.put_line("permanent error");

    WHEN OTHERS THEN

    dbms_output.put_line("others");

    end send_mail;

    3.执行发送邮件:

    exec send_mail("heyu@163.net","admin@163.net","我我","this is a oracle test mail");

    注意事项:上面的过程如果在编译中出现demo_base64.encode must be declared,请大家创建下面的包和包体;

    ---------------------------------

    CREATE OR REPLACE PACKAGE demo_base64 IS

    -- Base64-encode a piece of binary data.

    --

    -- Note that this encode function does not split the encoded text into

    -- multiple lines with no more than 76 bytes each as required by

    -- the MIME standard.

 --
    FUNCTION encode(r IN RAW) RETURN VARCHAR2;

    END;

    ------------------------------------

    CREATE OR REPLACE PACKAGE BODY demo_base64 IS

    TYPE vc2_table IS TABLE OF VARCHAR2(1) INDEX BY BINARY_INTEGER;

    map vc2_table;

    -- Initialize the Base64 mapping

    PROCEDURE init_map IS

    BEGIN

    map(0) :="A"; map(1) :="B"; map(2) :="C"; map(3) :="D"; map(4) :="E";

    map(5) :="F"; map(6) :="G"; map(7) :="H"; map(8) :="I"; map(9):="J";

    map(10):="K"; map(11):="L"; map(12):="M"; map(13):="N"; map(14):="O";

    map(15):="P"; map(16):="Q"; map(17):="R"; map(18):="S"; map(19):="T";

    map(20):="U"; map(21):="V"; map(22):="W"; map(23):="X"; map(24):="Y";

    map(25):="Z"; map(26):="a"; map(27):="b"; map(28):="c"; map(29):="d";

    map(30):="e"; map(31):="f"; map(32):="g"; map(33):="h"; map(34):="i";

    map(35):="j"; map(36):="k"; map(37):="l"; map(38):="m"; map(39):="n";

    map(40):="o"; map(41):="p"; map(42):="q"; map(43):="r"; map(44):="s";

    map(45):="t"; map(46):="u"; map(47):="v"; map(48):="w"; map(49):="x";

    map(50):="y"; map(51):="z"; map(52):="0"; map(53):="1"; map(54):="2";

    map(55):="3"; map(56):="4"; map(57):="5"; map(58):="6"; map(59):="7";

    map(60):="8"; map(61):="9"; map(62):="+"; map(63):="/";

    END;

    FUNCTION encode(r IN RAW) RETURN VARCHAR2 IS

    i pls_integer;

    x pls_integer;

    y pls_integer;

    v VARCHAR2(32767);

    BEGIN

    -- For every 3 bytes, split them into 4 6-bit units and map them to

    -- the Base64 characters

    i := 1;

    WHILE ( i + 2 <= utl_raw.length(r) ) LOOP

    x := to_number(utl_raw.substr(r, i, 1), "0X") * 65536 +

    to_number(utl_raw.substr(r, i + 1, 1), "0X") * 256 +

    to_number(utl_raw.substr(r, i + 2, 1), "0X");

    y := floor(x / 262144); v := v || map(y); x := x - y * 262144;

    y := floor(x / 4096); v := v || map(y); x := x - y * 4096;

    y := floor(x / 64); v := v || map(y); x := x - y * 64;

    v := v || map(x);

    i := i + 3;

    END LOOP;

    -- Process the remaining bytes that has fewer than 3 bytes.

    IF ( utl_raw.length(r) - i = 0) THEN

    x := to_number(utl_raw.substr(r, i, 1), "0X");

    y := floor(x / 4); v := v || map(y); x := x - y * 4;

    x := x * 16; v := v || map(x);

    v := v || "==";

    ELSIF ( utl_raw.length(r) - i = 1) THEN

    x := to_number(utl_raw.substr(r, i, 1), "0X") * 256 +

    to_number(utl_raw.substr(r, i + 1, 1), "0X");

    y := floor(x / 1024); v := v || map(y); x := x - y * 1024;

    y := floor(x / 16); v := v || map(y); x := x - y * 16;

    x := x * 4; v := v || map(x);

    v := v || "=";

    END IF;

    RETURN v;

    END;

    BEGIN

    init_map;

    END;

    --结束