database - PL/SQL: How select data from table and input into package accepting array type? -

i have pl/sql email package looks like:

create or replace package mail_pkg  pragma serially_reusable; -- avoids ora-04068 error type array table of varchar2(255);  procedure send( p_sender_email in varchar2,             p_from         in varchar2 default null,             p_to           in array default array(),             p_cc           in array default array(),             p_bcc          in array default array(),             p_subject      in varchar2 default null,             p_body         in clob default null); 

example usage be:

 begin     mail_pkg.send( p_sender_email => 'tim@company1.com',                p_from => 'john smith <johns@company2.com>',                p_to => mail_pkg.array( 'greg@company3.com','sarah@company4.com'),                p_cc => mail_pkg.array( 'admin@company5.com' ),                 p_bcc => mail_pkg.array( 'sue@company5.com' ),                 p_subject => 'this subject',                 p_body => 'hello, mail requested.' );     end; 

[note: looking e-mail package, here's link got from: http://asktom.oracle.com/pls/apex/f?p=100:11:0::::p11_question_id:255615160805 ]

i want use send email, email addresses in p_to input must input dynamically. is, can't hardcode them above. need come select statement. i'm familiar writing select statements, don't know how to result of selecte statement format can include in

p_to => mail_pkg.array( goes here??? ), 

code.

anyone know how can connect select statement (which returns email addresses) work package?

update:

based on feedback below, solution is:

create or replace procedure send_email (    in_name in varchar2  )   v_body clob;   v_to_array mail_pkg.array := mail_pkg.array(); v_counter int := 1;  begin  r in (select person_email email_table company_name=in_name) loop   v_to_array.extend;   v_to_array(v_counter) := r.person_email;   v_counter := v_counter +1; end loop;  -- send email  mail_pkg.send( p_sender_email => 'sam@company2.com',                p_from => 'admin@company2.com',                p_to => v_to_array,                p_bcc => mail_pkg.array( 'tim@company3.com' ),                 p_subject => 'the subject line goes here',                 p_body => 'this body message.' );    end send_email; 

you in 2 steps. first step use select statement in loop , populate array; second step use array in call mail_pkg.send, maybe this:

declare   v_counter number := 1;   v_to_arr mail_pkg.array := mail_pkg.array(); begin   r in select email_address table_of_email_addresses loop     v_to_arr.extend;     v_to_arr(v_counter) := r.email_address;     v_counter := v_counter +1;   end loop;    mail_pkg.send(p_to => v_to_arr, ... );   --note: code may require fine-tuning.   ... end ; 

---

as suggested in comments, bulk collect used here, might simple as:

declare   v_to_arr mail_pkg.array := mail_pkg.array(); begin    select email_address   bulk collect v_to_arr   table_of_email_addresses;    mail_pkg.send(p_to => v_to_arr, ... );   --note: code may require fine-tuning.   ... end ; 

i'm little rusty on bulk operations, might want read on them yourself.