CREATE OR REPLACE PROCEDURE send_mail(p_recipient IN VARCHAR2, p_message IN VARCHAR2, p_subject IN VARCHAR2, p_sender IN VARCHAR2)
AS v_mailhost VARCHAR2 (30) := 'XXXXXXXXX'; --SMTP server address
mail_conn UTL_SMTP.connection; msg VARCHAR2 (4000); p_user VARCHAR2 (30) := 'XXXXX'; --login smtp server user name p_pass VARCHAR2 (30) := 'XXXXX' ; --login smtp server user passwordBEGIN /* email content need a empty line */ --select count(*) into p_content from tel_user_info; msg := 'Date:' ||TO_CHAR (SYSDATE, 'dd mon yy hh24:mi:ss') || UTL_TCP.crlf || 'From: ' || p_sender || '<' || p_sender || '>' || UTL_TCP.crlf || 'To: ' || p_recipient || '<' || p_recipient || '>' || UTL_TCP.crlf || 'Subject: ' || p_subject || UTL_TCP.crlf || UTL_TCP.crlf || p_message; --|| p_content; --dbms_output.put_line(msg); mail_conn := UTL_SMTP.open_connection (v_mailhost, 25); UTL_SMTP.ehlo (mail_conn, v_mailhost); /* smtp server login */ UTL_SMTP.command (mail_conn, 'AUTH LOGIN'); UTL_SMTP.command (mail_conn, UTL_RAW.cast_to_varchar2 (UTL_ENCODE.base64_encode (UTL_RAW.cast_to_raw (p_user) ) ) ); UTL_SMTP.command (mail_conn, UTL_RAW.cast_to_varchar2 (UTL_ENCODE.base64_encode (UTL_RAW.cast_to_raw (p_pass) ) ) ); UTL_SMTP.mail (mail_conn, p_sender); UTL_SMTP.rcpt (mail_conn, p_recipient); /* send email */ UTL_SMTP.DATA (mail_conn, msg); UTL_SMTP.quit (mail_conn);END send_mail;
