Re: PL/SQL Collections

Paul Rowe wrote:

> Hi Daniel;
>
> Thanks for your help.. appreciate it... but I have one more request.
> My expertise is in Java, not Oracle PL/SQL ... so I am a bit dense
> when it comes to Oracle. I searched around usenet and asktom a bit
> and I could not find an exact match to what I am looking for. Could
> you provide a couple of links ? Thanks in advance.
>
> Sincerely,
>
> Paul_at_paulrowe.com
>
> Daniel Morgan <damorgan_at_exxesolutions.com> wrote in message news:<3F2AF7FB.4A29C0C8_at_exxesolutions.com>...
> > Paul Rowe wrote:
> >
> > > I have questions regarding PL/SQL Collections:
> > >
> > > 1. Is there an easy way to concatenate two like-typed collections
> > > together? I have to use a FOR LOOP which may not be efficient.
> >
> > It is very efficient as it takes place entirely in memory. You'll see
> > almost no performance hit doing it.
> >
> > >
> > > 2. Is there an anyway to put a collection into an IN clause? For
> > > instance, if I have a collection of varchar2 objects, and I want to
> > > execute a query like SELECT * from aTable where identifier IN
> > > <collection> Is there an efficient way to do this?
> > >
> > > sincerely,
> > >
> > > paul_at_paulrowe.com
> >
> > There is and it was posted here on the usenet within the last 60 days or
> > so. Likely also you will find it at asktom.oracle.com.

Just modify this sample to concatenate rather than compare:

DECLARE TYPE Clientele IS TABLE OF VARCHAR2(64); group1 Clientele := Clientele('Customer 1', 'Customer2'); group2 Clientele := Clientele('Customer 1', 'Customer3');

BEGIN

• Equality test causes compilation error. FOR i IN 1..2 LOOP IF group1(i) = group2(i) THEN dbms_output.put_line('It Is Equal'); ELSE
  • dbms_output.put_line('It Not Is Equal'); dbms_output.put_line(group1(i) || ' Is Not The Same As ' || group2(i)); END IF; END LOOP; END; /

--
Daniel Morgan
http://www.outreach.washington.edu/extinfo/certprog/oad/oad_crs.asp
damorgan_at_x.washington.edu
(replace 'x' with a 'u' to reply)