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; 

CodePudding user response：

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.

See also how to export data from log table to email body in oracle

CodePudding user response：

From the documentation, the comma_to_table procedure generates:

A PL/SQL table is returned, with values 1..n and n 1 is null.

So you are passing in a comma-separated list of 4 values, but you get at table with 5 rows, the fifth being null. It is that final null element that is causing an error when you send null as a recipient.

You can see that if you look at the a.COUNT value, or just the current output, from your final snippet - see this db<>fiddle.

You can either change your loop to

    FOR i IN 1..(a.COUNT - 1) LOOP

or more safely - in case you ever have a list with an empty element - skip nulls:

    FOR i IN 1..a.COUNT LOOP
        IF a(i) IS NOT NULL THEN
          dbms_output.put_line( a(i) );
        END IF;
    END LOOP;

You should also be sending To: and CC: headers (as lines in the data section) with your p_to and p_cc strings - which you may already be doing in the rest of the procedure, which you haven't shown - but should not send a BCC: header. That is really the only difference between CC and BCC; they are all recipients, but the latter are not identified in a header.

Also, the limitations of comma_to_table mean it may not really be suitable here - email addresses can be longer than 30 characters, and can include multibyte characters. Using collections (as @Wernfried showed) might be simpler. And you might want to look at the UTL_MAIL package as an alternative to UTL_SMTP.