Re: REF's and Varrays..help!

Something like this might be what you need. The first part of the pl/sql gets all the object ids into the array. The second part shows how to get the data for each object id in turn

create type jpl_row as object (id number(4), name varchar2(32));
/

create type jpl_ref as object (jpl_row_ref ref jpl_row);
/

create type jpl_array as table of jpl_ref;
/

drop table jpl_demo;
create table jpl_demo of jpl_row;

insert into jpl_demo values (1,'jpl');
insert into jpl_demo values (2,'xyz');
insert into jpl_demo values (3,'abc');

declare
     v_refs jpl_array;
     v_row jpl_row;
begin
     select
      cast(
           multiset(
                select ref(p) from jpl_demo p
           ) as jpl_array
      )
     into v_refs
     from dual;

     dbms_output.put_line('Count: ' || v_refs.count);

 for i_ct in 1..v_refs.count loop
      select value(p)
      into v_row
      from jpl_demo p
      where ref(p) = v_refs(i_ct).jpl_row_ref;

      dbms_output.put_Line(v_row.name);

 end loop;

end;
.
/

--

Jonathan Lewis
Yet another Oracle-related web site: www.jlcomp.demon.co.uk

saks1000_at_my-deja.com wrote in message <7l41vu$ci2$1_at_nnrp1.deja.com>... Received on Sun Jun 27 1999 - 13:25:50 CDT