SQL & PL/SQL

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