OraFAQ Forum: SQL & PL/SQL » E-Mail sending issue from Oracle Database

Home » SQL & PL/SQL » SQL & PL/SQL » E-Mail sending issue from Oracle Database (Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

E-Mail sending issue from Oracle Database [message #629612]

Fri, 12 December 2014 06:12

Manoj.Gupta.91
Messages: 239
Registered: March 2008
Location: Delhi

Senior Member

Hi All,

Purpose :- To send mail from Oracle database with different type of attachments. (1) Text File. (2) Excel File. (3) PDF.

Achieved Till Now :- I'm successfully able to send mail with message body and multiple Text File attachments.

Issue Facing :- (1) I'm not able to send mail to multiple recipient.
(2) I'm not able to send mail with PDF and Excel file as attachment. Even Single attachment.

Below is the code written by me for the same. To make this code run in your environment search for --Need To Change. text and replace it with appropriate values.

Please help me to resolve above two issues.

CREATE DIRECTORY TEST_MAIL_DIR AS '/home/oracle' ;             --Need To Change.
GRANT EXECUTE, READ, WRITE ON TEST_MAIL_DIR TO USER_NAME ;     --Need To Change.

CREATE OR REPLACE TYPE TYP_MAIL_ATTACHMENTS  AS OBJECT 
( 
     ATTACHMENT_TYPE         VARCHAR2(50)
    ,ATTACHMENT_PATH         VARCHAR2(500)
    ,ATTACHMENT_NAME         VARCHAR2(500)
) ;


CREATE OR REPLACE TYPE TYP_TBL_MAIL_ATTACHMENTS IS TABLE OF TYP_MAIL_ATTACHMENTS ;


BEGIN
  DBMS_NETWORK_ACL_ADMIN.create_acl 
  (
      acl          => 'open_acl_file.xml', 
      description  => 'A test of the ACL functionality',
      principal    => 'USER_NAME',        --Database User Name who will send mails.      --Need To Change.
      is_grant     => TRUE, 
      privilege    => 'connect',
      start_date   => SYSTIMESTAMP,
      end_date     => NULL
  ) ;

  DBMS_NETWORK_ACL_ADMIN.assign_acl 
  (
      acl         => 'open_acl_file.xml',
      host        => 'SMTP_Server.org',    --Exchange Server Address.       --Need To Change.
      lower_port  => NULL,
      upper_port  => NULL
  ) ; 

  COMMIT ;
END ;

begin
DBMS_NETWORK_ACL_ADMIN.drop_acl ( 
    acl         => 'open_acl_file.xml');
end;



create or replace 
PACKAGE pkg_Send_Mail
AS
    --****************************************************************************************************************
    --Object Name :-        pkg_Send_Mail
    --Created By  :-        Manoj Kumar
    --Created On  :-        11-December-2014
     --Version    :-        1.0
    --Purpose     :-        Standard package to send mail with different types of attachments.
    --                      (1) TEXT
    --                      (2) PDF
    --                      (3) EXCEL
    --****************************************************************************************************************
    -- -: MODIFICATION HISTORY :-
    ------------------------------------------------------------------------------------------------------------------
    --  Date              Name                     Purpose
    --****************************************************************************************************************
    --  11-Dec-2014      Manoj Kumar               Creation of package to send mail with different types of attachments.
    --
    --****************************************************************************************************************

    TYPE gr_Mail_Attachments IS RECORD
    (
        ls_Attach_Type      VARCHAR2(50),       --TEXT, PDF, EXCEL
        ls_Attach_Path      VARCHAR2(1000),
        ls_Attach_Name      VARCHAR2(500)
    ) ;

    TYPE gtt_Mail_Attachments IS TABLE OF gr_Mail_Attachments INDEX BY BINARY_INTEGER ;


   --****************************************************************************************************************
   --Object Name :-        Prc_Send_Mail
   --Created By  :-        Manoj Kumar
   --Created On  :-        11-December-2014
   --Version     :-        1.0
   --Purpose     :-        Standard procedure used to send mail.
   --****************************************************************************************************************

    PROCEDURE Prc_Send_Mail
    (
         p_in_Attachments    IN gtt_Mail_Attachments
        ,p_in_from           IN VARCHAR2    DEFAULT 'manoj.gupta.91@gmail.com'
        ,p_in_to             IN VARCHAR2    DEFAULT 'manoj.gupta.91@gmail.com'
        ,p_in_cc             IN VARCHAR2    DEFAULT 'manoj.gupta.91@gmail.com'
        ,p_in_bcc            IN VARCHAR2    DEFAULT 'manoj.gupta.91@gmail.com'
        ,p_in_subject        IN VARCHAR2    DEFAULT 'This is a test mail' 
        ,p_in_text_message   IN VARCHAR2    DEFAULT 'This is the mail body part for test mail'        --Multi Line Message limited to 32767 Characters.
    ) ;

END pkg_Send_Mail ;
/


create or replace 
PACKAGE BODY pkg_Send_Mail
AS
    --****************************************************************************************************************
    --Object Name :-        pkg_Send_Mail
    --Created By  :-        Manoj Kumar
    --Created On  :-        11-December-2014
     --Version    :-        1.0
    --Purpose     :-        Standard package to send mail with different types of attachments.
    --                      (1) TEXT
    --                      (2) PDF
    --                      (3) EXCEL
    --****************************************************************************************************************
    -- -: MODIFICATION HISTORY :-
    ------------------------------------------------------------------------------------------------------------------
    --  Date              Name                     Purpose
    --****************************************************************************************************************
    --  11-Dec-2014      Manoj Kumar               Creation of package to send mail with different types of attachments.
    --
    --****************************************************************************************************************


    PROCEDURE Prc_Send_Mail
    (
         p_in_Attachments    IN gtt_Mail_Attachments
        ,p_in_from           IN VARCHAR2    DEFAULT 'manoj.gupta.91@gmail.com'
        ,p_in_to             IN VARCHAR2    DEFAULT 'manoj.gupta.91@gmail.com'
        ,p_in_cc             IN VARCHAR2    DEFAULT 'manoj.gupta.91@gmail.com'
        ,p_in_bcc            IN VARCHAR2    DEFAULT 'manoj.gupta.91@gmail.com'
        ,p_in_subject        IN VARCHAR2    DEFAULT 'This is a test mail' 
        ,p_in_text_message   IN VARCHAR2    DEFAULT 'This is the mail body part for test mail'        --Multi Line Message limited to 32767 Characters.
    )
    AS 
        l_Mail_Connection         UTL_SMTP.Connection ;     --Connection Variable.
        lc_Mime_Boundary          CONSTANT VARCHAR2( 256 ) := '-----AABCDEFBBCCC0123456789DE' ;

        p_SMTP_Server             VARCHAR2( 30 ) := 'SMTP_Server.org' ;        --Need To Change.
        lc_Step                   CONSTANT PLS_INTEGER  := 12000 ;

        lfh_File_Handler          UTL_FILE.File_Type ;
        ls_File_Record_Buf        VARCHAR2(32767) := NULL ;
        lcv_Amnt_Bytes            CONSTANT PLS_INTEGER := 32767 ;
        ln_Attachment_Index       NUMBER(5) := 1 ;

        PROCEDURE Prc_Attach_BLOB_File
        (
               p_in_Attach_Name        VARCHAR2
              ,p_in_Attach_Path        VARCHAR2
        )
        IS
            l_Source_File       BFILE ;
            ln_Length           NUMBER(10) ;
            ln_Buffer_Size      INTEGER := 57 ;
            lv_Raw              RAW(57) ;
            I                   INTEGER := 1 ;
        BEGIN

            UTL_SMTP.Write_Data( l_Mail_Connection, '--' || lc_Mime_Boundary || UTL_TCP.Crlf ) ;
            UTL_SMTP.Write_Data( l_Mail_Connection, 'Content-Type: ' || p_in_Attach_Path || '; name="' || p_in_Attach_Name || '"' || UTL_TCP.Crlf ) ;
            UTL_SMTP.Write_Data( l_Mail_Connection, 'Content-Transfer-Encoding: BINARY' || UTL_TCP.Crlf ) ;
            UTL_SMTP.Write_Data( l_Mail_Connection, 'Content-Disposition: attachment; filename="' || p_in_Attach_Name || '"' || UTL_TCP.Crlf || UTL_TCP.Crlf ) ;

            l_Source_File := BFileName( 'TEST_MAIL_DIR', p_in_Attach_Name ) ; 

            DBMS_LOB.FileOpen( l_Source_File, DBMS_LOB.File_ReadOnly ) ;
            ln_Length := DBMS_LOB.GetLength( l_Source_File ) ;

            WHILE I < ln_Length LOOP
                DBMS_LOB.READ( l_Source_File, ln_Buffer_Size, I, lv_Raw ) ;
                UTL_SMTP.Write_RAW_Data( l_Mail_Connection, UTL_ENCODE.Base64_Encode( lv_Raw ) ) ;
                --UTL_SMTP.Write_RAW_Data( l_Mail_Connection, UTL_ENCODE.BASE64_ENCODE(UTL_TCP.Crlf) ) ;
                I := I + ln_Buffer_Size ;
            END LOOP ;

            UTL_SMTP.Write_Data( l_Mail_Connection, UTL_TCP.Crlf || UTL_TCP.Crlf ) ;

            DBMS_LOB.Close( l_Source_File ) ;
        EXCEPTION
            WHEN OTHERS THEN
                IF DBMS_LOB.FileIsOpen( l_Source_File ) = 1 THEN
                    DBMS_LOB.Close( l_Source_File ) ;
                END IF ;

                DBMS_OUTPUT.put_line( 'Error : 1' ) ;
                DBMS_OUTPUT.put_line ( DBMS_UTILITY.Format_Error_Backtrace ) ;
                DBMS_OUTPUT.put_line ( DBMS_UTILITY.Format_Error_Stack ) ;
                RAISE ;
        END Prc_Attach_BLOB_File ;

        PROCEDURE Prc_Attach_Text_File
        (
               p_in_Attach_Name        VARCHAR2
              ,p_in_Attach_Path        VARCHAR2
        )
        IS
        BEGIN
            UTL_SMTP.Write_Data( l_Mail_Connection, '--' || lc_Mime_Boundary || UTL_TCP.Crlf ) ;
            UTL_SMTP.Write_Data( l_Mail_Connection, 'Content-Type: ' || p_in_Attach_Path || '; name="' || p_in_Attach_Name || '"' || UTL_TCP.Crlf ) ;
            UTL_SMTP.Write_Data( l_Mail_Connection, 'Content-Transfer-Encoding: BINARY' || UTL_TCP.Crlf ) ;
            UTL_SMTP.Write_Data( l_Mail_Connection, 'Content-Disposition: attachment; filename="' || p_in_Attach_Name || '"' || UTL_TCP.Crlf || UTL_TCP.Crlf ) ;

            IF UTL_FILE.Is_Open( lfh_File_Handler ) THEN
                 UTL_FILE.FClose ( lfh_File_Handler ) ;
            END IF ;

            lfh_File_Handler := UTL_FILE.FOpen( 'TEST_MAIL_DIR', p_in_Attach_Name, 'R' ) ;     --Open Text File for reading.

            LOOP
                BEGIN
                    UTL_FILE.Get_Line( lfh_File_Handler, ls_File_Record_Buf, lcv_Amnt_Bytes ) ;

                    UTL_SMTP.Write_Data( l_Mail_Connection, ls_File_Record_Buf || UTL_TCP.Crlf ) ;
                EXCEPTION 
                    WHEN No_Data_Found THEN
                        --End of File has reached there is No More Data to be read.
                        EXIT ; 
                END ;
            END LOOP ;

            IF UTL_FILE.Is_Open( lfh_File_Handler ) THEN
                 UTL_FILE.FClose ( lfh_File_Handler ) ;
            END IF ;

            UTL_SMTP.Write_Data( l_Mail_Connection, UTL_TCP.Crlf || UTL_TCP.Crlf ) ;
        EXCEPTION
            WHEN OTHERS THEN
                DBMS_OUTPUT.put_line( 'Error : 1' ) ;
                DBMS_OUTPUT.put_line ( DBMS_UTILITY.Format_Error_Backtrace ) ;
                DBMS_OUTPUT.put_line ( DBMS_UTILITY.Format_Error_Stack ) ;
                RAISE ;
        END Prc_Attach_Text_File ;

    BEGIN

        l_Mail_Connection := UTL_SMTP.Open_Connection( p_SMTP_Server ) ;     --Opens a connection to an SMTP server.
        UTL_SMTP.Helo( l_Mail_Connection, 'SMTP_Server.org' ) ;                     --Perform initial handshaking with SMTP server after connecting.   --Need To Change.
        --l_Mail_Connection := UTL_SMTP.Open_Connection( p_SMTP_Host, NVL(p_SMTP_Port, 25) ) ;
        UTL_SMTP.Mail( l_Mail_Connection, p_in_From ) ;         --Initiates a mail transaction with the server, the destination is a mailbox.
        UTL_SMTP.Rcpt( l_Mail_Connection, p_in_To ) ;           --Specifies the recipient of an e-mail message.

        --Data Portion Starts.
        UTL_SMTP.Open_Data( l_Mail_Connection ) ;                            --Sends the DATA command.
        UTL_SMTP.Write_Data( l_Mail_Connection, 'Date: ' || TO_CHAR(SYSDATE, 'DD-MON-RRRR HH24:MI:SS') || UTL_TCP.Crlf ) ;         --Writes a portion of the e-mail message.
        UTL_SMTP.Write_Data( l_Mail_Connection, 'From: ' || p_in_From || UTL_TCP.Crlf ) ;
        UTL_SMTP.Write_Data( l_Mail_Connection, 'To: ' || p_in_To || UTL_TCP.Crlf ) ;
        UTL_SMTP.Write_Data( l_Mail_Connection, 'cc: ' || p_in_CC || UTL_TCP.Crlf ) ;
        UTL_SMTP.Write_Data( l_Mail_Connection, 'bcc: ' || p_in_BCC || UTL_TCP.Crlf ) ;
        UTL_SMTP.Write_Data( l_Mail_Connection, 'Subject: ' || p_in_Subject || UTL_TCP.Crlf ) ;
        UTL_SMTP.Write_Data( l_Mail_Connection, 'Reply-To: ' || p_in_From || UTL_TCP.Crlf ) ;
        UTL_SMTP.Write_Data( l_Mail_Connection, 'MIME-Version: 1.0' || UTL_TCP.Crlf ) ;
        UTL_SMTP.Write_Data( l_Mail_Connection, 'Content-Type: multipart/mixed; boundary="' || lc_Mime_Boundary || '"' || UTL_TCP.Crlf || UTL_TCP.Crlf ) ;
 
        IF p_in_Text_Message IS NOT NULL THEN
            UTL_SMTP.Write_Data( l_Mail_Connection, '--' || lc_Mime_Boundary || UTL_TCP.Crlf ) ;
            UTL_SMTP.Write_Data( l_Mail_Connection, 'Content-Type: text/plain; charset="iso-8859-1"' || UTL_TCP.Crlf || UTL_TCP.Crlf ) ;

            UTL_SMTP.Write_Data( l_Mail_Connection, p_in_Text_Message || UTL_TCP.Crlf || UTL_TCP.Crlf ) ;
        END IF ;

        <<Send_Text_Mail>>
        BEGIN
            FOR I IN p_in_Attachments.FIRST..p_in_Attachments.LAST
            LOOP

                IF p_in_Attachments.EXISTS(I) THEN
                    --Code for Text File Attachment.
                    IF p_in_Attachments(I).ls_Attach_Type = 'TEXT' THEN
                        Prc_Attach_Text_File( p_in_Attachments(I).ls_Attach_Name, p_in_Attachments(I).ls_Attach_Path ) ;
                    ELSIF p_in_Attachments(I).ls_Attach_Type = 'PDF' THEN
                        Prc_Attach_BLOB_File( p_in_Attachments(I).ls_Attach_Name, p_in_Attachments(I).ls_Attach_Path ) ;
                    ELSIF p_in_Attachments(I).ls_Attach_Type = 'EXCEL' THEN
                        Prc_Attach_BLOB_File( p_in_Attachments(I).ls_Attach_Name, p_in_Attachments(I).ls_Attach_Path ) ;
                    END IF ;
                END IF ;
            END LOOP ;
        END Send_Text_Mail ;

        UTL_SMTP.Write_Data( l_Mail_Connection, UTL_TCP.Crlf || UTL_TCP.Crlf ) ;
        UTL_SMTP.Write_Data( l_Mail_Connection, '--' || lc_Mime_Boundary || '--' || UTL_TCP.Crlf ) ;
        UTL_SMTP.Close_Data( l_Mail_Connection ) ;         --Closes the data session.

        UTL_SMTP.Quit( l_Mail_Connection ) ;               --Terminates an SMTP session and disconnects from the server

    EXCEPTION
        WHEN UTL_SMTP.Transient_Error OR UTL_SMTP.Permanent_Error THEN
            DBMS_OUTPUT.put_line( 'Error : 1' ) ;
            BEGIN
                UTL_SMTP.QUIT( l_Mail_Connection ) ;
                RAISE ;
            EXCEPTION
                WHEN UTL_SMTP.Transient_Error OR UTL_SMTP.Permanent_Error THEN
                    DBMS_OUTPUT.put_line( 'Error : 2' ) ;
                    NULL ; -- When the SMTP server is down or unavailable, we don't have
                           -- a connection to the server. The QUIT call will raise an exception that we can ignore.
                WHEN OTHERS THEN
                    DBMS_OUTPUT.put_line( 'Error : 3' ) ;
                    DBMS_OUTPUT.put_line ( DBMS_UTILITY.Format_Error_Backtrace ) ;
                    DBMS_OUTPUT.put_line ( DBMS_UTILITY.Format_Error_Stack ) ;
                    RAISE ;
            END ;

            UTL_SMTP.Quit( l_Mail_Connection ) ;
            RAISE_APPLICATION_ERROR( -20001, 'Failed to send mail due to the following error: ' || SQLERRM ) ;

        WHEN OTHERS THEN
            DBMS_OUTPUT.put_line( 'Error : 4' ) ;
            DBMS_OUTPUT.put_line ( DBMS_UTILITY.Format_Error_Backtrace ) ;
            DBMS_OUTPUT.put_line ( DBMS_UTILITY.Format_Error_Stack ) ;
            UTL_SMTP.Quit( l_Mail_Connection ) ;
            RAISE ;
    END Prc_Send_Mail ;

END pkg_Send_Mail ;
/

Execution Script

--Need To Change.
DECLARE
  A  pkg_Send_Mail.gtt_Mail_Attachments  ;
BEGIN

  A(1).ls_Attach_Type := 'PDF' ;
  A(1).ls_Attach_Path := '/home/oracle' ;
  A(1).ls_Attach_Name := 'manojpdf1.pdf' ;

/*
  A(1).ls_Attach_Type := 'EXCEL' ;
  A(1).ls_Attach_Path := '/home/oracle' ;
  A(1).ls_Attach_Name := 'manoj1.xlsx' ;
  */
/*
  A(1).ls_Attach_Type := 'TEXT' ;
  A(1).ls_Attach_Path := '/home/oracle' ;
  A(1).ls_Attach_Name := 'manoj2.txt' ;
*/
  pkg_Send_Mail.PRC_SEND_MAIL( a ) ;
end ;

Thanks & Regards
Manoj