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

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 Jul 19 2003 - 10:30:53 CDT