Getting collection into varchar2 variable
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3

Thread: Getting collection into varchar2 variable

Hybrid View

  1. #1
    Join Date
    Jan 2001
    Posts
    63

    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

  2. #2
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    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:
    Code:
    ...
    my_variable := 'Inactive employees: ';
    for i in 1..my_varray.LAST loop
      my_variable := my_variable || ', ' || my_varray(i).ename;
    end loop;
    -- now send the message with my_variable as a body
    ...
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  3. #3
    Join Date
    Jan 2001
    Posts
    63
    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!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width