An example Send Mail Function Using UTL_SMTP

CREATE OR REPLACE FUNCTION send_email (addr IN VARCHAR2, msg IN VARCHAR2)
RETURN VARCHAR2
IS
c UTL_SMTP.connection;
BEGIN
c := UTL_SMTP.open_connection ('smtp.test.edu');
UTL_SMTP.helo (c, 'smtp.test.edu');
UTL_SMTP.mail (c, 'TEST@test.edu');
UTL_SMTP.rcpt (c, addr);
UTL_SMTP.open_data (c);
UTL_SMTP.write_data (
c,
'From'
|| ': '
|| '"TEST SEND MAIL" '
|| UTL_TCP.CRLF);
UTL_SMTP.write_data (
c,
'To' || ': ' || '"Recipient" <' || addr || '>' || UTL_TCP.CRLF);
UTL_SMTP.write_data (
c,
'Subject' || ': ' || 'TEST FOR SEND MAIL' || UTL_TCP.CRLF);
UTL_SMTP.write_data (
c,
'Reply-To:'
|| '"YOUR NAME" '
|| UTL_TCP.crlf);
UTL_SMTP.write_data (c, msg);
UTL_SMTP.close_data (c);
UTL_SMTP.quit (c);
RETURN 'OK';
EXCEPTION
WHEN UTL_SMTP.transient_error OR UTL_SMTP.permanent_error
THEN
BEGIN
UTL_SMTP.quit (c);
EXCEPTION
WHEN UTL_SMTP.transient_error OR UTL_SMTP.permanent_error
THEN
NULL; -- When the SMTP server is down or unavailable, we don't have
-- a connection to the server. The quit call will raise an
-- exception that we can ignore.
END;

RETURN 'Failed to send mail due to the following error: ' || SQLERRM;
END;
/