Oracle send mail stored procedure

A stored procedure that sends a message

CREATE OR REPLACE PROCEDURE Up_send_mail (subject in varchar2 (500), mail_content in varchar2 (2000) Re_out out number (2)) AS/** compilation: withered leaves function: send email date: 2013-03-13 return code: 0 = send success 1 = send failed * * / smtp_conn utl_smtp.connection -- define the user name user_name varchar2 (50): = utl_raw.cast_to_varchar2 (utl_encode.base64_encode (utl_raw.cast_to_raw ('mailname@163.com');-- define the password user_paswd varchar2 (50): = utl_raw.cast_to_varchar2 (utl_encode.base64_encode (utl_raw.cast_to_raw (' mailpass') -- define email header lv_mail_header varchar2: = 'From:mailname@163.com' | | utl_tcp.CRLF | |' To:tomail@qq.com' | | utl_tcp.CRLF | | 'Subject:' | | subject | | utl_tcp.CRLF -- define email body lv_mail_content varchar2 (2000): = utl_tcp.CRLF | | mail_content; begin re_out: = 0; smtp_conn: = utl_smtp.open_connection ('smtp.163.com',25); utl_smtp.helo (smtp_conn,'smtp.163.com'); utl_smtp.command (smtp_conn,'AUTH LOGIN') Utl_smtp.command (smtp_conn,user_name); utl_smtp.command (smtp_conn,user_paswd); utl_smtp.mail (smtp_conn,''); utl_smtp.rcpt (smtp_conn,''); utl_smtp.open_data (smtp_conn); utl_smtp.write_data (smtp_conn,lv_mail_header); utl_smtp.write_raw_data (smtp_conn,utl_raw.cast_to_raw (lv_mail_content)) Utl_smtp.close_data (smtp_conn); utl_smtp.quit (smtp_conn); exception when others then re_out: = 1; utl_smtp.quit (smtp_conn); RETURN; end

Note:

Change mailname@163.com to the address of sending mailbox mailpass to the password of sending mailbox tomail@qq.com to the address of receiving mailbox

Call stored procedure

DECLARE subject_in varchar: = 'Oracle Tablespace mail'; mail_content_in varchar (2000): =' this is a test email. I don't know what it's for!' ; re_out_out number (2); BEGIN Up_send_mail (subject_in,mail_content_in,re_out_out); IF re_out_out=0 THEN DBMS_OUTPUT.put_line ('sent successfully'); ELSE DBMS_OUTPUT.put_line ('send failed'); END IF;END