Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

ORA-29279: SMTP permanent error: 501 5.1.3 Invalid address

chijarSep 17 2009 — edited Sep 18 2009
dear 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

Comments

Hans Forbrich
You may want to start by reviewing http://download.oracle.com/docs/cd/B19306_01/server.102/b14231/create.htm#sthref483

Host is the physical computer, machine, operating system ... but not the database.

So in general, 'Host Login Credentials' means 'what is the computer username and password (not database user/pwd) of the user who is to login on the host (machine) in order to do the work?'

In this case, it's probably the userid that owns Oracle .... surely you did not install the database as 'Administrator'.
damorgan
There is a demo here:
http://www.morganslibrary.org/reference/wallet.html
that shows all of the moving pieces.

The page is part of the library located at:
http://www.morganslibrary.org/library.html
699363
Actually, this post provided the solution to my problem: 3755696

Thanks for the replies to my original post.
Hans Forbrich
Excellent. And thanks for posting the pointer to solution.
1 - 4
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Oct 16 2009
Added on Sep 17 2009
3 comments
12,245 views