10 Replies Latest reply: Jun 25, 2013 12:09 AM by Raunaq RSS

    Adding Image as attachment to e-mail UTL_SMTP

    Raunaq

      Guys ,

       

      I am trying to add an image as attachment to an e-mail.

       

      I am reading the image from the table and then passing it as a BLOB in my procedure.

       

      Following is the part of the code which adds the attachment

      {code}

      PROCEDURE add_mail_attachment

                    (

                    /*

                 

                    in_att_mime_type  in  varchar2 character set any_cs default 'text/plain; charset=us-ascii',

                    in_attachment     IN  BLOB ,

                    in_att_file_name  in  varchar2 character set any_cs default null,

                    in_mail_conn      IN  UTL_SMTP.CONNECTION

                    )

      IS

      l_mail_conn       utl_smtp.connection;

      l_step            PLS_INTEGER  :=2147483647;

      BEGIN

       

          l_mail_conn:=in_mail_conn;

       

          if in_att_file_name is not null then

            UTL_SMTP.write_data(l_mail_conn, '--' || co_msg_boundary || UTL_TCP.crlf);

            UTL_SMTP.write_data(l_mail_conn, 'Content-Type: ' || in_att_mime_type || '; name="' || in_att_file_name || '"' || UTL_TCP.crlf);

            UTL_SMTP.write_data(l_mail_conn, 'Content-Disposition: attachment; filename="' || in_att_file_name || '"' || UTL_TCP.crlf || UTL_TCP.crlf);

       

            for i in 0 .. trunc((dbms_lob.getlength(in_attachment) - 1 )/l_step) loop

                   UTL_SMTP.write_data(l_mail_conn, DBMS_LOB.substr(in_attachment, l_step, i * l_step + 1));

            END LOOP;

       

            utl_smtp.write_data(l_mail_conn, utl_tcp.crlf || utl_tcp.crlf);

          end if;

         

         

         EXCEPTION

         WHEN OTHERS THEN

            sbs_error.error_handler(

                   in_error_no   => SQLCODE,

                   in_error_txt  => SQLERRM,

                   in_show_stack => TRUE

            );

       

      end;

      {code}

       

      And this is the code which i am running to test the code

      {code}

      declare

        in_sender                                                         varchar2(200);

        in_recipients                                    varchar2(200);

        in_subject                                                         varchar2(200);

        in_message                                                      varchar2(200);

        in_att_mime_type                                        varchar2(200);

        in_attachment                                                blob;

        in_att_file_name                                           varchar2(200);

        in_cc                                                                        varchar2(200);

        in_bcc                                                     varchar2(200);

        out_mail_conn                                               utl_smtp.connection;

        in_mail_conn                                   utl_smtp.connection;

        v_blob                                        blob;

       

      begin

            in_sender := 'abc@xyz.com';

            in_recipients := 'abc@xyz.com';

            in_subject := 'Image attachment';

            in_message := 'Test mails ,Please delete';

            in_cc:='abc@xyz.com';

            in_bcc:='abc@xyz.com';

           

            select image into v_blob

            from table

            where condition

       

            sbs_util.sbs_p_email_pkg_raunaq1.open_mail

            (

            in_sender     => in_sender,

            in_recipients => in_recipients,

            in_subject    => in_subject,

            in_message    => in_message,

            in_cc         => in_cc,

            in_bcc        => in_bcc,

            out_mail_conn => out_mail_conn

            );

                                       

            in_att_mime_type := 'application/x-pdf';

            in_attachment := v_blob;

            in_att_file_name := 'Att1.pdf';

                 

            sbs_util.sbs_p_email_pkg_raunaq1.add_mail_attachment

            (

            in_att_mime_type  => in_att_mime_type,

            in_attachment     => v_blob,

            in_att_file_name  => in_att_file_name,

            in_mail_conn      => out_mail_conn

            );

          

           

            sbs_util.sbs_p_email_pkg_raunaq1.close_mail(out_mail_conn);

           

           

           

      END;

       

      {code}

        • 2. Re: Adding Image as attachment to e-mail UTL_SMTP
          Raunaq

          Reading the same page.

           

          But i am getting the error

          ORA-29261: bad argument on the following line of code

           

          for i in 0 .. trunc((dbms_lob.getlength(in_attachment) - 1 )/l_step)

          • 3. Re: Adding Image as attachment to e-mail UTL_SMTP
            Hoek

            Well, for one thing, when comparing the values for l_step, here's the value and the comment from Tim's example:

            l_step        PLS_INTEGER  := 12000; -- make sure you set a multiple of 3 not higher than 24573

            and yours:

            l_step            PLS_INTEGER  :=2147483647

            which seems quite a bit higher than 24573...

            Did you adjust that already and rerun?

            • 4. Re: Adding Image as attachment to e-mail UTL_SMTP
              Raunaq

              Getting the following error after making it 24573

               

              PL/SQL: numeric or value error: raw variable length too long

              • 5. Re: Adding Image as attachment to e-mail UTL_SMTP
                Raunaq

                Yes i have adjusted that and re-run and then i get the file and the size is there

                 

                but on opening it does not open , i am using mime type image/png now

                • 6. Re: Adding Image as attachment to e-mail UTL_SMTP
                  Raunaq

                  When i run with 24573 and attach a large file  i get this error -> numeric or value error: character string buffer too small

                  • 7. Re: Adding Image as attachment to e-mail UTL_SMTP
                    Hoek

                    Since you're using packages that we don't know the code of, all I can think of is:

                    Adjust (were needed) and compile Tim's example on your own database and test it using your specific attachment.

                    If that one does work correctly, then compare your own code to it.

                    If that one doesn't work correctly too, then post a small narrowed down example reproducing the error we can run ourselves.

                     

                     


                    • 8. Re: Adding Image as attachment to e-mail UTL_SMTP
                      Raunaq

                      In the article , which you have shared .....

                       

                      For the BLOB version it shows the use of following line

                       

                      {code}

                      UTL_SMTP.write_data(l_mail_conn, UTL_RAW.cast_to_varchar2(UTL_ENCODE.base64_encode(DBMS_LOB.substr(p_attach_blob, l_step, i * l_step + 1))));

                      {/code}

                       

                      I dont understand if we are not using RAW data anywhere , then what is the use of the above line ?

                      Can you put somelight on it ?

                      And also what is the significance of using a multiple of 3?

                      • 9. Re: Adding Image as attachment to e-mail UTL_SMTP
                        Raunaq

                        And this is the code which i am using :

                         

                        There are basically 3 procedures

                        1.Open_mail() - Which is used to open a connection and start a mail message, The code is below

                        PROCEDURE open_mail (

                         

                         

                                            in_sender         IN  VARCHAR2 CHARACTER SET ANY_CS,

                                            in_recipients     IN  VARCHAR2 CHARACTER SET ANY_CS,

                                            in_subject        IN  VARCHAR2 CHARACTER SET ANY_CS DEFAULT NULL,

                                            in_message        IN  VARCHAR2 CHARACTER SET ANY_CS DEFAULT NULL,

                                            in_cc             IN  varchar2 character set any_cs default null,

                                            in_bcc            IN  varchar2 character set any_cs default null,

                                            out_mail_conn     OUT UTL_SMTP.CONNECTION

                                            )

                         

                         

                         

                        AS

                         

                          l_mail_conn       UTL_SMTP.CONNECTION;

                          l_email_addresses   VARCHAR2(32767);

                          l_send_email_add    VARCHAR2(32767);

                        BEGIN

                          IF in_sender IS NULL OR in_recipients IS NULL THEN

                          RAISE e_null_email_add;

                          END IF;

                         

                          l_email_addresses:=in_sender||','||in_recipients;

                          l_send_email_add:=rtrim(in_recipients||','||in_cc||','||in_bcc,',');

                         

                          IF (validate_email_address(l_email_addresses) AND validate_email_address(l_send_email_add))   --if none of the email addresses are invalid ,then go for mail

                          THEN

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

                          --establish the connection

                          --The snapon based smtp host is smtp.snapbs.com

                        --The default smtp port being 25

                         

                          UTL_SMTP.helo(l_mail_conn, co_smtp_server);

                          UTL_SMTP.mail(l_mail_conn, in_sender);

                          --Incase we have multiple recipients ,we need to loop through the recipient  ,invoking

                          --utl_smtp.rcpt once for each recipient ,mutiple recipien

                            FOR i IN ( SELECT REGEXP_SUBSTR (l_send_email_add, '[^,]+', 1, LEVEL) AS recipient  -- Replaced in_recipients with l_send_email_add

                                        FROM DUAL

                                         CONNECT BY REGEXP_SUBSTR (l_send_email_add, '[^,]+', 1, LEVEL) IS NOT NULL )

                            LOOP

                            UTL_SMTP.rcpt (l_mail_conn, i.recipient );

                            --using the same connection established to send mails to multiple recipients

                            END LOOP;

                          UTL_SMTP.open_data(l_mail_conn);

                          UTL_SMTP.WRITE_DATA(l_mail_conn, 'Date: ' || TO_CHAR(SYSDATE, 'DD-MON-YYYY HH24:MI:SS') || UTL_TCP.CRLF);

                          UTL_SMTP.WRITE_DATA(l_mail_conn, 'To: ' || IN_RECIPIENTS || UTL_TCP.CRLF);

                          UTL_SMTP.WRITE_DATA(l_mail_conn, 'Cc: ' || IN_CC|| UTL_TCP.CRLF); 

                          UTL_SMTP.write_data(l_mail_conn, 'Bcc: ' || in_bcc|| UTL_TCP.crlf);

                          UTL_SMTP.write_data(l_mail_conn, 'From: ' || in_sender || UTL_TCP.crlf);

                          UTL_SMTP.write_data(l_mail_conn, 'Subject: ' || in_subject || UTL_TCP.crlf);

                          UTL_SMTP.write_data(l_mail_conn, 'Reply-To: ' || in_sender || UTL_TCP.crlf);

                          utl_smtp.write_data(l_mail_conn, 'MIME-Version: 1.0' || utl_tcp.crlf);

                          UTL_SMTP.write_data(l_mail_conn, 'Content-Type: multipart/mixed; boundary="' || co_msg_boundary || '"' || UTL_TCP.crlf || UTL_TCP.crlf);--Ref#3

                          if in_message is not null then

                            UTL_SMTP.write_data(l_mail_conn, '--' || co_msg_boundary || UTL_TCP.crlf);

                            UTL_SMTP.write_data(l_mail_conn, 'Content-Type: text/plain; charset="iso-8859-1"' || UTL_TCP.crlf || UTL_TCP.crlf);

                            UTL_SMTP.write_data(l_mail_conn, in_message);

                            UTL_SMTP.write_data(l_mail_conn, UTL_TCP.crlf || UTL_TCP.crlf);

                        END IF;

                          out_mail_conn:=l_mail_conn;

                          ELSE

                          RAISE e_invalid_email_add;

                          END IF;

                         

                         

                         

                        2. Add_mail_attachment-To add the attachment to the e-mail , the code is below

                         

                        PROCEDURE add_mail_attachment

                         

                                      (

                          

                                      in_att_mime_type  in  varchar2 character set any_cs default 'text/plain; charset=us-ascii',

                                      in_attachment     IN  BLOB ,

                                      in_att_file_name  in  varchar2 character set any_cs default null,

                                      in_mail_conn      IN  UTL_SMTP.CONNECTION

                                      )

                         

                        IS

                        l_mail_conn       utl_smtp.connection;

                        l_step            PLS_INTEGER  :=24573;

                        BEGIN

                         

                            l_mail_conn:=in_mail_conn;

                            if in_att_file_name is not null then

                              UTL_SMTP.write_data(l_mail_conn, '--' || co_msg_boundary || UTL_TCP.crlf);

                              utl_smtp.write_data(l_mail_conn, 'Content-Type: ' || in_att_mime_type || '; name="' || in_att_file_name || '"' || utl_tcp.crlf);

                              UTL_SMTP.write_data(l_mail_conn, 'Content-Transfer-Encoding: base64' || UTL_TCP.crlf);

                              UTL_SMTP.write_data(l_mail_conn, 'Content-Disposition: attachment; filename="' || in_att_file_name || '"' || UTL_TCP.crlf || UTL_TCP.crlf);

                         

                              for i in 0 .. trunc((dbms_lob.getlength(in_attachment) - 1 )/l_step) loop

                                     UTL_SMTP.write_data(l_mail_conn, DBMS_LOB.substr(in_attachment, l_step, i * l_step + 1));--UTL_SMTP.write_data(l_mail_conn, UTL_RAW.cast_to_varchar2(UTL_ENCODE.base64_encode(DBMS_LOB.substr(in_attachment, l_step, i * l_step + 1))));

                              END LOOP;

                              utl_smtp.write_data(l_mail_conn, utl_tcp.crlf || utl_tcp.crlf);

                        end if;

                        end;

                         

                        --I have skipped the exception part

                         

                        3.Close_mail () - This procedure closes the connection and sends the e-mail , the code is as follows

                         

                        PROCEDURE close_mail(

                                            in_mail_con IN  UTL_SMTP.CONNECTION

                                            )

                         

                        is

                        l_mail_conn       UTL_SMTP.CONNECTION;

                        begin

                            l_mail_conn:=in_mail_con;

                            UTL_SMTP.write_data(l_mail_conn, '--' || co_msg_boundary || '--' || utl_tcp.crlf);

                            --Indicates that the e-mail message is complete

                            UTL_SMTP.close_data(l_mail_conn);

                            --Quit the connection

                            UTL_SMTP.quit(l_mail_conn);

                        END;

                         

                        --This is the complete code , Can you suggest where i am erring?

                         

                        I am not able to send e-mails after attaching images.

                         

                        The e-mail is sent but the attachment is empty.

                         

                        I have skipped the exception part of the code

                        • 10. Re: Adding Image as attachment to e-mail UTL_SMTP
                          Raunaq

                          It's working guys.

                          Thanks for the suggestion.