| How send a mail dynamically [message #366279] |
Fri, 12 December 2008 10:44  |
dba_raf
Messages: 13
Registered: December 2005
|
Junior Member |
|
|
Hi,
I've this table:
CREATE TABLE USER_MAIL
(
USER_NAME VARCHAR2(32 BYTE),
DATA_MODIFY DATE,
MAIL_ADDRESS VARCHAR2(64 BYTE)
);
INSERT INTO USER_MAIL ( USER_NAME, DATA_MODIFY, MAIL_ADDRESS ) VALUES (
'SMITH', TO_Date( '11/15/2008 12:02:00 AM', 'MM/DD/YYYY HH:MI:SS AM'), 'arc@gmail.com');
INSERT INTO USER_MAIL ( USER_NAME, DATA_MODIFY, MAIL_ADDRESS ) VALUES (
'TOM', TO_Date( '10/08/2008 12:03:00 AM', 'MM/DD/YYYY HH:MI:SS AM'), 'tom.sun@yahoo.it');
INSERT INTO USER_MAIL ( USER_NAME, DATA_MODIFY, MAIL_ADDRESS ) VALUES (
'AFM', TO_Date( '11/10/2008 12:01:00 AM', 'MM/DD/YYYY HH:MI:SS AM'), 'mail@gmail.com');
INSERT INTO USER_MAIL ( USER_NAME, DATA_MODIFY, MAIL_ADDRESS ) VALUES (
'SAM', TO_Date( '12/11/2008 12:02:00 AM', 'MM/DD/YYYY HH:MI:SS AM'), 'dany@gmail.com');
commit;
SELECT USER_NAME, DATA_MODIFY,MAIL_ADDRESS, (TRUNC(sysdate) - TRUNC(DATA_MODIFY)) DIFF
FROM USER_MAIL;
USER_NAME............DATA_MODIFY..........MAIL_ADDRESS......DIFF
SMITH............15/11/2008 0.02.00.......arc@gmail.com......27
TOM..............08/10/2008 0.03.00.......tom.sun@yahoo.it...65
AFM..............10/11/2008 0.01.00.......mail@gmail.com.....32
SAM..............11/12/2008 0.02.00.......dany@gmail.com......1
................................................................
................................................................more 1000 rows
I'd like to create a stored procedure that send mail at user name
when the difference between sysdate and data_modify is > 30 days.
I create correctly the procedure to send mail but I don't know how can I change the recipient when diff is > 30 days.
In my example I must send mail at TOM(tom.sun@yahoo.it) and AFM (mail@gmail.com)
How can I create this stored procedure?
Thanks in advance!
|
|
|
|
|
|
| Re: How send a mail dynamically [message #366284 is a reply to message #366280] |
Fri, 12 December 2008 11:30  |
dba_raf
Messages: 13
Registered: December 2005
|
Junior Member |
|
|
this is my correct procedure that send mail:
CREATE OR REPLACE PROCEDURE send_mail (sender IN varchar2,
recipient IN varchar2,
recipient2 IN varchar2,
oggetto IN varchar2,
messaggio IN varchar2) IS
mailhost varchar2(40) := 'mail.cww.it';
conn utl_smtp.connection;
crlf varchar2(2) := CHR(13)||CHR(10);
messag varchar2(3000);
v_sender varchar2(2000) := sender;
BEGIN
conn := utl_smtp.open_connection (mailhost, 25);
messag := 'DATE: '||to_char(sysdate, 'dd-mon-yy hh24:mi:ss')||crlf||
'FROM: <'||sender||'>'||crlf||
'SUBJECT: '||oggetto||crlf||
'TO: '||recipient||'; '
||recipient2||' '
||crlf||messaggio;
utl_smtp.helo(conn, mailhost);
utl_smtp.mail (conn, v_sender);
utl_smtp.rcpt (conn, recipient);
utl_smtp.rcpt (conn, recipient2);
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,sqlerrm);
END;
/
How can modify this procedure in my case?
Thanks
|
|
|
|
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
