| how to send mail from pl/sql procedure??? [message #162713] |
Mon, 13 March 2006 05:26  |
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 #162876 is a reply to message #162823] |
Mon, 13 March 2006 23:40  |
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
|
|
|
|
|
|
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
