BCC not correctly handled when sendin a mail out of an Oracle database via MS Exchange

Question

I send e-mails from an Oracle database via MS Exchange. For this I use "UTL_SMTP". But if I use "BCC", I see the header unchanged in the mail source of all recipients. So everyone can see who is in BCC.

What's wrong there? Do I have to consider anything else with BCC?

Here is my test code (with pseudo mail host):

DECLARE
    p_to        VARCHAR2(255 CHAR) := '[email protected]';
    p_bcc       VARCHAR2(255 CHAR) := '[email protected]';
    p_from      VARCHAR2(255 CHAR) := '[email protected]';
    p_subject   VARCHAR2(255 CHAR) := 'Testmail (BCC)';
    p_message   VARCHAR2(255 CHAR) := 'Testmail BCC.';
    p_smtp_host VARCHAR2(255 CHAR) := 'sm.test.com';
    p_smtp_port NUMBER             := 25;
    
    l_mail_conn UTL_SMTP.connection;
BEGIN
    l_mail_conn := UTL_SMTP.open_connection(p_smtp_host, p_smtp_port);
    
    UTL_SMTP.helo(l_mail_conn, p_smtp_host);
    UTL_SMTP.mail(l_mail_conn, p_from);
    
    UTL_SMTP.rcpt(l_mail_conn,p_to);
    UTL_SMTP.rcpt(l_mail_conn,p_bcc);
    
    UTL_SMTP.open_data(l_mail_conn);
    
    UTL_SMTP.write_data(l_mail_conn, 'To: ' || p_to || UTL_TCP.crlf);
    UTL_SMTP.write_data(l_mail_conn, 'BCC: ' || p_bcc || UTL_TCP.crlf);
    
    UTL_SMTP.write_data(l_mail_conn, 'From: ' || p_from || UTL_TCP.crlf);
    UTL_SMTP.write_data(l_mail_conn, 'Subject: ' || p_subject || UTL_TCP.crlf);
    
    
    UTL_SMTP.write_data(l_mail_conn, UTL_TCP.crlf);
    UTL_SMTP.write_data(l_mail_conn, p_message || UTL_TCP.crlf || UTL_TCP.crlf);
    UTL_SMTP.close_data(l_mail_conn);

    UTL_SMTP.quit(l_mail_conn);
END;
/

E-mail source of "[email protected]":

...
To: <[email protected]>
BCC: <[email protected]>
From: <[email protected]>
Subject: Testmail (BCC)
...

Thanks! Volker

Answer 1

Well, after googling again for an hour I found out, that I must not set the BCC header. BCC must only be sent with rcpt() function.

I found the solution here. The answer sais "... For example, sending a BCC is simple -- you just RCPT the person and DON"T put them in a CC: or TO: record. ...". And that was the problem, I was setting a "BCC" header. In the RFCs I found the header "BCC", so I added it, unfortunately.

Best, Volker