data type TABLE of Oracle

From: <andyho99_at_yahoo.com>
Date: 3 May 2006 14:05:22 -0700
Message-ID: <1146690322.697856.241740@g10g2000cwb.googlegroups.com>

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.org'||l_quote||', '||
l_quote||'abc_at_abc.com'||l_quote;

-- v_to varchar2(100) := 'abc_at_abc.org' ||', abc_at_abc.com';

v_to_test PGCPS_MAIL_PKG.array;

begin

        v_to_test := PGCPS_MAIL_PKG.array('abc_at_abc.org', 'abc_at_abc.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.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 Received on Wed May 03 2006 - 16:05:22 CDT