实例讲解如何通过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.

[1] [2] 下一页

【责编:Ken】