Ignoring null email field in oracle apex mail script

Question

I'm currently building a staffing CRM in Oracle Apex for my company and we have a lead generation form that allows recruiters to add any sales leads.

Once a recruiter adds a lead the page sends an email to the assigned sales person.

Now here's my issue, I need to get APEX.MAIL to ignore any null email address fields on the form page since management wants to be able to assign up to 3 sales people to receive that lead, though they may only assign 1 or 2.

How can I make APEX.MAIL ignore the page item for the sales email if the field is null.

Right now the "To" line is p_to=> :P3_SALESMAN

How I want it to look is p_to=> :P3_SALESMAN, :P3_SALESMAN2, :P3_SALESMAN,3,

But if :P3_SALESMAN2 or :P3_SALESMAN3 are null, I want APEX.MAIL to ignore them.

Or would that even cause a problem is the field is null?

Answer 1

You don't indicate how you are calling APEX.MAIL...

Since APEX.MAIL is a PLSQL pkg, one suggestion is to write a wrapper PLSQL pkg that will look at the parameters you pass in (P3_SALESMAN1.2,3...) and use If Else logic to evaluate the fields as null or not and then determine whether to call the APEX.MAIL pkg.

Writing in-line PLSQL in APEX quickly becomes a maintenance and reusability headache, so you should think about always using a PLSQL stored Proc/Func to encapsulate your logic and call it from the page.

Answer 2

Like Joe mentioned, using stored procedure is better than writing in-line pl/sql, which I learnt the hard way.

About building the list of email addresses, I can't think of another way, but to use if-else condition, and adding email addresses to a string, if they exist and passing the final string to p_to in APEX_MAIL.