Oracle ideas: ACL отправка почты

Использованы материалы
1) ORA-24248-XMLDB
2) Oracle-11g-Access-Control-List
3) telnet smtp

# telnet localhost smtp

Trying 127.0.0.1...
Connected to localhost.localdomain (127.0.0.1).
Escape character is '^]'.
220 fbreveal.com ESMTP Sendmail 8.13.8/8.13.8; Tue, 22 Oct 2013 05:05:59 -0400
HELO yahoo.com
250 tecadmin.net Hello tecadmin.net [127.0.0.1], pleased to meet you
mail from: sender@tecadmin.net
250 2.1.0 sender@tecadmin.net... Sender ok
rcpt to: myemail@ymail.com
250 2.1.5 myemail@ymail.com... Recipient ok
data
354 Enter mail, end with "." on a line by itself
Hey
This is test email only

Thanks
.
250 2.0.0 r9M95xgc014513 Message accepted for delivery
quit
221 2.0.0 fbreveal.com closing connection
Connection closed by foreign host.

------------------------------------------------------------------------------------------

1) sys as sysdba $ORACLE_HOME/rdbms/admin/catqm xdb sysaux temp YES

2) select comp_name , status from dba_registry;

3) DBMS_NETWORK_ACL_ADMIN.CREATE_ACL

4) DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL

5) grant execute on utl_http,UTL_SMTP

Пакет:

CREATE OR REPLACE PACKAGE pkg_email AS

c_pkg_name$c constant varchar2(100) := 'pkg_email';

procedure send_email(p_date in date default null);

-- отправка письма средствами oracle
procedure send_mail(email_from$c in varchar2:=null
,email_to$c in varchar2:=null
,subject$c in varchar2:=null
,message$c in varchar2:=null
,ContType$c in varchar2 := 'text/html'
);

END pkg_email;
/

CREATE OR REPLACE PACKAGE BODY pkg_email AS

type str_array is table of varchar(4000) index by binary_integer;
c_smtp_server$c constant varchar2(100) := '11.22.33.44';
c_sp$c constant varchar2(10) := ';;';

--слепить заголовок письма */
procedure send_header(fd$con in out utl_smtp.connection
,name$c in varchar2:=null
,header$c in varchar2:=null
)
as
begin
utl_smtp.write_data(fd$con, name$c || ': ' || header$c || utl_tcp.CRLF);
end send_header;

-- тема письма
procedure send_subject (fd$con in out utl_smtp.connection, string in varchar2) as
subject varchar2(32767):=null;
begin
--отправляем в base64 кодировке как есть windows-1251
subject:=utl_raw.cast_to_varchar2(utl_encode.base64_encode(utl_raw.cast_to_raw(string)));
subject:=replace(subject, chr(13),'');
subject:='=?WINDOWS-1251?B?'||replace(subject, chr(10),'')||'?=';
send_header(fd$con, 'Subject',subject);
end;

function split_str(str$c varchar2, delim$c varchar2)
return str_array
is
addr_beg$i integer:=1;
addr_end$i integer:=1;
len$i integer:=null;
addr$c varchar2(300):=null;
l_str$c varchar2(32767):=trim(str$c);
res$arr str_array;
begin
len$i:=length(l_str$c);
while (l_str$c is not null)
loop
--remove delimeters and spaces at the beginning
while(instr(l_str$c, delim$c)=1)and(l_str$c is not null)and(length(l_str$c) is not null)
loop
l_str$c:=trim(substr(l_str$c,2));
len$i:=length(l_str$c);
end loop;
if l_str$c is null or length(l_str$c) is null then
exit;
end if;
--search first delimeter in string
addr_end$i:=instr(l_str$c, delim$c, addr_beg$i);
if addr_end$i=0 then
--delimeter not found - then we take the whole string
addr_end$i:=len$i+1;
end if;
addr$c:=substr(l_str$c, addr_beg$i, addr_end$i-addr_beg$i);
if addr$c is not null then
res$arr(res$arr.count+1):=addr$c;
end if;

--shift to the next list element
l_str$c:=substr(l_str$c, addr_end$i+1);
len$i:=length(l_str$c);
end loop;
return res$arr;
end split_str;

--отдаем почтовой программе список получателей
procedure send_rcpt(fd$con in out utl_smtp.connection, rcpt_list$c varchar2:=null)
is
l_rcpt$c varchar2(32767):=null;
res$arr str_array;
x integer:=null;
begin
l_rcpt$c:=replace(rcpt_list$c,'<','');
l_rcpt$c:=replace(l_rcpt$c,'>','');

if instr(l_rcpt$c,',')=0 then
--только один получатель
utl_smtp.rcpt(fd$con, l_rcpt$c);
else
res$arr:=split_str(l_rcpt$c,',');
if res$arr.count>0 then
--много получателей
for x in res$arr.first .. res$arr.last
loop
utl_smtp.rcpt(fd$con, res$arr(x));
end loop;
end if;
end if;
end send_rcpt;

/*************************************/
/* отправка письма средствами oracle */
/*************************************/
procedure send_mail(email_from$c in varchar2:=null
,email_to$c in varchar2:=null
,subject$c in varchar2:=null
,message$c in varchar2:=null
,ContType$c in varchar2 := 'text/html'
) is
raw_message$w raw(32767):=null;
fd$con utl_smtp.connection:=null;
debug$n number:=0;
len_message$i integer:=null;
l_email_from$c varchar2(300):=trim(lower(email_from$c));
helo$c constant varchar2(50):='monitoring_system';

begin

--Что бы избежать проблем со спецсимволами перекодируем текст сообщения в base64
raw_message$w:=utl_encode.base64_encode(UTL_RAW.CAST_TO_RAW(message$c));
len_message$i:=utl_raw.length(raw_message$w);
fd$con := utl_smtp.open_connection(c_smtp_server$c);
utl_smtp.helo(fd$con, helo$c);
utl_smtp.mail(fd$con, l_email_from$c);
send_rcpt(fd$con, email_to$c);
--utl_smtp.rcpt(fd$con, email_to$c);
utl_smtp.open_data(fd$con);

send_header(fd$con, 'Content-Type', ContType$c||';charset="windows-1251"');
send_header(fd$con, 'Content-Transfer-Encoding', 'base64');
send_header(fd$con, 'MIME-Version', '1.0');
send_header(fd$con, 'Date', to_char(sysdate, 'Dy, DD Mon YYYY HH24:MI:SS', 'NLS_DATE_LANGUAGE=American'));
send_header(fd$con, 'From', '<'||l_email_from$c||'>');
send_header(fd$con, 'To', email_to$c);
send_subject(fd$con, subject$c); --тема сообщения посылается в кодировке windows-1251 закодированном base64
utl_smtp.write_data(fd$con, UTL_TCP.CRLF); --конец заголовка сообщения

--посылаем тело сообщения
if(len_message$i>0) then
utl_smtp.write_raw_data(fd$con, raw_message$w);
end if;

utl_smtp.close_data(fd$con);
utl_smtp.quit(fd$con);

exception
when others then
utl_smtp.rset(fd$con);
utl_smtp.quit(fd$con);
raise_application_error(-20001, 'send_mail[debug$n='||debug$n||']: '||SQLERRM(SQLCODE));
end send_mail;

function formatfloat(val number) return varchar2 is
begin
return trim(to_char(val,'999999999999999.00'));
end;

-------------------------------------------------------------------------------
procedure send_email(p_date in date default null) is
msg$c varchar2(32000) := ' ';
d_tod_date date := trunc(NVL(p_date,sysdate));
begin
msg$c := '<html><head><title>!!!</title></head><body>'
|| 'На сервере aaa</br> В базе данных bbb</br> Следующая статистика по изменению размера сегментов:' ||'<br />'||'<br />'
||'<table border="1">'
||'<tr><td>Схема</td><td>Вчера байт</br> ('||to_char(d_tod_date-1,'dd.mm.yyyy')||')</td><td>Сегодня байт</br> ('||to_char(d_tod_date,'dd.mm.yyyy')||')</td><td>Изменение Гб</td></tr>'
||'Проверка отправки сообщений'
||'</table>'
||'<br />'||'<br />'
|| 'Дата/время: ' ||to_char(sysdate,'dd.mm.yyyy hh24:mi')
|| '</body></html>';

send_mail( email_from$c => 'zzzzzzzz@yyyyyyy.ru'
,email_to$c => 'yakushev@xxxxxx.ru'
,subject$c => 'Server aaa. Segments statistics - '||to_char(d_tod_date,'dd.mm.yyyy')
,message$c => msg$c
);
end;
-------------------------------------------------------------------------------

END pkg_email;
/