Re: PL/SQL Collections

[Daniel Morgan]

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
<collectionIs there an efficient way to do this?
>
sincerely,
>
paul@paulrowe.c om

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.c om.

--
Daniel Morgan

http://www.outreach.washington.edu/extinfo/certprog/oad/oad_crs.asp

damorgan@x.wash ington.edu
(replace 'x' with a 'u' to reply)

[Paul Rowe]

Re: PL/SQL Collections

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@paulrowe.c om


Daniel Morgan <damorgan@exxes olutions.comwro te in message news:<3F2AF7FB. 4A29C0C8@exxeso lutions.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
<collectionIs there an efficient way to do this?

sincerely,

paul@paulrowe.c om

>
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.c om.

[Daniel Morgan]

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@paulrowe.c om
>
Daniel Morgan <damorgan@exxes olutions.comwro te in message news:<3F2AF7FB. 4A29C0C8@exxeso lutions.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
<collectionIs there an efficient way to do this?
>
sincerely,
>
paul@paulrowe.c om

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.c om.

Just modify this sample to concatenate rather than compare:

DECLARE

TYPE Clientele IS TABLE OF VARCHAR2(64);
group1 Clientele := Clientele('Cust omer 1', 'Customer2');
group2 Clientele := Clientele('Cust omer 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@x.wash ington.edu
(replace 'x' with a 'u' to reply)