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

Question

I have a PL/SQL email package that looks like:

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);

Example usage would be:

 begin
    mail_pkg.send( p_sender_email => '[email protected]',
               p_from => 'John Smith <[email protected]>',
               p_to => mail_pkg.array( '[email protected]','[email protected]'),
               p_cc => mail_pkg.array( '[email protected]' ), 
               p_bcc => mail_pkg.array( '[email protected]' ), 
               p_subject => 'This is my subject', 
               p_body => 'Hello, this is the mail you requested.' );
    end;

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

I want to use it to send an email, but the email addresses in the p_to 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_to => mail_pkg.array( WHAT GOES HERE??? ),

code.

Anyone know how I can connect a SELECT statement (which returns some email addresses) to work with this package?

UPDATE:

Based on feedback below, the solution is:

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 => '[email protected]',
               p_from => '[email protected]',
               p_to => v_to_array,
               p_bcc => mail_pkg.array( '[email protected]' ), 
               p_subject => 'the subject line goes here', 
               p_body => 'This is the body message.' );  

END send_email;

FrustratedWithFormsDesigner · Accepted Answer · 2013-05-08 16:44:12Z

3

You could do it in two steps. The first step is to use a SELECT statement in a loop and populate an array; the second step is to use that array in your call to mail_pkg.send, maybe something like this:

declare
  v_counter number := 1;
  v_to_arr mail_pkg.array := mail_pkg.array();
begin
  for r in Select email_address from 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: this code may require some fine-tuning.
  ...
end ;

As suggested in the comments, a bulk collect could probably be used here, might be as simple as:

declare
  v_to_arr mail_pkg.array := mail_pkg.array();
begin

  Select email_address
  bulk collect into v_to_arr
  from table_of_email_addresses;

  mail_pkg.send(p_to => v_to_arr, ... );
  --note: this code may require some fine-tuning.
  ...
end ;

I'm a little rusty on bulk operations, you might want to read up on them yourself.

edited May 8, 2013 at 16:44

answered May 8, 2013 at 15:29

FrustratedWithFormsDesigner

27.7k31 gold badges151 silver badges211 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

ggkmath Over a year ago

Thanks so much FrustratedWithFormsDes, I'm getting a compile error: PLS-00306: wrong number or types of arguments in call to 'SEND'. Any idea what it could be? (I'll update my original posting above to show what my current code looks like...)

FrustratedWithFormsDesigner Over a year ago

@ggkmath: That might be because I didn't complete the function call - too lazy to copy/paste all of your parameters. :P

FrustratedWithFormsDesigner Over a year ago

@ggkmath: Ok, I also updated to make sure that the v_to_arr was of type mail_pkg.array. If there is a globally defined type array that might not be compatible with mail_pkg.array.

ggkmath Over a year ago

That was it! Compiles fine now. Thanks so much!!!!! I've updated the UPDATE section to show the final code.

a1ex07 Over a year ago

Why not just SELECT ... BULK COLLECT INTO ?

Collectives™ on Stack Overflow

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

1 Answer 1

5 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related