I will be writing a procedure to read some employee data from our HR system and update a couple of employee tables in our work order system, joining on employee number. One of the things that needs to be reported to the work order system administrator is if an employee changes to an "inactive" status in the HR system. We are using the utl_smtp package to send emails for this notification, but only 1 email is wanted for all newly inactive employees found in a run of the procedure...not one e-mail per inactive employee. So, as I loop through my cursor of HR data, I'm trying to find a way to "collect" these employees and at the end, send the collection to the e-mail package as the "BODY" of the e-mail message which is a varchar2 datatype.
I've successfully used a VARRAY to collect the employees, but then don't know how to get that collection into a VARCHAR2 variable to pass to the e-mail package.