SEND MAIL THROUGH DATABASE BY UTL_SMTP

We can send mail thorough database by using the UTL_SMTP package.
To  make it work  check the following configuration before proceeding
Ø                         java option must be installed in the database.
Ø                         TCPconnection class (plsql.jar) must be loaded.
Ø                         Init parameters 
SHARED_POOL_SIZE > = 65M
JAVA_POOL_SIZE >= 50M
50M free in the SYSTEM tablespace
250M of rollback segment space
 
STEP 1. 
Run initjvm.sql through svrmgrl or by connecting as SYS 
ON NT  AT SQL/SVRMGRL    :  $ORACLE_HOME\javavm\install\initjvm.sql
ON UNIX AT SQL/SVRMGRL   : $ORACLE_HOME/javavm/install/initjvm.sql
 
STEP 2 : 
ON NT AT DOS PROMPT :
$ORACLE_HOME\plsql\jlib>loadjava -f -v -r -u sys/**** plsql.jar
ON UNIX AT UNIX PROMPT :
$ORACLE_HOME/plsql/jlib>loadjava -f -v -r -u sys/**** plsql.jar
 
The following output will be expected after running above command.
initialization complete
loading  : oracle/plsql/net/TCPConnection
creating : oracle/plsql/net/TCPConnection
loading  : oracle/plsql/net/InternetAddress
creating : oracle/plsql/net/InternetAddress
loading  : META-INF/MANIFEST.MF
creating : META-INF/MANIFEST.MF
resolver :
resolving: oracle/plsql/net/InternetAddress
resolving: oracle/plsql/net/TCPConnection
 
STEP 3: 
Run initplsj.sql through SVRMGRL or SYS 
ON NT  AT SQL/SVRMGRL    :   @$ORACLE_HOME\rdbms\admin\initplsj.sql
ON UNIX AT SQL/SVRMGRL    :   @$ORACLE_HOME/rdbms/admin/initplsj.sql
 
The following output is expected
Call completed.
Call completed.
Setup has completed . Let us see the code for sending mail.
 
Sample PL/SQL Code to send a mail through Oracle Database.
In the following plsql I am declaring variables for sender address which  is the email address of the person who is sending email , Receiver address which is the email address of the recipient , Email server which is the address of your email exchange server, Port number which is dedicated for email services.  Also you have to declare a variable conn  having a declaration type UTL_SMTP.CONNECTION  which establish a connection with the SMTP server. The other variable are:
 
Ø      UTL_SMTP.HELO  which does handshake with SMTP server.
Ø      UTL_SMTP.MAIL  which contains the mail id of sender  ( FROM).
Ø      UTL_SMTP.RCPT which  contains the mail id of the receiver.
Ø      UTL_SMTP.DATA  which grab the message buffer and send it.
Ø      UTL_SMTP.QUIT which closes the connection.
 
Declare
  SenderAddress   Varchar2(30) := 'sender@whatever.com';
  ReceiverAddress varchar2(30) := 'destination@whatever.com';
  EmailServer     varchar2(30) := 'mail.Test.com';
  Port number     := 25;
  conn UTL_SMTP.CONNECTION;
  crlf VARCHAR2( 2 ):= CHR( 13 ) || CHR( 10 );
  mesg VARCHAR2( 4000 );
  mesg_body varchar2(4000);
BEGIN
  conn:= utl_smtp.open_connection( EmailServer, Port );
  utl_smtp.helo( conn, EmailServer );
  utl_smtp.mail( conn, SenderAddress);
  utl_smtp.rcpt( conn, ReceiverAddress );
  mesg:=
        'Date: '||TO_CHAR( SYSDATE, 'dd Mon yy hh24:mi:ss' )|| crlf ||
        'From:'||SendorAddress|| crlf ||
        'Subject: Mail Through ORACLE Database' || crlf ||
        'To: '||ReceiverAddress || crlf ||   '' || crlf ||
         ' This is Mail from Oracle Database By Using UTL_SMTP Package' || crlf ||
         'It is very easy to configure  Tell me if you face any problems' ;
  utl_smtp.data( conn, mesg );
  utl_smtp.quit( conn );
EXCEPTION
   WHEN UTL_SMTP.INVALID_OPERATION THEN
      dbms_output.put_line(' Invalid Operation in Mail attempt using UTL_SMTP.');
   WHEN UTL_SMTP.TRANSIENT_ERROR THEN
      dbms_output.put_line(' Temporary e-mail issue - try again');
   WHEN UTL_SMTP.PERMANENT_ERROR THEN
      dbms_output.put_line(' Permanent Error Encountered.');
END;
/
Now let's try to send a mail to multiple recipients .
I created a table mailed and insert sender and receivers mail ids
 
SQL>desc mailid
Name              Null?    Type
----------------- -------- ------------
SEND_RECPT                 VARCHAR2(30)
MAILIDS                    VARCHAR2(50)
 
SQL> select * from mailid;
 
SEND_RECPT                       MAILIDS
------------------------------ ----------------------------
SENDER                          swadhwa@Test.com
RECPT                           UnixAdmin@Test.com
RECPT                           DBA@Test.com
RECPT                           DBA2@Test.com
 
 
CREATE or replace PROCEDURE MAILFROMDB ( MESSAGE  IN VARCHAR) AS
  cursor c1 is select send_recpt,mailids
                  from mailid;
  conn UTL_SMTP.CONNECTION;
  crlf VARCHAR2( 2 ):= CHR( 13 ) || CHR( 10 );
  mesg VARCHAR2( 4000 );
  mesg_body varchar2(4000);
  AdminMailid varchar2(30);
BEGIN
 /* Open connection */
  conn:= utl_smtp.open_connection( 'mail.Test.com', 25 );
 
 /* Hand Shake */
  utl_smtp.helo( conn, 'mail.Test.com' );
 
   /* Loop for configure sender and recipient to UTL_SMTP */
  for c1rec in c1 loop
    if c1rec.send_recpt = 'SENDER' then
         utl_smtp.mail( conn,c1rec.mailids);
    else
         utl_smtp.rcpt( conn,c1rec.mailids );
    end if;
  end loop;
 
  /* making a message buffer */
mesg:=
        'Date: '||TO_CHAR( SYSDATE, 'dd Mon yy hh24:mi:ss' )|| crlf ||
         'From: Oracle Database'|| crlf ||
         'Subject: Mail Through ORACLE Database' || crlf ||
         'To: All the Recipients '|| crlf 
          || crlf ||crlf||
         ' This is Mail from Oracle Database By Using UTL_SMTP Package'||crlf||
         'It is very easy to configure  Tell me if you face any problems'||crlf||message ;
 
  /* Configure sending message */
  utl_smtp.data( conn, mesg );
 
  /* closing connection */
  utl_smtp.quit( conn );
END;
/
 
SQL> execute mailfromdb('Send Feedback at myname@hotmail.com');
 
Now send mail to  many people via To ,CC or BCC.
To configure this and to make it more simpler I am created two tables MAILID and MAILID_ORDER.
MAILID  Table will contain the properties of send_recpt as TO,CC or BCC. There would be one property named as SENDER, responsible for sending mail.
MAILID_ORDER Table will contains the corresponding order number of Send_recpt property. This table will contains the distinct properties and order number. Order number is very important here as my procedure will configure recipient according to order number.
 
Configure Mailid and Mailid_order  table as follows :-
select * from mailid order by 1; 
SEND_RECPT                     EMAIL_ADDRESS
------------------------------ -------------------------------------
BCC                            shastrid@Test.com
BCC                            Tony@Test.com
CC                             Rohit@Test.com
CC                             UNIXADMIN@Test.com
CC                             John@Test.com
SENDER                         DBAADMIN@Test.com
TO                             swadhwa@Test.com
TO                             Manager@Test.com 
8 rows selected.
 
select * from mailid_order;
SEND_RECPT             ORDER_NO
-------------------- ----------
SENDER                        0
TO                            1
CC                            2
BCC                           3
 
 
CREATE or replace PROCEDURE MAILFROMDB ( MESSAGE  IN VARCHAR) AS
  cursor c1 is select mailid.send_recpt,email_address,order_no from mailid,mailid_order
               where Mailid.SEND_RECPT = Mailid_order.SEND_RECPT order by order_no;
  conn UTL_SMTP.CONNECTION;
  crlf VARCHAR2( 2 ):= CHR( 13 ) || CHR( 10 );
  mesg VARCHAR2( 4000 );
  mesg_body varchar2(4000);
  AdminMailid varchar2(30);
  vToList varchar2(2000);
  vCcList varchar2(2000);
  vBccList varchar2(2000);
  vSenderEmail varchar2(2000);
BEGIN
 /* Open connection */
  conn:= utl_smtp.open_connection( 'mail.Test.com', 25 );
 /* Hand Shake */
  utl_smtp.helo( conn, 'mail.Test.com' );
   /* Loop for configure sender and recipient to UTL_SMTP */
  for c1rec in c1 loop
    if c1rec.send_recpt = 'SENDER' then
         utl_smtp.mail( conn,c1rec.mailids);
         vSenderEmail := c1rec.mailids;
    else 
         utl_smtp.rcpt( conn,c1rec.mailids );
    end if;
   /* Making a TO list */
       if upper(c1rec.send_recpt) = 'TO' then
          vTolist := vToList || c1rec.mailids||';';
       end if;
   /* Making a CC list */
      if upper(c1rec.send_recpt) = 'CC' then
          vCclist := vCcList || c1rec.mailids||';';
       end if;
   /* Making a BCC list */ 
      if upper(c1rec.send_recpt) = 'BCC' then
          vBcclist := vBccList || c1rec.mailids||';';
       end if; 
  end loop;
  /* making a message buffer */
mesg:=
      'Date: '||TO_CHAR( SYSDATE, 'dd Mon yy hh24:mi:ss' )|| crlf ||
      'From: '||vSenderEmail|| crlf ||
      'Subject: Mail Through ORACLE Database' || crlf ||
      'To: '||VToList|| crlf|| 
      'Cc: '||VCcList||crlf ||
      'Bcc: '||VBccList||crlf||crlf||
      ' This is Mail from Oracle Database By Using UTL_SMTP Package'||crlf||
      'It is very easy to configure  Tell me if you face any problems'||crlf||message ;
  /* Configure sending message */
  utl_smtp.data( conn, mesg );
  /* closing connection */
  utl_smtp.quit( conn );
END;
/
If the sender email is configured in Microsoft exchange server. Then you will also get a failure notification in case of invalid email addresses.
If you want to send mail with attachment , you can send it by using java procedure . There is no method available as yet for it  by UTL_SMTP package. Refer to DOC 120994.1 on metalink.
 
Tip : How to Send Email through Oracle Database in HTML format
 Here is a  simple PLSQL to send email from Oracle Database (8.x) in HTML format.

Declare
  /* Address of the person who is sending Email */
  SendorAddress  Varchar2(50)  := 'sender@whatever.com'; 

  /* Address of the person who is receiving Email */
  ReceiverAddress varchar2(50) := 'Receiver@whatever.com;

 /* Address of your Email Server Configured for sending emails */
  EmailServer     varchar2(30) := 'server';

  /*  Port Number responsible for sending email */
  Port number  := 25;

  /* UTL_SMTP package establish a connection with the SMTP server */
  conn UTL_SMTP.CONNECTION;

  /* crlf  used  for carriage return */
  crlf VARCHAR2( 2 ):= CHR( 13 ) || CHR( 10 );

  /*  Variable for storing message contents */
  mesg VARCHAR2( 4000 );

  /* Variable for storing HTML code */
  mesg_body varchar2(4000)
   := '    <html>
            <head>
            <title>Oracle Techniques By Diego Pafumi</title>
            </head>
            <body bgcolor="#FFFFFF" link="#000080">
            <table cellspacing="0" cellpadding="0" width="100%">
            <tr align="LEFT" valign="BASELINE">
            <td width="100%" valign="middle"><h1><font color="#00008B"><b>Send Mail in HTML Format</b></font></h1>
             </td>
         </table>
          <ul>
           <li><b><a href="members.fortunecity.com/dpafumi/oracle.htm">Oracle Techniques is for DBAs </li>
           <l><b>                                     by Diego Pafumi </b> </l>             
              </ul>
             </body>
             </html>';
BEGIN
  /* Open Connection */
  conn:= utl_smtp.open_connection( EmailServer, Port );  

  /* Hand Shake */
  utl_smtp.helo( conn, EmailServer );

  /* Configure Sender and Recipient  with UTL_SMTP */
  utl_smtp.mail( conn, SendorAddress);
  utl_smtp.rcpt( conn, ReceiverAddress );

  /* Making Message buffer */
  mesg:=
        'Date: '||TO_CHAR( SYSDATE, 'dd Mon yy hh24:mi:ss' )|| crlf ||
         'From:'||SendorAddress|| crlf ||
         'Subject: Mail Through ORACLE Database' || crlf ||
         'To: '||ReceiverAddress || crlf ||
         '' || crlf ||mesg_body||'';
 
  /* Configure Sending Message */
  /*You need to put 'MIME-Verion: 1.0' (this is case-sensitive!) */
  /*Content-Type-Encoding is actually Content-Transfer-Encoding. */
  /*The MIME-Version, Content-Type, Content-Transfer-Encoding should */
  /* be the first 3 data items in your message */
  utl_smtp.data(conn, 'MIME-Version: 1.0' ||CHR(13)|| CHR(10)||'Content-type: text/html' || CHR(13)||CHR(10)||mesg);   

  /* Closing Connection */
  utl_smtp.quit( conn );
END;
/