UTL_SMTP attachment with mail text
NickName:ZerOne Ask DateTime:2015-09-23T21:03:32

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?

Copyright Notice:Content Author:「ZerOne」,Reproduced under the CC 4.0 BY-SA copyright license with a link to the original source and this disclaimer.
Link to original article:https://stackoverflow.com/questions/32740382/utl-smtp-attachment-with-mail-text

Answers
ZerOne 2015-09-24T07:53:15

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: \n\n c := UTL_SMTP.OPEN_CONNECTION(mailserver); \n\n UTL_SMTP.helo (c, mailserver);\n UTL_SMTP.MAIL(c, sFrom);\n UTL_SMTP.RCPT(c, sTo);\n UTL_SMTP.OPEN_DATA(c);\n\n\n UTL_SMTP.write_data(c, 'From: ' || sFrom || UTL_TCP.crlf);\n UTL_SMTP.write_data(c, 'To: ' || sTo || UTL_TCP.crlf);\n UTL_SMTP.write_data(c, 'Subject: ' || REPLACE(crec.descr, '[DATE]',TO_CHAR(sDATE,'DD.MM.YYYY')) || UTL_TCP.crlf);\n UTL_SMTP.write_data(c, 'MIME-Version: 1.0' || UTL_TCP.crlf);\n\n UTL_SMTP.write_data(c, 'Content-Type: multipart/mixed; boundary=\"' || c_mime_boundary || '\"' || UTL_TCP.crlf);\n\n -- Mail body:\n UTL_SMTP.write_data(c, '--' || c_mime_boundary || UTL_TCP.crlf); //new\n UTL_SMTP.write_data(c, 'Content-Type: text/plain' || UTL_TCP.crlf); //new\n UTL_SMTP.write_data(c, 'Text' || UTL_TCP.crlf); //new\n\n -- Set up attachment header\n UTL_SMTP.write_data(c, '--' || c_mime_boundary || UTL_TCP.crlf);\n UTL_SMTP.write_data(c, 'Content-Type: text/plain' || UTL_TCP.crlf);\n UTL_SMTP.write_data(c, 'Content-Disposition: attachment; filename=\"' || 'your_file_name.csv' || '\"' || UTL_TCP.crlf);\n\n -- Write attachment contents\n v_len := DBMS_LOB.getlength(cREPORT);\n v_index := 1;\n\n WHILE v_index <= v_len\n LOOP\n UTL_SMTP.write_data(c, DBMS_LOB.SUBSTR(cREPORT, 32000, v_index));\n v_index := v_index + 32000;\n END LOOP;\n\n -- End attachment\n UTL_SMTP.write_data(c, UTL_TCP.crlf);\n UTL_SMTP.write_data(c, '--' || c_mime_boundary || '--' || UTL_TCP.crlf);\n\n UTL_SMTP.CLOSE_DATA(c);\n UTL_SMTP.QUIT(c);\n",


More about “UTL_SMTP attachment with mail text” related questions

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...

Show Detail

How to send mail with an attachment to all users | Oracle APEX | UTL_SMTP method

It's been a hard time not able to get proper post where I can refer and develop the code. Need to follow only : UTL_SMTP method. This only works at my end Following is the way I have developed my

Show Detail

UTL_SMTP in postgresql

Im curruntly migrating oracle schema to postgresql 9.5 . Im using Ora2pg and it converted for me one function which is reponsible for sending mail to pgplsql. My code : CREATE OR REPLACE FUNCTION

Show Detail

Debugging with Oracle's utl_smtp

A client of mine uses Oracle 9i's utl_smtp to send mails out notifications to managers when their employees have made travel requests and they woul like quite a few changes made to the mailouts don...

Show Detail

How to re-design code with UTL_SMTP - Oracle Apex send mail

It's been a hard time not able to make my code to work with UTL_SMTP package. Can some one guide me to change below code and make it work with UTL_SMTP method I created a mailing application which ...

Show Detail

ACL error when trying to send mail via Oracle UTL_SMTP

I was trying to send an email via oracle utl_smtp, but every time i execute the apex_mail_p.mail procedure i get an ACL error, to be more precise it's the error ORA-24247. But i've created the acl,...

Show Detail

How to determine an attachment is there or not in WCF service when an user send a mail(either simple text mail or an attachment email)

How to determine an attachment is there or not in WCF service when an user send a mail(either simple text mail or an attachment email) using C#.NET

Show Detail

JavaMail mail and attachment

I am trying to send an email with html text and attachment using JavaMail. However, I can only seem to make one work at a time, either it sends the html text or sends the attachment, but not both, ...

Show Detail

Send email with pdf attachment with pl sql

I normally use utl_smtp to send emails in pl/sql, but recently I've had to send a pdf file as attachment and utl_smtp does not allow it. Can you help me on how to send one in pl/sql?

Show Detail

PHP Mail Attachment

I'm having problem with this php mail function.. it works on pdf file i receive the pdf file from mail good and nothing problem but the other file like zip, rar and docx files i receive some text l...

Show Detail