Re: bull collect question

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.

> I'm getting a runtime error
> ORA-00932: inconsistent datatypes
>
> What can that be? The datatypes do not seem to be inconsistent, they
> work with with regular FETCH INTO. Perhaps such usage of BULK COLLECT
> is not permitted (why?), it's just the error message that is
> confusing?
>
> thanks in advance
> Grzegorz
Received on Tue Jun 11 2002 - 18:50:56 CEST