asked on

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

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;

ASKER CERTIFIED SOLUTION

RCorfman

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial