1 2 Previous Next 24 Replies Latest reply: Sep 27, 2013 10:05 PM by user13014223 RSS

    Sending UTL_SMTP mail with single jpg attachment issue

    533177
      Hi all,

      From last few days i'm struggling to send email with jpg attachment. Recently I manage to send mail with text in the message body but failed to send an attachment with that. I've created one table having one column of LONG RAW data type which stores my images. Now I want to attach this image along with mail body. I've used following code to send text email. I'm using following version of oracle

      Oracle Database 10g Release 10.2.0.1.0 - Production
      PL/SQL Release 10.2.0.1.0 - Production
      CORE    10.2.0.1.0      Production
      TNS for 32-bit Windows: Version 10.2.0.1.0 - Production
      NLSRTL Version 10.2.0.1.0 - Production

      Forms version : Forms [32 Bit] Version 10.1.2.0.2 (Production)

      My code goes like this ....

      DECLARE

      v_From       VARCHAR2(80) := 'abc@xyz.com';
      v_Recipient  VARCHAR2(80) := 'abc@xyz.com';
      v_Subject    VARCHAR2(80) := 'testsubject';
      v_Mail_Host  VARCHAR2(30) := 'mymailserver';
      v_Mail_Conn  utl_smtp.Connection;
      crlf         VARCHAR2(2)  := chr(13)||chr(10);

      BEGIN

      v_Mail_Conn := utl_smtp.Open_Connection(v_Mail_Host, 25);

      utl_smtp.Helo(v_Mail_Conn, v_Mail_Host);

      utl_smtp.Mail(v_Mail_Conn, v_From);

      utl_smtp.Rcpt(v_Mail_Conn, v_Recipient);

      utl_smtp.Data(v_Mail_Conn,
      *'Date: ' || to_char(sysdate, 'Dy, DD Mon YYYY hh24:mi:ss') || crlf ||*
      *'From: ' || v_From || crlf ||*
      *'Subject: '|| v_Subject || crlf ||*
      *'To: ' || v_Recipient || crlf ||*

      *'MIME-Version: 1.0'|| crlf ||     -- Use MIME mail standard*
      *'Content-Type: multipart/mixed;'|| crlf ||*
      *' boundary="-----SECBOUND"'|| crlf ||*
      crlf ||

      *'-------SECBOUND'|| crlf ||*
      *'Content-Type: text/plain;'|| crlf ||*
      *'Content-Transfer_Encoding: 7bit'|| crlf ||*
      crlf ||
      *'some message text'|| crlf ||     -- Message body*
      *'more message text'|| crlf ||*
      crlf ||

      *'-------SECBOUND'|| crlf ||*
      -- 'Content-Type: text/plain;'|| crlf ||
      *'Content-Type: image/jpg;'|| crlf ||*
      -- ' name="excel.csv"'|| crlf ||
      *' name="d:\photo\xyz.jpg"'|| crlf ||*
      *'Content-Transfer_Encoding: 8bit'|| crlf ||*
      *'Content-Disposition: attachment;'|| crlf ||*
      -- ' filename="excel.csv"'|| crlf ||
      *' filename="xyz.jpg"'|| crlf ||*
      crlf ||
      *'MY ATTACHMENT '|| crlf ||     -- Content of attachment*
      --'JPG,file,attachement'|| crlf ||     -- Content of attachment
      crlf ||

      *'-------SECBOUND--'               -- End MIME mail*
      *);*

      utl_smtp.Quit(v_mail_conn);

      EXCEPTION
      WHEN utl_smtp.Transient_Error OR utl_smtp.Permanent_Error then
      raise_application_error(-20000, 'Unable to send mail: '||sqlerrm);
      --message('Unable to send mail');message('Unable to send mail');*
      END;

      From the above code I've managed to send file with jpg attachment but with
      nothing in it. I know i need some logic to build the content of jpg attachment but no idea how to do it... Please guide me how can i attach LONG RAW database image.

      Any Help ...

      Warm Regards,
      Percy
        • 1. Re: Sending UTL_SMTP mail with single jpg attachment issue
          Saubhik
          Here is a complete working example.

          Here is my file (images) and DIRECTORY object setups.
          C:\>dir *.jpg
           Volume in drive C has no label.
           Volume Serial Number is 6806-ABBD
          
           Directory of C:\
          
          08/04/2004  10:30 AM            83,794 Waterlilies.jpg
          10/11/2010  05:27 PM           105,542 Winter.jpg
                         2 File(s)        189,336 bytes
                         0 Dir(s)   8,408,399,872 bytes free
          
          C:\>sqlplus / as sysdba
          
          SQL*Plus: Release 10.2.0.3.0 - Production on Tue Jan 11 17:42:37 2011
          
          Copyright (c) 1982, 2006, Oracle.  All Rights Reserved.
          
          
          Connected to:
          Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
          With the Partitioning, OLAP and Data Mining options
          
          SQL> /* I have already created DIRECTORY OBJECT */
          SQL> SELECT directory_name,directory_path FROM dba_directories
            2  WHERE directory_name='SAUBHIK';
          
          DIRECTORY_NAME
          ------------------------------
          DIRECTORY_PATH
          ------------------------------------------------------------------------
          
          SAUBHIK
          C:\
          
          
          SQL>
          Now my actual code.
          SQL> conn scott@ORCLSB
          Enter password: *****
          Connected.
          SQL> DECLARE
            2    /*LOB operation related varriables */
            3    v_src_loc  BFILE := BFILENAME('SAUBHIK', 'Waterlilies.jpg');
            4    l_buffer   RAW(54);
            5    l_amount   BINARY_INTEGER := 54;
            6    l_pos      INTEGER := 1;
            7    l_blob     BLOB := EMPTY_BLOB;
            8    l_blob_len INTEGER;
            9    v_amount   INTEGER;
           10  
           11    /*UTL_SMTP related varriavles. */
           12    v_connection_handle  UTL_SMTP.CONNECTION;
           13    v_from_email_address VARCHAR2(30) := 'aaaa@bb.com';
           14    v_to_email_address   VARCHAR2(30) := 'xxxx@yy.com';
           15    v_smtp_host          VARCHAR2(30) := '9.182.156.144'; --My mail server, replace it with yours.
           16    v_subject            VARCHAR2(30) := 'Your Test Mail';
           17    l_message            VARCHAR2(200) := 'This is test mail using UTL_SMTP';
           18  
           19    /* This send_header procedure is written in the documentation */
           20    PROCEDURE send_header(pi_name IN VARCHAR2, pi_header IN VARCHAR2) AS
           21    BEGIN
           22      UTL_SMTP.WRITE_DATA(v_connection_handle,
           23                          pi_name || ': ' || pi_header || UTL_TCP.CRLF);
           24    END;
           25  
           26  BEGIN
           27    /*Preparing the LOB from file for attachment. */
           28    DBMS_LOB.OPEN(v_src_loc, DBMS_LOB.LOB_READONLY); --Read the file
           29    DBMS_LOB.CREATETEMPORARY(l_blob, TRUE); --Create temporary LOB to store the file.
           30    v_amount := DBMS_LOB.GETLENGTH(v_src_loc); --Amount to store.
           31    DBMS_LOB.LOADFROMFILE(l_blob, v_src_loc, v_amount); -- Loading from file into temporary LOB
           32    l_blob_len := DBMS_LOB.getlength(l_blob);
           33  
           34    /*UTL_SMTP related coding. */
           35    v_connection_handle := UTL_SMTP.OPEN_CONNECTION(host => v_smtp_host);
           36    UTL_SMTP.HELO(v_connection_handle, v_smtp_host);
           37    UTL_SMTP.MAIL(v_connection_handle, v_from_email_address);
           38    UTL_SMTP.RCPT(v_connection_handle, v_to_email_address);
           39    UTL_SMTP.OPEN_DATA(v_connection_handle);
           40    send_header('From', '"Sender" <' || v_from_email_address || '>');
           41    send_header('To', '"Recipient" <' || v_to_email_address || '>');
           42    send_header('Subject', v_subject);
           43  
           44    --MIME header.
           45    UTL_SMTP.WRITE_DATA(v_connection_handle,
           46                        'MIME-Version: 1.0' || UTL_TCP.CRLF);
           47    UTL_SMTP.WRITE_DATA(v_connection_handle,
           48                        'Content-Type: multipart/mixed; ' || UTL_TCP.CRLF);
           49    UTL_SMTP.WRITE_DATA(v_connection_handle,
           50                        ' boundary= "' || 'SAUBHIK.SECBOUND' || '"' ||
           51                        UTL_TCP.CRLF);
           52    UTL_SMTP.WRITE_DATA(v_connection_handle, UTL_TCP.CRLF);
           53  
           54    -- Mail Body
           55    UTL_SMTP.WRITE_DATA(v_connection_handle,
           56                        '--' || 'SAUBHIK.SECBOUND' || UTL_TCP.CRLF);
           57    UTL_SMTP.WRITE_DATA(v_connection_handle,
           58                        'Content-Type: text/plain;' || UTL_TCP.CRLF);
           59    UTL_SMTP.WRITE_DATA(v_connection_handle,
           60                        ' charset=US-ASCII' || UTL_TCP.CRLF);
           61    UTL_SMTP.WRITE_DATA(v_connection_handle, UTL_TCP.CRLF);
           62    UTL_SMTP.WRITE_DATA(v_connection_handle, l_message || UTL_TCP.CRLF);
           63    UTL_SMTP.WRITE_DATA(v_connection_handle, UTL_TCP.CRLF);
           64  
           65    -- Mail Attachment
           66    UTL_SMTP.WRITE_DATA(v_connection_handle,
           67                        '--' || 'SAUBHIK.SECBOUND' || UTL_TCP.CRLF);
           68    UTL_SMTP.WRITE_DATA(v_connection_handle,
           69                        'Content-Type: application/octet-stream' ||
           70                        UTL_TCP.CRLF);
           71    UTL_SMTP.WRITE_DATA(v_connection_handle,
           72                        'Content-Disposition: attachment; ' || UTL_TCP.CRLF);
           73    UTL_SMTP.WRITE_DATA(v_connection_handle,
           74                        ' filename="' || 'Waterlilies.jpg' || '"' || --My filename
           75                        UTL_TCP.CRLF);
           76    UTL_SMTP.WRITE_DATA(v_connection_handle,
           77                        'Content-Transfer-Encoding: base64' || UTL_TCP.CRLF);
           78    UTL_SMTP.WRITE_DATA(v_connection_handle, UTL_TCP.CRLF);
           79  /* Writing the BLOL in chunks */
           80    WHILE l_pos < l_blob_len LOOP
           81      DBMS_LOB.READ(l_blob, l_amount, l_pos, l_buffer);
           82      UTL_SMTP.write_raw_data(v_connection_handle,
           83                              UTL_ENCODE.BASE64_ENCODE(l_buffer));
           84      UTL_SMTP.WRITE_DATA(v_connection_handle, UTL_TCP.CRLF);
           85      l_buffer := NULL;
           86      l_pos    := l_pos + l_amount;
           87    END LOOP;
           88    UTL_SMTP.WRITE_DATA(v_connection_handle, UTL_TCP.CRLF);
           89  
           90    -- Close Email
           91    UTL_SMTP.WRITE_DATA(v_connection_handle,
           92                        '--' || 'SAUBHIK.SECBOUND' || '--' || UTL_TCP.CRLF);
           93    UTL_SMTP.WRITE_DATA(v_connection_handle,
           94                        UTL_TCP.CRLF || '.' || UTL_TCP.CRLF);
           95  
           96    UTL_SMTP.CLOSE_DATA(v_connection_handle);
           97    UTL_SMTP.QUIT(v_connection_handle);
           98    DBMS_LOB.FREETEMPORARY(l_blob);
           99    DBMS_LOB.FILECLOSE(v_src_loc);
          100  
          101  EXCEPTION
          102    WHEN OTHERS THEN
          103      UTL_SMTP.QUIT(v_connection_handle);
          104      DBMS_LOB.FREETEMPORARY(l_blob);
          105      DBMS_LOB.FILECLOSE(v_src_loc);
          106      RAISE;
          107  END;
          108  /
          
          PL/SQL procedure successfully completed.
          
          SQL> 
          By the way. You wrote
          having one column of LONG RAW data type which stores my images.
          . I think you store your image in BLOB column and then use this code (instead of BFILE) to write that BLOB into mail.

          Edited by: Saubhik on Jan 11, 2011 5:58 PM
          • 2. Re: Sending UTL_SMTP mail with single jpg attachment issue
            533177
            Hi saubik,
            Thanx for reply... does your code works with long raw datatype? My images were stored in database as a long raw datatype not locally. Any help in this regard.
            Percy
            • 3. Re: Sending UTL_SMTP mail with single jpg attachment issue
              Saubhik
              Long raw is not recommended. Try to use BLOB instead of that. A small demo may be like this:
              SQL> desc test_long_raw
               Name                                      Null?    Type
               ----------------------------------------- -------- ----------------------------
               ID                                                 NUMBER(38)
               IMAGE                                              LONG RAW
              
              SQL> SELECT * FROM test_long_raw;
              
                      ID I
              ---------- -
                       1 F
              
              SQL> CREATE TABLE test_long_raw_to_blob(id INTEGER,image BLOB);
              
              Table created.
              
              SQL> INSERT INTO test_long_raw_to_blob SELECT id,TO_LOB(image) FROM test_long_raw;
              
              1 row created.
              
              SQL> commit;
              
              Commit complete.
              
              SQL> DECLARE
                2    /*LOB operation related varriables */
                3    --v_src_loc  BFILE := BFILENAME('SAUBHIK', 'Waterlilies.jpg');
                4    v_src_loc BLOB :=EMPTY_BLOB;
                5    l_buffer   RAW(54);
                6    l_amount   BINARY_INTEGER := 54;
                7    l_pos      INTEGER := 1;
                8    l_blob     BLOB := EMPTY_BLOB;
                9    l_blob_len INTEGER;
               10    v_amount   INTEGER;
               11  
               12    /*UTL_SMTP related varriavles. */
               13    v_connection_handle  UTL_SMTP.CONNECTION;
               14    v_from_email_address VARCHAR2(30) := 'xxr@iyyyyy.com';
               15    v_to_email_address   VARCHAR2(30) := 'zzzzzz@vvvvvv.com';
               16    v_smtp_host          VARCHAR2(30) := '9.182.156.144'; --My mail server, replace it with yours.
               17    v_subject            VARCHAR2(30) := 'Your Test Mail';
               18    l_message            VARCHAR2(200) := 'This is test mail using UTL_SMTP';
               19  
               20    /* This send_header procedure is written in the documentation */
               21    PROCEDURE send_header(pi_name IN VARCHAR2, pi_header IN VARCHAR2) AS
               22    BEGIN
               23      UTL_SMTP.WRITE_DATA(v_connection_handle,
               24                          pi_name || ': ' || pi_header || UTL_TCP.CRLF);
               25    END;
               26  
               27  BEGIN
               28    /*Preparing the LOB from table for attachment. */
               29    SELECT image
               30    INTO l_blob
               31    FROM test_long_raw_to_blob
               32    WHERE id=1;
               33    --DBMS_LOB.OPEN(v_src_loc, DBMS_LOB.LOB_READONLY); --Read the file
               34    --DBMS_LOB.CREATETEMPORARY(l_blob, TRUE); --Create temporary LOB to store the file.
               35    --v_amount := DBMS_LOB.GETLENGTH(v_src_loc); --Amount to store.
               36    --DBMS_LOB.LOADFROMFILE(l_blob, v_src_loc, v_amount); -- Loading from file into temporary LOB
               37    l_blob_len := DBMS_LOB.getlength(l_blob);
               38  
               39    /*UTL_SMTP related coding. */
               40    v_connection_handle := UTL_SMTP.OPEN_CONNECTION(host => v_smtp_host);
               41    UTL_SMTP.HELO(v_connection_handle, v_smtp_host);
               42    UTL_SMTP.MAIL(v_connection_handle, v_from_email_address);
               43    UTL_SMTP.RCPT(v_connection_handle, v_to_email_address);
               44    UTL_SMTP.OPEN_DATA(v_connection_handle);
               45    send_header('From', '"Sender" <' || v_from_email_address || '>');
               46    send_header('To', '"Recipient" <' || v_to_email_address || '>');
               47    send_header('Subject', v_subject);
               48  
               49    --MIME header.
               50    UTL_SMTP.WRITE_DATA(v_connection_handle,
               51                        'MIME-Version: 1.0' || UTL_TCP.CRLF);
               52    UTL_SMTP.WRITE_DATA(v_connection_handle,
               53                        'Content-Type: multipart/mixed; ' || UTL_TCP.CRLF);
               54    UTL_SMTP.WRITE_DATA(v_connection_handle,
               55                        ' boundary= "' || 'SAUBHIK.SECBOUND' || '"' ||
               56                        UTL_TCP.CRLF);
               57    UTL_SMTP.WRITE_DATA(v_connection_handle, UTL_TCP.CRLF);
               58    -- Mail Body
               59    UTL_SMTP.WRITE_DATA(v_connection_handle,
               60                        '--' || 'SAUBHIK.SECBOUND' || UTL_TCP.CRLF);
               61    UTL_SMTP.WRITE_DATA(v_connection_handle,
               62                        'Content-Type: text/plain;' || UTL_TCP.CRLF);
               63    UTL_SMTP.WRITE_DATA(v_connection_handle,
               64                        ' charset=US-ASCII' || UTL_TCP.CRLF);
               65    UTL_SMTP.WRITE_DATA(v_connection_handle, UTL_TCP.CRLF);
               66    UTL_SMTP.WRITE_DATA(v_connection_handle, l_message || UTL_TCP.CRLF);
               67    UTL_SMTP.WRITE_DATA(v_connection_handle, UTL_TCP.CRLF);
               68  
               69    -- Mail Attachment
               70    UTL_SMTP.WRITE_DATA(v_connection_handle,
               71                        '--' || 'SAUBHIK.SECBOUND' || UTL_TCP.CRLF);
               72    UTL_SMTP.WRITE_DATA(v_connection_handle,
               73                        'Content-Type: application/octet-stream' ||
               74                        UTL_TCP.CRLF);
               75    UTL_SMTP.WRITE_DATA(v_connection_handle,
               76                        'Content-Disposition: attachment; ' || UTL_TCP.CRLF);
               77    UTL_SMTP.WRITE_DATA(v_connection_handle,
               78                        ' filename="' || 'Waterlilies.jpg' || '"' || --My filename
               79                        UTL_TCP.CRLF);
               80    UTL_SMTP.WRITE_DATA(v_connection_handle,
               81                        'Content-Transfer-Encoding: base64' || UTL_TCP.CRLF);
               82    UTL_SMTP.WRITE_DATA(v_connection_handle, UTL_TCP.CRLF);
               83  
               84    /* Writing the BLOL in chunks */
               85    WHILE l_pos < l_blob_len LOOP
               86      DBMS_LOB.READ(l_blob, l_amount, l_pos, l_buffer);
               87      UTL_SMTP.write_raw_data(v_connection_handle,
               88                              UTL_ENCODE.BASE64_ENCODE(l_buffer));
               89      UTL_SMTP.WRITE_DATA(v_connection_handle, UTL_TCP.CRLF);
               90      l_buffer := NULL;
               91      l_pos    := l_pos + l_amount;
               92    END LOOP;
               93    UTL_SMTP.WRITE_DATA(v_connection_handle, UTL_TCP.CRLF);
               94  
               95    -- Close Email
               96    UTL_SMTP.WRITE_DATA(v_connection_handle,
               97                        '--' || 'SAUBHIK.SECBOUND' || '--' || UTL_TCP.CRLF);
               98    UTL_SMTP.WRITE_DATA(v_connection_handle,
               99                        UTL_TCP.CRLF || '.' || UTL_TCP.CRLF);
              100  
              101    UTL_SMTP.CLOSE_DATA(v_connection_handle);
              102    UTL_SMTP.QUIT(v_connection_handle);
              103    DBMS_LOB.FREETEMPORARY(l_blob);
              104    --DBMS_LOB.FILECLOSE(v_src_loc);
              105  
              106  EXCEPTION
              107    WHEN OTHERS THEN
              108      UTL_SMTP.QUIT(v_connection_handle);
              109      DBMS_LOB.FREETEMPORARY(l_blob);
              110      --DBMS_LOB.FILECLOSE(v_src_loc);
              111      RAISE;
              112  END;
              113  /
              • 4. Re: Sending UTL_SMTP mail with single jpg attachment issue
                533177
                hi Saubhik,

                I'm trying your code with my form ,when i try to compile the form I'm getting following error

                implementation restriction : cannot directly access 'UTL_TCP.CRLF' remote package variable or cursor

                Can u figureout wats going wrong here.

                Percy
                • 5. Re: Sending UTL_SMTP mail with single jpg attachment issue
                  Saubhik
                  Define a variable like crlf varchar2(2) := chr(10)||chr(13); and use that in place of UTL_TCP.CRLF.
                  • 6. Re: Sending UTL_SMTP mail with single jpg attachment issue
                    533177
                    hi Saubhik ,

                    Thanx for ur reply . Mails are getting generated with the following output


                    --SAUBHIK.SECBOUND
                    Content-Type: text/plain;
                    charset=US-ASCII

                    This is test mail using UTL_SMTP

                    --SAUBHIK.SECBOUND
                    Content-Type: application/octet-stream
                    Content-Disposition: attachment;
                    filename="Waterlilies.jpg"
                    Content-Transfer-Encoding: base64

                    /9j/4AAQSkZJRgABAQEAAAAAAAD/2wCEABALDA4MChAODQ4SERATGCgaGBYWGDEj
                    JR0oOjM9
                    PDkzODdASFxOQERXRTc4UG1RV19iZ2hnPk1xeXBkeFxlZ2MBERISGBUYLxoaL2NC
                    OEJjY2Nj
                    Y2NjY2NjY2NjY2NjY2NjY2NjY2NjY2NjY2NjY2NjY2NjY2NjY2NjY2NjY2NjY//E
                    AaIAAAEF
                    AUAFMBKA
                    CgApAFMAoAKACgApiCgBKACgAoAKAEoAKAFoAKACgAoAKACkAUALQMKACgAoAKAC
                    kAUwFpAF
                    ABTAKACkAUAJQA0tTEML0CGbqCbic0+UjnDbV8hk6wYrRQOadbsP5Ucsa0sczk2N
                    LigBvm+1
                    K4B+A/CgBtAC7uOeKAHKu75d1AhCm0c0AR9O36UhibiaQCUAJxQAhx2NACZIoATc
                    O9ACH5ea
                    GGw9DuqDoi7kc3X2poznuNXoKBRHfdYYpGnwbFyCX+En6Vm0d1OpctioOkKBi0AJ
                    QAtACUAF
                    ABQAUAFAgoAKAEpgFABQAtIBKYBQAUxBSGFMQlABQAUAFABQAlAC0AJQAtABQAUg
                    CgBaACmA
                    UhhQAtACUgFpgJSAWgBKAFoASgBaAGE0xEZagRHupEhRYm4YrRQMpVEheM1soHFO
                    s2SpDk85
                    /CrsY3GyLs4NAiIZbgUAHl/71KwCfdoGAoAA3tQAh64DUALhh35oEAeQd6AFaQt7
                    0DGfUYoA
                    bUgNoAT8RSATP0pgJnPUUgEz/wDqpgIp2mkOLCQ9KSKqAfucUySMc4zQMsRffFQz
                    opS6GjH0
                    rI9KI+
                    GsTuiSVJqLQAUAJQAUCCgAoAKAEoAWmAUgCmAlABTAWkAlMQUAFACUAFABQAUALQ
                    AUAFABQA
                    gAoAKACg
                    ApgFAgoAKACgAoASgApiCgYUCCgBKAFoAKQwoAKACgAoAWgBKAFpAJQAtABQA1ul
                    MRXekQxl
                    BIUAHY1USZLQgiXLe1dUTypE8r7Ywq0xBafe3elAGlHyAPWkWjTjXCBfWsTUtL6V
                    JZMtAyZe
                    lICRRQA+kA8CgB+KADFACYpDG4pDEpARsPSgZHtpDFxxQITvQMdsNUIb5XFKw7ih
                    aBBtoAYV
                    oERMcDmgCFn6e9MCJj+dAEW6mISgYlIBjCmBn3MXcVojJoyJVIJWtUc7Is5oEKxz
                    QBLabfPA
                    PNZyOrD25jUrE9UKACgYUwCkIKAEoAKACgAoAKBBTAKQBTAKQCVQBQAUCCgYUCEo
                    AWgApDCg
                    AoAKACgBaACgApAFABQAUAIelMCs9BmxlIkKAHAZ6mmhMiXo3HFdaPJluMyGlyea
                    CSaDigZp
                    W5+akzRGpDwnuayNS3GKkonVaQyWgB60ASCgQu9VpgP3UABxQA3NSMWgBtIYdqYD
                    MCkMFAxS
                    AXAzVAKPloEN7UARMwxmkMie4UfWmIqyXag/e4osK5Vkv0bjPFVyk8xWkvtx+Xmq
                    UCeYj+3c
                    c5zT5Rcw1bjJosNSLMUu6s2i0yTNIoKQyBlzVCMbUIdkmRWiOeaKHTmqMiY7duPW
                    qAhiJSQH
                    0qGaUt7mvE25AawPYg7okpFhQAUDCgQlABQAUAFABQIKBhQIKYBQAUgCmAlMQUDC
                    kAlMQUAL
                    QAUAFAwpAFMBaQBQAUAFIAoAKACgBD0pgVn60jMZQSFACjpVIlkW/b1FdSPKluNH
                    BLY/Cgkk
                    t/lYZoGjTsxubmpZrE2oV6CsjYuKuDSGTDmkAtMBy0CGzXCximIpfbkHNMQw6ifX
                    c0LOZl6nNQzVGitx/FntWZRMt0oHXikaJXGvfxgVNzZUmQNd7sYpoUoWIC2Qc1oc
                    zKEsm1cG
                    rIM9yztkVZkIts7HpQKxct9NLHLHFLmLUDSSwRR61HOXyD/saqOBS5h8pC9l/kUc
                    wcpWNuE6
                    ZqhWI9uHyKALqn5KgsbQMbIuRTQMw5flc4rZHIyEKN/NMkbt2yipkaU/iNZDla5z
                    2kLSKCgQ
                    UDCmAlIQtABQAlABQAUAFMQUAFABQAUAJTAKQBTEFABQAUAFABSGFAC0AFABQAlA
                    C0AFIAoA
                    KAGt0pgV3pGYykSFMBwq4kTQ07Uz3rpR5k9xud1MkuWyjn8qRUTa0+MdaxZ0I0e1
                    QUSL0oAk
                    X0piGXD7UpiM18yH5vwFUSRmPaKAIxH/ANMzQBKtqjD5kxS5gsI2mxfw0c4chWfS
                    4xRzhyFZ
                    tO54NVzE8g0WTJ70rj5SaMPH9KRSLYPrUFjxHuHFSzaDsJ9mb1qTp9oKIuMUGM5X
                    I3JWtDmK
                    bRGY1RAv2dU60wsO6dBigdheE+/JQAz7TGOjsKOUXMSLdEfcl3fWp5Srk63gbhxt
                    NTyjuO+U
                    0wIjGPSncQi/KMUDHUhgelAGLdLi4raJyzKxHWrIFP3kqZFw3NFOBXMe2haRQUCC
                    gAoGFAhK
                    EtIZIh5pAWUNAyYGgBwNIB1IANAxtACZ4oAjZqAK8nK00BWe5wtIoyLxZ5BuQtj2
                    rSNjKVyh
                    bX19BPj5nX0NXZGSlJG/Dcb1BIKmsWjoTJTN71FiypPMW4WrUSGzH+z3Et5mRuK2
                    2MOVtmwI
                    I0i96zubJDEJJ9hUlFyJ81IFlGpDJRQAMeKQyBhSAiNMQ2gRWlNWiSq5q0Ipyj5v
                    atEYsswu
                    EiqWa0ys3Wsj0oqwlBQtAxKACgApCCgAoAKACgBKYgpDCmIKACgAoAKACgApgJQI
                    KACgApgF
                    IAoAKQxaACgAoAKACgAoAKACgAoAQ9KAK7igzZHSJCgCzZXLW8wYVSIaOpQi8twy
                    nmhox2I1
                    tSEJ3c0rBczZf+Pj5uMVvAwqFqzOVNJhEuioNBVpAWENAydTQA8UgHigA7UhjO1I
                    oAKACmAl
                    jFaGZfs6
                    j++Kgst/
                    KyB0O5D3qRkDcGmgZH0NWQTJyKkoWpGKMMKYDGipiDacYpgM2GgBnSgA/hzUgIDQ
                    MeuTSAlE
                    KAFFIBaY
                    CGkAKKYDu1AiBqBiikAtADhTAkFIBaAIZVpgVxVCJlakA8GgBJPu49aAKMnOaBjY
                    OtKQRLqH
                    n73Tv61Nx2BW3NREJDiKoQuOKAISPnoAWkMXFAhwoAQ0AMFMY+kAgpiEk+7SGRpQ
                    BMKAEoAK
                    BC0AFAxKAFoASgApAOpgFADSKAIWFIAU0ASZpgMn6CgRC9WSMTrSKRbf/U0gM4fP
                    cAdhVkF7
                    oKgs/9k=

                    SAUBHIK.SECBOUND

                    .



                    Do I need to make any changes . Also the error generated at form level is
                    button press unhandled exception ora-29278

                    Thanx...

                    Percy

                    Edited by: PERCY2 on Jan 13, 2011 1:00 AM
                    • 7. Re: Sending UTL_SMTP mail with single jpg attachment issue
                      533177
                      Hi Saubhik ,

                      Can u help me? Waiting for reply..

                      Percy
                      • 8. Re: Sending UTL_SMTP mail with single jpg attachment issue
                        Saubhik
                        Check your MIME format again for attachment. For ora-29278 check your SMTP server details. This is another version of the above code, which is working fine in my setup!. You can try it again (by changing the UTL_TCP.CRLF ! as suggested earlier)
                        SQL> 
                        SQL> CREATE TABLE test_long_raw(id INTEGER, image LONG RAW);
                        
                        Table created.
                        
                        SQL> DECLARE
                          2   v_return NUMBER;
                          3  BEGIN
                          4   v_return:=LoadLongRawFile('C:\Waterlilies.jpg');
                          5  END;
                          6  /
                        
                        PL/SQL procedure successfully completed.
                        
                        SQL> SELECT * FROM test_long_raw;
                        
                                ID I
                        ---------- -
                                 1 F
                        
                        SQL> CREATE TABLE test_long_raw_to_blob(id INTEGER,image BLOB);
                        
                        Table created.
                        
                        SQL> INSERT INTO test_long_raw_to_blob SELECT id,TO_LOB(image) FROM test_long_raw;
                        
                        1 row created.
                        
                        SQL> commit;
                        
                        Commit complete.
                        
                        SQL> SELECT id,DBMS_LOB.getlength(image) FROM test_long_raw_to_blob;
                        
                                ID DBMS_LOB.GETLENGTH(IMAGE)
                        ---------- -------------------------
                                 1                     83794
                        
                        SQL> DECLARE
                          2    /*LOB operation related varriables */
                          3    --v_src_loc  BFILE := BFILENAME('SAUBHIK', 'Waterlilies.jpg');
                          4    v_src_loc BLOB ;
                          5    l_buffer   RAW(54);
                          6    l_amount   BINARY_INTEGER := 54;
                          7    l_pos      INTEGER := 1;
                          8    l_blob     BLOB := EMPTY_BLOB;
                          9    l_blob_len INTEGER;
                         10    v_amount   INTEGER;
                         11   
                         12    /*UTL_SMTP related varriavles. */
                         13    v_connection_handle  UTL_SMTP.CONNECTION;
                         14    v_from_email_address VARCHAR2(30) := 'xxr@iyyyyy.com';
                         15    v_to_email_address   VARCHAR2(30) := 'xxxx@izzzzz.com';
                         16    v_smtp_host          VARCHAR2(30) := '9.182.156.144'; --My mail server, replace it with yours.
                         17    v_subject            VARCHAR2(30) := 'Your Test Mail';
                         18    l_message            VARCHAR2(200) := 'This is test mail using UTL_SMTP';
                         19   
                         20    /* This send_header procedure is written in the documentation */
                         21    PROCEDURE send_header(pi_name IN VARCHAR2, pi_header IN VARCHAR2) AS
                         22    BEGIN
                         23      UTL_SMTP.WRITE_DATA(v_connection_handle,
                         24                          pi_name || ': ' || pi_header || UTL_TCP.CRLF);
                         25    END;
                         26   
                         27   BEGIN
                         28    /*Preparing the LOB from table for attachment. */
                         29    SELECT image
                         30    INTO v_src_loc
                         31    FROM test_long_raw_to_blob
                         32    WHERE id=1;
                         33    
                         34    DBMS_LOB.CREATETEMPORARY(l_blob, TRUE); --Create temporary LOB to store the file.
                         35    v_amount := DBMS_LOB.GETLENGTH(v_src_loc); --Amount to store.
                         36    --DBMS_LOB.LOADFROMFILE(l_blob, v_src_loc, v_amount); -- Loading from file into temporary LOB
                         37    DBMS_LOB.COPY(l_blob,v_src_loc,v_amount);
                         38    l_blob_len := DBMS_LOB.getlength(l_blob);
                         39   
                         40    /*UTL_SMTP related coding. */
                         41    v_connection_handle := UTL_SMTP.OPEN_CONNECTION(host => v_smtp_host);
                         42    UTL_SMTP.HELO(v_connection_handle, v_smtp_host);
                         43    UTL_SMTP.MAIL(v_connection_handle, v_from_email_address);
                         44    UTL_SMTP.RCPT(v_connection_handle, v_to_email_address);
                         45    UTL_SMTP.OPEN_DATA(v_connection_handle);
                         46    send_header('From', '"Sender" <' || v_from_email_address || '>');
                         47    send_header('To', '"Recipient" <' || v_to_email_address || '>');
                         48    send_header('Subject', v_subject);
                         49   
                         50    --MIME header.
                         51    UTL_SMTP.WRITE_DATA(v_connection_handle,
                         52                        'MIME-Version: 1.0' || UTL_TCP.CRLF);
                         53    UTL_SMTP.WRITE_DATA(v_connection_handle,
                         54                        'Content-Type: multipart/mixed; ' || UTL_TCP.CRLF);
                         55    UTL_SMTP.WRITE_DATA(v_connection_handle,
                         56                        ' boundary= "' || 'SAUBHIK.SECBOUND' || '"' ||
                         57                        UTL_TCP.CRLF);
                         58    UTL_SMTP.WRITE_DATA(v_connection_handle, UTL_TCP.CRLF);
                         59    -- Mail Body
                         60    UTL_SMTP.WRITE_DATA(v_connection_handle,
                         61                        '--' || 'SAUBHIK.SECBOUND' || UTL_TCP.CRLF);
                         62    UTL_SMTP.WRITE_DATA(v_connection_handle,
                         63                        'Content-Type: text/plain;' || UTL_TCP.CRLF);
                         64    UTL_SMTP.WRITE_DATA(v_connection_handle,
                         65                        ' charset=US-ASCII' || UTL_TCP.CRLF);
                         66    UTL_SMTP.WRITE_DATA(v_connection_handle, UTL_TCP.CRLF);
                         67    UTL_SMTP.WRITE_DATA(v_connection_handle, l_message || UTL_TCP.CRLF);
                         68    UTL_SMTP.WRITE_DATA(v_connection_handle, UTL_TCP.CRLF);
                         69   
                         70    -- Mail Attachment
                         71    UTL_SMTP.WRITE_DATA(v_connection_handle,
                         72                        '--' || 'SAUBHIK.SECBOUND' || UTL_TCP.CRLF);
                         73    UTL_SMTP.WRITE_DATA(v_connection_handle,
                         74                        'Content-Type: application/octet-stream' ||
                         75                        UTL_TCP.CRLF);
                         76    UTL_SMTP.WRITE_DATA(v_connection_handle,
                         77                        'Content-Disposition: attachment; ' || UTL_TCP.CRLF);
                         78    UTL_SMTP.WRITE_DATA(v_connection_handle,
                         79                        ' filename="' || 'Waterlilies.jpg' || '"' || --My filename
                         80                        UTL_TCP.CRLF);
                         81    UTL_SMTP.WRITE_DATA(v_connection_handle,
                         82                        'Content-Transfer-Encoding: base64' || UTL_TCP.CRLF);
                         83    UTL_SMTP.WRITE_DATA(v_connection_handle, UTL_TCP.CRLF);
                         84   
                         85    /* Writing the BLOL in chunks */
                         86    WHILE l_pos < l_blob_len LOOP
                         87      DBMS_LOB.READ(l_blob, l_amount, l_pos, l_buffer);
                         88      UTL_SMTP.write_raw_data(v_connection_handle,
                         89                              UTL_ENCODE.BASE64_ENCODE(l_buffer));
                         90      UTL_SMTP.WRITE_DATA(v_connection_handle, UTL_TCP.CRLF);
                         91      l_buffer := NULL;
                         92      l_pos    := l_pos + l_amount;
                         93    END LOOP;
                         94    UTL_SMTP.WRITE_DATA(v_connection_handle, UTL_TCP.CRLF);
                         95   
                         96    -- Close Email
                         97    UTL_SMTP.WRITE_DATA(v_connection_handle,
                         98                        '--' || 'SAUBHIK.SECBOUND' || '--' || UTL_TCP.CRLF);
                         99                        
                        100    UTL_SMTP.WRITE_DATA(v_connection_handle,
                        101                        UTL_TCP.CRLF || '.' || UTL_TCP.CRLF);
                        102    UTL_SMTP.WRITE_DATA(v_connection_handle, UTL_TCP.CRLF);
                        103   
                        104    UTL_SMTP.CLOSE_DATA(v_connection_handle);
                        105    UTL_SMTP.QUIT(v_connection_handle);
                        106    DBMS_LOB.FREETEMPORARY(l_blob);
                        107    --DBMS_LOB.FILECLOSE(v_src_loc);
                        108   
                        109   EXCEPTION
                        110    WHEN OTHERS THEN
                        111      UTL_SMTP.QUIT(v_connection_handle);
                        112      DBMS_LOB.FREETEMPORARY(l_blob);
                        113      --DBMS_LOB.FILECLOSE(v_src_loc);
                        114      RAISE;
                        115   END;
                        116  /
                        
                        PL/SQL procedure successfully completed.
                        
                        SQL> 
                        • 9. Re: Sending UTL_SMTP mail with single jpg attachment issue
                          533177
                          Hi,
                          This is the code & I really dont where it is going wrong . It's running on button press event .I've changed my image column type to blob. but still problem persist. Mail is getting generated but with no image, just the encoded format...
                          DECLARE
                              /*LOB operation related varriables */
                              --v_src_loc  BFILE := BFILENAME('SAUBHIK', 'Waterlilies.jpg');
                              v_src_loc  BLOB :=EMPTY_BLOB;
                              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;
                              crlf                 varchar2(2) := chr(10)||chr(13); 
                            
                             /*UTL_SMTP related varriavles. */
                             v_connection_handle  UTL_SMTP.CONNECTION;
                             v_from_email_address VARCHAR2(30) := 'abc@mydomain.com';
                             v_to_email_address   VARCHAR2(30) := 'abc@mydomain.com';
                             v_smtp_host          VARCHAR2(30) := 'mymailserver'; --My mail server, replace it with yours.
                             v_subject            VARCHAR2(30) := 'Your Test Mail';
                             l_message            VARCHAR2(200) := 'This is test mail using UTL_SMTP';
                           
                           
                          
                             PROCEDURE send_header(pi_name IN VARCHAR2, pi_header IN VARCHAR2) AS
                             BEGIN
                                UTL_SMTP.WRITE_DATA(v_connection_handle,
                                                    pi_name || ': ' || pi_header || crlf);
                             END;
                            
                             
                             BEGIN
                              /*Preparing the LOB from table for attachment. */
                             SELECT image
                             INTO l_blob
                             FROM test_long_raw_to_blob
                             WHERE id=1;
                              
                              --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"');
                             send_header('To','"Recipient"');
                             send_header('Subject', v_subject);
                            
                              --MIME header.
                             UTL_SMTP.WRITE_DATA(v_connection_handle,
                                                  'MIME-Version: 1.0' || crlf);
                             UTL_SMTP.WRITE_DATA(v_connection_handle,
                                                  'Content-Type: multipart/mixed; ' || crlf);
                              UTL_SMTP.WRITE_DATA(v_connection_handle,
                                                 ' boundary= "' || 'SAUBHIK.SECBOUND' || '"' ||
                                               crlf);
                             UTL_SMTP.WRITE_DATA(v_connection_handle, crlf);
                             -- Mail Body
                            UTL_SMTP.WRITE_DATA(v_connection_handle,
                                                '--' || 'SAUBHIK.SECBOUND' || crlf);
                             UTL_SMTP.WRITE_DATA(v_connection_handle,
                                                 'Content-Type: text/plain;' || crlf);
                                                                           
                              UTL_SMTP.WRITE_DATA(v_connection_handle,
                                                 ' charset=US-ASCII' || crlf);
                             UTL_SMTP.WRITE_DATA(v_connection_handle, crlf);
                             UTL_SMTP.WRITE_DATA(v_connection_handle, l_message || crlf);
                             UTL_SMTP.WRITE_DATA(v_connection_handle, crlf);
                            
                              -- Mail Attachment
                             UTL_SMTP.WRITE_DATA(v_connection_handle,
                                                  '--' || 'SAUBHIK.SECBOUND' || crlf);
                              UTL_SMTP.WRITE_DATA(v_connection_handle,
                                                  'Content-Type: application/octet-stream' ||
                                                           crlf);
                            UTL_SMTP.WRITE_DATA(v_connection_handle,
                                                 'Content-Disposition: attachment; ' || crlf);
                             UTL_SMTP.WRITE_DATA(v_connection_handle,
                                                  ' filename="' || 'Waterlilies.jpg' || '"' || --My filename
                                                 crlf);
                              UTL_SMTP.WRITE_DATA(v_connection_handle,
                                                  'Content-Transfer-Encoding: base64' || crlf);
                             UTL_SMTP.WRITE_DATA(v_connection_handle, 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, crlf);
                               l_buffer := NULL;
                               l_pos    := l_pos + l_amount;
                              END LOOP;
                              UTL_SMTP.WRITE_DATA(v_connection_handle, crlf);
                           
                              -- Close Email
                              UTL_SMTP.WRITE_DATA(v_connection_handle,
                                                 '--' || 'SAUBHIK.SECBOUND' || '--' || crlf);
                              UTL_SMTP.WRITE_DATA(v_connection_handle,
                                                  crlf || '.' || 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;
                          Pls get me out of this :-(

                          Regards
                          Percy

                          Edited by: PERCY2 on Jan 13, 2011 5:56 AM
                          • 10. Re: Sending UTL_SMTP mail with single jpg attachment issue
                            533177
                            Thanks Shaubik your code works fine. Only change was required in the declaring section
                            I wrote :
                            crlf  varchar2(2) := chr(10)||chr(13);
                            Changed to :
                            crlf  varchar2(2) := chr(13)||chr(10);
                            gr8.... :-)

                            Percy
                            • 11. Re: Sending UTL_SMTP mail with single jpg attachment issue
                              533177
                              Hi Shaubik,
                              Your code works perfectly. I want to know , Can we have multiple attachments (may be 5-6 images from database) with same code in mail ? If yes, How can we achieve that ? I've tried but dont understand how to take all those images in one variable and pass it to mail procedure.

                              Thanks once again. Gud Day..!

                              Warm Regards,
                              Percy

                              Edited by: PERCY2 on Feb 21, 2011 12:43 AM
                              • 12. Re: Sending UTL_SMTP mail with single jpg attachment issue
                                Saubhik
                                Yes. It is possible to send multiple attachment. You need a cursor to get the handle for each image file and then write those BLOB separately to mail with appropriate MIME format.
                                I'll try to produce a demo at the end of the day (if I get some time out of my schedule in office).
                                • 13. Re: Sending UTL_SMTP mail with single jpg attachment issue
                                  533177
                                  Thanx. Awaiting your reply... Hope I'm not disturbing you :-)
                                  • 14. Re: Sending UTL_SMTP mail with single jpg attachment issue
                                    533177
                                    Hi Saubhik,

                                    Awaiting your reply..

                                    Warm Regds
                                    Percy
                                    1 2 Previous Next