1 2 Previous Next 17 Replies Latest reply: May 25, 2012 8:25 AM by Billy~Verreynne RSS

    How to put an image to any part of an e-mail using UTL_SMTP

    user49914949919675123
      We need to send an e-mail with the following format.
          ------------------------------------
      |COMPANY LOGO (JPEC IMAGE)          |
       ------------------------------------
      |                                    |
      |                                    |
      |              HTML table            |
      |                                    |
      |                                    |
       ------------------------------------
      The exact format is shown here: http://postimage.org/image/76v4e5tmd/
      Above the Automatic Payment Advice is the JPEG image.

      How do we CONSTRUCT THIS e-mail? Our DB is a 10g R2. We use UTL_SMTP. Problem is how to insert an image to any part of the e-mail (not as a separate attachment)?

      Edited by: Channa on May 24, 2012 5:51 AM
        • 1. Re: How to put an image to any part of an e-mail using UTL_SMTP
          Paul  Horth
          Not really anything to do with SQL or PL/SQL.

          Anyway, just embed it in an HTML img tag.

          The image tag should point to an url of the image.
          • 2. Re: How to put an image to any part of an e-mail using UTL_SMTP
            user49914949919675123
            Paul Horth wrote:
            Not really anything to do with SQL or PL/SQL.

            Anyway, just embed it in an HTML img tag.

            The image tag should point to an url of the image.
            Hi, thanks for the very quick reply. I can try this. But, I am thinking will the image be sent WITH THE MAIL?? I am saying this because for the image source we can do like this:
            <img src="c:\temp\header.bmp" alt="header" />
            My problem is, will the image be sent with the mail??? The recipient does not need to have the image in his c:\temp folder no???

            Edited by: Channa on May 24, 2012 6:09 AM
            • 3. Re: How to put an image to any part of an e-mail using UTL_SMTP
              Paul  Horth
              No, obviously the recipient won't have that image in his folder. That's why you need to provide an URL linking back to your image on your site.

              You can't send the image in the mail except by an attachment: which you don't want.
              • 4. Re: How to put an image to any part of an e-mail using UTL_SMTP
                user49914949919675123
                Paul Horth wrote:
                No, obviously the recipient won't have that image in his folder. That's why you need to provide an URL linking back to your image on your site.

                You can't send the image in the mail except by an attachment: which you don't want.
                Are you saying that the image must be "hosted" in a http server (in our servers) so that even outside people can access it using a URL like http://194.99.99.44:8080/temp/header.jpeg??

                I don't think this is possible since our customers wont allow it.

                What we want is to attach a JPEG in our DB server hard drive so that we can attach it to the top of the HTML table.

                Edited by: Channa on May 24, 2012 6:18 AM
                • 5. Re: How to put an image to any part of an e-mail using UTL_SMTP
                  Paul  Horth
                  >
                  Are you saying that the image must be "hosted" in a http server (in our servers) so that even outside people can access it using a URL like http://194.99.99.44:8080/temp/header.jpeg??
                  >

                  Yes. That's how it is normally done.

                  >
                  I don't think this is possible since our customers wont allow it.
                  >

                  Shame. OK, there is a sneaky method

                  You use
                  <img src="data:image/jpg;base64,<<your image in base 64 encoding>>" width="xx" height="yy" alt="your alt message" />
                  However this might annoy people as it stops the normal mechanism of your email asking whether you want to display images or not (I think).

                  Try and convince your customer that the URL method is what everyone else does.

                  Please read

                  http://en.wikipedia.org/wiki/Data_URI_scheme

                  In particular the "disadvantages" part.

                  Edited by: Paul Horth on 24-May-2012 06:33

                  Edited by: Paul Horth on 24-May-2012 06:44
                  • 6. Re: How to put an image to any part of an e-mail using UTL_SMTP
                    Saubhik
                    Yes. It is possible. Read this posts of Billy Verreynne to uderstand the MIME format.
                    Re: Sending HTML mail with inline/embeded images (My code is constructed on this input)
                    embeded image in email body in pl/sql
                    DECLARE
                      /*LOB operation related varriables */
                      v_src_loc  BFILE := BFILENAME('TEMP', 'otn.jpg');
                      l_buffer   RAW(54);
                      l_amount   BINARY_INTEGER := 54;
                      l_pos      INTEGER := 1;
                      l_blob     BLOB := EMPTY_BLOB;
                      l_blob_len INTEGER;
                      v_amount   INTEGER;
                    
                      /*UTL_SMTP related varriavles. */
                      v_connection_handle  UTL_SMTP.CONNECTION;
                      v_from_email_address VARCHAR2(30) := 'hr@xxx.com';
                      v_to_email_address   VARCHAR2(30) := 'saubbane@xxx.com';
                      v_smtp_host          VARCHAR2(30) := 'x.xxx.xxx.xxx'; --My mail server, replace it with yours.
                      v_subject            VARCHAR2(30) := 'Your Test Mail';
                      l_message            VARCHAR2(32767) := '<html>
                    <meta http-equiv=3DContent-Type content=3D"text/html; charset=3Dus-ascii">
                    <body background=3D"cid:otn.jpg@01CBB95B.9A1FD110">
                    ..rest of mail
                    </body>
                    </html>
                    ';
                    
                      /* This send_header procedure is written in the documentation */
                      PROCEDURE send_header(pi_name IN VARCHAR2, pi_header IN VARCHAR2) AS
                      BEGIN
                        UTL_SMTP.WRITE_DATA(v_connection_handle,
                                            pi_name || ': ' || pi_header || UTL_TCP.CRLF);
                      END;
                    
                    BEGIN
                      /*Preparing the LOB from file for attachment. */
                      DBMS_LOB.OPEN(v_src_loc, DBMS_LOB.LOB_READONLY); --Read the file
                      DBMS_LOB.CREATETEMPORARY(l_blob, TRUE); --Create temporary LOB to store the file.
                      v_amount := DBMS_LOB.GETLENGTH(v_src_loc); --Amount to store.
                      DBMS_LOB.LOADFROMFILE(l_blob, v_src_loc, v_amount); -- Loading from file into temporary LOB
                      l_blob_len := DBMS_LOB.getlength(l_blob);
                    
                      /*UTL_SMTP related coding. */
                      v_connection_handle := UTL_SMTP.OPEN_CONNECTION(host => v_smtp_host);
                      UTL_SMTP.HELO(v_connection_handle, v_smtp_host);
                      UTL_SMTP.MAIL(v_connection_handle, v_from_email_address);
                      UTL_SMTP.RCPT(v_connection_handle, v_to_email_address);
                      UTL_SMTP.OPEN_DATA(v_connection_handle);
                      send_header('From', '"Sender" <' || v_from_email_address || '>');
                      send_header('To', '"Recipient" <' || v_to_email_address || '>');
                      send_header('Subject', v_subject);
                    
                      --MIME header.
                      UTL_SMTP.WRITE_DATA(v_connection_handle,
                                          'MIME-Version: 1.0' || UTL_TCP.CRLF);
                      UTL_SMTP.WRITE_DATA(v_connection_handle,
                                          'Content-Type: multipart/related; ' || UTL_TCP.CRLF);
                      UTL_SMTP.WRITE_DATA(v_connection_handle,
                                          ' boundary= "' || 'SAUBHIK.SECBOUND' || '"' ||
                                          UTL_TCP.CRLF);
                      UTL_SMTP.WRITE_DATA(v_connection_handle, UTL_TCP.CRLF);
                    
                      -- Mail Body
                      UTL_SMTP.WRITE_DATA(v_connection_handle,
                                          '--' || 'SAUBHIK.SECBOUND' || UTL_TCP.CRLF);
                      UTL_SMTP.WRITE_DATA(v_connection_handle,
                                          'Content-Type: text/html;' || UTL_TCP.CRLF);
                      UTL_SMTP.WRITE_DATA(v_connection_handle,
                                          ' charset=US-ASCII' || UTL_TCP.CRLF);
                    UTL_SMTP.WRITE_DATA(v_connection_handle,
                                          'Content-Transfer-Encoding: quoted-printable' || UTL_TCP.CRLF);                      
                      UTL_SMTP.WRITE_DATA(v_connection_handle, UTL_TCP.CRLF);
                      UTL_SMTP.WRITE_DATA(v_connection_handle, l_message || UTL_TCP.CRLF);
                      UTL_SMTP.WRITE_DATA(v_connection_handle, UTL_TCP.CRLF);
                    
                      -- Mail Attachment
                      UTL_SMTP.WRITE_DATA(v_connection_handle,
                                          '--' || 'SAUBHIK.SECBOUND' || UTL_TCP.CRLF);
                      UTL_SMTP.WRITE_DATA(v_connection_handle,
                                          'Content-Disposition: inline; filename="otn.jpg"' || UTL_TCP.CRLF);
                      UTL_SMTP.WRITE_DATA(v_connection_handle,
                                          'Content-Type: image/jpg; name="otn.jpg"' ||
                                          UTL_TCP.CRLF);
                    UTL_SMTP.WRITE_DATA(v_connection_handle,
                                          'Content-ID: <otn.jpg@01CBB95B.9A1FD110>; ' ||
                                          UTL_TCP.CRLF);                      
                      UTL_SMTP.WRITE_DATA(v_connection_handle,
                                          'Content-Transfer-Encoding: base64' || UTL_TCP.CRLF);
                      UTL_SMTP.WRITE_DATA(v_connection_handle, UTL_TCP.CRLF);
                      UTL_SMTP.WRITE_DATA(v_connection_handle, UTL_TCP.CRLF);
                      
                      /* Writing the BLOL in chunks */
                      WHILE l_pos < l_blob_len LOOP
                        DBMS_LOB.READ(l_blob, l_amount, l_pos, l_buffer);
                        UTL_SMTP.write_raw_data(v_connection_handle,
                                                UTL_ENCODE.BASE64_ENCODE(l_buffer));
                                                
                        UTL_SMTP.WRITE_DATA(v_connection_handle, UTL_TCP.CRLF);
                        l_buffer := NULL;
                        l_pos    := l_pos + l_amount;
                      END LOOP;
                      UTL_SMTP.WRITE_DATA(v_connection_handle, UTL_TCP.CRLF);
                    
                      -- Close Email
                      UTL_SMTP.WRITE_DATA(v_connection_handle,
                                          '--' || 'SAUBHIK.SECBOUND' || '--' || UTL_TCP.CRLF);
                      UTL_SMTP.WRITE_DATA(v_connection_handle,
                                          UTL_TCP.CRLF || '.' || UTL_TCP.CRLF);
                    
                      UTL_SMTP.CLOSE_DATA(v_connection_handle);
                      UTL_SMTP.QUIT(v_connection_handle);
                      DBMS_LOB.FREETEMPORARY(l_blob);
                      DBMS_LOB.FILECLOSE(v_src_loc);
                    
                    EXCEPTION
                      WHEN OTHERS THEN
                        UTL_SMTP.QUIT(v_connection_handle);
                        DBMS_LOB.FREETEMPORARY(l_blob);
                        DBMS_LOB.FILECLOSE(v_src_loc);
                        RAISE;
                    END;
                    Otn logo is in my database server and It will embade otn logo all over the mail body!.

                    Edited by: Saubhik on May 24, 2012 9:06 PM

                    Changed the original IP and email address. I should have done this earlier!: Saubhik on May 25, 2012 11:20 AM
                    • 7. Re: How to put an image to any part of an e-mail using UTL_SMTP
                      user49914949919675123
                      Hello Saubhik. I created a stored procedure with your method. I cannot compile it since it gives this error:
                      http://s13.postimage.org/lylypgtlj/error.gif
                      • 8. Re: How to put an image to any part of an e-mail using UTL_SMTP
                        Billy~Verreynne
                        Come on!

                        How do you expect to be a developer if you are unable to fix an elementary syntax error??

                        You have syntax highlighting. You have a number of parameters being passed to send_header(). You have the call/parameter signature of send_header().

                        Use logic and fix it.

                        If you cannot, then you should reconsider your career choice. Syntax errors are insignificant issues in the life of a developer - there are 1000x more complex errors than a mere syntax issue that developers need to solve...
                        • 9. Re: How to put an image to any part of an e-mail using UTL_SMTP
                          Paul  Horth
                          Billy,

                          He's not a developer, he's a tech lead! :-)
                          • 10. Re: How to put an image to any part of an e-mail using UTL_SMTP
                            user49914949919675123
                            Hello, I tried this method: <img src="data:image/jpg;base64,<<your image in base 64 encoding>>" width="xx" height="yy" alt="your alt message" /> suggested by Paul Horth. It's really good and simple and works when I use the Windows SMTP server in our development env. But, when I deployed the code in the live (which uses a Linux SMTP server) it does not work. Images comes as blank. I tried adding boundaries but it did not work either. Why does it work on Windows and not on Linux?? Does not make sense?

                            To: Billy Verreynne: Please come to our 3rd would country and try work here. Then you understand. Please don't compare 1st world countries with 3rd world countries OK. There is a reason why we are called 3rd world no. We don't have "career choice" here. We are stuck with what we started with. No offense meant.

                            Edited by: Channa on May 25, 2012 2:47 AM

                            Edited by: Channa on May 25, 2012 2:49 AM
                            • 11. Re: How to put an image to any part of an e-mail using UTL_SMTP
                              Paul  Horth
                              Sorry, don't know why my suggestion doesn't work on Linux. I find it strange as it should just pass tags through.
                              • 12. Re: How to put an image to any part of an e-mail using UTL_SMTP
                                Billy~Verreynne
                                Channa wrote:
                                Hello, I tried this method: <img src="data:image/jpg;base64,<<your image in base 64 encoding>>" width="xx" height="yy" alt="your alt message" /> suggested by Paul Horth. It's really good and simple and works when I use the Windows SMTP server in our development env. But, when I deployed the code in the live (which uses a Linux SMTP server) it does not work. Images comes as blank. I tried adding boundaries but it did not work either. Why does it work on Windows and not on Linux?? Does not make sense?
                                Mime formatting (the set of specifications that governs e-mail standards) are platform and operating system agnostic. So whatever the problem is, it is with how your code formats the e-mail. Windows Exchange Server has a nasty habit of ripping the entire raw e-mail, which you provide for delivery, apart. It then reformats the mail, adds its own Mime boundaries, changes Mime types and so on. In your case, this could be fixing a Mime formatting you have in that mail. Or it is Outlook that (typical Microsoft) implements its own warped interpretation of the Mime specifications.
                                To: Billy Verreynne: Please come to our 3rd would country and try work here. Then you understand. Please don't compare 1st world countries with 3rd world countries OK. There is a reason why we are called 3rd world no. We don't have "career choice" here. We are stuck with what we started with. No offense meant.
                                What makes you think I'm in a 1st world country? It is 3rd world when I look out of the window here.

                                That said, my comment was not aimed at 3rd world/1st world or anything like that. It was about the basic syntax error that your screenshot showed. Something that even a developer with 1 years of experience should be able to fix. Heck, the quote was not closed and syntax highlighting clearly showed that the statement on the next line was in a different colour, indicating that it is not considered as a new program statement.

                                My comment was meant to be a wakeup call to pay closer attention to what the compiler error says, what the editor says, and to use basic logic and simple deduction to resolve it.
                                • 13. Re: How to put an image to any part of an e-mail using UTL_SMTP
                                  Paul  Horth
                                  Billy,

                                  I am hoping my proposal isn't that non-standard: it is covered by RFC 2557 http://www.ietf.org/rfc/rfc2557.txt
                                  MIME Encapsulation of Aggregate Documents, such as HTML (MHTML).

                                  However, whether everyone implements it, I don't know.
                                  • 14. Re: How to put an image to any part of an e-mail using UTL_SMTP
                                    user49914949919675123
                                    Hi Saubhik I tried your method also. That was not a syntax error. I had to put the 2 mails of the sender and recipient there. Only after reading the code I figured that out.      ;)

                                    Anyway I tried it, put the image in the folder in the DB machine, created the Oracle directory and specified in the place and ran. I get a mail but no image. The place for the image is empty, even outline of empty image is not showing.

                                    I also thought SMTP mail servers all worked the same way??? In Windows or Linux???

                                    My Exact code is here:
                                    PROCEDURE Saubhik2 IS
                                      /*LOB operation related varriables */
                                      v_src_loc  BFILE := BFILENAME('INS_IMAGES', 'PA-Mail-LOGO.JPG');
                                      l_buffer   RAW(54);
                                      l_amount   BINARY_INTEGER := 54;
                                      l_pos      INTEGER := 1;
                                      l_blob     BLOB := EMPTY_BLOB;
                                      l_blob_len INTEGER;
                                      v_amount   INTEGER;
                                    
                                      /*UTL_SMTP related varriavles. */
                                      v_connection_handle  UTL_SMTP.CONNECTION;
                                      v_from_email_address VARCHAR2(130) := 'xxxxx@abc.com';
                                      v_to_email_address   VARCHAR2(130) := 'yyyyy@abc.com';
                                      v_smtp_host          VARCHAR2(130) := '192.168.22.31'; --My mail server, replace it with yours.
                                      v_subject            VARCHAR2(130) := 'Your Test Mail';
                                      l_message            VARCHAR2(32767) := '<html>
                                    <meta http-equiv=3DContent-Type content=3D"text/html; charset=3Dus-ascii">
                                    <body background=3D"cid:otn.jpg@01CBB95B.9A1FD110">
                                    ..rest of mail
                                    </body>
                                    </html>
                                    ';
                                    
                                      /* This send_header procedure is written in the documentation */
                                      PROCEDURE send_header(pi_name IN VARCHAR2, pi_header IN VARCHAR2) AS
                                      BEGIN
                                        UTL_SMTP.WRITE_DATA(v_connection_handle,
                                                            pi_name || ': ' || pi_header || UTL_TCP.CRLF);
                                      END;
                                    
                                    BEGIN
                                      /*Preparing the LOB from file for attachment. */
                                      DBMS_LOB.OPEN(v_src_loc, DBMS_LOB.LOB_READONLY); --Read the file
                                      DBMS_LOB.CREATETEMPORARY(l_blob, TRUE); --Create temporary LOB to store the file.
                                      v_amount := DBMS_LOB.GETLENGTH(v_src_loc); --Amount to store.
                                      DBMS_LOB.LOADFROMFILE(l_blob, v_src_loc, v_amount); -- Loading from file into temporary LOB
                                      l_blob_len := DBMS_LOB.getlength(l_blob);
                                    
                                      /*UTL_SMTP related coding. */
                                      v_connection_handle := UTL_SMTP.OPEN_CONNECTION(host => v_smtp_host);
                                      UTL_SMTP.HELO(v_connection_handle, v_smtp_host);
                                      UTL_SMTP.MAIL(v_connection_handle, v_from_email_address);
                                      UTL_SMTP.RCPT(v_connection_handle, v_to_email_address);
                                      UTL_SMTP.OPEN_DATA(v_connection_handle);
                                      send_header('From', 'xxxxx@abc.com');
                                      send_header('To', 'yyyyy@abc.com');
                                      send_header('Subject', v_subject);
                                    
                                      --MIME header.
                                      UTL_SMTP.WRITE_DATA(v_connection_handle,
                                                          'MIME-Version: 1.0' || UTL_TCP.CRLF);
                                      UTL_SMTP.WRITE_DATA(v_connection_handle,
                                                          'Content-Type: multipart/related; ' || UTL_TCP.CRLF);
                                      UTL_SMTP.WRITE_DATA(v_connection_handle,
                                                          ' boundary= "' || 'SAUBHIK.SECBOUND' || '"' ||
                                                          UTL_TCP.CRLF);
                                      UTL_SMTP.WRITE_DATA(v_connection_handle, UTL_TCP.CRLF);
                                    
                                      -- Mail Body
                                      UTL_SMTP.WRITE_DATA(v_connection_handle,
                                                          '--' || 'SAUBHIK.SECBOUND' || UTL_TCP.CRLF);
                                      UTL_SMTP.WRITE_DATA(v_connection_handle,
                                                          'Content-Type: text/html;' || UTL_TCP.CRLF);
                                      UTL_SMTP.WRITE_DATA(v_connection_handle,
                                                          ' charset=US-ASCII' || UTL_TCP.CRLF);
                                    UTL_SMTP.WRITE_DATA(v_connection_handle,
                                                          'Content-Transfer-Encoding: quoted-printable' || UTL_TCP.CRLF);
                                      UTL_SMTP.WRITE_DATA(v_connection_handle, UTL_TCP.CRLF);
                                      UTL_SMTP.WRITE_DATA(v_connection_handle, l_message || UTL_TCP.CRLF);
                                      UTL_SMTP.WRITE_DATA(v_connection_handle, UTL_TCP.CRLF);
                                    
                                      -- Mail Attachment
                                      UTL_SMTP.WRITE_DATA(v_connection_handle,
                                                          '--' || 'SAUBHIK.SECBOUND' || UTL_TCP.CRLF);
                                      UTL_SMTP.WRITE_DATA(v_connection_handle,
                                                          'Content-Disposition: inline; filename="otn.jpg"' || UTL_TCP.CRLF);
                                      UTL_SMTP.WRITE_DATA(v_connection_handle,
                                                          'Content-Type: image/jpg; name="otn.jpg"' ||
                                                          UTL_TCP.CRLF);
                                    UTL_SMTP.WRITE_DATA(v_connection_handle,
                                                          'Content-ID: <otn.jpg@01CBB95B.9A1FD110>; ' ||
                                                          UTL_TCP.CRLF);
                                      UTL_SMTP.WRITE_DATA(v_connection_handle,
                                                          'Content-Transfer-Encoding: base64' || UTL_TCP.CRLF);
                                      UTL_SMTP.WRITE_DATA(v_connection_handle, UTL_TCP.CRLF);
                                      UTL_SMTP.WRITE_DATA(v_connection_handle, UTL_TCP.CRLF);
                                    
                                      /* Writing the BLOL in chunks */
                                      WHILE l_pos < l_blob_len LOOP
                                        DBMS_LOB.READ(l_blob, l_amount, l_pos, l_buffer);
                                        UTL_SMTP.write_raw_data(v_connection_handle,
                                                                UTL_ENCODE.BASE64_ENCODE(l_buffer));
                                    
                                        UTL_SMTP.WRITE_DATA(v_connection_handle, UTL_TCP.CRLF);
                                        l_buffer := NULL;
                                        l_pos    := l_pos + l_amount;
                                      END LOOP;
                                      UTL_SMTP.WRITE_DATA(v_connection_handle, UTL_TCP.CRLF);
                                    
                                      -- Close Email
                                      UTL_SMTP.WRITE_DATA(v_connection_handle,
                                                          '--' || 'SAUBHIK.SECBOUND' || '--' || UTL_TCP.CRLF);
                                      UTL_SMTP.WRITE_DATA(v_connection_handle,
                                                          UTL_TCP.CRLF || '.' || UTL_TCP.CRLF);
                                    
                                      UTL_SMTP.CLOSE_DATA(v_connection_handle);
                                      UTL_SMTP.QUIT(v_connection_handle);
                                      DBMS_LOB.FREETEMPORARY(l_blob);
                                      DBMS_LOB.FILECLOSE(v_src_loc);
                                    
                                    EXCEPTION
                                      WHEN OTHERS THEN
                                        UTL_SMTP.QUIT(v_connection_handle);
                                        DBMS_LOB.FREETEMPORARY(l_blob);
                                        DBMS_LOB.FILECLOSE(v_src_loc);
                                        RAISE;
                                    END;
                                    Edited by: Channa on May 25, 2012 5:56 AM

                                    Edited by: Channa on May 25, 2012 5:58 AM
                                    1 2 Previous Next