Return two lists in PLSQL

I am trying to return a list of 3 VARRAYS/COLLECTIONS to my application. I am having trouble though, think I am either implementing the solution incorrectly

create or replace
PROCEDURE "GENERATE_PEOPLE" 
(
  -- In this开发者_如何转开发 example pi_string will be "This.is.a.test"
  pi_string         IN  VARCHAR2 ,
  po_firstnames     OUT VARRAY , 
  po_lastnames      OUT VARRAY ,
  po_descriptions   OUT VARRAY ,
  po_error_code     OUT VARCHAR2 ,
  po_error_message  OUT VARCHAR2 
)
IS
  CURSOR people_cursor IS SELECT firstname, lastname, description FROM people;   
BEGIN 

  FOR person_rec IN people_cursor
    LOOP
      -- This is where I am trying to return 3 collections of po_firstnames, po_lastnames, po_descriptions
      -- The print statements below print out exactly what it is I am trying to return!
      dbms_output.put_line('Firstname: '   || person_rec.firstname);
      dbms_output.put_line('Lastname: '    || person_rec.lastname); 
      dbms_output.put_line('Description: ' || pi_string || person_rec.description);

      -- This is where the values would be added to the list/array/collection
      po_firstnames(num?)   := person_rec.firstname;
      po_lastnames(num?)    := person_rec.lastname;
      po_descriptions(num?) := pi_string || person_rec.description;      
    END LOOP;
  RETURN;
END;

Any help is greatly appreciated

Oracle 10g

And I am calling it as such:

DECLARE 
  TYPE po_firstnames AS vc2_array;
  TYPE po_lastnames AS vc2_array;
  TYPE po_descriptions AS vc2_array;
  po_error_code VARCHAR2(50);
  po_error_message VARCHAR2(50);
BEGIN
  GENERATE_PEOPLE
  (
    'This.is.a.test' , 
    po_firstnames    ,
    po_lastnames     ,
    po_descriptions  ,
    po_error_code    ,
    po_error_message   
   );  
END;

VARRAY cannot be used directly as the type of a parameter or variable. Instead you need to create a TYPE that is a VARRAY like this (e.g.):

create type vc2_array as varray(100) of varchar2(4000);

then:

create or replace
PROCEDURE "GENERATE_PEOPLE" 
(
  -- In this example pi_string will be "This.is.a.test"
  pi_string         IN  VARCHAR2 ,
  po_firstnames     OUT vc2_array , 
  po_lastnames      OUT vc2_array ,
  po_descriptions   OUT vc2_array ,
  po_error_code     OUT VARCHAR2 ,
  po_error_message  OUT VARCHAR2 
)

Generally I would use TABLE rather than VARRAY, bacause with TABLE you do not have to specify a maximum number of elements:

create type vc2_array as table of varchar2(4000);

The values can then be assigned like this in your loop:

  num := num+1; -- num must be declared above and initialised to 0
  po_firstnames(num)   := person_rec.firstname;
  po_lastnames(num)    := person_rec.lastname;
  po_descriptions(num) := pi_string || person_rec.description;

However it would be more efficient to do this:

create or replace
PROCEDURE "GENERATE_PEOPLE" 
(
  -- In this example pi_string will be "This.is.a.test"
  pi_string         IN  VARCHAR2 ,
  po_firstnames     OUT vc2_array , 
  po_lastnames      OUT vc2_array ,
  po_descriptions   OUT vc2_array ,
  po_error_code     OUT VARCHAR2 ,
  po_error_message  OUT VARCHAR2 
)
IS
BEGIN
  SELECT firstname, lastname, pi_string||description
  BULK COLLECT INTO po_firstnames, po_lastnames, po_descriptions
  FROM people;
END;