Getting collection into varchar2 variable
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.
How can I do this, or what should I do instead??
Thanks in advance
Once you have collected all the inactive employees into your varray, simply loop through all its ellements and concatenate the employees into a variable. Something like this:
my_variable := 'Inactive employees: ';
for i in 1..my_varray.LAST loop
my_variable := my_variable || ', ' || my_varray(i).ename;
-- now send the message with my_variable as a body
ASCII a stupid question, get a stupid ANSI
24 hours in a day .... 24 beer in a case .... coincidence?
Perfect, Jurij; thank you!
I have not used arrays much and have been unclear about referencing the elements, but your recommendation has worked great.
Happy New Year!
Click Here to Expand Forum to Full Width