Thanks for the question, Rick.

Asked: August 16, 2002 - 5:49 pm UTC

Last updated: November 30, 2018 - 6:19 am UTC

Version: 8.1.7

Viewed 1000+ times

You Asked

Hi Tom. I'm trying to get your package mail_pkg (found it on this site) to accept multiple values for the p_to (recipient list).

begin
mail_pkg.send
( p_sender_email => 'me@acme.com',
p_from => 'Oracle Database Account <me@acme.com>',
p_to => mail_pkg.array( 'coyote@acme.com', 'roadrunner@acme.com' ),
p_cc => mail_pkg.array( 'kermit@acme.com' ),
p_bcc => mail_pkg.array( 'noone@dev.null' ),
p_subject => 'This is a subject',
p_body => 'Hello Tom, this is the mail you need' );
end;
is how you were executing it. That part all works, but I cannot seem to wrap my brain around how to return multiple email addresses from a table. the table has several rows, each with a separate email address in the contactemail column. how do I select all of these emails into the variables to pass them to the p_to array?

I'm sure it's something simple, but I simply cannot get it to work.
I've tried a bunch of things but I'm stuck.

TIA,

Rick

and Tom said...

You can just use a select into like this:

ops$tkyte@ORA8I.WORLD>
ops$tkyte@ORA8I.WORLD> declare
2 l_to mail_pkg.array;
3 begin
4 select username BULK COLLECT INTO l_to
5 from all_users
6 where rownum <= 10;
7 end;
8 /

PL/SQL procedure successfully completed.

or:

declare
l_to mail_pkg.array := mail_pkg.array();
begin
for x in ( select .... )
loop
l_to.extend;
l_to(l_to.count) := x.some_field;
end loop;

Rating

(15 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

sheesh

Rick, August 16, 2002 - 8:06 pm UTC

how simple! I was looking at infinitely harder solutions, none of which worked very elegantly (and most not at all!).

Thanks again,

Rick

yep, me again...

Rick, September 05, 2002 - 7:09 pm UTC

Requirements have changed somewhat. I am currently using your procedure html_email and it works great! The only drawback is that I need to send the identical email to several people at once, so looping through the list is simply taking too much time. How would I customize the html_email procedure to accept multiple email addresses ( p_to ) similar to the mail_pkg package, but without losing the html_email functionality?

Thanks again Tom for this great service and your interesting answers.

Cheers,

Rick

September 05, 2002 - 11:16 pm UTC

this is in reference to the procedure at:

</code> http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:1739411218448




Well, you would change the code that is:

begin
    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 );




into code that looks like mail_pkg.send:

http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:255615160805 <code>

Thanks again!

Rick, September 06, 2002 - 12:44 pm UTC

uhhh...

Rick, September 06, 2002 - 1:23 pm UTC

I got most of it to go. What I'm doing is using one of your functions (get_transposed (think it was yours)) to contcatenate the emails into a string, like:
'joe@abc.com','asdf@asdf.com','asdf@erere.com' etc.
This all works fine. if I cut and paste that into
the ( p_to => ) (like at the top of this page) then it works. Yet when I assign that string to a varchar2 variable, it returns me the error:

ORA-20002: 553 Invalid address syntax

Any idea why it works when it's pasted manually, but not in a variable?

here's how I'm sending it:

begin
mail_pkg2.send2
('me@notrealemail.com',
'Oracle Database <me2@notrealemail.com>',
mail_pkg2.array(p_to),
mail_pkg2.array(bccme@notrealemail.com),
'This is a subject',
'This is the email you sent',
'<html><body> test email</body></html>');
end;
/

Cheers,

Rick

September 06, 2002 - 1:40 pm UTC

Add some dbms_outputs right before the utl_smtp call that pumps out this string to see what is actually getting there. Something like:

dbms_output.put_line( '"' || p_to || '"' );
for x in ( select dump(p_to) x from dual )
loop
dbms_output.put_line( x.x );
end loop;

(and don't forget to remove the dbms_output after you figure it out since it has a 255 byte/line limit and will undoubtably cause you heartburn later if you leave it in!)

forgot the quotes

A reader, September 06, 2002 - 1:30 pm UTC

that should read:

begin
mail_pkg2.send2
('me@notrealemail.com',
'Oracle Database <me2@notrealemail.com>',
mail_pkg2.array(p_to),
mail_pkg2.array('bccme@notrealemail.com'),
'This is a subject',
'This is the email you sent',
'<html><body> test email</body></html>');
end;
/

good point of dbms_output.put_line

Rick, September 06, 2002 - 1:49 pm UTC

Anyways, I tried it. The results look exactly like what I'm
putting into the string that I am pasting manually:

"'addy1 <fake1@email.com>','addy2 <fake2@email.com>'"

(I've replace the actual email addresses, but you get the gist). I don't see a problem there. I just cut 'n pasted that line (minus the double quotes) into the send part of the procedure and it worked fine.

This is very irritating, and I'm sorry if I'm wasting your time.

Thanks,

Rick

;-)

September 06, 2002 - 2:32 pm UTC

what about the DUMP (maybe there is some magic bad character we cannot see hiding in there!)

here's the dump(p_to)

Rick, September 06, 2002 - 2:48 pm UTC

Typ=1 Len=65: 39,66,114,105,99,107,32,60,98,97,110,100,101,114,115,111,110,64,80,114,101,115,105,110,101,116,46,99,111,109,62,39,44,39,66,114,105,99,107,97,103,97,105,110,32,60,98,114,105,99,107,108,101,110,97,64,80,114,101,115,105,110,101,116,46,99,111,109,62,39

I had to concatenate the dump together (substr(p_to,1,65)) and substr(p_to,66,100 (approx.))

September 06, 2002 - 3:51 pm UTC

and if you compare that to the dump of the cut and pasted example we see??

tested a few things

you know my name by now..., September 06, 2002 - 3:26 pm UTC

If I put the separate email addresses inside quotes, like so:
'''adsf@asdf.com','asdf_adsf@asdss.com'''
then it is treated as one long string, and the addresses are rendered invalid (invalid address error). Could, by concatenating the various email addresses into a string with this function:

create or replace function concat_emails(p_idnum in number )
return varchar2
is
l_str varchar2(1000) default null;
l_sep varchar2(1) default null;
begin
for get_email_string in (
select distinct ''''||contactname||' <'||contactemail||'>'||'''' contactinfo
from contacts
where contactemail is not null
and (clientid = p_idnum or tagid = p_idnum)
and receive_alerts = 'Y') loop
l_str := l_str || l_sep || get_email_string.contactinfo;
l_sep := ',';
end loop;
return l_str;
end;
/
show errors

possibly be the problem? Seems likely at this point.

how else could I do a bulk send of the email using the mail_pkg.array(p_to); ??

My ignorance is blinding at times....

;-]

re: dump

Rick, September 06, 2002 - 4:21 pm UTC

if you mean by the p_to dump, and the dump of the actual string (the one that works) then they are identical.

If this is taking up too much of your time, just let me know in a private email.

Cheers,

Rick

September 06, 2002 - 7:22 pm UTC

If they are IDENTICAL all the way down - then, well, I'm stumped. I don't have any idea at that point. If the input to the utl_smtp call is identical byte for byte -- I don't know what to say.

heh heh

Rick, September 06, 2002 - 7:38 pm UTC

sorry about that. I've been pulling my hair out all day, but I think I may have an idea how to get around it.
What I'm going to do is parse the concatenated string of emails into separate variables and just pass them to the p_to line, separated by commas. it works when I put variables in like that, containing only one value.

Oh well, I'm learning lots (mainly at your expense!!)

thanks again,

Rick the pest.

;-]

how to add multiple email address in UTL_SMTP.mail

A reader, December 07, 2011 - 12:03 pm UTC

Can you have the email package send out one email to all the recipients, instead of one email to each person?

December 07, 2011 - 1:40 pm UTC

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1739411218448

Emails

A reader, December 07, 2011 - 2:51 pm UTC

return multiple address to mail_pkg

liz, November 26, 2018 - 12:56 pm UTC

Hi tom, still on the send mail_pkg i have executed the code below to get email addresses separated by semicolon from db table into l_to, but its throwing error...PLS-00306: wrong number or types of arguments in call to '||'.
kindly assist me resolve for i'm stuck
DECLARE
l_to mail_pkg.array := mail_pkg.array();
l_sep varchar2(1) default null;
BEGIN
FOR get_emailstring IN
(select EAEMAL bulk collect
INTO l_to from T_EMAIL where EAAN8 in (select LBAN8 FROM LEAVE WHERE LBLTTP = 'AL' AND (LBLEAVCOM/100) > 15) and EAEMAL is not null)
LOOP
l_sep := ';';
l_to := l_to || l_sep|| get_emailstring.EAEMAL;
dbms_output.put_line(l_to||';'||get_emailstring.EAEMAL);
END LOOP;
END;
/

November 26, 2018 - 5:09 pm UTC

You don't want to bulk collect INSIDE a for loop. That's just weird.

Remove "bulk collect INTO l_to" and you should be fine.

return multiple address to mail_pkg

liz, November 28, 2018 - 8:23 am UTC

Hi again, I managed to use your feedback to edit the code as below but now I can't send mail to each of the addresses returned by cursor, even though the procedure completes successfully. I believe the problem lies in this line of code - p_to => mail_pkg.array(a_store). Please assist me!

l_to mail_pkg.array := mail_pkg.array();
l_sep varchar2(1) default null;
a_store VARCHAR2 (32767);
CURSOR CUR1 IS
SELECT DISTINCT EAEMAL FROM T_EMAIL where EAAN8 in (select LBAN8 FROM LEAVE WHERE LBLTTP = 'AL' AND (LBLEAVCOM/100) > 15) and (EAEMAL is not null and trim(EAEMAL) like '%@%');
BEGIN
OPEN CUR1;
FETCH CUR1 BULK COLLECT INTO l_to;
CLOSE CUR1;
FOR indx IN l_to.first..l_to.last
LOOP
l_sep := ';';
a_store := l_to(indx);
END LOOP;
mail_pkg.send (p_sender_email => 'JdeAdmin@???.co.ke',
p_from => 'DoNotReply@Jde.com',
p_to => mail_pkg.array(a_store),
p_cc => mail_pkg.array( ),
p_bcc => mail_pkg.array( ),
p_subject => 'LEAVE BALANCE NOTICE',
p_body => 'Hello, this requires your immediate attention. Your annual leave balance is above 15 days and you are at risk of loosing them if you fail to go before beginning of new financial year.' );
END;
/

November 29, 2018 - 12:47 am UTC

Change

p_to => mail_pkg.array(a_store),

p_to => l_to,

We are passing the array, not the string of addresses

return multiple address to mail_pkg

liz, November 30, 2018 - 5:21 am UTC

Thank you very much tom. I'm sorted.

November 30, 2018 - 6:19 am UTC

Glad we could help

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library

Breadcrumb

Question and Answer

You Asked

and Tom said...

Rating

Comments

sheesh

yep, me again...

Thanks again!

uhhh...

forgot the quotes

good point of dbms_output.put_line

here's the dump(p_to)

tested a few things

re: dump

heh heh

how to add multiple email address in UTL_SMTP.mail

Emails

return multiple address to mail_pkg

return multiple address to mail_pkg

return multiple address to mail_pkg

More to Explore

PL/SQL demos

PL/SQL docs