Home » SQL & PL/SQL » SQL & PL/SQL » How to send a mail from PL/sql
How to send a mail from PL/sql [message #184689] Thu, 27 July 2006 08:21 Go to next message
hari_bk
Messages: 110
Registered: March 2006
Senior Member
Hi

Based on clients requirements. I need to prepare a SQL statement and the list of columns to be sent to number of users by mail. For this I need to set a process to send a mail from Oracle. This is a weekly process.

whats the process to follow for this.

I never worked on this can any one help me!

Pls give detailed answer.

Thanks
Hari
Re: How to send a mail from PL/sql [message #184692 is a reply to message #184689] Thu, 27 July 2006 08:33 Go to previous messageGo to next message
BlackSwan
Messages: 25047
Registered: January 2009
Location: SoCal
Senior Member
The read fine manual found at http://tahiti.oracle.com WRT the Oracle supplied procedure UTL_SMTP or UTL_MAIL depending upon oracle version.
icon12.gif  Re: How to send a mail from PL/sql [message #185350 is a reply to message #184689] Tue, 01 August 2006 05:26 Go to previous messageGo to next message
makrand82
Messages: 1
Registered: August 2006
Junior Member
The following procedure will help u send mail through pl-SQl...
..

CREATE OR REPLACE procedure p_send_mail
( sender IN VARCHAR2,
recipient IN VARCHAR2,
subject IN VARCHAR2,
message IN VARCHAR2)
IS
mailhost VARCHAR2(30) := '160.1.10.32';
mail_conn utl_smtp.connection;
crlf VARCHAR2( 2 ):= CHR( 13 ) || CHR( 10 );
mesg VARCHAR2( 4000 );
BEGIN
mail_conn := utl_smtp.open_connection(mailhost, 25);
mesg:= 'Date: ' || TO_CHAR( SYSDATE, 'dd Mon yy hh24:mi:ss' ) || crlf ||
'From: <'||sender||'>' || crlf ||
'Subject: '||subject || crlf ||
'To: '||recipient || crlf ||
'' || crlf || message;
utl_smtp.helo(mail_conn, mailhost);
utl_smtp.mail(mail_conn, sender);
utl_smtp.rcpt(mail_conn, recipient);
utl_smtp.data(mail_conn, mesg);
utl_smtp.quit(mail_conn);
END;
/

With Regards And Thanks
Makrand Patil
Software Engg(Oracle)
Kotak Mahindra BanK Pvt Ltd
Makrand82@yahoo.com
Re: How to send a mail from PL/sql [message #197388 is a reply to message #184689] Wed, 11 October 2006 02:04 Go to previous messageGo to next message
hany_marawan
Messages: 195
Registered: April 2005
Location: Cairo - Egypt
Senior Member
In this example you write
mailhost VARCHAR2(30) := '160.1.10.32';
what's IP?
Is this the incoming mail server(POP3)?
Or outgoing mail server(SMTP)?
Or what
Thanks
Re: How to send a mail from PL/sql [message #197457 is a reply to message #197388] Wed, 11 October 2006 05:27 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Did you even give it a thought yourself?
You want to send a mail. Should you know the IP of the outgoing server or the incoming server?
Furthermore, a couple of lines below that line there are several lines referring to utl_smtp.

It's not that hard, is it?
Re: How to send a mail from PL/sql [message #339925 is a reply to message #185350] Sat, 09 August 2008 14:46 Go to previous messageGo to next message
durgadas.menon
Messages: 365
Registered: December 2007
Location: Nowhere
Senior Member
Hi,

I had a few questions on this, please bear with me as I am new to this package (UTL_SMTP, UTL_MAIL).

The procedure p_send_mail has sender's email ID, receiver's email ID, message and subject but how will it be able to access the mail server as every email account will have its username and password.

I am trying to understand how this will work for me. I have my official email ID das@xxx.com the email server of which is being handled by another company. I have configured my MS Outlook with the POP3 and SMTP ID given by the company and entered my username and password as well. When I have to access my email through web I have to go to that company's (mail server company) website webmail.brinkster.com and enter my username and password to access my email. How will the UTL_SMTP or UTL_MAIL package work for me?

Or is that we have to have an email server of our before we can use this package?

Excuse me for this, I read many discussions in this forum but was not able to get the answer
Re: How to send a mail from PL/sql [message #339928 is a reply to message #184689] Sat, 09 August 2008 17:08 Go to previous messageGo to next message
BlackSwan
Messages: 25047
Registered: January 2009
Location: SoCal
Senior Member
>Or is that we have to have an email server of our before we can use this package?
Yes & these packages ONLY send messages & you don't need any username/password to do that.
An email message can be sent by using only the telnet utilty if you really know the SMTP protocol & formatting.

& please stop hijacking threads that are many months old.

[Updated on: Sat, 09 August 2008 17:28] by Moderator

Report message to a moderator

Re: How to send a mail from PL/sql [message #339940 is a reply to message #339928] Sun, 10 August 2008 00:23 Go to previous message
durgadas.menon
Messages: 365
Registered: December 2007
Location: Nowhere
Senior Member
Thanks..
Previous Topic: Pivoting table with 3 dimentions
Next Topic: Self Outer Join query
Goto Forum:
  


Current Time: Wed Dec 07 14:54:44 CST 2016

Total time taken to generate the page: 0.11656 seconds