Sending Mail Using PL/SQL Code

Sending Mail Using PL/SQL Code:

DECLARE
v_From VARCHAR2(80) := 'oracle@mycompany.com';
v_Recipient VARCHAR2(80) := 'ravindra.varma@gmail.com';
v_Subject VARCHAR2(80) := 'test subject';
v_Mail_Host VARCHAR2(30) := 'mail.gmail.com';
v_Mail_Conn utl_smtp.Connection;
crlf VARCHAR2(2) := chr(13)||chr(10);
BEGIN
/* Open the connection to host server using
UTL_SMTP.OPEN_CONNECTION */
v_Mail_Conn := utl_smtp.Open_Connection(v_Mail_Host, 25);
/* Use UTL_SMTP.HELO function to initialize handshaking with
host server */
utl_smtp.Helo(v_Mail_Conn, v_Mail_Host);
/* Use UTL_SMTP.MAIL function/procedure to initiate the server
transaction */
utl_smtp.Mail(v_Mail_Conn, v_From);
/* Specify recipient name using UTL_SMTP.RCPT function/procedure */
utl_smtp.Rcpt(v_Mail_Conn, v_Recipient);
/* Compose email data using UTL_SMTP.DATA */
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
);
/* Use UTL_SMTP.QUIT method to terminate the connection */
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);
END;