-
utl_smtp package with a table
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
-
Re: utl_smtp package with a table
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
Tomaž
"A common mistake that people make when trying to design something completely
foolproof is to underestimate the ingenuity of complete fools" - Douglas Adams
-
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!
Cheers!
OraKid.
-
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!
Cheers!
OraKid.
-
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
-
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!
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|