Hi Janet :
Try to use this code.
It works only in 8.1 above version of database.
create or replace PROCEDURE send_mail (sender IN VARCHAR2,
recipient IN VARCHAR2,
message IN VARCHAR2)
IS
mailhost VARCHAR2(30) := 'internet';
smtp_error EXCEPTION;
mail_conn utl_tcp.connection;
PROCEDURE smtp_command(command IN VARCHAR2,
ok IN VARCHAR2 DEFAULT '250')
IS
response varchar2(3);
lixo pls_integer;
BEGIN
lixo := utl_tcp.write_line(mail_conn, command);
response := substr(utl_tcp.get_line(mail_conn), 1, 3);
IF (response <> ok) THEN
RAISE smtp_error;
END IF;
END;
BEGIN
mail_conn := utl_tcp.open_connection(mailhost, 25);
smtp_command('HELO ' || mailhost);
smtp_command('MAIL FROM: ' || sender);
smtp_command('RCPT TO: ' || recipient);
smtp_command('DATA', '354');
smtp_command(message);
smtp_command('QUIT', '221');
utl_tcp.close_connection(mail_conn);
EXCEPTION
WHEN OTHERS THEN
null;
END;
create or replace PROCEDURE PR_ENVIA_EMAIL
(sender IN VARCHAR2,
recipient IN VARCHAR2,
subj IN VARCHAR2,
body IN VARCHAR2)
as
crlf VARCHAR2(2):= CHR( 13 ) || CHR( 10 );
mesg VARCHAR2(32000);
mail_conn UTL_SMTP.CONNECTION;
cc_recipient VARCHAR2(50) default '<email>';
bcc_recipient VARCHAR2(50) default '<email>';
BEGIN
mail_conn := utl_smtp.open_connection('<servidor email>', 25);
utl_smtp.helo(mail_conn, 'mailhost');
utl_smtp.mail(mail_conn, sender);
utl_smtp.rcpt(mail_conn, recipient);
utl_smtp.rcpt(mail_conn, cc_recipient);
utl_smtp.rcpt(mail_conn, bcc_recipient);
mesg:= 'Date: ' || TO_CHAR( SYSDATE, 'dd Mon yy hh24:mi:ss' ) || crlf ||
'From: ' || sender || crlf ||
'To: ' || recipient || crlf ||
'Cc: ' || cc_recipient || crlf ||
'Bcc: ' || bcc_recipient || crlf ||
'Reply-to: '<email>, <email>' || crlf ||
'X-Sent-by: Widesoft Widelog - 1 ' || crlf ||
'X-Event: Deu pau no procedimento W_supxxx1 ' || crlf ||
'Subject: ' || subj || crlf;
mesg:= mesg || '' || crlf || body;
utl_smtp.data(mail_conn, mesg);
utl_smtp.quit(mail_conn);
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line(sqlerrm);
END;
exec PR_ENVIA_EMAIL ('<email>','<email>','Test Message - ','Dear friend :
' || chr(13) || chr(10) || 'It is a very good resource' );
Eriovaldo
[EMAIL PROTECTED]
Brazil
>From: Janet Linsy <[EMAIL PROTECTED]>
>Reply-To: [EMAIL PROTECTED]
>To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
>Subject: send email through pl/sql ???
>Date: Tue, 17 Jul 2001 20:45:23 -0800
>
>Hi all,
>
>Does anybody know how to send email through pl/sql?
>thank you.
>
>Janet
>
>__________________________________________________
>Do You Yahoo!?
>Get personalized email addresses from Yahoo! Mail
>http://personal.mail.yahoo.com/
>--
>Please see the official ORACLE-L FAQ: http://www.orafaq.com
>--
>Author: Janet Linsy
> INET: [EMAIL PROTECTED]
>
>Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
>San Diego, California -- Public Internet access / Mailing Lists
>--------------------------------------------------------------------
>To REMOVE yourself from this mailing list, send an E-Mail message
>to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
>the message BODY, include a line containing: UNSUB ORACLE-L
>(or the name of mailing list you want to be removed from). You may
>also send the HELP command for other information (like subscribing).
_________________________________________________________________________
Get Your Private, Free E-mail from MSN Hotmail at http://www.hotmail.com.
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Eca Eca
INET: [EMAIL PROTECTED]
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
San Diego, California -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
