send email from stored procedure

Question

i need to send email via oracle database pl/sql, the stored procedure must including authentication (username/password)

Answer 1

Stored Procedure for writing email.

 CREATE OR REPLACE PROCEDURE send_mail
        (  sender     IN VARCHAR2,
           recipient  IN VARCHAR2,
           subject    IN VARCHAR2,
           message    IN VARCHAR2)
        IS
          mailhost     VARCHAR2(30) := 
'YOUR.SMTP.SERVER';
          mail_conn    utl_smtp.connection; 

         crlf VARCHAR2( 2 ):= CHR( 13 ) 
|| CHR( 10 );
         mesg VARCHAR2( 1000 );

        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;

And call your stored procedure as below:

 begin

 send_mail('sender e-mail','dest e-mail','here 
goes subject','here goes 
message');

end;