以下的文章主要是以實例演示的方式來講解如何正確的執行Oracle發送郵件,以下的文章就就是對其實際的操作步驟的講解,如果你對其相關的實際操作有興趣的話,你就可以對以下的文章點擊觀看了。
Linux AS3+Oracle 9.2||10.20.
從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.創建Oracle發送郵件的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.執行Oracle發送郵件:
- exec send_mail('[email protected]','[email protected]','我我','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.