Send Mail Through Database by UTL_SMTP  

 

 

 

                                                   SAMEER  WADHWA

                                                   SamWad@Msn.com

 

 

 

SEND MAIL THROUGH DATABASE BY UTL_SMTP

 

This is a feature of Oracle 8i (8.1.6 release 2) . We can send mail thorough database by using 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

  SendorAddress  Varchar2(30)  := 'swadhwa@Test.com';

  ReceiverAddress varchar2(30) := 'DBA@Test.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, SendorAddress);

  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 );

END;

/

 

 

 

Now let us 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                           MANAGER@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 Wadhwa_s@hotmail.com');

 

PL/SQL procedure successfully completed.

 

 

                    

                          

          

 

 

 

 

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 :-

 

 

ora816 SamSQL :> 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.

 

ora816 SamSQL :> 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.

 

 

 

 

 

 

 

 

 

                                

 

     Conclusion :-  You observed that how powerful this package is  and how easily we can configure  it.

 

 

     Thanks for reading

     Sameer Wadhwa

     Wadhwa_S@Hotmail.com

     SamWad@MSN.com

 

 

 

 

Copyrightã   Oracle Techniques  Sameer Wadhwa  ( All rights reserved  )