Home » SQL & PL/SQL » SQL & PL/SQL » How send a mail dynamically (Oracle 9.2.0.1.0)
How send a mail dynamically [message #366279] Fri, 12 December 2008 10:44 Go to next message
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 #366280 is a reply to message #366279] Fri, 12 December 2008 11:21 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
I create correctly the procedure to send mail but I don't know how can I change the recipient when diff is > 30 days.

You mean someone gives a procedure to send a mail.

Add a where clause to your SELECT.

Regards
Michel
Re: How send a mail dynamically [message #366284 is a reply to message #366280] Fri, 12 December 2008 11:30 Go to previous messageGo to next message
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
Re: How send a mail dynamically [message #366289 is a reply to message #366284] Fri, 12 December 2008 12:18 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Use a table of recipient, or a string containing the list of recipient.

Regards
Michel
Re: How send a mail dynamically [message #366291 is a reply to message #366289] Fri, 12 December 2008 12:23 Go to previous messageGo to next message
dba_raf
Messages: 13
Registered: December 2005
Junior Member
Michel Cadot wrote on Fri, 12 December 2008 12:18
Use a table of recipient, or a string containing the list of recipient.

Regards
Michel



TABLE USER_MAIL has the recipients
Re: How send a mail dynamically [message #366297 is a reply to message #366291] Fri, 12 December 2008 12:48 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I mean in your procedure, isn't the question:
Quote:
How can modify this procedure in my case?

Regards
Michel
Re: How send a mail dynamically [message #366323 is a reply to message #366297] Fri, 12 December 2008 16:44 Go to previous message
dba_raf
Messages: 13
Registered: December 2005
Junior Member
Thanks for your great Help!
Previous Topic: search file from system
Next Topic: sql problem
Goto Forum:
  


Current Time: Fri Dec 09 00:00:44 CST 2016

Total time taken to generate the page: 0.07442 seconds