Oracle Outgoing Mail stored procedures

CREATE OR REPLACE PROCEDURE send_mail (

P_recipient VARCHAR2,--Mail Recipient

P_subject VARCHAR2,--Mail header

P_message VARCHAR2--Message body

)

Is

--The following four variables should be assigned according to the actual mail server

V_mailhost VARCHAR2 (+): = ' smtp.cheyipai.com '; --SMTP server Address

V_user VARCHAR2 (+): = ' Wangxuhui '; --User name to log on to the SMTP server

V_pass VARCHAR2 (): = ' Tweatce3d '; --Login to the SMTP server password

V_sender VARCHAR2 (): = ' [email protected] '; --Sender mailbox, general and Ps_user correspondence

V_conn UTL_SMTP. Connection; --Connection to the mail server

V_MSG varchar2 (4000); --Mail content

BEGIN

V_conn: = Utl_smtp.open_connection (V_mailhost, 25);

Utl_smtp.ehlo (V_conn, v_mailhost); --Is using EHLO () instead of helo () function

--otherwise it will be reported: ORA-29279:SMTP permanent error: 503 5.5.2 Send Hello first.

Utl_smtp.command (V_conn, ' AUTH LOGIN '); --SMTP Server login check

Utl_smtp.command (V_conn,utl_raw.cast_to_varchar2 (Utl_encode.base64_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)));

Utl_smtp.mail (V_conn, V_sender); --Set Sender

UTL_SMTP.RCPT (V_conn, p_recipient); --Set recipient

--Create a message content to send note the header message and the message body are blank lines

V_msg: = ' Date: ' | | To_char (sysdate, ' dd Mon yy hh24:mi:ss ')

|| Utl_tcp. CRLF | | ' From: ' | | ' < ' | | V_sender | | ' > '

|| Utl_tcp. CRLF | | ' To: ' | | ' < ' | | p_recipient | | ' > '

|| Utl_tcp. CRLF | | ' Subject: ' | | P_subject

|| Utl_tcp. CRLF | | Utl_tcp. CRLF-This is preceded by a header message

||    P_message; --This is the message body

Utl_smtp.open_data (V_conn); --Open stream

Utl_smtp.write_raw_data (V_conn, Utl_raw.cast_to_raw (v_msg)); -The title and content can be written in Chinese

Utl_smtp.close_data (V_conn); --Close the stream

Utl_smtp.quit (V_conn); --Close connection

EXCEPTION

When OTHERS Then

Dbms_output.put_line (Dbms_utility.format_error_stack);

Dbms_output.put_line (Dbms_utility.format_call_stack);

END Send_mail;

This article is from "Pioneer Home" blog, please make sure to keep this source http://jackwxh.blog.51cto.com/2850597/1851957

Oracle Outgoing Mail stored procedures