troubleshooting Question

Oracle 9i html email being sent through util_smtp. Looping the CC

Avatar of mintsii
mintsii asked on
Oracle Database
2 Comments1 Solution1303 ViewsLast Modified:
Ok, here is the issue.  I have a procedure that sends html email through the oracle db.  It loops through the cursor and CC's everyone in the table for that cursor.  It is suppose to iterate and send one message at a time and CC those in people in the cursor, one at a time.  

Here is the problem, it sends the first email perfectly, but any of the email bodies after that look like the following (and have "undiscloded recipients" in the TO field):

--a1b2c3d4e3f2g1--MIME-Version: 1.0
To: feine001@umn.edu
From: mints@umn.edu
Subject: test subject
cc: fein0015@hotmail.com
Reply-To: mints@umn.edu
Content-Type: multipart/alternative; boundary="a1b2c3d4e3f2g1"
--a1b2c3d4e3f2g1
content-type: text/plain; charset=us-ascii

testing

--a1b2c3d4e3f2g1
content-type: text/html;

<b>bold test</b><ul><li>bullet 1</li></ul>
--a1b2c3d4e3f2g1--




HERE IS THE PROCEDURE>>>> THANKS!!!

procedure html_email
is
  CURSOR email_data_cur
       IS
         SELECT email from testing where email is not null;
 
        email_data_cur_rec   email_data_cur%ROWTYPE;
      
    p_to             varchar2(50) := 'test@um.edu';
    p_from           varchar2(50) := 'testing@umn.edu';
    p_subject        varchar2(50) := 'test subject';
    p_text           varchar2(50) := 'testing';
    p_html           varchar2(50) := '<b>bold test</b>' || '<ul><li>bullet 1</li></ul>' ;
    p_smtp_hostname  varchar2(50) := 'mail.cs.um.edu';
    p_smtp_portnum   varchar2(50) := 25;
      cc_mail         VARCHAR2(50)  ;
      
      
      
      
      l_boundary      varchar2(255) 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
      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);

end loop;
end;

END email_retention;
Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 1 Answer and 2 Comments.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 2 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros