Home » SQL & PL/SQL » SQL & PL/SQL » send mail through oracle
send mail through oracle [message #240467] Fri, 25 May 2007 00:12 Go to next message
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 #240485 is a reply to message #240467] Fri, 25 May 2007 00:53 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
The lines you see are the responses of the smtp process on the mail server.
Re: send mail through oracle [message #240664 is a reply to message #240485] Fri, 25 May 2007 08:06 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Go to previous message
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.....
Previous Topic: How to shrink the size of a table its indexes after a delete command?
Next Topic: BOM Explosion package
Goto Forum:
  


Current Time: Wed Dec 07 22:28:16 CST 2016

Total time taken to generate the page: 0.05525 seconds