CREATE OR REPLACE 
PACKAGE pkg_mail AUTHID CURRENT_USER IS
/*************************************************************************/
/* Purpose: Send Mail from PL/SQL                                        */
/* Author:  M Selvaggio 7/22/2002                                        */
/* See Package Body for modification history                             */
/*                                                                       */
/*************************************************************************/
-- Public Global Declarations

Type varchar_tab is table of varchar2(48) index by binary_integer;
addr_list    varchar_tab;

procedure send_mail(to_addr   varchar2,
                    from_addr varchar2,
                    subject   varchar2,
                    text_msg  varchar2,
                    sql_query varchar2);
procedure send_mail(to_addr   varchar_tab,
                    from_addr varchar2,
                    subject   varchar2,
                    text_msg  varchar2,
                    sql_query varchar2);  
PROCEDURE open_mail_server;
PROCEDURE To_Who  (P_EMAIL_ADDRESS VARCHAR2);
PROCEDURE To_Who  (P_EMAIL_LIST VARCHAR_TAB);
PROCEDURE From_Who (P_EMAIL_ADDRESS VARCHAR2);
PROCEDURE close_Mail_Server;  
PROCEDURE Build_Message (message_body varchar2);
PROCEDURE Build_Message (message_body varchar2, 
                     sql_query varchar2); 
END; 
.
/
CREATE OR REPLACE 
PACKAGE BODY pkg_mail IS
/*************************************************************************/
/* Purpose: Send Mail from PL/SQL                                        */
/* Author:  M Selvaggio 7/22/2002                                        */
/*                                                                       */
/*************************************************************************/
/* MODIFICATION HISTORY                                                  */
/* Person       Date        Comments                                     */
/* ---------    ----------- ------------------------------------------   */
/* M, Selvaggio 7/22/2002   Created                                      */
/* M. Selvaggio 8/15/2002   Added to_Many for multiple recipient support */ 
/*                           and SQL query support                       */
/*************************************************************************/
-- Private Global Variables
  v_mailhost_c          varchar2(500);
  v_mail_connection_c   utl_smtp.connection;
  v_message_c           varchar2(32767);
  v_recipient_c         varchar2(500);
  v_sender_c            varchar2(500);
  v_subject_c           varchar2(500);
  v_many                varchar2(1) := 'N';

/*************************************************************************/
/* Send_Mail: Send mail using single address construct                   */
/*************************************************************************/
/* Parameters:                                                           */
/*  to_addr: single recipient email address                              */
/*  from_addr: sender                                                    */
/*  subject: subject_line                                                */
/*  text_msg: text for message body                                      */
/*  sql_query: formatted query for message body.  Note: the query should */
/*             concatenate all columns and padding into 1 column         */         
/*************************************************************************/
procedure send_mail(to_addr   varchar2,
                    from_addr varchar2,
                    subject   varchar2,
                    text_msg  varchar2,
                    sql_query varchar2)  
is

v_email_list varchar_tab;

Begin
  -- Initialize variables
  v_sender_c := from_addr;
  v_recipient_c := to_addr;
  v_subject_c := subject;
  v_message_c := text_msg;
  --
  -- Call Mail Procedures
  -- 
  open_mail_server;
  From_Who(v_sender_c);
  To_Who(v_recipient_c);
  --
  -- If no query is passed in , just send text message
  --
  if sql_query is null then
   build_message(v_message_c);
  else
   build_message(v_message_c,sql_query);
  end if;
  close_mail_server;

EXCEPTION
    WHEN others THEN
        raise_application_error(-20900, 'Error in PKG_MAIL.SEND_MAIL(SINGLE)' ||
        ' Error code: ' || SQLCODE ||'Error Message: ' || SQLERRM);
end;
/*************************************************************************/
/* Send_Mail: Send mail using multiple address construct                 */
/*************************************************************************/
/* Parameters:                                                           */
/*  to_addr: PL/SQL table of recipients                                  */
/*  from_addr: sender                                                    */
/*  subject: subject_line                                                */
/*  text_msg: text for message body                                      */
/*  sql_query: formatted query for message body.  Note: the query should */
/*             concatenate all columns and padding into 1 column         */ 
/*************************************************************************/
procedure send_mail(to_addr   varchar_tab,
                    from_addr varchar2,
                    subject   varchar2,
                    text_msg  varchar2,
                    sql_query varchar2)  
is

Begin
  -- Initialize variables
  v_sender_c := from_addr;
  addr_list := to_addr;
  v_subject_c := subject;
  v_message_c := text_msg;
  --
  -- Call Mail Procedures
  -- 
  open_mail_server;
  From_Who(v_sender_c);
  To_Who(addr_list);
  --
  -- If no query is passed in , just send text message
  --
  if sql_query is null then
   build_message(v_message_c);
  else
   build_message(v_message_c,sql_query);
  end if;
  close_mail_server;

EXCEPTION
    WHEN others THEN
        raise_application_error(-20900, 'Error in PKG_MAIL.SEND_MAIL(MANY)' ||
        ' Error code: ' || SQLCODE ||'Error Message: ' || SQLERRM);  
end;
/*************************************************************************/
/* Open_Mail_Server: Establish connection to mail server                 */
/*************************************************************************/
PROCEDURE open_mail_server
is
-- Open Mail Server Connection
Begin 
  -- Set mail server conenction and perform initial handshake
  v_mailhost_c := 'mymailserver.mydomain.com';
  v_mail_connection_c := utl_smtp.open_connection(v_mailhost_c);
  utl_smtp.helo(v_mail_connection_c,v_mailhost_c);

EXCEPTION
    WHEN others THEN
        raise_application_error(-20900, 'Error in PKG_MAIL.OPEN_MAIL_SERVER ' ||
        ' Error code: ' || SQLCODE ||'Error Message: ' || SQLERRM);
end;

/*************************************************************************/
/* To_Who: Set single recipient                                          */
/*************************************************************************/
PROCEDURE To_Who (P_EMAIL_ADDRESS VARCHAR2)
is
-- Set Single Recipient
Begin 
  utl_smtp.rcpt(v_mail_connection_c,P_EMAIL_ADDRESS);  
EXCEPTION
    WHEN others THEN
        raise_application_error(-20900, 'Error in PKG_MAIL.TO_WHO(SINGLE)' ||
        ' Error code: ' || SQLCODE ||'Error Message: ' || SQLERRM); 
end;

/*************************************************************************/
/* To_Who: Set multiple recipients                                       */
/*************************************************************************/
PROCEDURE To_Who (P_EMAIL_LIST VARCHAR_tab)
is
-- Set Multiple Recipients
Begin 
  v_many := 'Y';
  for indx in p_email_list.first .. p_email_list.last
   loop 
     utl_smtp.rcpt(v_mail_connection_c,P_EMAIL_LIST(indx));
  end loop;
EXCEPTION
    WHEN others THEN
        raise_application_error(-20900, 'Error in PKG_MAIL.TO_WHO(MANY)' ||
        ' Error code: ' || SQLCODE ||'Error Message: ' || SQLERRM);  
end;

/*************************************************************************/
/* From_Who: Set the sender                                              */
/*************************************************************************/
PROCEDURE From_Who (P_EMAIL_ADDRESS VARCHAR2)
is
-- Set Sender
Begin 
  utl_smtp.mail(v_mail_connection_c,P_EMAIL_ADDRESS); 
EXCEPTION
    WHEN others THEN
        raise_application_error(-20900, 'Error in PKG_MAIL.FROM_WHO' ||
        ' Error code: ' || SQLCODE ||'Error Message: ' || SQLERRM); 
end;

/*************************************************************************/
/* Close_Mail_Server: Close mail server connection                       */
/*************************************************************************/
PROCEDURE close_mail_server
is
-- Close Mail Connection
Begin
  utl_smtp.quit(v_mail_connection_c);
EXCEPTION
    WHEN others THEN
        raise_application_error(-20900, 'Error in PKG_MAIL.CLOSE_MAIL_SERVER' ||
        ' Error code: ' || SQLCODE ||'Error Message: ' || SQLERRM);
end;

/*************************************************************************/
/* Build_Message: If no query is passed in , send a text message         */
/*************************************************************************/
PROCEDURE Build_Message (message_body varchar2)
is
-- Send a Text Message
Begin 
  utl_smtp.open_data(v_mail_connection_c);  
  utl_smtp.write_data(v_mail_connection_c,'From: "'||v_sender_c||'" <'||v_sender_c||'>'|| utl_tcp.CRLF);
  if v_many = 'Y' then
    utl_smtp.write_data(v_mail_connection_c,'To: "Distribution" <Distribution>'|| utl_tcp.CRLF);
  else 
    utl_smtp.write_data(v_mail_connection_c,'To: "'||v_recipient_c||'" <'||v_recipient_c||'>'|| utl_tcp.CRLF);
  end if;
  utl_smtp.write_data(v_mail_connection_c,'Subject: "'||v_subject_c||'"'|| utl_tcp.CRLF);
  utl_smtp.write_data(v_mail_connection_c,utl_tcp.CRLF||message_body);  
  utl_smtp.close_data(v_mail_connection_c);
  v_many := 'N';

EXCEPTION
    WHEN others THEN
        raise_application_error(-20900, 'Error in PKG_MAIL.BUILD_MESSAGE(TEXT)' ||
        ' Error code: ' || SQLCODE ||'Error Message: ' || SQLERRM);

end;

/*************************************************************************/
/* Build_Message: If query is passed in, send a text message and results */
/*************************************************************************/
PROCEDURE BUild_Message (message_body varchar2,
                     sql_query varchar2)
is
-- Send a Text Message and Data

type refcursor is REF CURSOR;
report_cur refcursor;
report_rec varchar2(4000);

BEGIN

   OPEN report_cur FOR sql_query;
   
   utl_smtp.open_data(v_mail_connection_c);  
   utl_smtp.write_data(v_mail_connection_c,'From: "'||v_sender_c||'" <'||v_sender_c||'>'|| utl_tcp.CRLF);
   --
   -- If multiple recipients, set header 'to' info to 'Distribution'
   --
   if v_many = 'Y' then
    utl_smtp.write_data(v_mail_connection_c,'To: "Distribution" <Distribution>'|| utl_tcp.CRLF);
   else 
    utl_smtp.write_data(v_mail_connection_c,'To: "'||v_recipient_c||'" <'||v_recipient_c||'>'|| utl_tcp.CRLF);
   end if;
   utl_smtp.write_data(v_mail_connection_c,'Subject: "'||v_subject_c||'"'|| utl_tcp.CRLF);
   utl_smtp.write_data(v_mail_connection_c,utl_tcp.CRLF||message_body);
   utl_smtp.write_data(v_mail_connection_c,utl_tcp.CRLF||'==========================');
   --
   -- Fetch cursor and add to email text   
   --
   LOOP
    FETCH report_cur INTO report_rec;  
     EXIT WHEN report_cur%NOTFOUND;   
      v_message_c := report_rec;
      utl_smtp.write_data(v_mail_connection_c,utl_tcp.CRLF||v_message_c); 
   END LOOP;
   
   utl_smtp.close_data(v_mail_connection_c);
   v_many := 'N';

EXCEPTION
    WHEN others THEN
        raise_application_error(-20900, 'Error in PKG_MAIL.BUILD_MESSAGE(QUERY)' ||
        ' Error code: ' || SQLCODE ||'Error Message: ' || SQLERRM);

END;

END;
.
/