Sending Email using PL/SQL Procedure

CREATE OR REPLACE PROCEDURE XXX_send_mail (p_to        IN VARCHAR2,                                       p_from      IN VARCHAR2,                                       p_message   IN VARCHAR2,                                       p_smtp_host IN VARCHAR2,                                       p_smtp_port IN NUMBER DEFAULT 25)AS  l_mail_conn   UTL_SMTP.connection;BEGIN  l_mail_conn := UTL_SMTP.open_connection(p_smtp_host, p_smtp_port);  UTL_SMTP.helo(l_mail_conn, p_smtp_host);  UTL_SMTP.mail(l_mail_conn, p_from);  UTL_SMTP.rcpt(l_mail_conn, p_to);  UTL_SMTP.data(l_mail_conn, p_message || UTL_TCP.crlf || UTL_TCP.crlf);  UTL_SMTP.quit(l_mail_conn);END;/BEGIN  XXX_send_mail(p_to    => 'nileshapps@gmail.com',--'roger.drolet@theoicllc.com', --receiver's email id            p_from      => 'nileshapps@gmail.com',            p_message   => 'Test Message for Form via OIC Database for Manual Cantrol Form',            p_smtp_host => 'oicllc-01.oicllc.net');END;



Sending Email using PL/SQL Procedure


You can send mails using the following UTL_SMTP package
DECLARE  v_From      VARCHAR2(80) := 'oracle@mycompany.com';  v_Recipient VARCHAR2(80) := 'test@mycompany.com';  v_Subject   VARCHAR2(80) := 'test subject';  v_Mail_Host VARCHAR2(30) := 'mail.mycompany.com';  v_Mail_Conn utl_smtp.Connection;  crlf        VARCHAR2(2)  := chr(13)||chr(10);BEGIN v_Mail_Conn := utl_smtp.Open_Connection(v_Mail_Host, 25); utl_smtp.Helo(v_Mail_Conn, v_Mail_Host); utl_smtp.Mail(v_Mail_Conn, v_From); utl_smtp.Rcpt(v_Mail_Conn, v_Recipient); utl_smtp.Data(v_Mail_Conn,   'Date: '   || to_char(sysdate, 'Dy, DD Mon YYYY hh24:mi:ss') || crlf ||   'From: '   || v_From || crlf ||   'Subject: '|| v_Subject || crlf ||   'To: '     || v_Recipient || crlf ||   crlf ||   'some message text'|| crlf ||      -- Message body   'more message text'|| crlf ); utl_smtp.Quit(v_mail_conn);EXCEPTION WHEN utl_smtp.Transient_Error OR utl_smtp.Permanent_Error then   raise_application_error(-20000, 'Unable to send mail: '||sqlerrm);