utl_smtp package with a table

[raf]

Hi,

I run this procedure:

CREATE OR REPLACE procedure send_mail ( mitt IN varchar2,
destinatario1 IN varchar2,
object IN varchar2,
messagge IN varchar2) IS

cursor my_cur is
select ename
from emp;

mailhost varchar2(40):= 'pop.mail.xxxxx.it';

conn utl_smtp.connection;
crlf varchar2(2):=CHR(13)||CHR(10);
messag varchar2(3000);
v_mitt varchar2(2000) := mitt;
BEGIN

for rec_msg in my_cur
loop
messag := rec_msg.ename;
end loop;

conn := utl_smtp.open_connection (mailhost, 25);

messag := 'DATE: '||to_char(sysdate, 'dd-mon-yy hh24:mi:ss')||crlf||
'FROM: <'||mitt||'>'||crlf||
'SUBJECT: '||object||crlf||
'TO: '||destinatario1||' '||crlf||messagge;

utl_smtp.helo(conn, mailhost);
utl_smtp.mail (conn, v_mitt);
utl_smtp.rcpt (conn, destinatario1);
utl_smtp.data(conn, messag);
utl_smtp.quit(conn);
EXCEPTION
when utl_smtp.transient_error or utl_smtp.permanent_error then
utl_smtp.quit(conn);
raise_application_error (-20000, 'error: '||sqlerrm);
END send_mail;




This procedure run correctly if I insert in MESSAGE one text, but don't send the values of col ENAME in table scott.emp;

What I wrong in this procedure??

Could you help me?

Thanks
Raf

[TomazZ]

I am not sure what are you trying to do, but this part

Originally posted by raf
for rec_msg in my_cur
loop
messag := rec_msg.ename;
end loop;

will assign to "messag" only the last ename fetched.
I don't think you meant it this way.

Uff, even more... code after opening connection overwrites even this ename

[balajiyes]

see u are assigning messag to ename first and then this
messag := 'DATE: '||to_char(sysdate, 'dd-mon-yy hh24:mi:ss')||crlf||
'FROM: <'||mitt||'>'||crlf||
'SUBJECT: '||object||crlf||
'TO: '||destinatario1||' '||crlf||messagge;

You have to concat the messag (ename) with messag('DATE: '||to_char(sysdate, 'dd-mon-yy hh24:mi:ss')||crlf||
'FROM: <'||mitt||'>'||crlf||
'SUBJECT: '||object||crlf||
'TO: '||destinatario1||' '||crlf||messagge

try this and modify,

CREATE OR REPLACE procedure send_mail ( mitt IN varchar2,
destinatario1 IN varchar2,
object IN varchar2,
messagge IN varchar2) IS

cursor my_cur is
select ename
from emp;

mailhost varchar2(40):= 'pop.mail.xxxxx.it';

conn utl_smtp.connection;
crlf varchar2(2):=CHR(13)||CHR(10);
messag varchar2(3000);
v_mitt varchar2(2000) := mitt;
BEGIN
conn := utl_smtp.open_connection (mailhost, 25);

--but u hv to decide where u need to paste the fetched ename...

for rec_msg in my_cur
loop

messag := 'DATE: '||to_char(sysdate, 'dd-mon-yy hh24:mi:ss')||crlf||
'FROM: <'||mitt||'>'||crlf||
'SUBJECT: '||object||crlf||
'TO: '||destinatario1||' '||crlf||messagge || i.ename;

utl_smtp.helo(conn, mailhost);
utl_smtp.mail (conn, v_mitt);
utl_smtp.rcpt (conn, destinatario1);
utl_smtp.data(conn, messag);
utl_smtp.quit(conn);

messag := rec_msg.ename;
end loop;

EXCEPTION
when utl_smtp.transient_error or utl_smtp.permanent_error then
utl_smtp.quit(conn);
raise_application_error (-20000, 'error: '||sqlerrm);
END send_mail;

Cheers!

[balajiyes]

hi try this made a an error in my previ. reply

CREATE OR REPLACE procedure send_mail ( mitt IN varchar2,
destinatario1 IN varchar2,
object IN varchar2,
messagge IN varchar2) IS

cursor my_cur is
select ename
from emp;

mailhost varchar2(40):= 'pop.mail.xxxxx.it';

conn utl_smtp.connection;
crlf varchar2(2):=CHR(13)||CHR(10);
messag varchar2(3000);
v_mitt varchar2(2000) := mitt;
BEGIN
conn := utl_smtp.open_connection (mailhost, 25);

--but u hv to decide where u need to paste the fetched ename...

for rec_msg in my_cur
loop

messag := 'DATE: '||to_char(sysdate, 'dd-mon-yy hh24:mi:ss')||crlf||
'FROM: <'||mitt||'>'||crlf||
'SUBJECT: '||object||crlf||
'TO: '||destinatario1||' '||crlf||messagge || i.ename;

utl_smtp.helo(conn, mailhost);
utl_smtp.mail (conn, v_mitt);
utl_smtp.rcpt (conn, destinatario1);
utl_smtp.data(conn, messag);
messag := rec_msg.ename;
end loop;

utl_smtp.quit(conn);

EXCEPTION
when utl_smtp.transient_error or utl_smtp.permanent_error then
utl_smtp.quit(conn);
raise_application_error (-20000, 'error: '||sqlerrm);
END send_mail;

Cheers!

[raf]

ok balajiyes,
I tried this procedure, but It send 14 mails for each record of the table emp.
I'd like to send just one mail with 14 records.

How can I do it??

Thanks
Raf

[balajiyes]

hi if u have to achive this

This procedure run correctly if I insert in MESSAGE one text, but don't send the values of col ENAME in table scott.emp;

You have to loop each record.

Cheers!