Re: send email through pl/sql ???

From: Eca Eca <ecaeser_at_hotmail.com>
Date: Wed, 18 Jul 2001 04:25:33 -0700
Message-ID: <F001.0034D1FA.20010718041521@fatcity.com>

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
eca_at_widesoft.com.br
Brazil

>From: Janet Linsy <janetlinsy_at_yahoo.com>
>Reply-To: ORACLE-L_at_fatcity.com
>To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
>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: janetlinsy_at_yahoo.com
>
>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: ListGuru_at_fatcity.com (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: ecaeser_at_hotmail.com

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: ListGuru_at_fatcity.com (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).

Received on Wed Jul 18 2001 - 06:25:33 CDT