Re: bull collect question

kiurek_at_poczta.onet.pl (Grzegorz Mackiewicz) wrote in message news:<c47cd990.0206132358.12d33296_at_posting.google.com>...
> dyou98_at_aol.com (D.Y.) wrote in message news:<f369a0eb.0206110850.2b3cbdc7_at_posting.google.com>...
> > kiurek_at_poczta.onet.pl (Grzegorz Mackiewicz) wrote in message news:<c47cd990.0206110049.7df96ce9_at_posting.google.com>...
> > > hello,
> > >
> > > I'm having some problems using BULK COLLECT operator with collections.
> > > The following does not work for me:
> > >
> > > I'm building the following dynamic cursor statement:
> > >
> > > sql_text:='SELECT CAST(MULTISET(SELECT T1.c1, T2.c2, T3.c3
> > > FROM TABLE1 T1, TABLE(CAST(:col AS COLLECTION1)) T2
> > > WHERE T1.c1=T2.c1) AS COLLECTION2) FROM DUAL';
> > >
> > > OPEN cv FOR sql_text USING c1;
> > >
> > > and now when using
> > > LOOP
> > > FETCH cv INTO c2;
> > > EXIT WHEN cv%NOTFOUND;
> > > END LOOP;
> > >
> > Looks like your cursur returns collections and c2 is defined that way.
> > So this regular fetch works fine.
> >
> > > everything is just fine
> > > But, when trying to use BULK COLLECT here:
> > >
> > > FETCH cv BULK COLLECT INTO c2;
> > >
> >
> > Here you are trying to fetch a collection of collections from the cursor.
> > I am not sure if it's permitted. Even if it is, you may need to redefine c2
> > as a collection of objects (collection2 in your example). This is just a
> > thought. I haven't tested it.
>
> Thanks,
>
> What I'd like to achieve is the same result set as in the previous
> example. How can I do this using BULK COLLECT?
>
> What do you mean I'm trying to get a collection of collections?
> Perhaps I'm not understanding how BULK COLLECT works...
>

I was having trouble with my browser. If you already got this posting please
ignore.

Bulk collect returns a collection of records from your cursor. Each logical
record from your cursor already is a collection. That's why I mentioned
collection of collections (records).

In your example, selecting from dual only returns one logical record so there
is really no need to think about bulk collect. In fact, and I just thought
of this, you may not be able to use bulk collect if your records already are collections. The reason is, you would need to define either bounded (varray)
or unbounded (PL/SQL table) arrays to hold the records. But I don't think you
can define arrays of collections in Oracle8i. I am not sure about 9i. Please
test it yourself.

FYI, it's best to post this kind of questions to comp.databases.oracle.server.
There is much more traffic there, and you'll get quicker answers.

> regards,
> Grzegorz
Received on Tue Jun 18 2002 - 18:45:23 CEST