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)
E-Mail sending issue from Oracle Database [message #629612] Fri, 12 December 2014 06:12 Go to next message
Manoj.Gupta.91
Messages: 237
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
Re: E-Mail sending issue from Oracle Database [message #629613 is a reply to message #629612] Fri, 12 December 2014 06:32 Go to previous messageGo to next message
ThomasG
Messages: 3211
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
One thing that cought my eye:

You specify "Content-Transfer-Encoding: BINARY", but you add Base64 encoded data.

Other than that, what is the actual problem? Does the mail not get sent? Is the content garbled or wrong?

It might be simpler if you don't do the debugging by "looking at the code", but first write the MIME data you create to a file, and then have a look at the MIME data to see if the format is valid.

Re: E-Mail sending issue from Oracle Database [message #629615 is a reply to message #629613] Fri, 12 December 2014 06:35 Go to previous messageGo to next message
Manoj.Gupta.91
Messages: 237
Registered: March 2008
Location: Delhi
Senior Member
Hi,

I'm able to send mail with PDF and Excel attachments. But when I open attachments those are corrupt, just cannot open them.

Regards
Manoj
Re: E-Mail sending issue from Oracle Database [message #629617 is a reply to message #629615] Fri, 12 December 2014 06:48 Go to previous messageGo to next message
ThomasG
Messages: 3211
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Then they are encoded in a wrong way. As I already mentioned, try if it works if you just change "Content-Transfer-Encoding: BINARY" to "Content-Transfer-Encoding: base64" first.

If that still doesn't work you have to debug the MIME format you create. One option would be that you send the same Excel and PDF from a "normal" mail client, and then compare the source of the received mail between that and the Oracle generated one for differences.
Re: E-Mail sending issue from Oracle Database [message #629620 is a reply to message #629612] Fri, 12 December 2014 08:19 Go to previous messageGo to next message
joy_division
Messages: 4949
Registered: February 2005
Location: East Coast USA
Senior Member
To send to multiple recipients, all you do is make a comma separated list, in quotes of course.
Re: E-Mail sending issue from Oracle Database [message #629629 is a reply to message #629620] Fri, 12 December 2014 10:26 Go to previous messageGo to next message
ThomasG
Messages: 3211
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
joy_division wrote on Fri, 12 December 2014 15:19
To send to multiple recipients, all you do is make a comma separated list, in quotes of course.


That's where a lot of people (including me for quite a while) get confused. Recipients are handled differently on the MIME and SMTP level.

On the MIME level yes. You put multiple recipients in the "to" field and separate them by commas.

On the SMTP level you have to specify multiple RCPT commands.

RFC821

RECIPIENT (RCPT)

            This command is used to identify an individual recipient of
            the mail data; multiple recipients are specified by multiple
            use of this command.




Re: E-Mail sending issue from Oracle Database [message #629637 is a reply to message #629629] Fri, 12 December 2014 15:10 Go to previous messageGo to next message
joy_division
Messages: 4949
Registered: February 2005
Location: East Coast USA
Senior Member
Thank you Thomas. I didn't know that, and I guess I never had to think about it because I must have been using it at the MIME level.
Re: E-Mail sending issue from Oracle Database [message #629686 is a reply to message #629637] Mon, 15 December 2014 01:26 Go to previous messageGo to next message
Manoj.Gupta.91
Messages: 237
Registered: March 2008
Location: Delhi
Senior Member
Thank you very much all of you for your valuable feedback.

I'm able to do it. Below is the solution I've developed. I've tested it with EXCEL, PDF, TEXT, ZIP files. I'm able to send mail to multiple recipients.

Please suggest if I can improve this service further.

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
    --                      (4) ZIP
    --****************************************************************************************************************
    -- -: MODIFICATION HISTORY :-
    ------------------------------------------------------------------------------------------------------------------
    --  Date              Name                     Purpose
    --****************************************************************************************************************
    --  11-Dec-2014      Manoj Kumar               Creation of package to send mail with different types of attachments.
    --
    --****************************************************************************************************************

    --****************************************************************************************************************
    --User defined types for Attachments.
    --****************************************************************************************************************
    TYPE gr_Mail_Attachments IS RECORD
    (
        ls_Attach_Type      VARCHAR2(50),       --TEXT, PDF, EXCEL, ZIP
        ls_Attach_Path      VARCHAR2(1000),     --OS Path where file is saved.
        ls_Attach_Name      VARCHAR2(500)       --Attachment file name with appropriate file extension.
    ) ;

    TYPE gtt_Mail_Attachments IS TABLE OF gr_Mail_Attachments INDEX BY BINARY_INTEGER ;

    --****************************************************************************************************************
    --User defined types for mail recipients.
    --****************************************************************************************************************
    TYPE gr_Mail_Recipients IS RECORD
    (
        ls_Recipient_Type           VARCHAR2(50),       --TO, CC, BCC
        ls_Recipient_Mail_Addr      VARCHAR2(1000)      --Mail address of recipient.
    ) ;

    TYPE gtt_Mail_Recipients_List IS TABLE OF gr_Mail_Recipients 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_SMTP_Server               IN VARCHAR2
        ,p_in_Domain_Name               IN VARCHAR2
        ,p_in_From                      IN VARCHAR2
        ,p_in_Subject                   IN VARCHAR2
        ,p_in_Text_Message              IN VARCHAR2                         --Multi Line Message limited to 32767 Characters.
        ,p_in_Mail_Recipients_List      IN gtt_Mail_Recipients_List         --List of mail Recipients.
        ,p_in_Attachments               IN gtt_Mail_Attachments             --List of Attachments.
    ) ;

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
    --                      (4) ZIP
    --****************************************************************************************************************
    -- -: 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_SMTP_Server               IN VARCHAR2
        ,p_in_Domain_Name               IN VARCHAR2
        ,p_in_From                      IN VARCHAR2
        ,p_in_Subject                   IN VARCHAR2
        ,p_in_Text_Message              IN VARCHAR2                         --Multi Line Message limited to 32767 Characters.
        ,p_in_Mail_Recipients_List      IN gtt_Mail_Recipients_List         --List of mail Recipients.
        ,p_in_Attachments               IN gtt_Mail_Attachments             --List of Attachments.
    )
    AS 
        l_Mail_Connection         UTL_SMTP.Connection ;     --Connection Variable.
        lc_Mime_Boundary          CONSTANT VARCHAR2( 256 ) := '-----AABCDEFBBCCC0123456789DE' ;

        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_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: BASE64' || 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 ) ) ;
                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 : 5' ) ;
                DBMS_OUTPUT.put_line ( DBMS_UTILITY.Format_Error_Backtrace ) ;
                DBMS_OUTPUT.put_line ( DBMS_UTILITY.Format_Error_Stack ) ;
                RAISE ;
        END Prc_Attach_File ;

    BEGIN

        l_Mail_Connection := UTL_SMTP.Open_Connection( p_in_SMTP_Server ) ;     --Opens a connection to an SMTP server.      --SMTP Server Name.
        UTL_SMTP.Helo( l_Mail_Connection, p_in_Domain_Name ) ;                  --Perform initial handshaking with SMTP server after connecting.  --Domain Name.

        UTL_SMTP.Mail( l_Mail_Connection, p_in_From ) ;         --Initiates a mail transaction with the server, the destination is a mailbox.

        FOR R IN p_in_Mail_Recipients_List.FIRST..p_in_Mail_Recipients_List.LAST
        LOOP
            IF p_in_Mail_Recipients_List.EXISTS(R) THEN
                IF TRIM( UPPER( p_in_Mail_Recipients_List(R).ls_Recipient_Type ) ) = 'TO' THEN
                    UTL_SMTP.Rcpt( l_Mail_Connection, p_in_Mail_Recipients_List(R).ls_Recipient_Mail_Addr ) ;           --Specifies the recipient of an e-mail message.
                END IF ;
            END IF ;
        END LOOP ;

        --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, 'Subject: ' || p_in_Subject || UTL_TCP.Crlf ) ;
        UTL_SMTP.Write_Data( l_Mail_Connection, 'From: ' || p_in_From || UTL_TCP.Crlf ) ;
        UTL_SMTP.Write_Data( l_Mail_Connection, 'Reply-To: ' || p_in_From || UTL_TCP.Crlf ) ;

        FOR R IN p_in_Mail_Recipients_List.FIRST..p_in_Mail_Recipients_List.LAST
        LOOP
            IF p_in_Mail_Recipients_List.EXISTS(R) THEN
                IF TRIM( UPPER( p_in_Mail_Recipients_List(R).ls_Recipient_Type ) ) = 'TO' THEN
                    UTL_SMTP.Write_Data( l_Mail_Connection, 'To: ' || '"Recipient" <' || p_in_Mail_Recipients_List(R).ls_Recipient_Mail_Addr || '>' ||  UTL_TCP.Crlf ) ;
                ELSIF TRIM( UPPER( p_in_Mail_Recipients_List(R).ls_Recipient_Type ) ) = 'CC' THEN
                    UTL_SMTP.Write_Data( l_Mail_Connection, 'CC: ' || '"Recipient" <' || p_in_Mail_Recipients_List(R).ls_Recipient_Mail_Addr || '>' ||  UTL_TCP.Crlf ) ;
                ELSIF TRIM( UPPER( p_in_Mail_Recipients_List(R).ls_Recipient_Type ) ) = 'BCC' THEN
                    UTL_SMTP.Write_Data( l_Mail_Connection, 'BCC: ' || '"Recipient" <' || p_in_Mail_Recipients_List(R).ls_Recipient_Mail_Addr || '>' ||  UTL_TCP.Crlf ) ;
                END IF ;
            END IF ;
        END LOOP ;

        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 TRIM(UPPER(p_in_Attachments(I).ls_Attach_Type)) IN ( 'TEXT', 'PDF', 'EXCEL', 'ZIP' ) THEN
                        --Prc_Attach_Text_File( p_in_Attachments(I).ls_Attach_Name, p_in_Attachments(I).ls_Attach_Path ) ;
                        Prc_Attach_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 :
<<Test_Send_Mail >>
DECLARE
    Mail_Recipients        pkg_Send_Mail.gtt_Mail_Recipients_List ;
    Mail_Attachments       pkg_Send_Mail.gtt_Mail_Attachments  ;
BEGIN

    Mail_Recipients(1).ls_Recipient_Type := 'TO' ;
    Mail_Recipients(1).ls_Recipient_Mail_Addr := 'mailaddressto_1@yahoo.com' ;

    Mail_Recipients(2).ls_Recipient_Type := 'TO' ;
    Mail_Recipients(2).ls_Recipient_Mail_Addr := 'mailaddressto_2@yahoo.com' ;

    Mail_Recipients(3).ls_Recipient_Type := 'TO' ;
    Mail_Recipients(3).ls_Recipient_Mail_Addr := 'mailaddressto_3@yahoo.com' ;

    Mail_Recipients(1).ls_Recipient_Type := 'CC' ;
    Mail_Recipients(1).ls_Recipient_Mail_Addr := 'mailaddresscc_1@yahoo.com' ;

    Mail_Recipients(2).ls_Recipient_Type := 'CC' ;
    Mail_Recipients(2).ls_Recipient_Mail_Addr := 'mailaddresscc_2@yahoo.com' ;

    Mail_Recipients(3).ls_Recipient_Type := 'CC' ;
    Mail_Recipients(3).ls_Recipient_Mail_Addr := 'mailaddresscc_3@yahoo.com' ;

    Mail_Recipients(1).ls_Recipient_Type := 'BCC' ;
    Mail_Recipients(1).ls_Recipient_Mail_Addr := 'mailaddressbcc_1@yahoo.com' ;

    Mail_Recipients(2).ls_Recipient_Type := 'BCC' ;
    Mail_Recipients(2).ls_Recipient_Mail_Addr := 'mailaddressbcc_2@yahoo.com' ;

    Mail_Recipients(3).ls_Recipient_Type := 'BCC' ;
    Mail_Recipients(3).ls_Recipient_Mail_Addr := 'mailaddressbcc_3@yahoo.com' ;

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

    Mail_Attachments(2).ls_Attach_Type := 'EXCEL' ;
    Mail_Attachments(2).ls_Attach_Path := '/home/oracle' ;
    Mail_Attachments(2).ls_Attach_Name := 'myxls1.xlsx' ;

    Mail_Attachments(3).ls_Attach_Type := 'TEXT' ;
    Mail_Attachments(3).ls_Attach_Path := '/home/oracle' ;
    Mail_Attachments(3).ls_Attach_Name := 'mytxt1.txt' ;

    Mail_Attachments(4).ls_Attach_Type := 'ZIP' ;
    Mail_Attachments(4).ls_Attach_Path := '/home/oracle' ;
    Mail_Attachments(4).ls_Attach_Name := 'myzip1.zip' ;

    Mail_Attachments(5).ls_Attach_Type := 'EXCEL' ;
    Mail_Attachments(5).ls_Attach_Path := '/home/oracle' ;
    Mail_Attachments(5).ls_Attach_Name := 'myxls2.xlsx' ;

    pkg_Send_Mail.Prc_Send_Mail
    ( 
         p_in_SMTP_Server               => 'SMTP_Server.org'
        ,p_in_Domain_Name               => 'domainname.in'
        ,p_in_From                      => 'mailaddressfrom@yahoo.com'
        ,p_in_Subject                   => 'This is a test mail subject line'
        ,p_in_Text_Message              => 'This is multi line mail body part for test mail'        --Multi Line Message limited to 32767 Characters.
        ,p_in_Mail_Recipients_List      => Mail_Recipients
        ,p_in_Attachments               => Mail_Attachments
    ) ;

END Test_Send_Mail ;


Required Settings :
In addition to above below piece of code need to be executed once for successful execution of above one.
CREATE DIRECTORY TEST_MAIL_DIR AS '/home/oracle' ;
GRANT EXECUTE, READ, WRITE ON TEST_MAIL_DIR TO USER_NAME ;			--Need to change.

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 ;


Thanks & Regards
Manoj

[Updated on: Mon, 15 December 2014 05:20]

Report message to a moderator

Re: E-Mail sending issue from Oracle Database [message #630541 is a reply to message #629686] Tue, 30 December 2014 00:55 Go to previous messageGo to next message
Manoj.Gupta.91
Messages: 237
Registered: March 2008
Location: Delhi
Senior Member
Hi All,

Below is updated one with some improvements.
(1) Bigger mail body text like CLOB.
(2) Mail body can be plain text as well as HTML.
(3) Recipient list can be comma or semicolon separated. (You may use some other separator also).


Required Settings :
CREATE DIRECTORY TEST_MAIL_DIR AS '/home/oracle' ;                              --Need to change Directory Name and Path.
GRANT EXECUTE, READ, WRITE ON TEST_MAIL_DIR TO USER_NAME ;			--Need to change User Name.

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 User Name.
      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 SMTP Server address.
      lower_port  => NULL,
      upper_port  => NULL
  ) ; 

  COMMIT ;
END ;

--Execute if required to re-create.
BEGIN
	DBMS_NETWORK_ACL_ADMIN.drop_acl ( acl => 'open_acl_file.xml' ) ;
END ;



Updated Package :
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, PDF, EXCEL, ZIP, WORD
    --                      (2) Mail body can be plain text as well as HTML.
    --
    --****************************************************************************************************************
    -- -: MODIFICATION HISTORY :-
    ------------------------------------------------------------------------------------------------------------------
    --  Date              Name                     Purpose
    --****************************************************************************************************************
    --  11-Dec-2014      Manoj Kumar               Creation of package to send mail with different types of attachments.
    --
    --****************************************************************************************************************

    --****************************************************************************************************************
    --User defined types for Attachments.
    --****************************************************************************************************************
    TYPE gr_Mail_Attachments IS RECORD
    (
        ls_Attach_Type      VARCHAR2(50),       --TEXT, PDF, EXCEL, ZIP, WORD
        ls_Attach_Path      VARCHAR2(1000),     --OS Path where file is saved.
        ls_Attach_Name      VARCHAR2(500)       --Attachment file name with appropriate file extension.
    ) ;

    TYPE gtt_Mail_Attachments IS TABLE OF gr_Mail_Attachments INDEX BY BINARY_INTEGER ;

    --****************************************************************************************************************
    --User defined types for mail recipients.
    --INDIVIDUAL_LIST => Each mail address is mentioned separately in each element of collection.
    --SEPARATED_LIST  => List of mail addresses separated using some Separator.
    --****************************************************************************************************************
    gs_Individual_list      CONSTANT VARCHAR2(30) := 'INDIVIDUAL_LIST' ;
    gs_Separated_list       CONSTANT VARCHAR2(30) := 'SEPARATED_LIST' ;

    TYPE gr_Mail_Recipients IS RECORD
    (
        ls_Recipient_Type           VARCHAR2(50),        --TO, CC, BCC
        ls_Recipient_Mail_Addr      VARCHAR2(1000),      --Mail address of recipient.
        ls_List_Type                VARCHAR2(30)    DEFAULT  gs_Individual_list,        --INDIVIDUAL_IN_ROW, SEPARATED_LIST
        ls_List_Separator           VARCHAR2(1)     DEFAULT  NULL
    ) ;

    TYPE gtt_Mail_Recipients_List IS TABLE OF gr_Mail_Recipients 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_SMTP_Server               IN VARCHAR2
        ,p_in_Domain_Name               IN VARCHAR2
        ,p_in_From                      IN VARCHAR2
        ,p_in_Subject                   IN VARCHAR2
        ,p_in_Text_Message              IN CLOB
        ,p_in_Mail_Recipients_List      IN gtt_Mail_Recipients_List         --List of mail Recipients. (Atleast one recipient is mandatory).
        ,p_in_Attachments               IN gtt_Mail_Attachments             --List of Attachments. (Attachment list can be empty).
    ) ;

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, PDF, EXCEL, ZIP, WORD
    --                      (2) Mail body can be plain text as well as HTML.
    --
    --Note :-               I've faced a unique scenario while working on this utility. Whenever I try to break CLOB
    --                      mail message to smaller chunks of VARCHAR2 because of limitation of UTL_SMTP.Write_Data
    --                      to write VARCHAR2 length I'm facing an issue that if I break it in more than 8K i.e.
    --                      8192 character chunks the number of characters it can gives in final output are lesser 
    --                      than that of actually expected to be returned by DBMS_LOB.SubStr function. The reason
    --                      I could find is expected to be NLS_CHARACTERSET=AL32UTF8 as this characterset is a varying 
    --                      width characterset which means that the code for 1 character can be 1,2,3 or 4 bytes long 
    --                      The default value of DB_BLOCK_SIZE is 8192 bytes.
    --                      ( More than 8191 gives issue if NLS_CHARACTERSET=AL32UTF8 and DB_BLOCK_SIZE=8192 )
    --                      It may not be exact root cause but it is suspected and need more analysis.
    --****************************************************************************************************************
    -- -: MODIFICATION HISTORY :-
    ------------------------------------------------------------------------------------------------------------------
    --  Date              Name                     Purpose
    --****************************************************************************************************************
    --  11-Dec-2014      Manoj Kumar               Creation of package to send mail with different types of attachments.
    --
    --****************************************************************************************************************

    lcn_Max_String_Len           CONSTANT NUMBER(15) := 8000 ;

    --Cursor to break list of mail addresses into multiple rows where Separator is used.
    CURSOR Cur_Mail_Recipients_List
    (
         ps_Recipient_Type            VARCHAR2
        ,ps_Recipient_Mail_Addr       VARCHAR2
        ,ps_List_Separator            VARCHAR2
    )
    IS
        WITH 
            MAILING_LIST AS
            (
                SELECT 
                       ps_Recipient_Type            Recipient_Type
                      ,ps_Recipient_Mail_Addr       Mail_Addr
                FROM DUAL
            )
        SELECT * 
        FROM
        (
            SELECT 
                   Recipient_Type
                  ,TRIM(REGEXP_SUBSTR( ML.Mail_Addr, '[^' || ps_List_Separator || ']+', 1, LEVEL)) Mail_Addr
            FROM MAILING_LIST ML
            CONNECT BY LEVEL <= LENGTH( REGEXP_REPLACE( ML.Mail_Addr, '[^' || ps_List_Separator || ']+')) + 1
        ) WHERE Mail_Addr IS NOT NULL ;

    PROCEDURE Prc_Send_Mail
    (
         p_in_SMTP_Server               IN VARCHAR2
        ,p_in_Domain_Name               IN VARCHAR2
        ,p_in_From                      IN VARCHAR2
        ,p_in_Subject                   IN VARCHAR2
        ,p_in_Text_Message              IN CLOB
        ,p_in_Mail_Recipients_List      IN gtt_Mail_Recipients_List         --List of mail Recipients. (Atleast one recipient is mandatory).
        ,p_in_Attachments               IN gtt_Mail_Attachments             --List of Attachments. (Attachment list can be empty).
    )
    AS 
        l_Mail_Connection         UTL_SMTP.Connection ;     --Connection Variable.
        lc_Mime_Boundary          CONSTANT VARCHAR2( 256 ) := '-----AABCDEFBBCCC0123456789DE' ;
        ls_Text_Message           VARCHAR2(32767) ;

        PROCEDURE Prc_Attach_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: BASE64' || 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 ) ;      --Need to change Directory 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 ) ) ;
                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 ;

                --Put your error/exception logging code here.
                DBMS_OUTPUT.Put_Line( 'Error : 5' ) ;
                DBMS_OUTPUT.Put_Line ( DBMS_UTILITY.Format_Error_Backtrace ) ;
                DBMS_OUTPUT.Put_Line ( DBMS_UTILITY.Format_Error_Stack ) ;
                RAISE ;
        END Prc_Attach_File ;

    BEGIN

        DBMS_OUTPUT.Put_Line( 'Mail Sending Procedure Started.' ) ;
        l_Mail_Connection := UTL_SMTP.Open_Connection( p_in_SMTP_Server ) ;     --Opens a connection to an SMTP server.      --SMTP Server Name.
        UTL_SMTP.Helo( l_Mail_Connection, p_in_Domain_Name ) ;                  --Perform initial handshaking with SMTP server after connecting.  --Domain Name.

        UTL_SMTP.Mail( l_Mail_Connection, p_in_From ) ;         --Initiates a mail transaction with the server, the destination is a mailbox.

        IF p_in_Mail_Recipients_List.COUNT = 0 THEN
            RAISE_APPLICATION_ERROR( -20001, 'Recipient list cannot be empty.....!!' ) ;
        END IF ;

        FOR R IN p_in_Mail_Recipients_List.FIRST..p_in_Mail_Recipients_List.LAST
        LOOP
            IF p_in_Mail_Recipients_List.EXISTS(R) THEN
                --UTL_SMTP.Rcpt command actually sends mail to recipients.
                --Whereas UTL_SMTP.Write_Data( l_Mail_Connection, 'To: "Recipient" <manoj.kumar@yahoo.com>' ||  UTL_TCP.Crlf ) ;
                --is only used to display mail address in mail headers but it actually doesn't send mail.
                IF p_in_Mail_Recipients_List(R).ls_List_Type = gs_Individual_list THEN

                    UTL_SMTP.Rcpt( l_Mail_Connection, p_in_Mail_Recipients_List(R).ls_Recipient_Mail_Addr ) ;           --Specifies the recipient of an e-mail message.

                ELSIF p_in_Mail_Recipients_List(R).ls_List_Type = gs_Separated_list THEN

                    IF p_in_Mail_Recipients_List(R).ls_List_Separator IS NULL THEN
                        RAISE_APPLICATION_ERROR( -20002, 'Separator parameter cannot be NULL.....!!' ) ;
                    END IF ;

                    FOR Rec_Mail_Recipients_List IN Cur_Mail_Recipients_List( p_in_Mail_Recipients_List(R).ls_Recipient_Type, TRIM(p_in_Mail_Recipients_List(R).ls_Recipient_Mail_Addr), p_in_Mail_Recipients_List(R).ls_List_Separator )
                    LOOP
                        UTL_SMTP.Rcpt( l_Mail_Connection, Rec_Mail_Recipients_List.Mail_Addr ) ;           --Specifies the recipient of an e-mail message.
                    END LOOP ;

                END IF ;

            END IF ;
        END LOOP ;

        --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, 'Subject: ' || p_in_Subject || UTL_TCP.Crlf ) ;
        UTL_SMTP.Write_Data( l_Mail_Connection, 'From: ' || p_in_From || UTL_TCP.Crlf ) ;
        UTL_SMTP.Write_Data( l_Mail_Connection, 'Reply-To: ' || p_in_From || UTL_TCP.Crlf ) ;

        FOR R IN p_in_Mail_Recipients_List.FIRST..p_in_Mail_Recipients_List.LAST
        LOOP
            IF p_in_Mail_Recipients_List.EXISTS(R) THEN

                IF p_in_Mail_Recipients_List(R).ls_List_Type = gs_Individual_list THEN

                    IF TRIM( UPPER( p_in_Mail_Recipients_List(R).ls_Recipient_Type ) ) = 'TO' THEN
                        UTL_SMTP.Write_Data( l_Mail_Connection, 'To: ' || '"Recipient" <' || TRIM(p_in_Mail_Recipients_List(R).ls_Recipient_Mail_Addr) || '>' ||  UTL_TCP.Crlf ) ;
                    ELSIF TRIM( UPPER( p_in_Mail_Recipients_List(R).ls_Recipient_Type ) ) = 'CC' THEN
                        UTL_SMTP.Write_Data( l_Mail_Connection, 'CC: ' || '"Recipient" <' || TRIM(p_in_Mail_Recipients_List(R).ls_Recipient_Mail_Addr) || '>' ||  UTL_TCP.Crlf ) ;
                    ELSIF TRIM( UPPER( p_in_Mail_Recipients_List(R).ls_Recipient_Type ) ) = 'BCC' THEN
                        UTL_SMTP.Write_Data( l_Mail_Connection, 'BCC: ' || '"Recipient" <' || TRIM(p_in_Mail_Recipients_List(R).ls_Recipient_Mail_Addr) || '>' ||  UTL_TCP.Crlf ) ;
                    END IF ;

                ELSIF p_in_Mail_Recipients_List(R).ls_List_Type = gs_Separated_list THEN

                    FOR Rec_Mail_Recipients_List IN Cur_Mail_Recipients_List( p_in_Mail_Recipients_List(R).ls_Recipient_Type, TRIM(p_in_Mail_Recipients_List(R).ls_Recipient_Mail_Addr), p_in_Mail_Recipients_List(R).ls_List_Separator )
                    LOOP

                        IF TRIM( UPPER( Rec_Mail_Recipients_List.Recipient_Type ) ) = 'TO' THEN
                            UTL_SMTP.Write_Data( l_Mail_Connection, 'To: ' || '"Recipient" <' || Rec_Mail_Recipients_List.Mail_Addr || '>' ||  UTL_TCP.Crlf ) ;
                        ELSIF TRIM( UPPER( Rec_Mail_Recipients_List.Recipient_Type ) ) = 'CC' THEN
                            UTL_SMTP.Write_Data( l_Mail_Connection, 'CC: ' || '"Recipient" <' || Rec_Mail_Recipients_List.Mail_Addr || '>' ||  UTL_TCP.Crlf ) ;
                        ELSIF TRIM( UPPER( Rec_Mail_Recipients_List.Recipient_Type ) ) = 'BCC' THEN
                            UTL_SMTP.Write_Data( l_Mail_Connection, 'BCC: ' || '"Recipient" <' || Rec_Mail_Recipients_List.Mail_Addr || '>' ||  UTL_TCP.Crlf ) ;
                        END IF ;

                    END LOOP ;
                END IF ;
            END IF ;
        END LOOP ;

        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, 'Content-Type: text/html; charset="iso-8859-1"' || UTL_TCP.Crlf || UTL_TCP.Crlf ) ;

            --Breaking CLOB Mail Body text to text chunks of characters so that these chunks can be written to mail.
            --Reason : Data which can be written using UTL_SMTP.Write_Data has VARCHAR2 data type which has a limit of 32767 characters/bytes.
            FOR MB IN (
                        SELECT LEVEL - 1 Loop_Count
                        FROM DUAL 
                        CONNECT BY LEVEL <= (CEIL(DBMS_LOB.GetLength(p_in_Text_Message)/lcn_Max_String_Len) + 1)
                      )
            LOOP
                ls_Text_Message := DBMS_LOB.SubStr(p_in_Text_Message, lcn_Max_String_Len, (MB.Loop_Count * lcn_Max_String_Len) + 1 ) ;

                UTL_SMTP.Write_Data( l_Mail_Connection, ls_Text_Message ) ;
            END LOOP ;

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

        <<Send_Text_Mail>>
        BEGIN
            IF p_in_Attachments.COUNT <> 0 THEN     --Check if Attachment List is empty or not.
                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 TRIM(UPPER(p_in_Attachments(I).ls_Attach_Type)) IN ( 'TEXT', 'PDF', 'EXCEL', 'ZIP', 'WORD' ) THEN
                            --Prc_Attach_Text_File( p_in_Attachments(I).ls_Attach_Name, p_in_Attachments(I).ls_Attach_Path ) ;
                            Prc_Attach_File( p_in_Attachments(I).ls_Attach_Name, p_in_Attachments(I).ls_Attach_Path ) ;
                        END IF ;
                    END IF ;
                END LOOP ;
            END IF ;
        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

        DBMS_OUTPUT.Put_Line( 'Mail Sending Procedure Completed.' ) ;
    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
                    --Put your error/exception logging code here.
                    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 ) ;
            --Put your error/exception logging code here.
            RAISE_APPLICATION_ERROR( -20001, 'Failed to send mail due to the following error: ' || SQLERRM ) ;

        WHEN OTHERS THEN
            --Put your error/exception logging code here.
            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 :
<<Test_Send_Mail>>
DECLARE
    Mail_Recipients        pkg_Send_Mail.gtt_Mail_Recipients_List ;
    Mail_Attachments       pkg_Send_Mail.gtt_Mail_Attachments ;
BEGIN
    Mail_Recipients(1).ls_Recipient_Type := 'TO' ;
    Mail_Recipients(1).ls_Recipient_Mail_Addr := 'manoj.kumar@yahoo.com;manoj.gupta@gmail.com' ;
    Mail_Recipients(1).ls_List_Type := pkg_Send_Mail.gs_Separated_list ;
    Mail_Recipients(1).ls_List_Separator := ';' ;

    Mail_Recipients(2).ls_Recipient_Type := 'CC' ;
    Mail_Recipients(2).ls_Recipient_Mail_Addr := 'manoj.kumar1@yahoo.com;manoj.gupta1@gmail.com' ;
    Mail_Recipients(2).ls_List_Type := pkg_Send_Mail.gs_Separated_list ;
    Mail_Recipients(2).ls_List_Separator := ';' ;

    --File attachment. Make sure manojpdf1.pdf and manojpdf2.pdf exists on below path.
    Mail_Attachments(1).ls_Attach_Type := 'PDF' ;
    Mail_Attachments(1).ls_Attach_Path := '/home/oracle' ;
    Mail_Attachments(1).ls_Attach_Name := 'manojpdf1.pdf' ;

    Mail_Attachments(2).ls_Attach_Type := 'PDF' ;
    Mail_Attachments(2).ls_Attach_Path := '/home/oracle' ;
    Mail_Attachments(2).ls_Attach_Name := 'manojpdf2.pdf' ;

    pkg_Send_Mail.Prc_Send_Mail
    ( 
         p_in_SMTP_Server               => 'SMTP_Server.org'
        ,p_in_Domain_Name               => 'domain.in'
        ,p_in_From                      => 'manoj.kumar@yahoo.com'
        ,p_in_Subject                   => 'This is HTML test mail subject line.'
        ,p_in_Text_Message              => '<!DOCTYPE html>
                                            <html>
                                            <head>
                                              <title>Page Title</title>
                                            </head>
                                            <body>
                                              <h1>This is a Heading</h1>
                                              <p>This is a paragraph.</p>
                                            </body>
                                            </html>'
        ,p_in_Mail_Recipients_List      => Mail_Recipients
        ,p_in_Attachments               => Mail_Attachments
    ) ;

END Test_Send_Mail ;


Thanks & Regards
Manoj

[Updated on: Tue, 30 December 2014 06:19]

Report message to a moderator

Re: E-Mail sending issue from Oracle Database [message #632402 is a reply to message #630541] Thu, 29 January 2015 23:29 Go to previous messageGo to next message
Manoj.Gupta.91
Messages: 237
Registered: March 2008
Location: Delhi
Senior Member
Hi All,

I'm able to send text or HTML mail with or without attachments from Oracle DB using my service. This works only with our company's SMTP server and I'm only able to send mail to my company's mail addresses.
Can you please help me to figure out below two issues. I tried lot of things but these doesn't worked for me.


(1) Send mail to mail address other than company's mail address.
      Eg. Sender    :   manoj.gupta@mycompany.com
          Recipient :   manoj.gupta@yahoo.com    OR    manoj.gupta@gmail.com    OR    manoj.gupta@mycompany.com


(2) Send mail to and from mail address other than company's mail address.
      Eg. Sender    :   manoj.gupta@yahoo.com    OR    manoj.gupta@gmail.com
          Recipient :   manoj.gupta@yahoo.com    OR    manoj.gupta@gmail.com    OR    manoj.gupta@mycompany.com


Recipient list may contain multiple addresses using valid separator. (Semicolon for example)
Eg. Recipient :   manoj.gupta@yahoo.com;manoj.gupta@gmail.com;manoj.gupta@mycompany.com


Thanks & Regards
Manoj

Re: E-Mail sending issue from Oracle Database [message #632403 is a reply to message #632402] Fri, 30 January 2015 00:08 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>Can you please help me to figure out below two issues.

Both problems are MTA configuration issues & have NOTHING to do with Oracle database.
Re: E-Mail sending issue from Oracle Database [message #644441 is a reply to message #632403] Thu, 05 November 2015 23:43 Go to previous messageGo to next message
oralover2006
Messages: 144
Registered: January 2010
Location: India
Senior Member
BlackSwan wrote on Fri, 30 January 2015 11:38
>Can you please help me to figure out below two issues.

Both problems are MTA configuration issues & have NOTHING to do with Oracle database.


dear BlackSwan, i am facing the same problem, can send email to my own domain but can't send to other, i can send emails through MS Outlook to any other domain, can you help me about it as you have mentioned "MTA configuration issues", i have to contact the people responsible to handle the mail server or any thing else ?

regards.

[Updated on: Thu, 05 November 2015 23:44]

Report message to a moderator

Re: E-Mail sending issue from Oracle Database [message #644443 is a reply to message #644441] Fri, 06 November 2015 00:56 Go to previous messageGo to next message
Michel Cadot
Messages: 67920
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
i have to contact the people responsible to handle the mail server

Yes.

Re: E-Mail sending issue from Oracle Database [message #644444 is a reply to message #644443] Fri, 06 November 2015 01:20 Go to previous messageGo to next message
oralover2006
Messages: 144
Registered: January 2010
Location: India
Senior Member
thanks Michel Smile
Re: E-Mail sending issue from Oracle Database [message #644702 is a reply to message #644444] Fri, 13 November 2015 15:54 Go to previous messageGo to next message
Bill B
Messages: 1971
Registered: December 2004
Senior Member
You need the administrator of your mail server to turn on relay for the oracle server that is running the package.
Re: E-Mail sending issue from Oracle Database [message #652083 is a reply to message #629686] Wed, 01 June 2016 02:54 Go to previous message
Paul_A
Messages: 1
Registered: June 2016
Junior Member
Hi,
Excellent example, works like a charm.
I've got to attach a html file to email (as attachment!), and this works with
Mail_Attachments(1).ls_Attach_Type := 'EXCEL' ;

I changed the code, and added the type 'HTML' to it. This works fine, so I wonder, what is the use of those types...

In any case, thanks again for your example!

Paul
Previous Topic: Chinese Characters not display in table
Next Topic: package error
Goto Forum:
  


Current Time: Tue Jul 27 20:52:55 CDT 2021