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

http://blog.sqlauthority.com/2013/01/16/sql-server-send-email-from-sql-database-configure-database-mail-sql-in-sixty-seconds-039-video/

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;


While Shirish's solution looks ok (I didn't test it), consider the implications of taking non-transactional actions (sending email) during the course of a transaction.  What happens if you make update 1, update 2, then send a mail, then update 3, and then rollback?  The 3 updates are rolled back, but you can't recall that email.  It's gone, sent, in the recipient's mailbox, even if the corresponding transaction was rolled back.  Just something to think about....

there is no authentication to use user and password,still missing
