Sending mail using UTL_SMTP.

[debasisdas]

The follwing sample code uses predefined package UTL_SMTP to send mail.

This package was first available in the version 8.1.7 .

[code=oracle]
create or replace procedure sendmail(sender varchar2,recipi ent varchar2,subjec t varchar2, text

varchar2)
IS
mailhost VARCHAR2(64) := '192.168.1.32';
--The name of the SMTP server host
port constant number(2):=25;
--The port number on which SMTP server is listening (usually 25).
timeout number :=180;
--The time in seconds that the UTL_SMTP package waits before giving up in a read or write

operation in this connection.
--In read operations, this package gives up if no data is available for reading immediately.
--In write operations, this package gives up if the output buffer is full and no data is to be

sent into the network without being blocked.
--Zero (0) indicates not to wait at all.
--NULL indicates to wait forever.
mail_conn utl_smtp.connec tion;
BEGIN
--dbms_output.put _line(UTL_SMTP. VRFY (mail_conn,reci pient));
mail_conn := utl_smtp.open_c onnection(mailh ost, port,timeout);
--Helo performs initial handshaking with SMTP server after connecting
utl_smtp.helo(m ail_conn, mailhost);
--Mail Initiates a mail transaction with the server
utl_smtp.mail(m ail_conn, sender);
--Specifies the recipient of an e-mail message
utl_smtp.rcpt(m ail_conn, recipient);
-- open_data(), write_data(), and close_data() into a single call to data().
--Sends the DATA command
utl_smtp.open_d ata(mail_conn);
utl_smtp.write_ data(mail_conn, 'From'||':'|| Sender || UTL_TCP.CRLF);
utl_smtp.write_ data(mail_conn, 'To'||':'|| recipient || UTL_TCP.CRLF);
utl_smtp.write_ data(mail_conn, 'Subject' ||':'|| subject || UTL_TCP.CRLF);
--Writes a portion of the e-mail message
utl_smtp.write_ data(mail_conn, text);
--Closes the data session
utl_smtp.close_ data(mail_conn) ;
utl_smtp.quit(m ail_conn);
--dbms_output.put _line('Your message has been sent...!');
EXCEPTION
WHEN UTL_SMTP.PERMAN ENT_ERROR THEN
BEGIN
utl_smtp.quit(m ail_conn);
END;
RAISE_APPLICATI ON_ERROR(-20101,'This id has Permanent Error');
WHEN UTL_SMTP.TRANSI ENT_ERROR THEN
BEGIN
utl_smtp.quit(m ail_conn);
END;
RAISE_APPLICATI ON_ERROR(-20102,'SMTP transient error:');
WHEN UTL_SMTP.INVALI D_OPERATION THEN
BEGIN
utl_smtp.quit(m ail_conn);
END;
RAISE_APPLICATI ON_ERROR(-20103,'Invalid Operation in Mail using UTL_SMTP.');
WHEN OTHERS THEN
RAISE_APPLICATI ON_ERROR(-20104,'Some other Error ...!');
end;
/

[/code]

To execute the above procedure try the following code.

[code=oracle]
exec sendmail('sende r@sender.com',' recipient@recip ient.com','Hi', 'Test Mail');
[/code]