Не работает процедура приема почты:
| Код |
CREATE PROCEDURE TEST_POP3_BIO_2 is
type MailRec is record(
addr_p varchar2(500),
from_p varchar2(500),
to_p varchar2(500),
subj_p varchar2(500),
date1_p varchar2(500),
leng_p integer);
type MailTableType is
table of MailRec index by binary_integer;
MailTable MailTableType;
----
host_p varchar2(25) :='pop.mail.ru';
user_p varchar2(25) :='******';
pass_p varchar2(25) :='******';
c utl_tcp.connection;
i smallint := 0;
nn smallint := 0;
z nvarchar2(32767) := 'none';
---------------------------------------
x1 smallint := 0;
x2 smallint := 0;
x0 smallint := 0;
nn2 smallint := 0;
file1 UTL_FILE.FILE_TYPE;
y varchar2(32767) := '';
--data VARCHAR2(256);
z2 varchar2(32767) := '';
z21 varchar2(32767) := '';
z22 varchar2(32767) := '';
z3 varchar2(1) := '';
d1 smallint := 0;
d2 smallint := 0;
d3 varchar2(500);
cod smallint := 0;
mail_nn INTEGER;
--
datep_p smallint := 0;
subp_p smallint := 0;
top_p smallint := 0;
fromp_p smallint := 0;
--
begin
--utl_tcp.close_connection(c);
c := utl_tcp.open_connection(host_p, 110);
if substr(utl_tcp.get_line(c, true),1,3) = '+OK' then
y := utl_tcp.write_line(c, 'user '||user_p);
if substr(utl_tcp.get_line(c, true),1,3) = '+OK' then
y := utl_tcp.write_line(c, 'pass '||pass_p);
if substr(utl_tcp.get_line(c, true),1,3) = '+OK' then
y := utl_tcp.write_line(c, 'list');
if substr(utl_tcp.get_line(c, true),1,3) = '+OK' then
loop
z := utl_tcp.get_line(c,true);
exit when substr(z,1,1) = '.';
nn := to_number(substr(z,1,instr(z,' ',1)));
MailTable(nn).leng_p := to_number(substr(z,instr(z,' ')+1));
end loop;
if nn > 0 then
x1:=1;
end if;
for nn in 1..MailTable.count loop
i := utl_tcp.write_line(c, 'retr '||nn);
i:= 0;
x2:= 0;
if substr(utl_tcp.get_line(c, true),1,3) = '+OK' then
select SEQ_POP3_1.nextval --sequences
into mail_nn
from dual;
--execute immediate
--' create or replace directory '||temp||' as '''||'c:\ecologic\temp'||'''';----ðàñøàðåâàíèå äèðåêòîðèè
file1:= SYS.UTL_FILE.FOPEN('c:\ecologic\temp', 'mail_'||mail_nn||'.eml', 'w'); --íàäî ðàñøàðèòü â Oracle
z := utl_tcp.get_line(c);
datep_p:= 0;
subp_p:= 0;
top_p:= 0;
fromp_p:= 0;
if instrb(z,'Return-Path: ')>0 then
d1:= instr(z,'<')+1;
d2:= instr(z,'>',d1)-d1;
d3:= substr(z,d1,d2);
MailTable(nn).addr_p:=(d3);
--else
-- MailTable(nn).addr_p:='empty';
end if;
loop
------//tochka
--dbms_output.put_line('tochka-0');
------////tochka
IF (utl_tcp.available(c) > 0) THEN
if substr(z,1,1) = '.'then
x0:=length(z);
z:=substr(z,2,x0);
end if;
z2:=replace(replace(z,chr(13)),chr(10));
------//tochka
--dbms_output.put_line('dl-:'||utl_tcp.available(c));
--dbms_output.put_line('z-:'||substr(z2,1,250));
------////tochka
if length(z2) > 250 then
z21:=substr(z2,1,250);
z22:=substr(z2,251,length(z2));
z21:=replace(replace(z21,chr(13)),chr(10));
z22:=replace(replace(z22,chr(13)),chr(10));
UTL_FILE.put_line(file1, z21);
UTL_FILE.put_line(file1, substr(z22,1,250));
else
UTL_FILE.put_line(file1, z2);
end if;
z := utl_tcp.get_line(c);
--z := substr(utl_tcp.get_line(c),1,254);
ELSE
x2:= 1;
END IF;
exit when x2 = 1;
------//tochka
--dbms_output.put_line('RP-:'||instrb(z,'Return-Path: '));
--dbms_output.put_line('z-:'||substr(z2,1,250));
------////tochka
if (instrb(z,'From: ')>0) AND (fromp_p = 0) then
d1:= instr(z,'<')+1;
d2:= instr(z,'>',d1)-d1;
d3:= substr(z,d1,d2);
MailTable(nn).from_p := replace(replace(replace(d3,'From: '),chr(13)),chr(10));
fromp_p:= 1;
end if;
if (instrb(z,'To: ')>0) AND (top_p = 0) then
d1:= instr(z,'<')+1;
d2:= instr(z,'>',d1)-d1;
d3:= substr(z,d1,d2);
MailTable(nn).to_p := replace(replace(replace(d3,'To: '),chr(13)),chr(10));
top_p:= 1;
end if;
cod:= 0;
if (instrb(z,'Subject: ')>0) AND (subp_p = 0) then
------
if instr(z,'B?')>0 then
d1:= instr(z,'B?')+2;
d2:= instr(z,'?=',d1)-d1;
d3:= substr(z,d1,d2);
if instr(z,'=?koi8-r?B?')>0 then
MailTable(nn).subj_p:= convert(UTL_RAW.cast_to_varchar2(utl_encode.base64_decode(UTL_RAW.cast_to_raw(d3))), 'CL8MSWIN1251', 'CL8KOI8R');
--elsif instr(z,'=?windows-1251?B?')>0 then
-- MailTable(nn).subj_p:= BK.TEST_DEC_1(d3);
else
MailTable(nn).subj_p:= UTL_RAW.cast_to_varchar2(utl_encode.base64_decode(UTL_RAW.cast_to_raw(d3)));
end if;
else
MailTable(nn).subj_p:= replace(replace(replace(z,'Subject: '),chr(13)),chr(10));
end if;
subp_p:= 1;
------
end if;
if (instrb(z,'Date: ')>0) AND (datep_p = 0) then
MailTable(nn).date1_p:= replace(replace(replace(z,'Date: '),chr(13)),chr(10));
datep_p:= 1;
end if;
end loop;
------
insert into TEST_EMAIL(nom_t, addr_t, from_t, to_t, subj_t, date1_t)
values(nvl(mail_nn,0), nvl(MailTable(nn).addr_p,'empty'), nvl(MailTable(nn).from_p,'empty'), nvl(MailTable(nn).to_p,'empty'), nvl(MailTable(nn).subj_p,'empty'), nvl(MailTable(nn).date1_p,'empty'));
------
i:= utl_tcp.write_line(c, 'dele '||nn);
z := utl_tcp.get_line(c);
i:= 0;
------//tochka
--dbms_output.put_line('tochka-1');
------////tochka
SYS.UTL_FILE.FCLOSE(file1);
z := '';
end if;
------//tochka
--dbms_output.put_line('tochka-2');
------////tochka
end loop;
------//tochka
--dbms_output.put_line('tochka-3');
-----////tochka
end if;
------//tochka
--dbms_output.put_line('tochka-4');
------////tochka
end if;
------//tochka
--dbms_output.put_line('tochka-5');
------////tochka
end if;
------//tochka
--dbms_output.put_line('tochka-6');
------////tochka
end if;
------//tochka
--dbms_output.put_line('tochka-7');
------////tochka
i := utl_tcp.write_line(c, 'quit');
---------------------------------------------------------------------------------------------------
--for i in 1..MailTable.count loop
--dbms_output.put_line('From: '||MailTable(i).from_p);
--dbms_output.put_line('To: '||MailTable(i).to_p);
-- dbms_output.put_line('Subject: '||MailTable(i).subj_p);
--dbms_output.put_line('------------------------------------------------------');
--end loop;
dbms_output.put_line('ALL: '||MailTable.count);
---------------------------------------------------------------------------------------------------
UTL_FILE.FCLOSE(file1);
utl_tcp.close_connection(c);
------//tochka
--dbms_output.put_line('tochka-8');
------////tochka
exception
when utl_tcp.end_of_input then
UTL_FILE.FCLOSE(file1);
utl_tcp.close_connection(c);
end;
|
|
