ORA-29279: SMTP permanent error: 501 5.1.3 Invalid address
chijarSep 17 2009 — edited Sep 18 2009dear all.
Facts:
- Oracle 9.2.0.4
- AIX 5.3
- mail server 1: suse linux 9
- mail server 2: exchange 2007
i have problem with package utl_smtp. When i put in my own procedure the parameters Cc and/or Bcc shows me the error describe in this thread.
But when i put only the To and From everything is ok.
if i configured my connection through mail server 1 (linux) everything is ok (with Cc and Bcc). But if i configured my connection through mail server 2 (exchange) and send with Cc and/or Bcc shows error.
Both mail servers is configured in order to send mail (relay) and i tested telnet with port 25 and everything is ok.
I searched in metalink and it not shows an error same like i putted and the articles is not that i want:
My code is:
CREATE OR REPLACE PROCEDURE SEND_EMAIL_MIO(efrom in varchar2, eto in varchar2, esubject in varchar2, ebody in varchar2, ecc in char:=NULL, ebcc in char:=NULL) IS
BEGIN
DECLARE
MSG VARCHAR2(14000);
CONN UTL_SMTP.CONNECTION;
CRLF VARCHAR2(2):=CHR(13)||CHR(10);
TYPE registro is record (name varchar2(200), email varchar2(200));
TYPE addresslist_tab IS TABLE OF REGISTRO INDEX BY BINARY_INTEGER;
addrcnt BINARY_INTEGER:=0;
myfrom addresslist_tab;
myto addresslist_tab;
mycc addresslist_tab;
mybcc addresslist_tab;
cmyfrom binary_integer:=0;
cmyto binary_integer:=0;
cmycc binary_integer:=0;
cmybcc binary_integer:=0;
mes number(2);
nmes char(3);
rawdata raw(32767);
MSGaux VARCHAR2(14000);
Aux varchar2(4000);
FUNCTION separate(inemailx varchar2) return addresslist_tab is
begin
declare
inemail varchar2(4000);
p number;
rr addresslist_tab;
p1 varchar2(512);
p2 varchar2(512);
n1 varchar2(100);
n2 varchar2(100);
procedure getname(inadd varchar2) is
begin
declare
pp number;
qq number;
begin
pp:=instr(inadd,'<');
qq:=instr(inadd,'>');
if pp=0 then
n1:=null; n2:=inadd;
else
n1:=ltrim(rtrim(substr(inadd,1,pp-1)));
n2:=ltrim(rtrim(substr(inadd,pp+1,qq-pp-1)));
end if;
end;
end;
begin
addrcnt:=0;
if inemailx is null then
return (rr);
end if;
inemail:=replace(inemailx,';',',');
p:=instr(inemail, ','); if p=0 then p:=instr(inemail, ';'); end if;
if p=0 then
getname(inemail);
rr(1).name:=n1;
rr(1).email:=n2;
addrcnt:=1;
else
p2:=inemail;
loop
p1:=ltrim(rtrim(substr(p2, 1, p-1)));
if not p1 is null then
addrcnt:=addrcnt+1;
getname(p1);
rr(addrcnt).name:=n1;
rr(addrcnt).email:=n2;
end if;
p2:=substr(p2, p+1, 2000);
if not p2 is null then
p:=instr(p2, ','); if p=0 then p:=instr(p2, ';'); end if;
if p =0 then
addrcnt:=addrcnt+1;
getname(ltrim(rtrim(p2)));
rr(addrcnt).name:=n1;
rr(addrcnt).email:=n2;
exit;
end if;
else
exit;
end if;
end loop;
end if;
return(rr);
end;
end;
BEGIN
MSG:=NULL;
myfrom:=separate(efrom); cmyfrom:=addrcnt;
myto:=separate(eto); cmyto:=addrcnt;
-- if ecc is not null and trim(ecc)<>'' then mycc:=separate(ecc); cmycc:=addrcnt; else cmycc:=0; end if;
-- if ebcc is not null and trim(ebcc)<>'' then mybcc:=separate(ebcc); cmybcc:=addrcnt; else cmybcc:=0; end if;
if ecc is not null and length(trim(ecc))>0 then mycc:=separate(ecc); cmycc:=addrcnt; else cmycc:=0; end if;
if ebcc is not null and length(trim(ebcc))>0 then mybcc:=separate(ebcc); cmybcc:=addrcnt; else cmybcc:=0; end if;
conn:=utl_smtp.open_connection('172.16.40.63',25);
utl_smtp.helo(conn, '172.16.32.1');
if cmyfrom <> 0 then
utl_smtp.mail(conn, myfrom(1).email);
else
utl_smtp.mail(conn, 'sistemas@DOMINIO.COM.PE');
end if ;
for x in 1..cmyto loop
utl_smtp.rcpt(conn, myto(x).email);
end loop;
if cmycc<>0 then
for x in 1..cmycc loop
utl_smtp.rcpt(conn, 'cc:'||mycc(x).email);
end loop;
end if;
if cmybcc<>0 then
for x in 1..cmybcc loop
utl_smtp.rcpt(conn, 'bcc:'||mybcc(x).email);
end loop;
end if;
mes:=to_char(sysdate,'mm');
nmes:=substr('JanFebMarAprMayJunJulAugSepOctNovDec',(mes-1)*3+1,3);
-- msg:='Date: '||to_char(sysdate,'dd Mon yy hh24:mi:ss')||crlf;
msg:='Date: '||to_char(sysdate,'dd ')||nmes||to_char(sysdate,' yyyy hh24:mi:ss')||' -0500'||crlf;
if cmyfrom<>0 then
if myfrom(1).name is not null then
msg:=msg||'From: '||chr(34)||myfrom(1).name||chr(34)||' <'||myfrom(1).email||'>'||crlf;
else
if myfrom(1).email is not null then
msg:=msg||'From: '||myfrom(1).email||crlf;
else
msg:=msg||'From: '||chr(34)||'Default'||chr(34)||' <sistemas@DOMINIO.COM.PE>'||crlf;
end if;
end if;
else
msg:=msg||'From: '||chr(34)||'Default'||chr(34)||' <sistemas@DOMINIO.COM.PE>'||crlf;
end if;
for x in 1..cmyto loop
if myto(x).name is not null then
msg:=msg||'To: '||chr(34)||myto(x).name||chr(34)||' <'||myto(x).email||'>'||crlf;
else
msg:=msg||'To: '||myto(x).email||crlf;
end if;
end loop;
if cmycc<>0 then
for x in 1..cmycc loop
if mycc(x).name is not null then
-- msg:=msg||'To: '||chr(34)||mycc(x).name||chr(34)||' <'||mycc(x).email||'>'||crlf;
msg:=msg||'Cc: '||chr(34)||mycc(x).name||chr(34)||' <'||mycc(x).email||'>'||crlf;
else
msg:=msg||'Cc: '||mycc(x).email||crlf;
end if;
end loop;
end if;
if cmybcc<>0 then
for x in 1..cmybcc loop
if mybcc(x).name is not null then
-- msg:=msg||'To: '||chr(34)||mybcc(x).name||chr(34)||' <'||mybcc(x).email||'>'||crlf;
msg:=msg||'Bcc: '||chr(34)||mybcc(x).name||chr(34)||' <'||mybcc(x).email||'>'||crlf;
else
msg:=msg||'Bcc: '||mybcc(x).email||crlf;
end if;
end loop;
end if;
msg:=msg||'Precedence: bulk'||crlf;
msg:=msg||'Subject: '||esubject||crlf;
-- msg:=msg||'MIME-Version: 1.0'||crlf;
-- msg:=msg||'Content-Type: text/plain; charset=utf-8'||crlf;
-- msg:=msg||'Content-Transfer-Encoding: 8bit'||crlf;
msg:=msg||''||crlf;
MSGaux:=msg||ebody||' '||crlf;
utl_smtp.open_data(conn);
utl_smtp.write_data(conn, msg);
msg:=ebody||' '||crlf;
rawdata:=utl_raw.cast_to_raw(msg);
--utl_smtp.write_data(conn, msg);
utl_smtp.write_raw_data(conn, rawdata);
utl_smtp.close_data(conn);
--msg:=ebody;
--utl_smtp.data(conn, msg);
utl_smtp.quit(conn);
begin
insert into system.log_mail (message,status,error) values (MSGaux,'OK',NULL);
commit;
exception
when others then null;
end;
/* */
begin
insert into registro_correos(usuario, fecha, destino, titulo)
values(user, sysdate, eto, esubject);
commit;
exception
when others then null;
end;
exception
when others then
begin
begin
MSGaux := 'From: '||efrom||crlf||'To: '||eto||crlf||'Subject :'||esubject||crlf||'Body: '||ebody||crlf||'C: '||nvl(ecc,' ')||crlf||'BCC: '||nvl(ebcc,' ') ;
Aux :=TO_CHAR(SQLCODE)||' - '|| SQLERRM;
insert into system.log_mail (message,status,error) values (MSGaux,'FALLO',Aux);
commit;
exception
when others then null;
end;
/* */
utl_smtp.quit(conn);
exception
when others then null;
end;
END;
END;
/
... and apologize my english is not very well.
thanks