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

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 (‘[email protected]’,’[email protected]’,’Hai Jack’,’This is database generated mail from my side.’);

Procedure Executed

Now check your email box.