Oracle mail, oracle

Oracle mail, oracle

Create or replace procedure PRC_sendmail (p_receiver VARCHAR2, -- mail recipient p_subject VARCHAR2, -- mail title p_message VARCHAR2 -- mail body) IS -- the following four variables should be assigned v_smtphost VARCHAR2 (30): = 'smtp .qq.com 'according to the actual mail server; -- smtp server address (hotmail IS smtp.live.com, test failed) v_smtpport number (5): = 25; -- smtp Service port v_user VARCHAR2 (30): = '45 @ qq.com '; -- user name used to log on to the SMTP server v_pass VARCHAR2 (20 ): = 'xiyuan '; -- the password used to log on to the SMTP server v_sender VARCHAR2 (50): = '45 @ qq.com'; -- the sender's email address, which generally corresponds to v_conn UTL_SMTP.connection of ps_user; -- connection to the email server v_msg varchar2 (4000); -- mail content

BEGIN

V_conn: = UTL_SMTP.open_connection (v_smtphost, v_smtpport); -- use the ehlo () instead of the helo () function -- otherwise the ORA-29279: SMTP permanent error: 503 5.5.2 Send hello first. UTL_SMTP.ehlo (v_conn, v_smtphost );

-- Smtp server logon verification UTL_SMTP.command (v_conn, 'auth login'); UTL_SMTP.command (v_conn, encode (UTL_RAW.cast_to_raw (v_user); UTL_SMTP.command (v_conn, UTL_RAW.cast_to_varchar2 (UTL_ENCODE.base64_encode (UTL_RAW.cast_to_raw (v_pass ))));

-- Set the sender and recipient UTL_SMTP.mail (v_conn, '<' | v_sender | '>'); UTL_SMTP.rcpt (v_conn, '<' | p_javaser | '> ');

-- Create the mail content to be sent. Note that a line v_msg: = 'date: 'is required between the header information and the mail body. | TO_CHAR (SYSDATE, 'yyyy mm dd hh24: mi: ss ') | UTL_TCP.CRLF | 'from:' | v_sender | ''| UTL_TCP.CRLF | ': '| p_receiver | ''| UTL_TCP.CRLF | 'subject:' | p_subject | UTL_TCP.CRLF -- header information | p_message; -- This is the body of the email.

-- Enable the stream UTL_SMTP.open_data (v_conn); -- the title and content are available in the Chinese alphabet (v_conn, UTL_RAW.cast_to_raw (v_msg); -- disable the stream UTL_SMTP.close_data (v_conn ); -- disable UTL_SMTP.quit (v_conn );

Exception when others then DBMS_OUTPUT.put_line (bytes); DBMS_OUTPUT.put_line (DBMS_UTILITY.format_call_stack); UTL_SMTP.quit (v_conn); END PRC_sendmail;

Run the script:

SQL> exec PRC_sendmail (p_receiver => '1970 @ qq.com ', p_subject =>' t ttt', p_message => 'yy in yy ');