Trying to send multiple bcc in oracle UTL_MAIL

I am trying to send multiple email addresses through an Oracle package/procedure. I have the procedure working when sending only a single to, cc, bcc. (That means I can send one of each.) But although have a loop that generates a correct, single email address (from a comma-separated-list), the procedure fails. I'm hoping someone can point out my code problem.

Note. The code for the package works; just the function two write multple rcpt lines does not.

When I use a separate code block to send the message (see below), I get the following error: Error report - ORA-29279: SMTP permanent error: 550 Internal error: Invalid argument

First, here is my process_recipients procedure code:

   PROCEDURE process_recipients(p_mail_conn IN OUT UTL_SMTP.connection,
    p_list      IN     VARCHAR2) IS
    
    a    DBMS_UTILITY.UNCL_ARRAY;
    len  PLS_INTEGER;
BEGIN
    DBMS_UTILITY.COMMA_TO_TABLE(p_list, len, a);
    FOR i IN 1..a.COUNT LOOP
        --dbms_output.put_line( a(i) );
        UTL_SMTP.rcpt(p_mail_conn, a(i) );
    END LOOP;

END process_recipients;

Here is the relevant code from the send_email procedure, which will call proccess_recipients...

  PROCEDURE send_email (
p_mail_server   IN VARCHAR2
, p_mail_port       IN VARCHAR2
, p_from            IN VARCHAR2
, p_to              IN VARCHAR2
, p_cc              IN VARCHAR2 DEFAULT NULL
, p_bcc             IN VARCHAR2 DEFAULT NULL
, p_subject         IN VARCHAR2
, p_html_msg            IN VARCHAR2 DEFAULT NULL    
, p_text_msg            IN VARCHAR2 DEFAULT NULL
) IS

l_mail_conn   UTL_SMTP.connection;
l_boundary    VARCHAR2(50) := '----=*#abc1234321cba#*=';    


 BEGIN
    l_mail_conn := UTL_SMTP.open_connection(p_mail_server,p_mail_port);
UTL_SMTP.helo(l_mail_conn, p_mail_server);
UTL_SMTP.mail(l_mail_conn, p_from);
--UTL_SMTP.rcpt(l_mail_conn, p_to);
process_recipients(l_mail_conn, p_to);   
IF p_cc IS NOT NULL THEN
    UTL_SMTP.rcpt(l_mail_conn, p_cc);
END IF;
IF p_bcc IS NOT NULL THEN
    UTL_SMTP.rcpt(l_mail_conn, p_bcc);
END IF;

Here is the code which actually executes the package/procedure:

    DECLARE
-- l_tab dbms_utility.lname_array;
html_message VARCHAR2(2000);

BEGIN
 --GZ_SEND_EMAIL.p_send_v_test_email_address('smtp.isu.edu','25','[email protected]','<em>This</em> is a 3nd test message');
 html_message := '<strong>Curabitur a arcu id erat fermentum dapibus et at leo.</strong> <p>Nunc placerat finibus pellentesque. Curabitur id ligula ac dui dictum bibendum. Suspendisse eget neque risus. </p>';
 
 GZ_SEND_EMAIL.send_email(
    p_mail_server => 'smtp.isu.edu'
    , p_mail_port => '25'
    , p_from => '[email protected]'
    , p_to => '[email protected],[email protected]'
    , p_cc => '[email protected]'
    , p_bcc => NULL
    , p_subject => 'Come join me for lunch at 11:07.'
    , p_html_msg => html_message
    , p_text_msg => 'Lorem ipsum dolor sit amet, consectetur adipiscing elit. Ut vel justo quis felis pellentesque iaculis. Sed sed tristique felis, non condimentum erat. Duis maximus, ligula quis imperdiet euismod, velit sem consectetur erat, a pellentesque ex dui id massa. Suspendisse a dolor elit.'
    );
  
END;

And, here is a final snippit (not in the Procedure) which I used to test that I was actually sending single email addresses. (Note. I've used fake emails here, but not in my tests.)

DECLARE
 a    DBMS_UTILITY.UNCL_ARRAY;
 len  PLS_INTEGER;
 p_list VARCHAR2(200);
BEGIN
    p_list := '[email protected],[email protected],[email protected],[email protected]';
    DBMS_UTILITY.COMMA_TO_TABLE(p_list, len, a);
    FOR i IN 1..a.COUNT LOOP
        dbms_output.put_line( a(i) );
    END LOOP;
END;

Solution

I use it like this:

CREATE OR REPLACE TYPE VARCHAR_TABLE_TYPE AS TABLE OF VARCHAR2(1000);

PRIORITY_HIGH           CONSTANT INTEGER := 1;
PRIORITY_NORMAL         CONSTANT INTEGER := 3;
PRIORITY_LOW            CONSTANT INTEGER := 5;

PROCEDURE SendMail(
    Subject IN VARCHAR2, 
    Message IN OUT CLOB, 
    ToMail IN VARCHAR_TABLE_TYPE,   
    FromMail IN VARCHAR2, 
    FromName IN VARCHAR2,
    PRIORITY IN T_MAIL_PRIORITY DEFAULT PRIORITY_NORMAL) IS

    SMTP_PORT               CONSTANT INTEGER := 25;
    SMTP_SERVER             CONSTANT VARCHAR2(50):= 'mailhost';
    MIME_BOUNDARY           CONSTANT VARCHAR2(50) := '====Multipart.Boundary.689464861147414354====';

    con UTL_SMTP.CONNECTION;
    ret UTL_SMTP.REPLY;

BEGIN

    -- setup mail header
    con := UTL_SMTP.OPEN_CONNECTION(SMTP_SERVER, SMTP_PORT);
    ret := UTL_SMTP.HELO(con, SYS_CONTEXT('USERENV', 'DB_DOMAIN'));
    ret := UTL_SMTP.MAIL(con, FromMail);
    FOR i IN ToMail.FIRST..ToMail.LAST LOOP
        Recipients := Recipients ||ToMail(i)||',';
        ret := UTL_SMTP.RCPT(con, ToMail(i));
    END LOOP;
    ret := UTL_SMTP.OPEN_DATA(con);

    UTL_SMTP.WRITE_DATA(con, 'From: "'||FromName||'" <'||FromMail||'>'||UTL_TCP.CRLF);
    UTL_SMTP.WRITE_DATA(con, 'To: '||REGEXP_REPLACE(Recipients, ',$')||UTL_TCP.CRLF);
    UTL_SMTP.WRITE_DATA(con, 'Subject: '||Subject||UTL_TCP.CRLF);
    UTL_SMTP.WRITE_DATA(con, 'Date: '||TO_CHAR(CURRENT_TIMESTAMP, 'Dy, DD Mon YYYY hh24:mi:ss TZHTZM', 'NLS_DATE_LANGUAGE = American')||UTL_TCP.CRLF);  
    UTL_SMTP.WRITE_DATA(con, 'X-Priority: '||PRIORITY||UTL_TCP.CRLF);
    UTL_SMTP.WRITE_DATA(con, 'MIME-Version: 1.0' || UTL_TCP.CRLF);

   ...
END;

SendMail(
    'Come join me for lunch at 11:07.', 
    Message, 
    VARCHAR_TABLE_TYPE('[email protected]', '[email protected]', '[email protected]',  '[email protected]'),   
    '[email protected]', 
    'scholar');

I think it works the same if you like to add Bcc and/or Cc addresses.