Home » SQL & PL/SQL » SQL & PL/SQL » How to send mail through pl/sql (Oracle 10G)
How to send mail through pl/sql [message #362610] Wed, 03 December 2008 04:00 Go to next message
aviva4500
Messages: 122
Registered: July 2008
Location: bangalore
Senior Member
Dear All,
I have a pl/sql which sends the mail through pl/sql.
Below is the code which i am using .

DECLARE
l_mailhost VARCHAR2(64) := 'x.com';
l_from VARCHAR2(64) := 'y.com';
l_to VARCHAR2(64) := 'z.com';
l_mail_conn UTL_SMTP.connection;
BEGIN
l_mail_conn := UTL_SMTP.open_connection('x.com', 25);
UTL_SMTP.helo(l_mail_conn, 'x.com');
UTL_SMTP.mail(l_mail_conn, l_from);
UTL_SMTP.rcpt(l_mail_conn, l_to);
UTL_SMTP.data(l_mail_conn, 'Single string message.' || Chr(13));
UTL_SMTP.quit(l_mail_conn);
END;


I have a table named test which has a date column here in the above code need to add a condition when sysdate = test.sdate then the above code should send a mail to the receiptents.can anyone guide me on this

Thanks & Regards,

Hammer
Re: How to send mail through pl/sql [message #362611 is a reply to message #362610] Wed, 03 December 2008 04:03 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
What is actually your problem?
You don't know how to test a date in a table?
You don't know how to call "your" procedure?
You don't how to schedule?
Question

Regards
Michel
Re: How to send mail through pl/sql [message #362763 is a reply to message #362611] Thu, 04 December 2008 01:37 Go to previous messageGo to next message
aviva4500
Messages: 122
Registered: July 2008
Location: bangalore
Senior Member
Hi Michel,
I have tried Michel don't mistake me,but i am sorry for the above post here below is the code which i have execued with no errors but the mail is not sent.Am i missing something.

SQL> DECLARE
2 l_mailhost VARCHAR2(64) := 'x.co.in';
3 l_from VARCHAR2(64) := 'y.co.in';
4 l_to VARCHAR2(64) := 'z.co.in';
5 l_mail_conn UTL_SMTP.connection;
6 v_date date;
7 BEGIN
8 SELECT TO_DATE(TO_CHAR(sdate,'DD-MON-YYYY'))
9 INTO v_date
10 FROM test t;
11 IF TO_DATE(TO_CHAR(v_date,'DD-MON-YYYY')) = TO_DATE(TO_CHAR(sysdate,'DD-MON-YYYY')) THEN
12 l_mail_conn := UTL_SMTP.open_connection('x.co.in', 25);
13 UTL_SMTP.helo(l_mail_conn, 'x.co.in');
14 UTL_SMTP.mail(l_mail_conn, l_from);
15 UTL_SMTP.rcpt(l_mail_conn, l_to);
16 UTL_SMTP.data(l_mail_conn, 'Single string message.' || Chr(13));
17 UTL_SMTP.quit(l_mail_conn);
18 END IF;
19 END;
20 /

PL/SQL procedure successfully completed.



Thanks & Regards,
Hammer.
Re: How to send mail through pl/sql [message #362769 is a reply to message #362763] Thu, 04 December 2008 01:46 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Investigate in your smtp server side.
And please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter) and align the columns in result.
Use the "Preview Message" button to verify.

Regards
Michel
Re: How to send mail through pl/sql [message #362774 is a reply to message #362769] Thu, 04 December 2008 02:07 Go to previous messageGo to next message
aviva4500
Messages: 122
Registered: July 2008
Location: bangalore
Senior Member
Hi Michel,
Now instead of webaddress(x.com) i used the ipaddress(x.x.x.x) but still the same.

DECLARE
l_MailHost VARCHAR2(64) := 'x.co.in';
l_From VARCHAR2(64) := 'y.co.in';
l_To VARCHAR2(64) := 'z.co.in';
l_Mail_Conn utl_smtp.Connection;
v_Date DATE;
BEGIN
SELECT To_date(To_char(sDate,'DD-MON-YYYY'))
INTO v_Date
FROM Test t;

IF To_Date(To_Char(v_Date,'DD-MON-YYYY')) = To_Date(To_Char(SYSDATE,'DD-MON-YYYY')) THEN
l_Mail_Conn := utl_smtp.Open_Connection('x.co.in',25);

utl_smtp.HeLo(l_Mail_Conn,'x.co.in');

utl_smtp.Mail(l_Mail_Conn,l_From);

utl_smtp.rcpt(l_Mail_Conn,l_To);

utl_smtp.Data(l_Mail_Conn,'Single string message.'
||chr(13));

utl_smtp.Quit(l_Mail_Conn);
END IF;
END;
Thanks & Regards,
Hammer.
Re: How to send mail through pl/sql [message #362789 is a reply to message #362774] Thu, 04 December 2008 02:39 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Same answer.

Regards
Michel
Re: How to send mail through pl/sql [message #362797 is a reply to message #362789] Thu, 04 December 2008 02:57 Go to previous messageGo to next message
aviva4500
Messages: 122
Registered: July 2008
Location: bangalore
Senior Member
Hi Michel,
I have investigated the smtp server side and for your kind reference i used the below code which sends the mail, and works perfectly.But if i add the if condition it is not sending the mail.

DECLARE
l_MailHost VARCHAR2(64) := 'x.co.in';
l_From VARCHAR2(64) := 'abc@x.co.in';
l_To VARCHAR2(64) := 'def@x.co.in';
l_Mail_Conn utl_smtp.Connection;
BEGIN
l_Mail_Conn := utl_smtp.Open_Connection('x.x.x.x',25);

utl_smtp.HeLo(l_Mail_Conn,'x.x.x.x');

utl_smtp.Mail(l_Mail_Conn,l_From);

utl_smtp.rcpt(l_Mail_Conn,l_To);

utl_smtp.Data(l_Mail_Conn,'Single string message.'
||chr(13));

utl_smtp.Quit(l_Mail_Conn);
END;



Thanks & Regards,
Hammer.

Re: How to send mail through pl/sql [message #362800 is a reply to message #362797] Thu, 04 December 2008 03:03 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Continue to investigate and to format your post.

Regards
Michel
Re: How to send mail through pl/sql [message #362808 is a reply to message #362800] Thu, 04 December 2008 03:17 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
You're on 10g - why not have a look at UTL_MAIL
Re: How to send mail through pl/sql [message #362820 is a reply to message #362808] Thu, 04 December 2008 03:43 Go to previous messageGo to next message
aviva4500
Messages: 122
Registered: July 2008
Location: bangalore
Senior Member
Dear All,
I think I am confusing,my concern is i have a table named test which has a column name called sdate which holds some date information and i am having around 10 data,in that particular column.I need to send a mail if the values in the date column matches sysdate, the pl/sql code should send a mail.For your kind reference i used the below code which executes fine.And the mail is sent for the below code.

DECLARE
l_mailhost VARCHAR2(64) := 'x.com';
l_from VARCHAR2(64) := 'y.com';
l_to VARCHAR2(64) := 'z.com';
l_mail_conn UTL_SMTP.connection;
BEGIN
l_mail_conn := UTL_SMTP.open_connection('x.com', 25);
UTL_SMTP.helo(l_mail_conn, 'x.com');
UTL_SMTP.mail(l_mail_conn, l_from);
UTL_SMTP.rcpt(l_mail_conn, l_to);
UTL_SMTP.data(l_mail_conn, 'Single string message.' || Chr(13));
UTL_SMTP.quit(l_mail_conn);
END;



Below is the code where i added the condition and executed with out error but the mail was not sent.

DECLARE
l_MailHost VARCHAR2(64) := 'x.com';
l_From VARCHAR2(64) := 'abc.x.com;
l_To VARCHAR2(64) := 'def.x.com;
l_Mail_Conn utl_smtp.Connection;
v_Date DATE;
BEGIN
SELECT To_date(To_char(sDate,'DD-MON-YYYY'))
INTO v_Date
FROM Test t;

IF To_Date(To_Char(v_Date,'DD-MON-YYYY')) = To_Date(To_Char(SYSDATE,'DD-MON-YYYY')) THEN
l_Mail_Conn := utl_smtp.Open_Connection('x.com',25);

utl_smtp.HeLo(l_Mail_Conn,'x.com');

utl_smtp.Mail(l_Mail_Conn,l_From);

utl_smtp.rcpt(l_Mail_Conn,l_To);

utl_smtp.Data(l_Mail_Conn,'Single string message.'
||chr(13));

utl_smtp.Quit(l_Mail_Conn);
END IF;
END;




Thanks & Regards,
Hammer.

[Updated on: Thu, 04 December 2008 03:44]

Report message to a moderator

Re: How to send mail through pl/sql [message #362822 is a reply to message #362820] Thu, 04 December 2008 03:46 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
So you're asking how to loop through a table and perform an action for every row that meets a certain condiiton?

BEGIN
  FOR rec IN (SELECT * FROM table WHERE trunc(date_column) = trunc(sysdate)) LOOP
    call_mail_procedure;
  END LOOP;
END;


Is that what you're looking for?
Re: How to send mail through pl/sql [message #362830 is a reply to message #362822] Thu, 04 December 2008 04:02 Go to previous messageGo to next message
aviva4500
Messages: 122
Registered: July 2008
Location: bangalore
Senior Member
Hi JRowbottom,
This is the data which i have in the table named test.Below is for your kind reference.

SQL> select * from test;

SDATE
---------
04-DEC-08
03-DEC-08
07-DEC-08
08-DEC-08
09-DEC-08
11-DEC-08
12-DEC-08

7 rows selected.


I am trying to implement the below condition,so that when the date matches, the pl/sql code should send a mail.

IF To_Date(To_Char(v_Date,'DD-MON-YYYY')) = To_Date(To_Char(SYSDATE,'DD-MON-YYYY'))


So in the above data we have 07-DEC-08 after three days when the condition matches the mail has to be sent.


Thanks & Regards,
Hammer

Re: How to send mail through pl/sql [message #362835 is a reply to message #362830] Thu, 04 December 2008 04:20 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
It's a pretty simple change to the code I posted.
This willl loop through your table and call the procedure call_mail_procedure for each row where sdate sthe same day as the current sysdate

BEGIN
  FOR rec IN (SELECT * FROM test WHERE trunc(sdate) = trunc(sysdate)) LOOP
    call_mail_procedure;
  END LOOP;
END;


I'm guessing that you didn't write that mail procedure yourself.
This is really basic pl/sql here - if you're having trouble with this, you could do with reading up on Pl/Sql
Re: How to send mail through pl/sql [message #362849 is a reply to message #362835] Thu, 04 December 2008 05:24 Go to previous messageGo to next message
aviva4500
Messages: 122
Registered: July 2008
Location: bangalore
Senior Member
Hi JRowbottom,
I have tried this already but i got the following error.I know that i am missing some part over here.

ERROR at line 3:
ORA-06550: line 3, column 6:
PLS-00103: Encountered the symbol "PROC_TEST_MAIL" when expecting one of the
following:
:= . ( @ % ;
The symbol ":=" was substituted for "PROC_TEST_MAIL" to continue.


Thanks & Regards,
Hammer.


[Updated on: Thu, 04 December 2008 05:49]

Report message to a moderator

Re: How to send mail through pl/sql [message #362912 is a reply to message #362849] Thu, 04 December 2008 08:49 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Hang on a second while I put my Acme Telepathic Perceptual Transfer Helmet on, and look through your eyes to see what the actual code you've run is.

Ahh drat - it's not Vista compatible.

I guess you're out of luck, unless you actually think it's worth posting the code as well as the error message.
Previous Topic: Delete Statement
Next Topic: ORA-00922: missing or invalid option (merged 3)
Goto Forum:
  


Current Time: Sun Dec 04 12:40:55 CST 2016

Total time taken to generate the page: 0.10544 seconds