Hi,

I'm writing a PL/SQL program to send emails. I copied a email packages
from the web and it works fine. However, I have problem if I put the
email ids as variable instead of hardcoded in the calling program.

The package header is as follows:

create or replace package PGCPS_MAIL_PKG
as
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 long default null );
end PGCPS_MAIL_PKG;
/

In my calling program:

set serveroutput on size 1000000
declare

l_quote VARCHAR2(1) := '''';
v_to varchar2(100) := l_quote||'abc (AT) abc (DOT) org'||l_quote||', '||
l_quote||'abc (AT) abc (DOT) com'||l_quote;
-- v_to varchar2(100) := 'abc (AT) abc (DOT) org' ||', abc (AT) abc (DOT) com';

v_to_test PGCPS_MAIL_PKG.array;

begin
v_to_test := PGCPS_MAIL_PKG.array('abc (AT) abc (DOT) org', 'abc (AT) abc (DOT) com'); --
WORKS
-- v_to_test := PGCPS_MAIL_PKG.array(v_to); -- DOES NOT WORK
dbms_output.put_line(v_to);
PGCPS_MAIL_PKG.send
(
p_sender_email => 'abc (AT) abc (DOT) org',
p_to => v_to_test,
p_subject => 'This is a subject',
p_body => 'Hello Andy, this is the mail you need!!!'
);
end;
/

Is it possible using variable to build a TABLE?

Thanks, Andew

I found that I need to use a constructor, such as v_to_test :=
PGCPS_MAIL_PKG.array(1,2,3,4,5,6,7,8,9,10); before I assign a value to
element. It seems I have to give it some numbers. If I use v_to_test :=
PGCPS_MAIL_PKG.array(), it still fails.

Andrew