How to send an E-Mail from 10g Oracle Database

email from plsql

Just follow the below mentioned steps to send an e-mail from Oracle 10g database using PL/SQL.

Step:1    First install SMTP Service in Server as per steps given below.

Go to Control Panel->  Add or Remove Programs-> Click on Add/Remove Windows Components

Select Internet Information Service (IIS) option and click on Details button to check whether SMTP Service is installed or not. If not installed then select SMTP check box.Note: This process should be done on server machine.


Step:2    To enable e-mailing from oracle Database, install UTL_MAIL also.

Follow below command in the sequence as prescribed.


C:>sqlplus /nolog
SQL*Plus: Release 10.2.0.1.0 - Production on Thu Oct 4 22:49:45 2012
Copyright (c) 1982, 2005, Oracle.  All rights reserved.SQL>

SQL> connect sys/password as sysdba

Connected. 

SQL> @$ORACLE_HOME/rdbms/admin/utlmail.sql 

Package created. 

Synonym created. 

SQL> @$ORACLE_HOME /rdbms/admin/prvtmail.plb 

Package body created. 

No errors.  

Now configure SMTP_OUT_SERVER parameter by alter connect. To do this first connect to SYS user and fire below command to configure SMTP_OUT_SERVER. 

SQL> alter system set smtp_out_server = '<ip_address:port>' scope=Both; 

System altered.

25 = Default SMTP Port

eg: alter system set smtp_out_server = ’192.168.0.1:25′ scope=both;

Now database is configured.

Now create a procedure to send an e-mail from oracle 10g database.

CREATE OR REPLACE PROCEDURE SENDMAIL ( 
    p_sender      INVARCHAR2,
    p_recipient   IN VARCHAR2, 
    p_subject   IN VARCHAR2,
    p_message     INVARCHAR2    
) 
AS
 l_mailhost VARCHAR2 (255) := 'Domain.MAIL.com';
 l_mail_conn UTL_SMTP.connection;
    PROCEDURE SEND_HEADER(NAME IN VARCHAR2, HEADER IN VARCHAR2) AS
        BEGIN
    UTL_SMTP.WRITE_DATA(l_mail_conn, NAME || ': ' || HEADER || UTL_TCP.CRLF);
    END;            

    BEGIN

     --make connection to smtp

    l_mail_conn := UTL_SMTP.open_connection (l_mailhost, 25);

     --    For Database 10g     

    utl_smtp.HELO(l_mail_conn, l_mailhost);

      ---Authentication

    utl_smtp.command( l_mail_conn, 'AUTH LOGIN');
    utl_smtp.command( l_mail_conn, utl_raw.cast_to_varchar2( utl_encode.base64_encode( utl_raw.cast_to_raw( 'USERNAME' ))) );
    utl_smtp.command( l_mail_conn, utl_raw.cast_to_varchar2( utl_encode.base64_encode( utl_raw.cast_to_raw( 'PASSWORD' ))) );    

    ---Sender Address    

    UTL_SMTP.mail (l_mail_conn, p_sender);    

    ---Recipient Address    

    UTL_SMTP.rcpt (l_mail_conn, p_recipient);    

        --start the mail body    

    UTL_SMTP.open_data (l_mail_conn);
    SEND_HEADER('FROM',    p_sender);

        SEND_HEADER('TO',      p_recipient);      
        SEND_HEADER('SUBJECT', p_subject);
        SEND_HEADER('DATE', SYSDATE);       

    utl_smtp.write_data(l_mail_conn, 'Content-Type: text/html'  || utl_tcp.crlf);
    UTL_SMTP.write_data (l_mail_conn, UTL_TCP.CRLF || p_message);
    UTL_SMTP.close_data (l_mail_conn);
    UTL_SMTP.quit (l_mail_conn);    

    -- Exception

   EXCEPTION
       WHEN UTL_SMTP.TRANSIENT_ERROR OR UTL_SMTP.PERMANENT_ERROR THEN
                UTL_SMTP.QUIT(l_mail_conn);    
END SENDMAIL;
/

Now execute the procedure to send e-mail from Oracle 10g database.

Exec SENDMAIL (‘username@test.com’,’username2@test.com’,’Hai Jack’,’This is database generated mail from my side.’);

Procedure Executed

Now check your email box.

SiteGround Black Friday Sale Below Banner

Get Free Blogging Tips & Technology updates in your Email !!!

Subscribe via RSS feed

The following two tabs change content below.
Anil Kumar
IT Blogger Tips focus on Blogging Tips, SEO Tips, Social Media, SQL Tips, PL/SQL Tips, Oracle DBA, Linux/Unix, Latest Technology, How Tos and Technical Solutions. You can find us on Facebook | Twitter |
Find on Google+

  • Anonymous

    Thanks…

  • Ajay

    Hi,

    Thanks for the information provide

    I followed the steps mentioned in the blog, procedure was successfull but mail was not sent. Can you please help me on this.

    Thanks,
    Ajay

    • Hi Ajay,

      The process has been checked and working properly but i faced one issue that was the same as you mentioned.
      I talked to my IT department and they told me we will have to give permission to send emails to particular PC or Server.
      After the rights my e-mail got started.

      You can check the same thing at your end too.

Siteground SALE
70% OFF SiteGround WordPress Hosting – $2.95/month (Exclusive Offer !!!)