Re: Question on the BULK COLLECT by a cursor using a database link

Jusung:

    With your suggestion, I tried on my procedure, now it works perfectly. Thanks.

C Chang

Jusung Yang wrote:

> Things like this are much easier in 9i. BULK COLLECT into PL/SQL
> record type without using database object type...
>
> This works with 9i, should work with 8i also.
>
> SQL> create or replace type o_typ_1 as object (object_name
> VARCHAR2(128), object_type VARCHAR2(18));
> 2 /
>
> Type created.
>
> SQL> create or replace type t_o_typ_1 as table of o_typ_1;
> 2 /
>
> Type created.
>
> SQL>
> SQL> declare
> 2 x t_o_typ_1;
> 3 begin
> 4 select cast(multiset(select object_name,object_type from
> user_objects_at_phoenix_scott
> 5 where rownum<10) as t_o_typ_1) into x from dual;
> 6 for i in 1..x.count loop
> 7 dbms_output.put_line(x(i).object_name||' '||x(i).object_type);
> 8 end loop;
> 9 end;
> 10 /
> ADDRESS_O TYPE
> ADDRESS_T TYPE
> BONUS TABLE
> DEPT TABLE
> EMP TABLE
> I_SNAP$_MV2_MVTEST INDEX
> I_SNAP$_MV_NESTED_FAST INDEX
> JYANG_SNAP_TEST TABLE
> JYANG_SNAP_TEST MATERIALIZED VIEW
>
> PL/SQL procedure successfully completed.
>
> SQL>
>
> - Jusung Yang
>
> cschang <cschang_at_maxinter.net> wrote in message news:<3F18ACC4.E4552E2D_at_maxinter.net>...
> > System: 8.1.6 on NT 4 w/ sp6a 6 HD PIII CPU and 1G RAM
> > In my procedure, I have a query to use remote database link to the data
> > source. I defined the query by a cursor. According to Tom and Oracle
> > knowledge, the FETCH ? BULK COLLECT INTO won?t work with the query using
> > the database link. Tom suggested to define a new array datatype
> > something as:
> > create or replace type vcArray as table of varchar2(35);
> > /
> > and a query such as
> > select cast( multiset( select username
> > from all_users_at_ora8i.world
> > where rownum <= 10 ) as vcArray )
> > into l_data
> > from dual;
> > to obtain all the records at once.
> > My question is : because the vcArray type is an one-dimensional array
> > type, how can I apply such trick for querying records of multiple
> > columns?
> > I have tried without success. Do I have to write there separated CAST(
> > MULTISET (?) in the outer SELECT..? It does not sound right. Can any
> > one give a hint? Thanks.
> >
> > C Chang
Received on Sat Aug 02 2003 - 23:13:03 CDT