Send Mail through database (10g) using UTL_MAIL

from
http://www.psoug.org/reference/utl_mail.html
http://www.e-ammar.com/Oracle_TIPS/oramail.htm
http://www.dizwell.com/prod/node/135

This note shows how to send emails with the new package provided in 10g, UTL_MAIL.

UTL_MAIL is a wrapper, which internally uses UTL_TCP and UTL_SMTP, the "old" packages to send emails.
You can see that, when you receive an errorstack. It contains the functions UTL_TCP and UTL_SMTP. The UTL_MAIL package is much easier to use than the UTL_SMTP package.
The package is loaded by running the following scripts:

    CONN sys/password AS SYSDBA
    @$ORACLE_HOME/rdbms/admin/utlmail.sql
    @$ORACLE_HOME/rdbms/admin/prvtmail.plb

In addition the SMTP_OUT_SERVER parameter must be set to identify the SMTP server:

    CONN sys/password AS SYSDBA
    ALTER SYSTEM SET smtp_out_server = 'smpt.server.com:25' SCOPE=SPFILE;
 
Grants the execute on UTL_MAIL privilege either to PUBLIC or to the user   which will use the package, running one of this statement from SYS:
     GRANT EXECUTE ON UTL_MAIL TO PUBLIC;

With the configuration complete we can now send a mails:

Simple sample to test the SEND procedure:
declare
begin
 utl_mail.send(
   sender => me@domain.com',
   recipients => 'person1@domain.com,person2@domain.com',
   cc         => 'person3@domain.com',
   bcc        => 'myboss@domain.com',
   subject => 'Testing utl_mail',
   message => 'The receipt of this email means'||
              ' that shutting down the database'||
              ' works for UTL_MAIL '
   );
EXCEPTION
   WHEN OTHERS THEN
     raise_application_error(-20001,'The following error has occured: ' || sqlerrm);  
END;
/

In the samples with attachments, the file cannot exceed the 32k size, because  the attachment argument type.

Sample sending emails with attachments.
To run this example the UTL_FILE_DIR database parameter must points to the directory 'D:\DBA\Script',  and there must be a file named Report_DB_Info.txt in that directory.
The attached file can not be
 
CREATE OR REPLACE DIRECTORY UTL_FILE_DIR as 'D:\DBA\Script';

CREATE OR REPLACE PROCEDURE send_email_attach AS
   fHandle utl_file.file_type;
   vTextOut varchar2(32000);
   text varchar2(32000);
BEGIN 
   fHandle := UTL_FILE.FOPEN('UTL_FILE_DIR','Report_DB_Info.txt','r');
   IF UTL_FILE.IS_OPEN(fHandle) THEN
     DBMS_OUTPUT.PUT_LINE('File read open');
   ELSE
     DBMS_OUTPUT.PUT_LINE('File read not open');
   END IF;
 
   loop
     begin
     UTL_FILE.GET_LINE(fHandle,vTextOut);
     text:=text||vTextOut;
     --  dbms_output.put_line(length(text));
     EXCEPTION
       WHEN NO_DATA_FOUND THEN EXIT;
     end;
   END LOOP;    
   --dbms_output.put_line(length(text));
   UTL_FILE.FCLOSE(fHandle);
 
  UTL_MAIL.SEND_ATTACH_VARCHAR2(sender => 'xxx@oracle.com', recipients => 'xxx@oracle.com', subject => 'Testmail', message => 'Hallo', attachment => text, ATT_INLINE => FALSE); 

EXCEPTION
     WHEN OTHERS THEN
     --  dbms_output.put_line('Error');
--    raise_application_error(-20001,'The following error has occured: ' || sqlerrm);  
END;
/


Sample sending emails with RAW attachments.
set serveroutput on;

CREATE OR REPLACE DIRECTORY UTL_FILE_DIR as 'D:\DBA\Script';
grant read on directory UTL_FILE_DIR to public;

DECLARE
  fil               BFILE;
  file_len          PLS_INTEGER;
  MAX_LINE_WIDTH    PLS_INTEGER := 54;
  buf               RAW(2100);
  amt               BINARY_INTEGER := 2000;
  pos               PLS_INTEGER := 1;        /* pointer for each piece */
  filepos           PLS_INTEGER := 1;        /* pointer for the file   */
  filenm            VARCHAR2(50) := 'clouds.jpg'; /* binary file attachment */
  data              RAW(2100);
  chunks            PLS_INTEGER;
  len               PLS_INTEGER;
  modulo            PLS_INTEGER;
  pieces            PLS_INTEGER;
  err_num           NUMBER;
  err_msg           VARCHAR2(100); 
  resultraw         RAW(32000);
  
    BEGIN
        /*  Assign the file a handle   */
        fil := BFILENAME('BFILE_DIR', filenm);

        /*  Get the length of the file in bytes  */
        file_len := dbms_lob.getlength(fil);
       
        /*  Get the remainer when we divide by amt  */
        modulo := mod(file_len, amt);
       
        /*  How many pieces?  */
        pieces := trunc(file_len / amt);
        if (modulo <> 0) then
            pieces := pieces + 1;
        end if;

        /*  Open the file  */
        dbms_lob.fileopen(fil, dbms_lob.file_readonly);
 
        /*  Read the first amt into the buffer  */
        dbms_lob.read(fil, amt, filepos, buf);

        /*  For each piece of the file . . .  */
        FOR i IN 1..pieces LOOP

            /*  Position file pointer for next read */
            filepos := i * amt + 1;

            /*  Calculate remaining file length  */
            file_len := file_len - amt;

            /*  Stick the buffer contents into data  */
            data := utl_raw.concat(data, buf);

            /*  Calculate the number of chunks in this piece  */
            chunks := trunc(utl_raw.length(data) / MAX_LINE_WIDTH);

            /*  Don't want too many chunks  */
            IF (i <> pieces) THEN
                chunks := chunks - 1;
            END IF;
 
            /*  For each chunk in this piece . . .  */
            FOR j IN 0..chunks LOOP

                /*  Position ourselves in this piece */
                pos := j * MAX_LINE_WIDTH + 1;
 
                /*  Is this the last chunk in this piece?  */
                IF (j <> chunks) THEN
                    len := MAX_LINE_WIDTH;
                ELSE
                    len := utl_raw.length(data) - pos + 1;
                    IF (len > MAX_LINE_width) THEN
                        len := MAX_LINE_WIDTH;
                    END IF;
                END IF;
                                /*  If we got something, let's write it  */
                IF (len > 0 ) THEN

                resultraw := resultraw || utl_raw.substr(data, pos, len);
                END IF;
            END LOOP;

            /*  Point at the rest of the data buffer  */
            IF (pos + len <= utl_raw.length(data)) THEN
                data := utl_raw.substr(data, pos + len);
            ELSE
                data := NULL;
            END IF;
 
            /*  We're running out of file, only get the rest of it  */
            if (file_len < amt and file_len > 0) then
                amt := file_len;
            end if;
        
            /*  Read the next amount into the buffer  */
            dbms_lob.read(fil, amt, filepos, buf);

        END LOOP;

    /*  Don't forget to close the file  */
    dbms_lob.fileclose(fil);

    UTL_MAIL.SEND_ATTACH_RAW(sender => 'xxx@oracle.com', recipients => 'xxx@oracle.com', subject => 'Testmail', message => 'Hallo', attachment => resultraw, att_filename => 'clouds.jpg');     
EXCEPTION
   WHEN OTHERS THEN
   --dbms_output.put_line('Error');
   raise_application_error(-20001,'The following error has occured: ' || sqlerrm);  
END;
/






Send Mail through database using 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;
/