title: PL/SQL: How select data from table and input into package accepting array type?
body: <p>I have a PL/SQL email package that looks like:</p>
  
  <pre><code>create or replace package mail_pkg 
  as
  PRAGMA SERIALLY_REUSABLE; -- this avoids ORA-04068 error
  type array is 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);
  </code></pre>
  
  <p>Example usage would be:</p>
  
  <pre><code> begin
      mail_pkg.send( p_sender_email =&gt; 'tim@company1.com',
                 p_from =&gt; 'John Smith &lt;johns@company2.com&gt;',
                 p_to =&gt; mail_pkg.array( 'greg@company3.com','sarah@company4.com'),
                 p_cc =&gt; mail_pkg.array( 'admin@company5.com' ), 
                 p_bcc =&gt; mail_pkg.array( 'sue@company5.com' ), 
                 p_subject =&gt; 'This is my subject', 
                 p_body =&gt; 'Hello, this is the mail you requested.' );
      end;
  </code></pre>
  
  <p>[note: for anyone looking for an e-mail package, here's the link I got it from:
  <a href="http://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:255615160805" rel="nofollow">http://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:255615160805</a> ]</p>
  
  <p>I want to use it to send an email, but the email addresses in the <code>p_to</code> input must be input dynamically. That is, I can't hardcode them like above. They need to come from a SELECT statement. I'm familiar with writing select statements, but I don't know how to to get the result of the selecte statement into a format that I can include in the </p>
  
  <pre><code>p_to =&gt; mail_pkg.array( WHAT GOES HERE??? ),
  </code></pre>
  
  <p>code. </p>
  
  <p>Anyone know how I can connect a SELECT statement (which returns some email addresses) to work with this package?</p>
  
  <p>UPDATE:</p>
  
  <p>Based on feedback below, the solution is:</p>
  
  <pre><code>create or replace procedure send_email ( 
    in_name IN varchar2
   )
  
  AS
  
  v_body clob;  
  v_to_array mail_pkg.array := mail_pkg.array();
  v_counter int := 1;
  
  BEGIN
  
  FOR r IN (SELECT person_email FROM email_table WHERE 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 =&gt; 'sam@company2.com',
                 p_from =&gt; 'admin@company2.com',
                 p_to =&gt; v_to_array,
                 p_bcc =&gt; mail_pkg.array( 'tim@company3.com' ), 
                 p_subject =&gt; 'the subject line goes here', 
                 p_body =&gt; 'This is the body message.' );  
  
  END send_email;
  </code></pre>