1 Reply Latest reply: Nov 2, 2012 4:25 AM by Jim Smith RSS

    email is sent correctly, but attachment appears in the body

    875608
      Hi,

      I am trying to send emails with attachments in pl/sql.

      I heard it can be done.

      I tried it, the code runs wiithout erros.
      But i cannot see the actual attachment.

      The content of the attachment appears in the body of the email.

      In the email i see this:

      ************************************************
      This is an automated email. Please do not reply!
      ************************************************
      MIME-Version: 1.0
      Content-Type: multipart/mixed; boundary="-----AABCDEFBBCCC0123456789DE"

      This is a multi-part message in MIME format.
      -------AABCDEFBBCCC0123456789DE
      Content-Type: text/html;US-ASCII
      Content-Transfer-Encoding: base64
      Content-Disposition: attachment; filename="your_file_name.csv"

      SYS,ICOL$,TABLE,2009-06-19 15:35:34
      SYS,I_USER1,INDEX,2009-06-19 15:35:34
      SYS,CON$,TABLE,2009-06-19 15:35:34
      SYS,UNDO$,TABLE,2009-06-19 15:35:34
      SYS,C_COBJ#,CLUSTER,2009-06-19 15:35:34
      SYS,I_OBJ#,INDEX,2009-06-19 15:35:34
      SYS,PROXY_ROLE_DATA$,TABLE,2009-06-19 15:35:34
      SYS,I_IND1,INDEX,2009-06-19 15:35:34
      SYS,I_CDEF2,INDEX,2009-06-19 15:35:34
      SYS,I_PROXY_ROLE_DATA$_1,INDEX,2009-06-19 15:35:34
      SYS,FILE$,TABLE,2009-06-19 15:35:34
      SYS,UET$,TABLE,2009-06-19 15:35:34
      SYS,I_FILE#_BLOCK#,INDEX,2009-06-19 15:35:34
      SYS,I_FILE1,INDEX,2009-06-19 15:35:34
      SYS,I_CON1,INDEX,2009-06-19 15:35:34
      SYS,I_OBJ3,INDEX,2009-06-19 15:35:34
      SYS,I_TS#,INDEX,2009-06-19 15:35:34
      SYS,I_CDEF4,INDEX,2009-06-19 15:35:34
      SYS,IND$,TABLE,2009-06-19 15:35:34

      -------AABCDEFBBCCC0123456789DE--

      The part between the boundry
      (-------AABCDEFBBCCC0123456789DE--) should come as an attachment.

      Code for attachment:
      PROCEDURE R040_attach
      IS
      v_clob clob := empty_clob();
      c_mime_boundary VARCHAR2(256) := '-----AABCDEFBBCCC0123456789DE';
      v_len INTEGER;
      v_index INTEGER;



      BEGIN
      FOR x IN (SELECT *
      FROM all_objects
      WHERE ROWNUM < 20)
      LOOP
      v_clob :=
      v_clob
      || x.owner
      || ','
      || x.object_name
      || ','
      || x.object_type
      || ','
      || TO_CHAR(x.created, 'yyyy-mm-dd hh24:mi:ss')
      || UTL_TCP.crlf;
      END LOOP;


      UTL_SMTP.write_data(c, 'MIME-Version: 1.0' || UTL_TCP.crlf);

      UTL_SMTP.write_data(
      c,
      'Content-Type: multipart/mixed; boundary="' || c_mime_boundary || '"' || UTL_TCP.crlf
      );
      UTL_SMTP.write_data(c, UTL_TCP.crlf);
      UTL_SMTP.write_data(
      c,
      'This is a multi-part message in MIME format.' || UTL_TCP.crlf
      );

      UTL_SMTP.write_data(c, '--' || c_mime_boundary || UTL_TCP.crlf);
      --UTL_SMTP.write_data(c, 'Content-Type: text/html;US-ASCII' || UTL_TCP.crlf);

      --Content-Type: image/jpeg

      UTL_SMTP.write_data(
      c,
      'Content-Type: text/html;US-ASCII'
      || UTL_TCP.crlf
      || 'Content-Transfer-Encoding: base64'
      || UTL_TCP.crlf
      );



      -- Set up attachment header
      UTL_SMTP.write_data(
      c,
      'Content-Disposition: attachment; filename="'
      || 'your_file_name.csv'
      || '"'
      || UTL_TCP.crlf
      );
      UTL_SMTP.write_data(c, UTL_TCP.crlf);

      -- Write attachment contents

      v_len := DBMS_LOB.getlength(v_clob);
      v_index := 1;

      WHILE v_index <= v_len
      LOOP
      UTL_SMTP.write_data(c, DBMS_LOB.SUBSTR(v_clob, 32000, v_index));
      v_index := v_index + 32000;
      END LOOP;

      --
      -- End attachment
      UTL_SMTP.write_data(c, UTL_TCP.crlf);
      UTL_SMTP.write_data(c, '--' || c_mime_boundary || '--' || UTL_TCP.crlf);

      EXCEPTION
      WHEN OTHERS THEN
      RAISE_APPLICATION_ERROR( -20110, 'Dwx0110 - R030 - ' || SQLERRM );
      END R040_attach;

      Any help would be appreciated.
      Thank you.
        • 1. Re: email is sent correctly, but attachment appears in the body
          Jim Smith
          This is the forum for Oracle's SQL Developer tool, not for general SQL and PL/SQL questions.

          Questions like this will get a better response in the PL/SQL forum.

          Here is some code I have used to send emails with attachments.
            utl_smtp.open_data(conn);
            utl_smtp.write_data(conn,'Subject:'||subject);
             utl_smtp.write_data( conn, utl_tcp.crlf );
          
            utl_smtp.write_data( conn, 'Content-Disposition: attachment; filename="attachment"' || utl_tcp.crlf);
              utl_smtp.write_data( conn, 'Content-Transfer-Encoding: base64' || utl_tcp.crlf );
              utl_smtp.write_data( conn, utl_tcp.crlf );
          
              v_length := dbms_lob.getlength(attachment);
          
              <<while_loop>>
              while v_offset < v_length loop
                dbms_lob.read( attachment, v_buffer_size, v_offset, v_raw );
                utl_smtp.write_raw_data( conn, utl_encode.base64_encode(v_raw) );
                utl_smtp.write_data( conn, utl_tcp.crlf );
                v_offset := v_offset + v_buffer_size;
              end loop while_loop;
          
              utl_smtp.write_data( conn, utl_tcp.crlf );
            utl_smtp.write_data(conn,utl_tcp.crlf||utl_tcp.crlf);
            utl_smtp.write_data(conn,content);
            utl_smtp.write_data(conn,utl_tcp.crlf||utl_tcp.crlf);
          
            utl_smtp.close_data(conn);
          content is a varchar holding the body of the email.
          attachment is a blob holding the attachment.

          Edited by: Jim Smith on Nov 2, 2012 9:24 AM