CREATE OR REPLACE PACKAGE PKG_SEND_MAILS AS
 ----------------------------------------------------------------------------------
 --	 SEND MAILS ALL ADRESS WHIT my_id=id
 ----------------------------------------------------------------------------------
 PROCEDURE SEND_ALERT_MSG (my_id IN NUMBER);
 PROCEDURE SEND_ALERT_MSG (my_id IN NUMBER, my_text in varchar2);
------------------------------------------------------------------------------------
--end package
------------------------------------------------------------------------------------
end;


CREATE OR REPLACE PACKAGE BODY PKG_SEND_MAILS AS

PROCEDURE SEND_MAIL (
  msg_from    varchar2,
  msg_to      varchar2,
  msg_subject varchar2,
  msg_text    varchar2   )

IS
  c  utl_smtp.connection;
  rc integer;
  mailhost    VARCHAR2(30) := 'localhost';   -- local database host


BEGIN
  c := utl_smtp.open_connection(mailhost, 25); -- SMTP on port 25
  utl_smtp.helo(c, mailhost);
  utl_smtp.mail(c, msg_from);
  utl_smtp.rcpt(c, msg_to);
  utl_smtp.data(c,'From: ' || msg_from || utl_tcp.crlf ||
                  'To: ' || msg_to || utl_tcp.crlf ||
                  'Subject: ' || msg_subject ||
                   utl_tcp.crlf || msg_text);
  utl_smtp.quit(c);

  EXCEPTION
    WHEN UTL_SMTP.INVALID_OPERATION THEN
       dbms_output.put_line(' Invalid Operation in Mail attempt
                              using UTL_SMTP.');
    WHEN UTL_SMTP.TRANSIENT_ERROR THEN
       dbms_output.put_line(' Temporary e-mail issue - try again');

    WHEN UTL_SMTP.PERMANENT_ERROR THEN
       dbms_output.put_line(' Permanent Error Encountered.');

END;
/


PROCEDURE SEND_ALERT_MSG (my_id IN NUMBER) is
	v_date varchar(100);

	cursor all_recipients is
       select alert_address, msg_subject, msg_text from alert_msg_receiver, alert_msg_type
	   where alert_msg_receiver.id=alert_msg_type.id and alert_msg_type.id=my_id;

 begin
 	v_date:= to_char(sysdate,'dd-mm-yyyy hh24:mi:ss');
    for r_all_recp in all_recipients loop
	    send_mail('webmaster@oracle-consulting.de',''||r_all_recp.alert_address
	              ||'',''||r_all_recp.msg_subject||' date: '||v_date
	              ||'',''||r_all_recp.msg_text||'');
    end loop;
 end;

PROCEDURE SEND_ALERT_MSG (my_id  NUMBER,
                          my_text varchar2) is
	v_date varchar(100);

	cursor all_recipients is
       select alert_address, msg_subject from alert_msg_receiver, alert_msg_type
	   where alert_msg_receiver.id=alert_msg_type.id and alert_msg_type.id=my_id;

 begin
 	v_date:= to_char(sysdate,'dd-mm-yyyy hh24:mi:ss');
    for r_all_recp in all_recipients loop
	    send_mail('webmaster@oracle-consulting.de',''
	              ||r_all_recp.alert_address||'',''
	              ||r_all_recp.msg_subject||' date: '
	              ||v_date||'',''||my_text||'');
    end loop;
 end;
-----------------------------------------------------------------------------------
--end package
------------------------------------------------------------------------------------
end;
/