Home > Archive > Other Oracle database topics > November 2005 > Send email whith oracle









You are viewing an archived Text-only version of the thread. To view this thread in it's original format and/or if you want to reply to this thread please [click here]

 

Author Send email whith oracle
Alen Cappelletti

2005-11-28, 11:23 am

Hi all,
I write from Italy and with this procedure I want send a E-mail from
Oracle.
Ok, all works fine except the messagge body.

-------
CREATE OR REPLACE PROCEDURE send_mail_alen(
v_sender in varchar2,
v_recipient in varchar2,
v_ccrecipient in varchar2,
v_subject in varchar2,
v_message in varchar2
)
AS
crlf VARCHAR2(2) := UTL_TCP.CRLF;
l_mailhost varchar2(255) := '10.138.0.15' ;
l_mail_conn utl_smtp.connection ;
v_header varchar2(1000);

BEGIN
--
-- Start the connection.
--
l_mail_conn := utl_smtp. open_connection(l_ma
ilhost, 25) ;

v_header:= 'Date: '||TO_CHAR(SYSDATE,'
dd Mon yy hh24:mi:ss')||crlf||

'From: '||v_sender||''||crl
f||
'Subject: '||v_subject||crlf||

'To: '||v_recipient||crlf
||
'CC: '||v_ccrecipient;


--
-- Handshake with the SMTP server
--
utl_smtp.helo(l_mail_conn, l_mailhost) ;
utl_smtp.mail(l_mail_conn, p_sender) ;
utl_smtp.rcpt(l_mail_conn, p_recipient ) ;
utl_smtp. open_data(l_mail_con
n) ;


--
-- Write the header
--
utl_smtp. write_data(l_mail_co
nn, v_header);
utl_smtp. write_data(l_mail_co
nn, crlf||v_message ) ;
utl_smtp. close_data(l_mail_co
nn) ;
utl_smtp.quit(l_mail_conn );
--dbms_output.put_line('Message send to the user successfully') ;
END;


-----------------
CALL IT...
BEGIN
send_mail_alen(
'cocker@libero.it',
'alen.cappelletti@it.scc.com',
'cocker@libero.it,aaa@llll.it',
'Oggetto del messaggio',
'saaaaaaaaaaaaaaaaaa
lve...');
END;

I have Notes client and the only field black is my message body.
Someone have a solution or a TIP?

TNKS ALen

DA Morgan

2005-11-28, 1:23 pm

Alen Cappelletti wrote:
> Hi all,
> I write from Italy and with this procedure I want send a E-mail from
> Oracle.
> Ok, all works fine except the messagge body.
>
> -------
> CREATE OR REPLACE PROCEDURE send_mail_alen(
> v_sender in varchar2,
> v_recipient in varchar2,
> v_ccrecipient in varchar2,
> v_subject in varchar2,
> v_message in varchar2
> )
> AS
> crlf VARCHAR2(2) := UTL_TCP.CRLF;
> l_mailhost varchar2(255) := '10.138.0.15' ;
> l_mail_conn utl_smtp.connection ;
> v_header varchar2(1000);
>
> BEGIN
> --
> -- Start the connection.
> --
> l_mail_conn := utl_smtp. open_connection(l_ma
ilhost, 25) ;
>
> v_header:= 'Date: '||TO_CHAR(SYSDATE,'
dd Mon yy hh24:mi:ss')||crlf||

> 'From: '||v_sender||''||crl
f||
> 'Subject: '||v_subject||crlf||

> 'To: '||v_recipient||crlf
||
> 'CC: '||v_ccrecipient;
>
>
> --
> -- Handshake with the SMTP server
> --
> utl_smtp.helo(l_mail_conn, l_mailhost) ;
> utl_smtp.mail(l_mail_conn, p_sender) ;
> utl_smtp.rcpt(l_mail_conn, p_recipient ) ;
> utl_smtp. open_data(l_mail_con
n) ;
>
>
> --
> -- Write the header
> --
> utl_smtp. write_data(l_mail_co
nn, v_header);
> utl_smtp. write_data(l_mail_co
nn, crlf||v_message ) ;
> utl_smtp. close_data(l_mail_co
nn) ;
> utl_smtp.quit(l_mail_conn );
> --dbms_output.put_line('Message send to the user successfully') ;
> END;
>
>
> -----------------
> CALL IT...
> BEGIN
> send_mail_alen(
> 'cocker@libero.it',
> 'alen.cappelletti@it.scc.com',
> 'cocker@libero.it,aaa@llll.it',
> 'Oggetto del messaggio',
> 'saaaaaaaaaaaaaaaaa
alve...');
> END;
>
> I have Notes client and the only field black is my message body.
> Someone have a solution or a TIP?
>
> TNKS ALen

Nowhere do you seem to assign a value to: v_message. I'd be looking
for a line such as:

v_message := 'My message';

--
Daniel A. Morgan
http://www.psoug.org
damorgan@x.washington.edu
(replace x with u to respond)
Andy Hassall

2005-11-28, 8:23 pm

On 28 Nov 2005 08:34:50 -0800, "Alen Cappelletti" <cocker@libero.it> wrote:

>I write from Italy and with this procedure I want send a E-mail from
>Oracle.
>Ok, all works fine except the messagge body.
>
> v_header:= 'Date: '||TO_CHAR(SYSDATE,'
dd Mon yy hh24:mi:ss')||crlf||

> 'From: '||v_sender||''||crl
f||
> 'Subject: '||v_subject||crlf||

> 'To: '||v_recipient||crlf
||
> 'CC: '||v_ccrecipient;

OK, so v_header doesn't end in CRLF.

& #91;snip]
> --
> -- Write the header
> --
> utl_smtp. write_data(l_mail_co
nn, v_header);

You write v_header, and you're still on the same line - no CRLF output yet.

> utl_smtp. write_data(l_mail_co
nn, crlf||v_message ) ;

You write out one CRLF, and then start the body; but you need another CRLF
before the body, to terminate the header section with a blank line.

The write_data line for v_header probably just needs "||crlf".
--
Andy Hassall :: andy@andyh.co.uk :: http://www.andyh.co.uk
http://www.andyhsoftware.co.uk/space :: disk and FTP usage analysis tool
Andy Hassall

2005-11-28, 8:23 pm

On Mon, 28 Nov 2005 10:02:25 -0800, DA Morgan <damorgan@psoug.org> wrote:

>Nowhere do you seem to assign a value to: v_message.

It's a parameter.
[color=darkred]
--
Andy Hassall :: andy@andyh.co.uk :: http://www.andyh.co.uk
http://www.andyhsoftware.co.uk/space :: disk and FTP usage analysis tool
DA Morgan

2005-11-28, 8:23 pm

Andy Hassall wrote:[color=darkred
]
> On Mon, 28 Nov 2005 10:02:25 -0800, DA Morgan <damorgan@psoug.org> wrote:
>
>
>
>
> It's a parameter.
>
>

Didn't notice it so your response is correct. Thanks for catching it.
--
Daniel A. Morgan
http://www.psoug.org
damorgan@x.washington.edu
(replace x with u to respond)
PHernandez

2005-11-28, 8:23 pm

Andy Hassall ha escrito:

> On 28 Nov 2005 08:34:50 -0800, "Alen Cappelletti" <cocker@libero.it> wrote:
>
>
> OK, so v_header doesn't end in CRLF.
>
> [snip]
>
> You write v_header, and you're still on the same line - no CRLF output yet.
>
>
> You write out one CRLF, and then start the body; but you need another CRLF
> before the body, to terminate the header section with a blank line.
>
> The write_data line for v_header probably just needs "||crlf".

Likely, and when that is solved he'll need to actually add the cc's to
the send list as well, the Cc header is not magic (and why have it
there in case v_ccrecipient is null?).

--ph

Alen Cappelletti

2005-11-29, 3:23 am

OK, thanks Andy...
now I'write my BODY message but no v_header.

------
CREATE OR REPLACE PROCEDURE send_mail_alen(
v_sender in varchar2,
v_recipient in varchar2,
v_ccrecipient in varchar2,
v_subject in varchar2,
v_message in varchar2
)
AS
crlf VARCHAR2(2) := UTL_TCP.CRLF;
l_mailhost varchar2(255) := '10.138.0.15' ;
l_mail_conn utl_smtp.connection ;
v_header varchar2(1000);

BEGIN
--
-- Start the connection.
--
l_mail_conn := utl_smtp. open_connection(l_ma
ilhost, 25) ;

v_header:= 'Date: '||TO_CHAR(SYSDATE,'
dd Mon yy hh24:mi:ss')||crlf||

'From: '||v_sender||''||crl
f||
'Subject: '||v_subject||crlf||

'To: '||v_recipient||crlf
||
'CC: '||v_ccrecipient;


--
-- Handshake with the SMTP server
--
utl_smtp.helo(l_mail_conn, l_mailhost) ;
utl_smtp.mail(l_mail_conn, v_sender) ;
utl_smtp.rcpt(l_mail_conn, v_recipient ) ;
utl_smtp. open_data(l_mail_con
n) ;


--
-- Write the header
--
utl_smtp. write_data(l_mail_co
nn, v_header||crlf);
utl_smtp. write_data(l_mail_co
nn, crlf||v_message ) ;
utl_smtp. close_data(l_mail_co
nn) ;
utl_smtp.quit(l_mail_conn );
--dbms_output.put_line('Message send to the user successfully') ;
END;

bsc7080mqc@mylxhq.com

2005-11-29, 7:23 am

If you like you can look at the routine that I collectively put together regarding email and oracle. Hit me up with an email or
visit my site. It is free for the taking. We currently use the same mail process in our production environment... about 2 years
going now.

On 29 Nov 2005 01:09:04 -0800, "Alen Cappelletti" <cocker@libero.it> wrote:

>OK, thanks Andy...
>now I'write my BODY message but no v_header.
>
>------
>CREATE OR REPLACE PROCEDURE send_mail_alen(
> v_sender in varchar2,
> v_recipient in varchar2,
> v_ccrecipient in varchar2,
> v_subject in varchar2,
> v_message in varchar2
> )
>AS
> crlf VARCHAR2(2) := UTL_TCP.CRLF;
> l_mailhost varchar2(255) := '10.138.0.15' ;
> l_mail_conn utl_smtp.connection ;
> v_header varchar2(1000);
>
>BEGIN
> --
> -- Start the connection.
> --
> l_mail_conn := utl_smtp. open_connection(l_ma
ilhost, 25) ;
>
> v_header:= 'Date: '||TO_CHAR(SYSDATE,'
dd Mon yy hh24:mi:ss')||crlf||

> 'From: '||v_sender||''||crl
f||
> 'Subject: '||v_subject||crlf||

> 'To: '||v_recipient||crlf
||
> 'CC: '||v_ccrecipient;
>
>
> --
> -- Handshake with the SMTP server
> --
> utl_smtp.helo(l_mail_conn, l_mailhost) ;
> utl_smtp.mail(l_mail_conn, v_sender) ;
> utl_smtp.rcpt(l_mail_conn, v_recipient ) ;
> utl_smtp. open_data(l_mail_con
n) ;
>
>
> --
> -- Write the header
> --
> utl_smtp. write_data(l_mail_co
nn, v_header||crlf);
> utl_smtp. write_data(l_mail_co
nn, crlf||v_message ) ;
> utl_smtp. close_data(l_mail_co
nn) ;
> utl_smtp.quit(l_mail_conn );
> --dbms_output.put_line('Message send to the user successfully') ;
>END;
BChase
bsc7080mqcXX@mylxhhq
.com
(remove XX to contact)

********************
********************
********************
****
Resource Library is now Online @ http://www.MyOraclePortal.com
********************
********************
********************
****

********************
********************
********************
****
15 gigabytes + 330 gigabytes bandwidth for as little as
7.50 a month.

Extra 500 megabytes space when using the below coupon code
http://www.servage.net/?coupon=cust11668
********************
********************
********************
****
Sponsored Links





Also available: Server administration forum archive | Web Design forum archive | Software forum archive | Hardware reviews archive | Programming forum archive

Copyright 2008 droptable.com