Can We Assign Multiple Email Addresses to a Single Variable While Sending Emails Through PL/SQL?

I am using PL/SQL for sending e-mails. I am having a requirement in which number of recipients are variable and more then ten. Instead of using separate variables for individual e-mail recipients, is there any way to use a single variable and assign all the email addresses to that only. So that I will be able to write only a single variable for sending mails in the code. Is it possible?

For a test case, I assigned three email address to a single variable
( l_recipients := email@removed , ' || email@removed , ' || email@removed ' ) but this code writing failed. I am not getting any emails by using this method. Is there any other method? Or can anyone correct me, if I am wrong in writing and using this particular code? Any suggestions will be helpful.

I am using the UTL_SMTP package and the UTL_SMTP.RCPT function receives varchar2 as recipient email address as parameter. But in my case, do I have to write separate function calls (utl_smtp.rcpt) for each recipient?

The problem is, the number of recipient is also not constant. So, how will I handle this?

Judith has provided the solution. Use a loop, send them out individually. It may be that you can batch them up by providing the emails in a list separated with a semicolon or some such but I have used Judith's approach and it is pretty foolproof.

As a general rule (which somebody else mentioned), but depending on your situation, it is better to mail to lists that are managed by an email directory such as Active Directory/LDAP/Exchange etc rather than try to send out lots of (hardwired) emails one at a time to individuals. PL/SQL should always be able to succeed when it sends mail, which won't be the case if somebody's individual email changes in your 'world'. Mailing lists do have to be managed, but they are less likely to mutate or be invalid than individual email addresses. This may of course be irrelevant for your situation.

Your error handling here is very weak. It is entirely possible that one of your email addresses may fail but you should handle that more gracefully than just blowing up. It might be worth reviewing the exceptions raised by utl_smtp. WHEN OTHERS is lazy. It ought to be the last exception handler, not the only one.

Copyright 1998-2015 Ziff Davis, LLC (Toolbox.com). All rights reserved. All product names are trademarks of their respective companies. Toolbox.com is not
affiliated with or endorsed by any company listed at this site.