--Just call the function with the appropriate parameters.
--send a message to the mail for a person

CREATE OR REPLACE FUNCTION send_mail (
   p_sender      IN   VARCHAR2
 , p_recipient   IN   VARCHAR2
 , p_subject     IN   VARCHAR2
 , p_message     IN   VARCHAR2
 , p_mailhost    IN   VARCHAR2 DEFAULT 'your.mail.server'
)
   RETURN VARCHAR2
IS
   v_mail_conn   UTL_SMTP.connection;
   v_msg         VARCHAR2 (4000);
   v_status      VARCHAR2 (100)      := 'Success';
   crlf          VARCHAR2 (2)        := CHR (13) || CHR (10);
BEGIN
--Connect to server, listener 25
   v_mail_conn := UTL_SMTP.open_connection (p_mailhost, 25);
--Perform initial handshaking with SMTP server after connecting
   UTL_SMTP.helo (v_mail_conn, p_mailhost);
--Initiate a mail transaction with the server. The destination is a mailbox.
   UTL_SMTP.mail (v_mail_conn, p_sender);
--Specify the recipient
   UTL_SMTP.rcpt (v_mail_conn, p_recipient);
--Add date, from, and subgject to message
--Add passed in message to rest of message
   v_msg :=
         'Date: '
      || TO_CHAR (SYSDATE, 'dd Mon yy hh24:mi:ss')
      || crlf
      || 'From: '
      || p_sender
      || crlf
      || 'Subject: '
      || p_subject
      || crlf
      || 'To: '
      || p_recipient
      || crlf
      || ''
      || crlf
      || p_message;

/*Note: If the length of v_msg is greater than 2000 the following error
will occur ORA-06502: PL/SQL: numeric or value error so the message is
truncated to 2000 characters*/
   IF LENGTH (v_msg) > 2000
   THEN
      v_msg := SUBSTR (v_msg, 1, 2000);
   END IF;

--Specify the body of an email message
   UTL_SMTP.DATA (v_mail_conn, v_msg);
--Terminate an SMTP session and disconnect from the server
   UTL_SMTP.quit (v_mail_conn);
   RETURN v_status;
EXCEPTION
   WHEN OTHERS
   THEN
      RETURN ('Util.Send_Mail: ' || SQLERRM);
END;