Home » Developer & Programmer » JDeveloper, Java & XML » how to send mail from pl/sql procedure???
how to send mail from pl/sql procedure??? [message #162713] Mon, 13 March 2006 05:26 Go to next message
b_chugh
Messages: 68
Registered: August 2005
Location: delhi
Member
hi

I am trying to send an email from the following procedure but it is unable to recognise the recepient.


1 CREATE OR REPLACE PROCEDURE SEND_MAIL (
2 msg_from varchar2 := 'oracle',
3 msg_to varchar2 :='b_chugh@yahoo.com',
4 msg_subject varchar2 := 'E-Mail message from your database',
5 msg_text varchar2 := 'hi dear' )
6 IS
7 c utl_tcp.connection;
8 rc integer;
9 BEGIN
10 c := utl_tcp.open_connection('10.0.40.127', 25); -- open the SMTP port 25 on local mach
11 dbms_output.put_line(utl_tcp.get_line(c, TRUE));
12 rc := utl_tcp.write_line(c, 'HELO localhost');
13 dbms_output.put_line(utl_tcp.get_line(c, TRUE));
14 rc := utl_tcp.write_line(c, 'MAIL FROM: '||msg_from);
15 dbms_output.put_line(utl_tcp.get_line(c, TRUE));
16 rc := utl_tcp.write_line(c, 'RCPT TO: '||msg_to);
17 dbms_output.put_line(utl_tcp.get_line(c, TRUE));
18 rc := utl_tcp.write_line(c, 'DATA'); -- Start message body
19 dbms_output.put_line(utl_tcp.get_line(c, TRUE));
20 rc := utl_tcp.write_line(c, 'Subject: '||msg_subject);
21 rc := utl_tcp.write_line(c, '');
22 rc := utl_tcp.write_line(c, msg_text);
23 rc := utl_tcp.write_line(c, '.'); -- End of message body
24 dbms_output.put_line(utl_tcp.get_line(c, TRUE));
25 rc := utl_tcp.write_line(c, 'QUIT');
26 dbms_output.put_line(utl_tcp.get_line(c, TRUE));
27 utl_tcp.close_connection(c); -- Close the connection
28 EXCEPTION
29 when others then
30 raise_application_error(
31 -20000, 'Unable to send e-mail message from pl/sql because of: '||
32 sqlerrm);
33* END;
SQL> /

Procedure created.

SQL> exec send_mail
220 d-nitin.india.rsystems.com Microsoft ESMTP MAIL Service, Version:
5.0.2195.6713 ready at Mon, 13 Mar 2006 16:49:37 +0530
250 d-nitin.india.rsystems.com Hello [10.0.10.65]
250 2.1.0 oracle@d-nitin.india.rsystems.com....Sender OK
550 5.7.1 Unable to relay for b_chugh@yahoo.com
554 5.5.2 No valid recipients
500 5.3.3 Unrecognized command
500 5.3.3 Unrecognized command

PL/SQL procedure successfully completed.




Pls help if any one can tell me where I am missing out.

Re: how to send mail from pl/sql procedure??? [message #162823 is a reply to message #162713] Mon, 13 March 2006 14:38 Go to previous messageGo to next message
mchadder
Messages: 224
Registered: May 2005
Location: UK
Senior Member
Hello.

Not too sure, but I would recommend using the UTL_SMTP package, though. It's a lot easier, especially when you have read this URL from AskTom ( http://asktom.oracle.com/pls/ask/f?p=4950:8:12403776483772909517::NO::F4950_P8_DISPLAYID,F4950_P8_CRITERIA:255615160805 ).

Rgds.
Re: how to send mail from pl/sql procedure??? [message #162876 is a reply to message #162823] Mon, 13 March 2006 23:40 Go to previous messageGo to next message
b_chugh
Messages: 68
Registered: August 2005
Location: delhi
Member
hi
This smtp method is also giving the same error.




1 declare
2 p_sender VARCHAR2(100):='bhaskar.prakash@india.rsystems.com';
3 p_recipient VARCHAR2(100):='bhaskar.prakash@india.rsystems.com';
4 p_message VARCHAR2(100):='hi dear';
5 -- l_mailhost VARCHAR2(255) := 'aria.us.oracle.com';
6 l_mailhost VARCHAR2(255) := 'd-nitin.india.rsystems.com';
7 l_mail_conn utl_smtp.connection;
8 BEGIN
9 l_mail_conn := utl_smtp.open_connection(l_mailhost, 25);
10 utl_smtp.helo(l_mail_conn, l_mailhost);
11 utl_smtp.mail(l_mail_conn, p_sender);
12 utl_smtp.rcpt(l_mail_conn, p_recipient);
13 utl_smtp.open_data(l_mail_conn );
14 utl_smtp.write_data(l_mail_conn, p_message);
15 utl_smtp.close_data(l_mail_conn );
16 utl_smtp.quit(l_mail_conn);
17* end;
SQL> /
declare
*
ERROR at line 1:
ORA-29279: SMTP permanent error: 550 5.7.1 Unable to relay for bhaskar.prakash@india.rsystems.com
ORA-06512: at "SYS.UTL_SMTP", line 17
ORA-06512: at "SYS.UTL_SMTP", line 98
ORA-06512: at "SYS.UTL_SMTP", line 240
ORA-06512: at line 12


Re: how to send mail from pl/sql procedure??? [message #162937 is a reply to message #162876] Tue, 14 March 2006 04:05 Go to previous messageGo to next message
rsoma
Messages: 15
Registered: April 2005
Location: Chennai, India
Junior Member
Hi

Is that the SMTP server is valid and it is working fine.
Re: how to send mail from pl/sql procedure??? [message #163524 is a reply to message #162876] Fri, 17 March 2006 01:29 Go to previous messageGo to next message
amolnk
Messages: 9
Registered: March 2006
Location: India
Junior Member
ask your mail admin, if any restrictions placed with the relays.
it might be the one creating the problem.
Re: how to send mail from pl/sql procedure??? [message #163777 is a reply to message #162713] Sun, 19 March 2006 22:46 Go to previous messageGo to next message
b_chugh
Messages: 68
Registered: August 2005
Location: delhi
Member
hi
Thanks Mchadder and rsoma. I have checked the SMPTP server with my admin and now it is working fine. Its really very simple and compact.



Re: how to send mail from pl/sql procedure??? [message #414058 is a reply to message #163524] Mon, 20 July 2009 04:06 Go to previous messageGo to next message
Mell
Messages: 1
Registered: July 2009
Junior Member
Hi,

May you please provide the steps you took with your Admin. I spoke to my Admin but he doesn't have a solution.

Thanks in advance
Re: how to send mail from pl/sql procedure??? [message #414060 is a reply to message #414058] Mon, 20 July 2009 04:10 Go to previous message
ThomasG
Messages: 3211
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
I would suggest:

Step 1: Sent him the "SMTP permanent error" message
Step 2: If he doesn't have a solution, contact his boss.
Previous Topic: Produce XML schema
Next Topic: Putting method into ADF
Goto Forum:
  


Current Time: Thu Mar 28 20:48:58 CDT 2024