CODEWITHSUNDEEP
oracle-databaseemailplsqlemail-attachments
ZerOne
ZerOne

Reputation: 1326

UTL_SMTP attachment with mail text

I need to send an email with attachment and text with Oracle. I used UTL_MAIL before, but with this function, attachments can't have a size bigger than (I think) 32K. So I tried to send it with UTL_SMTP, which works better for attachments.

This is my code so far: 

  c := UTL_SMTP.OPEN_CONNECTION(mailserver);
  UTL_SMTP.helo (c, mailserver);
  UTL_SMTP.MAIL(c, sFrom);
  UTL_SMTP.RCPT(c, sTo);
  UTL_SMTP.OPEN_DATA(c);


   UTL_SMTP.write_data(c, 'From: ' || sFrom || UTL_TCP.crlf);
   UTL_SMTP.write_data(c, 'To: ' || sTo || UTL_TCP.crlf);
   UTL_SMTP.write_data(c, 'Subject: ' || REPLACE(crec.descr, '[DATE]',TO_CHAR(sDATE,'DD.MM.YYYY')) || UTL_TCP.crlf);
   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/plain' || 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(cREPORT);
  v_index := 1;

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

  --
  -- End attachment
  UTL_SMTP.write_data(c, UTL_TCP.crlf);
  -- MY TEXT:
  UTL_SMTP.write_data(c,  'This is a text.' || UTL_TCP.crlf);
  UTL_SMTP.write_data(c, '--' || c_mime_boundary || '--' || UTL_TCP.crlf);

  UTL_SMTP.CLOSE_DATA(c);
  UTL_SMTP.QUIT(c);

So the email with attachment works like a charm, but the email text is not been send within the email. Is it possible to send it with attachment + text?

Upvotes: 0

Views: 7426

Answers (2)

ZerOne
ZerOne

Reputation: 1326

So, I already answered my own question. You have to use the c_mime_boundary as a delimiter between mail content and attachment content. This means the following: 

  c := UTL_SMTP.OPEN_CONNECTION(mailserver);  

  UTL_SMTP.helo (c, mailserver);
  UTL_SMTP.MAIL(c, sFrom);
  UTL_SMTP.RCPT(c, sTo);
  UTL_SMTP.OPEN_DATA(c);


  UTL_SMTP.write_data(c, 'From: ' || sFrom || UTL_TCP.crlf);
  UTL_SMTP.write_data(c, 'To: ' || sTo || UTL_TCP.crlf);
  UTL_SMTP.write_data(c, 'Subject: ' || REPLACE(crec.descr, '[DATE]',TO_CHAR(sDATE,'DD.MM.YYYY')) || UTL_TCP.crlf);
  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);

  -- Mail body:
  UTL_SMTP.write_data(c, '--' || c_mime_boundary || UTL_TCP.crlf); //new
  UTL_SMTP.write_data(c, 'Content-Type: text/plain' || UTL_TCP.crlf); //new
  UTL_SMTP.write_data(c, 'Text' || UTL_TCP.crlf); //new

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

  -- Write attachment contents
  v_len := DBMS_LOB.getlength(cREPORT);
  v_index := 1;

  WHILE v_index <= v_len
  LOOP
    UTL_SMTP.write_data(c, DBMS_LOB.SUBSTR(cREPORT, 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);

  UTL_SMTP.CLOSE_DATA(c);
  UTL_SMTP.QUIT(c);

Upvotes: 5

Shaun Peterson
Shaun Peterson

Reputation: 1790

I think you need to copy 

 UTL_SMTP.write_data(c, 'Content-Type: text/plain' || UTL_TCP.crlf);

up above 

UTL_SMTP.write_data(c,  'This is a multi-part message in MIME format.' || UTL_TCP.crlf);

This should let it know what type of text it is expecting.

Cheers

Upvotes: 0

Related Questions