Silverterra 3d blog

There is a general problem to send an invisible copy of email from oracle stored procedure.
Let’s start with a simple mailer propcedure and add a new recipient (second one) to the connection – that will be BCC!
This second one recipient is missing from the header “To:” section and will be invisible.

  ...
  c := UTL_SMTP.OPEN_CONNECTION(p_mailserv); -- 1. open a server connection
  UTL_SMTP.HELO(c, p_mailserv);              -- 2. say hello
  UTL_SMTP.MAIL(c, p_mailsender);            -- 3. say want to send email
  UTL_SMTP.RCPT(c, p_recipient_email);       -- 4. put the recipient (visible one)
                                             --    I will put into the header 
                                             --    "To" section as well
                                             --    that's why will be visible
  IF p_bcc <> ' ' THEN
    UTL_SMTP.RCPT(c, p_bcc);                 -- 5. put a new recipient (bcc)
  END IF;
  ...
  UTL_SMTP.OPEN_DATA(c);

and here is a working sample from and oracle8i webdb environment:

PROCEDURE Html_Email(
  p_from_email IN VARCHAR2,
  p_from_alias IN VARCHAR2,
  p_recipient_email IN VARCHAR2,
  p_recipient_alias IN VARCHAR2,
  p_subj IN VARCHAR2 DEFAULT ' ',
  p_body IN VARCHAR2 DEFAULT ' ',
  p_bcc in varchar2 default ' '
) IS

/*
 this procedure is to send html based email
*/

p_mailserv VARCHAR2(100) := '** the mailer server **'; -- mail server
p_mailsender VARCHAR2(100) := '** sender account **';  -- account to send email
c UTL_SMTP.CONNECTION; 

PROCEDURE send_header(NAME IN VARCHAR2, HEADER IN VARCHAR2) AS
BEGIN
  UTL_SMTP.WRITE_DATA(c, NAME || ': ' || HEADER || UTL_TCP.CRLF);
END; 

PROCEDURE send_data(l_body_html VARCHAR2) AS
l_offset NUMBER;
l_ammount NUMBER;
BEGIN
  l_offset  := 1;
  l_ammount := 1900;
  WHILE l_offset < LENGTH(l_body_html) LOOP
    UTL_SMTP.WRITE_DATA(c,SUBSTR(l_body_html, l_offset, l_ammount));
    l_offset := l_offset + l_ammount ;
    l_ammount := LEAST(1900, LENGTH(l_body_html) - l_ammount);
  END LOOP;
END; 

BEGIN
    
  -- open connection and prepare the sending
  c := UTL_SMTP.OPEN_CONNECTION(p_mailserv);
  UTL_SMTP.HELO(c, p_mailserv);
  UTL_SMTP.MAIL(c, p_mailsender);
  UTL_SMTP.RCPT(c, p_recipient_email);

  -- put the bcc if we need
  IF p_bcc <> ' ' THEN
    UTL_SMTP.RCPT(c, p_bcc);
  END IF;
    
  -- header
  UTL_SMTP.OPEN_DATA(c);
  send_header('From', '"' || p_from_alias || '" <' || p_from_email || '>');
  send_header('To', '"' || p_recipient_alias || '" <' || p_recipient_email || '>'); 
  send_header('Subject', p_subj); 
  send_header('Content-Type', 'text/html;charset=windows-1251');
    
  -- body
  send_data(p_body);

  -- close the connection
  UTL_SMTP.CLOSE_DATA(c);
  UTL_SMTP.QUIT(c);

  -- error catching
  EXCEPTION
    WHEN UTL_SMTP.TRANSIENT_ERROR OR UTL_SMTP.PERMANENT_ERROR THEN
      BEGIN
        UTL_SMTP.QUIT(c);
      EXCEPTION
        WHEN UTL_SMTP.TRANSIENT_ERROR OR UTL_SMTP.PERMANENT_ERROR THEN
          NULL;
      END;
      htp.p('Failed to send mail due to the following error: ' || SQLERRM); 

END;

Tuesday, May 27th, 2014 at 22:32.
Filed under oracle.