Home » SQL & PL/SQL » SQL & PL/SQL » send mail through oracle
| send mail through oracle [message #240467] |
Fri, 25 May 2007 00:12  |
pardeep
Messages: 20
Registered: June 2001
Location: Chennai
|
Junior Member |
|
|
Hi,
I am trying to send the message from oracle to my frnd mail-id using following program.this program i taken from one of forum.This procedure id compiled successfully. but when i am exceuting this procedure with passing these parameters it showing like that..
DECLARE
MSG_FROM VARCHAR2(200);
MSG_TO VARCHAR2(200);
MSG_SUBJECT VARCHAR2(200);
MSG_TEXT VARCHAR2(200);
BEGIN
MSG_FROM := 'ORACLE';
MSG_TO := 'SUBRAMANYAM.K@POLARIS.CO.IN.';
MSG_SUBJECT := 'TEST';
MSG_TEXT := 'HI SUBBU,PARDEEP HERE , SENDING MEGS FROM ORACLE';
SCOTT.SEND_MAIL ( MSG_FROM, MSG_TO, MSG_SUBJECT, MSG_TEXT );
COMMIT;
END;
/
220 PSLDB01 ESMTP Sendmail 8.13.7+Sun/8.13.7; Fri, 25 May 2007 09:57:16 +0530
(IST)
250 PSLDB01 Hello localhost [127.0.0.1], pleased to meet you
250 2.1.0 ORACLE... Sender ok
250 2.1.5 SUBRAMANYAM.K@POLARIS.CO.IN.... Recipient ok
354 Enter mail, end with "." on a line by itself
250 2.0.0 l4P4RGlA016336 Message accepted for delivery
221 2.0.0 PSLDB01 closing connection
What is the meaning of this output.And i don't knwo also how to open tcp connection .what ip-address and port number i have to pass in utl_tcp.connection .which i given ,might be wrong.
but how would i know his ip address and port number.can any body help me.
CREATE OR REPLACE PROCEDURE SEND_MAIL (
msg_from varchar2 := 'oracle',
msg_to varchar2,
msg_subject varchar2 := 'E-Mail message from your database',
msg_text varchar2 := '' )
IS
c utl_tcp.connection;
rc integer;
BEGIN
c := utl_tcp.open_connection('127.0.0.1', 25); -- open the SMTP port 25 on local machine
dbms_output.put_line(utl_tcp.get_line(c, TRUE));
rc := utl_tcp.write_line(c, 'HELO localhost');
dbms_output.put_line(utl_tcp.get_line(c, TRUE));
rc := utl_tcp.write_line(c, 'MAIL FROM: '||msg_from);
dbms_output.put_line(utl_tcp.get_line(c, TRUE));
rc := utl_tcp.write_line(c, 'RCPT TO: '||msg_to);
dbms_output.put_line(utl_tcp.get_line(c, TRUE));
rc := utl_tcp.write_line(c, 'DATA'); -- Start message body
dbms_output.put_line(utl_tcp.get_line(c, TRUE));
rc := utl_tcp.write_line(c, 'Subject: '||msg_subject);
rc := utl_tcp.write_line(c, '');
rc := utl_tcp.write_line(c, msg_text);
rc := utl_tcp.write_line(c, '.'); -- End of message body
dbms_output.put_line(utl_tcp.get_line(c, TRUE));
rc := utl_tcp.write_line(c, 'QUIT');
dbms_output.put_line(utl_tcp.get_line(c, TRUE));
utl_tcp.close_connection(c); -- Close the connection
EXCEPTION
when others then
raise_application_error(
-20000, 'Unable to send e-mail message from pl/sql because of: '||
sqlerrm);
END;
thanks in advance.........
|
|
|
|
|
|
| Re: send mail through oracle [message #240664 is a reply to message #240485] |
Fri, 25 May 2007 08:06  |
pardeep
Messages: 20
Registered: June 2001
Location: Chennai
|
Junior Member |
|
|
hi Frank,
thank for reply fastly...
but message is not reached to that mail-id.and i m not sure also which ip address i given that is correct or not.bcaz it was already there.this ip something 127.0.0.1 and my frnd ip-x.y.z.1 and i given port 25 . i don't know also which port i have to pass there.from where i have to take also?
please help me....
|
|
|
|
| Re: send mail through oracle [message #240697 is a reply to message #240664] |
Fri, 25 May 2007 09:32 |
Ronald Beck
Messages: 121
Registered: February 2003
|
Senior Member |
|
|
The first thing you have to understand is how mail gets sent. A mail server listens for incoming messages on a port. When it sees "stuff" on the port, it acts on what it receives. When you executed your procedure, you got...
220 PSLDB01 ESMTP Sendmail 8.13.7+Sun/8.13.7; Fri, 25 May 2007 09:57:16 +0530
(IST)
250 PSLDB01 Hello localhost [127.0.0.1], pleased to meet you
250 2.1.0 ORACLE... Sender ok
250 2.1.5 SUBRAMANYAM.K@POLARIS.CO.IN.... Recipient ok
354 Enter mail, end with "." on a line by itself
250 2.0.0 l4P4RGlA016336 Message accepted for delivery
221 2.0.0 PSLDB01 closing connection
The first line shows that you connected with an SMTP email daemon on a system somewhere. The second line indicates that your procedure sent the HELO command to the email server and the server responded. Third line is the server accepting your "From" line. Fourth line is the server accepting your "To" line. The fifth line is directions on how to end the lines of data so that SMTP email knows when you're done. Line six indicates that your email message was accepted for delivery (all data has been sent) and the last line shows that you closed the connection to the SMTP server.
Now, let's look at the email procedure...
CREATE OR REPLACE PROCEDURE SEND_MAIL (
msg_from varchar2 := 'oracle',
msg_to varchar2,
msg_subject varchar2 := 'E-Mail message from your database',
msg_text varchar2 := '' )
IS
c utl_tcp.connection;
rc integer;
BEGIN
c := utl_tcp.open_connection('127.0.0.1', 25); -- open the SMTP port 25 on local machine
dbms_output.put_line(utl_tcp.get_line(c, TRUE));
rc := utl_tcp.write_line(c, 'HELO localhost');
dbms_output.put_line(utl_tcp.get_line(c, TRUE));
rc := utl_tcp.write_line(c, 'MAIL FROM: '||msg_from);
dbms_output.put_line(utl_tcp.get_line(c, TRUE));
rc := utl_tcp.write_line(c, 'RCPT TO: '||msg_to);
dbms_output.put_line(utl_tcp.get_line(c, TRUE));
rc := utl_tcp.write_line(c, 'DATA'); -- Start message body
dbms_output.put_line(utl_tcp.get_line(c, TRUE));
rc := utl_tcp.write_line(c, 'Subject: '||msg_subject);
rc := utl_tcp.write_line(c, '');
rc := utl_tcp.write_line(c, msg_text);
rc := utl_tcp.write_line(c, '.'); -- End of message body
dbms_output.put_line(utl_tcp.get_line(c, TRUE));
rc := utl_tcp.write_line(c, 'QUIT');
dbms_output.put_line(utl_tcp.get_line(c, TRUE));
utl_tcp.close_connection(c); -- Close the connection
EXCEPTION
when others then
raise_application_error(
-20000, 'Unable to send e-mail message from pl/sql because of: '||
sqlerrm);
END;
The first thing the SEND_MAIL procedure needs to do is create a connection to an SMTP mail server. In this example, 127.0.0.1 is the host IP address and 25 is the port. 127.0.0.1 is usually defined as the LOCALHOST, or the system you're on. Since you got an answer to your connection, I'm assuming you have some sort of mail server running on your system.
The commands HELO, MAIL_FROM:, RCPT_TO:, DATA and QUIT are all SMTP email commands that tell the server what you're sending it.
Allthe reply information you got back when you executed this procedure indicates that your email message was understood and accepted by the email service running on the system that you ran the procedure on.
Now, it's possible that the email server on your local system has not been configured to forward email out to where it's supposed to go which could be why you didn't get the message. You need to check with one of your system administrators to find out which machine is the SMTP server for your company or school. Then, replace the IP address 127.0.0.1 with the IP address of the real SMTP server. The port should remain 25 as this is the normal SMTP mail port.
HTH,
Ron
|
|
|
|
| Re: send mail through oracle [message #240929 is a reply to message #240697] |
Sun, 27 May 2007 21:32 |
pardeep
Messages: 20
Registered: June 2001
Location: Chennai
|
Junior Member |
|
|
Hi Ronald,
Thanks for reply,u given me very good explaination,now i understood the functioning of this procedure and meaning also.but still one confusion is there.I know System IP-Address and default gateway (start>>>run>>>>cmd>>>ipconfig) On this system IP-Address and default gateway IP-Address is available so i want to aks this IP addrees and Smtp IP-Address is same or SMTP IP-Address will different from this IP-Address and port will same or different?
waiting for reply.....
|
|
|
|
Goto Forum:
Current Time: Thu Dec 05 01:40:30 CST 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
