We help IT Professionals succeed at work.

Oracle 9i, Utl_smtp,  Sending email through the DB and want receipt request

mintsii
mintsii asked
on
Medium Priority
1,648 Views
Last Modified: 2007-11-27
I have a package that sends email via the UTL_SMTP protocol.  It works great, however, I want to add a receipt request.  So that when the subject gets an email, they will be asked to send a receipt.

PROCEDURE html_email_7
   IS
      CURSOR email_data_cur
      IS
         select  email, survey.participant_id, random_sevens from survey, contact_info
 where survey.participant_id = contact_info.participant_id
 and random_sevens = 7;

      email_data_cur_rec   email_data_cur%ROWTYPE;
      p_to                 VARCHAR2 (50)            := 'asdf@asdf.com';
      p_from               VARCHAR2 (50)            := 'asdf@asdf.com';
      p_subject            VARCHAR2 (50)
                                  :=  Newsletter March 2006';
      p_text               VARCHAR2 (2000);
      p_html               VARCHAR2 (32767);

      p_smtp_hostname      VARCHAR2 (50)            := 'mail.asdf.comdu';
      p_smtp_portnum       VARCHAR2 (50)            := 25;
      cc_mail              VARCHAR2 (50);
      l_boundary           VARCHAR2 (32767)         DEFAULT 'a1b2c3d4e3f2g1';
      l_connection         UTL_SMTP.connection;
      l_body_html          CLOB                     := EMPTY_CLOB;
      --This LOB will be the email message
      l_offset             NUMBER;
      l_ammount            NUMBER;
      l_temp               VARCHAR2 (32767)         DEFAULT NULL;
   BEGIN
      FOR email_data_cur_rec IN email_data_cur
      LOOP
                 p_html :=
               '<head><meta http-equiv=Content-Type content="text/html; charset=windows-1252">
<body>

This is the body message.

</body>';
         cc_mail := email_data_cur_rec.email;
         l_connection :=
                    UTL_SMTP.open_connection (p_smtp_hostname, p_smtp_portnum);
         UTL_SMTP.helo (l_connection, p_smtp_hostname);
         UTL_SMTP.mail (l_connection, p_from);
         UTL_SMTP.rcpt (l_connection, p_to);
         UTL_SMTP.rcpt (l_connection, cc_mail);
         l_temp := l_temp || 'MIME-Version: 1.0' || CHR (13) || CHR (10);
         l_temp := l_temp || 'To: ' || p_to || CHR (13) || CHR (10);
         l_temp := l_temp || 'From: ' || p_from || CHR (13) || CHR (10);
         l_temp := l_temp || 'Subject: ' || p_subject || CHR (13) || CHR (10);
         l_temp := l_temp || 'cc: ' || cc_mail || CHR (13) || CHR (10);
         l_temp := l_temp || 'Reply-To: ' || p_from || CHR (13) || CHR (10);
         l_temp :=
               l_temp
            || 'Content-Type: multipart/alternative; boundary='
            || CHR (34)
            || l_boundary
            || CHR (34)
            || CHR (13)
            || CHR (10);
----------------------------------------------------
-- Write the headers
         DBMS_LOB.createtemporary (l_body_html, FALSE, 10);
         DBMS_LOB.WRITE (l_body_html, LENGTH (l_temp), 1, l_temp);
----------------------------------------------------
-- Write the text boundary
         l_offset := DBMS_LOB.getlength (l_body_html) + 1;
         l_temp := '--' || l_boundary || CHR (13) || CHR (10);
         l_temp :=
               l_temp
            || 'content-type: text/plain; charset=us-ascii'
            || CHR (13)
            || CHR (10)
            || CHR (13)
            || CHR (10);
         DBMS_LOB.WRITE (l_body_html, LENGTH (l_temp), l_offset, l_temp);
----------------------------------------------------
-- Write the plain text portion of the email
         l_offset := DBMS_LOB.getlength (l_body_html) + 1;
         DBMS_LOB.WRITE (l_body_html, LENGTH (p_text), l_offset, p_text);
-----------------------------------------------------
-- Write the HTML boundary
         l_temp :=
               CHR (13)
            || CHR (10)
            || CHR (13)
            || CHR (10)
            || '--'
            || l_boundary
            || CHR (13)
            || CHR (10);
         l_temp :=
               l_temp
            || 'content-type: text/html;'
            || CHR (13)
            || CHR (10)
            || CHR (13)
            || CHR (10);
         l_offset := DBMS_LOB.getlength (l_body_html) + 1;
         DBMS_LOB.WRITE (l_body_html, LENGTH (l_temp), l_offset, l_temp);
----------------------------------------------------
-- Write the HTML portion of the message
         l_offset := DBMS_LOB.getlength (l_body_html) + 1;
         DBMS_LOB.WRITE (l_body_html, LENGTH (p_html), l_offset, p_html);
----------------------------------------------------
-- Write the final html boundary
         l_temp :=
                  CHR (13) || CHR (10) || '--' || l_boundary || '--'
                  || CHR (13);
         l_offset := DBMS_LOB.getlength (l_body_html) + 1;
         DBMS_LOB.WRITE (l_body_html, LENGTH (l_temp), l_offset, l_temp);
----------------------------------------------------
-- Send the email in 1900 byte chunks to UTL_SMTP
         l_offset := 1;
         l_ammount := 1900;
         UTL_SMTP.open_data (l_connection);

         WHILE l_offset < DBMS_LOB.getlength (l_body_html)
         LOOP
            UTL_SMTP.write_data (l_connection,
                                 DBMS_LOB.SUBSTR (l_body_html,
                                                  l_ammount,
                                                  l_offset
                                                 )
                                );
            l_offset := l_offset + l_ammount;
            l_ammount :=
                     LEAST (1900, DBMS_LOB.getlength (l_body_html) - l_ammount);
         END LOOP;

         UTL_SMTP.close_data (l_connection);
         UTL_SMTP.quit (l_connection);
         DBMS_LOB.freetemporary (l_body_html);
         l_temp := NULL;
      END LOOP;
   END;
Comment
Watch Question

Commented:
Add this into the header and I believe it will work...
l_temp := l_temp || 'Disposition-Notification-To: ' || p_from || CHR (13) || CHR (10);

Remember, it is dependent on the email client to honor this or not...
         l_temp := l_temp || 'MIME-Version: 1.0' || CHR (13) || CHR (10);
         l_temp := l_temp || 'To: ' || p_to || CHR (13) || CHR (10);
         l_temp := l_temp || 'From: ' || p_from || CHR (13) || CHR (10);
         l_temp := l_temp || 'Subject: ' || p_subject || CHR (13) || CHR (10);
         l_temp := l_temp || 'cc: ' || cc_mail || CHR (13) || CHR (10);
         l_temp := l_temp || 'Reply-To: ' || p_from || CHR (13) || CHR (10);
         l_temp := l_temp || 'Disposition-Notification-To: ' || p_from || CHR (13) || CHR (10);

This will send the notification to the same place you have the reply-to going to...

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.