1 2 Previous Next 17 Replies Latest reply on Jan 20, 2011 9:36 AM by San

    UTL_SMTP mail with attachment( Problem in attaching zip file)

    San
      Hi All,

      I used the below code for sending email with attachment. but when i try to add the message body its not working in the sense its not attaching my file. when i commented that line its attaching the file.
      commented lines:
       -- utl_smtp.write_data(mail_conn,UTL_TCP.CRLF ||'Body' ||':'|| text || UTL_TCP.CRLF);
      
        --utl_smtp.write_data(mail_conn,UTL_TCP.CRLF||text || UTL_TCP.CRLF );
      How to resolve this issue???

      Complete procedure.
      create or replace
      procedure sssl_send_mail (
            p_sender varchar2,
            p_recipient varchar2,
            p_cc varchar2,
            p_subject varchar2,
            p_filename varchar2,
            text varchar2) is     
          --c utl_smtp.connection;
          v_raw raw(57);
          v_length integer := 0;
          v_buffer_size integer := 57;
          v_offset integer := 1;
          mailhost    VARCHAR2(64) := 'xxxxxxxxxx';
          port constant number(2):=25;
          timeout number :=180;
          mail_conn  utl_smtp.connection;   
       p_blob Blob;
       temp_os_file bfile;
       ex number;  
      begin  
         DBMS_LOB.CREATETEMPORARY(p_blob,true);
         temp_os_file := BFILENAME ('xxxxxxxx',p_filename);
         ex := dbms_lob.fileexists(temp_os_file);
            if ex = 1 then
               dbms_lob.fileopen(temp_os_file, dbms_lob.file_readonly);
               dbms_lob.loadfromfile(p_blob,temp_os_file, dbms_lob.getlength(temp_os_file));
               dbms_lob.fileclose(temp_os_file);
             end if;
         mail_conn := utl_smtp.open_connection(mailhost, port,timeout);
         utl_smtp.helo(mail_conn, mailhost);
         utl_smtp.mail(mail_conn, p_sender);
         utl_smtp.rcpt(mail_conn, p_recipient);
         utl_smtp.rcpt(mail_conn, p_cc);
      
      
         utl_smtp.open_data(mail_conn);
        utl_smtp.write_data(mail_conn,'From'||':'|| p_sender || UTL_TCP.CRLF);
        utl_smtp.write_data(mail_conn,'To'||':'|| p_recipient || UTL_TCP.CRLF);
        utl_smtp.write_data(mail_conn,'CC'||':'|| p_cc || UTL_TCP.CRLF);
      
      
        utl_smtp.write_data(mail_conn,'Subject' ||':'|| p_subject || UTL_TCP.CRLF);
      
       -- utl_smtp.write_data(mail_conn,UTL_TCP.CRLF ||'Body' ||':'|| text || UTL_TCP.CRLF);
      
        --utl_smtp.write_data(mail_conn,UTL_TCP.CRLF||text || UTL_TCP.CRLF );
      
      
      
          utl_smtp.write_data( mail_conn, 'Content-Disposition: attachment; filename="' || p_filename || '"' || utl_tcp.crlf);
          utl_smtp.write_data( mail_conn, 'Content-Transfer-Encoding: base64' || utl_tcp.crlf );
          utl_smtp.write_data( mail_conn, utl_tcp.crlf ); 
          v_length := dbms_lob.getlength(p_blob);     
          <<while_loop>>
          while v_offset < v_length loop
            dbms_lob.read( p_blob, v_buffer_size, v_offset, v_raw );
            utl_smtp.write_raw_data( mail_conn, utl_encode.base64_encode(v_raw) );
            utl_smtp.write_data( mail_conn, utl_tcp.crlf );
            v_offset := v_offset + v_buffer_size;
          end loop while_loop;
          utl_smtp.write_data( mail_conn, utl_tcp.crlf );
          utl_smtp.close_data(mail_conn);
          utl_smtp.quit(mail_conn);
        exception
          when utl_smtp.transient_error or utl_smtp.permanent_error then
            utl_smtp.quit(mail_conn);
            raise;
          when others then
          raise;
        end;
      Please help me out to resolve this issue.

      Thanks in advance.

      Cheers ,
      Shan.

      Edited by: Shan on 13 Jan, 2011 1:08 PM

      Edited by: Shan on 14 Jan, 2011 3:22 PM
        • 1. Re: UTL_SMTP mail with attachment
          damorgan
          If it works when you comment out the lines ... what is the issue? Comment out the lines.

          Somehow, I suspect, this is not the case so please clarify what is happening.

          Any exceptions generated?

          Any reason not to use the UTL_MAIL package which greatly simplifies the code.
          • 2. Re: UTL_SMTP mail with attachment
            San
            Hi ,

            Thanks for your kind reply.

            When i un commented that line its not giving any exception but its not attaching my file , my attachment going as a text in the mail. When i commented that line its attaching my file in the mail.

            Yes, UTL_mail is the simplest one. But i want to attach the file in my mail file size should be more than 32 k . Thats the reason i am not using utl_mail.

            Cheers,
            Shan.
            • 3. Re: UTL_SMTP mail with attachment
              Billy~Verreynne
              Shan wrote:

              When i un commented that line its not giving any exception but its not attaching my file , my attachment going as a text in the mail. When i commented that line its attaching my file in the mail.
              Please have a look at how to create Mime (Multipurpose Internet Mail Extensions) - the specification memos are listed in this URL.

              To create an attachment, one would typically specify the Mime type as mixed and supply a boundary - and then use this boundary to separate an attachment from the mail body, and to separate attachments from one another. Have a look at the sample Mime body I posted in {message:id=4402801}.

              Sometimes the easiest way to format proper Mime bodies using PL/SQL, is to create the type of mail you want to send using your mail reader, send it to yourself, and then view the raw e-mail in your mail reader to see how the formatting looks like and works.
              1 person found this helpful
              • 4. Re: UTL_SMTP mail with attachment
                Saubhik
                Here is a complete code for what you are trying to achieve.
                Re: Sending UTL_SMTP mail with single jpg attachment issue
                1 person found this helpful
                • 5. Re: UTL_SMTP mail with attachment
                  San
                  Hi ,

                  As per the link i have to store my file into blob. then i have to write . am i right???

                  My above code is working with gmail as an attachment but in hotmail and yahoo what ever file format i send its showing as a content not an attachment.


                  How to resolve this???
                  • 6. Re: UTL_SMTP mail with attachment
                    Saubhik
                    I didn't get your question about store into BLOB. I am reading the file from disk (BFILE) and then storing it into temporary LOB. I send a file into my hotmail and it came as an attachment.
                    DECLARE
                      /*LOB operation related varriables */
                      v_src_loc  BFILE := BFILENAME('SAUBHIK', 'Waterlilies.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) := 'xxxx@yyyyy.com';--change your email address
                      v_to_email_address   VARCHAR2(30) := 'xxxxx@hotmail.com'; --change your email address
                      v_smtp_host          VARCHAR2(30) := '9.182.156.144'; --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';
                    
                      /* 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/mixed; ' || 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/plain;' || UTL_TCP.CRLF);
                      UTL_SMTP.WRITE_DATA(v_connection_handle,
                                          ' charset=US-ASCII' || 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-Type: application/octet-stream' ||
                                          UTL_TCP.CRLF);
                      UTL_SMTP.WRITE_DATA(v_connection_handle,
                                          'Content-Disposition: attachment; ' || UTL_TCP.CRLF);
                      UTL_SMTP.WRITE_DATA(v_connection_handle,
                                          ' filename="' || 'Waterlilies.jpg' || '"' || --My filename
                                          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);
                    
                      /* 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;
                    http://saubbane.blogspot.com/2011/01/sending-binary-attachmentimages-in-mail.html
                    • 7. Re: UTL_SMTP mail with attachment
                      San
                      Hi , see my below code
                      DECLARE
                          /*LOB operation related varriables */
                         v_src_loc  BFILE := BFILENAME('BROKERREPORTS', 'navval-ARN-3280-12-JAN-11.dbf');
                          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(50) := 'DistributorServices@sundarambnpparibasfs.in';
                          v_to_email_address   VARCHAR2(50) := 'er.narun_shan@yahoo.com';
                          v_smtp_host          VARCHAR2(30) := 'sbssld1'; --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';
                        
                          /* 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" <'||'>');
                         
                          send_header('TO', '"Recipient" <'||'>');
                          
                          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/mixed; ' || 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/plain;' || UTL_TCP.CRLF);
                          UTL_SMTP.WRITE_DATA(v_connection_handle,
                                              ' charset=US-ASCII' || 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-Type: application/octet-stream' ||
                                              UTL_TCP.CRLF);
                          UTL_SMTP.WRITE_DATA(v_connection_handle,
                                              'Content-Disposition: attachment; ' || UTL_TCP.CRLF);
                          UTL_SMTP.WRITE_DATA(v_connection_handle,
                                              ' filename="' || 'navval-ARN-3280-12-JAN-11.dbf' || '"' || --My filename
                                              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);
                        /* 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;
                       / 
                      after i ran the above block i checked my yahoo mail but i didnt recieve any mails.

                      Am i missed anything in the above code?

                      and also what is this 'SAUBHIK.SECBOUND' ??? why you gave this in the code???

                      Help me out to resolve the issue.

                      Cheers,
                      Shan

                      Edited by: Shan on 12 Jan, 2011 4:34 PM
                      • 8. Re: UTL_SMTP mail with attachment
                        Saubhik
                        after i ran the above block i checked my yahoo mail but i didnt recieve any mails.
                        Are you receiving mails in other domains, like hotmail, gmail,your official domain etc ? Or you are not getting mails anywhere ? The first thing to check is your mail server and associated port.
                        what is this 'SAUBHIK.SECBOUND' ??? why you gave this in the code???
                        :) This is nothing but a MIME boundary. You can use anything. Help yourself to understand MIME. Billy has provided a very valuable guidance in this regard. See the links from his posts.
                        • 9. Re: UTL_SMTP mail with attachment
                          San
                          Hi,

                          Some Problems i am facing with this procedure. when i try to add CC in this procedure. Its sending the mail for recipent , But the CC person not getting the mail. How to resolve this??
                          create or replace
                          PROCEDURE sssl_send_mail( p_sender varchar2,
                                p_recipient varchar2,
                                p_cc varchar2,
                                p_subject varchar2,
                                p_filename varchar2,
                                text varchar2) is  
                          
                              /*LOB operation related varriables */
                             v_src_loc  BFILE; 
                             l_buffer   RAW(200);
                             l_amount   BINARY_INTEGER := 200;
                             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(200); 
                              v_to_email_address   VARCHAR2(200) ;
                              v_smtp_host          VARCHAR2(10) ;
                              v_subject            VARCHAR2(500) ;
                              l_message            VARCHAR2(3000);
                            
                              /* This send_header procedure is written in the documentation */
                              PROCEDURE send_header(pi_name IN VARCHAR2, pi_header IN VARCHAR2) AS
                              BEGIN
                              --dbms_output.put_line('entering into procedure');
                              --dbms_output.put_line(pi_name || ': ' || pi_header);
                                UTL_SMTP.WRITE_DATA(v_connection_handle,
                                                    pi_name || ': ' || pi_header || UTL_TCP.CRLF);
                              END;
                           
                            BEGIN
                             v_src_loc             := BFILENAME('BROKERREPORTS',p_filename);
                             v_from_email_address  := p_sender;
                             v_to_email_address    := p_recipient;
                             v_smtp_host           := 'sbssld1'; --My mail server, replace it with yours.
                             v_subject             := p_subject;
                              
                             l_message      := 'test';
                           
                              /*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', v_from_email_address || '<'||'>');
                             
                              send_header('TO', v_to_email_address || '<'||'>');
                              
                              send_header('CC', p_cc        || '<'||'>');
                              
                              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/mixed; ' || 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/plain;' || UTL_TCP.CRLF);
                              UTL_SMTP.WRITE_DATA(v_connection_handle,
                                                  ' charset=US-ASCII' || 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-Type: application/octet-stream' ||
                                                  UTL_TCP.CRLF);
                              UTL_SMTP.WRITE_DATA(v_connection_handle,
                                                  'Content-Disposition: attachment; ' || UTL_TCP.CRLF);
                              UTL_SMTP.WRITE_DATA(v_connection_handle,
                                                  ' filename="' || p_filename || '"' || --My filename
                                                  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);
                            /* 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);
                               sssl_internal_error_track(sqlcode,sqlerrm,'SSSL_SEND_MAIL',NULL);
                            END;
                          Thanks in Advance.

                          Cheers,
                          Shan.

                          Edited by: Shan on 13 Jan, 2011 12:33 PM

                          Edited by: Shan on 13 Jan, 2011 1:05 PM
                          • 10. Re: UTL_SMTP mail with attachment
                            Saubhik
                            Add a line something like UTL_SMTP.RCPT(v_connection_handle, v_cc_email_address); and then the updated MIME header as you have defined.
                            The thing is you have to add a UTL_SMTP.RCPT for every TO,CC and BCC.
                            1 person found this helpful
                            • 11. Re: UTL_SMTP mail with attachment
                              San
                              Hi ,

                              When i try to attach the zip file its not sending correct zip file to my recipent.

                              How to resolve this???


                              Thanks in advance

                              Cheers,
                              Shan.
                              • 12. Re: UTL_SMTP mail with attachment( Problem in adding CC)
                                San
                                Hi ,

                                When i try to attach the zip file its not sending correct zip file to my recipent.

                                How to resolve this???

                                Thanks in advance

                                Cheers,
                                Shan
                                • 13. Re: UTL_SMTP mail with attachment( Problem in adding CC)
                                  Saubhik
                                  What is the actual problem you are facing ? Is the attached zip file getting corrupted ? or it is not attaching at all, among the many zip files, it is picking the wrong name ? Any Oracle error you encountered ?
                                  • 14. Re: UTL_SMTP mail with attachment( Problem in adding CC)
                                    San
                                    Yes My file is getting corrupted. I hope its attaching only the zip folder not inside content file.

                                    I am getting empty zip folder.

                                    i am not getting any oracle error.

                                    code which is used
                                    SSSL_SEND_MAIL(
                                        P_SENDER => xxxxx,
                                        P_RECIPIENT => xxxxx,
                                        P_CC =>xxxxxx
                                        P_SUBJECT => 'test zip mail',
                                        P_FILENAME => xxxxxxx.ZIP',
                                        TEXT => 'test'
                                      );
                                    i am passing the zip file name only.


                                    Shan.,...

                                    Edited by: Shan on 14 Jan, 2011 3:48 PM
                                    1 2 Previous Next