Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: REF's and Varrays..help!

Re: REF's and Varrays..help!

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Mon, 28 Jun 1999 09:29:39 +0100
Message-ID: <930558883.10479.0.nnrp-03.9e984b29@news.demon.co.uk>


A variation on a theme:
if you want to get the OIDs and the data at the same time you could do something like this:

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

create type jpl_row_array as table of jpl_row; /

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

create type jpl_ref_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_ref_array := jpl_ref_array();
     v_rows jpl_row_array := jpl_row_array();

     v_row jpl_row;

     cursor c1 is
          select
               ref(p)  ref_bit,
               value(p)  val_bit
          from
               jpl_demo p;

begin
     for r1 in c1 loop

          v_refs.extend;
          v_rows.extend;

          v_refs(v_refs.last) := jpl_ref(r1.ref_bit);
          v_rows(v_rows.last) :=  r1.val_bit;

     end loop;

     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 || ' - ' ||
               v_rows(i_ct).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>...
>Does anybody know how to take the OIDs of an object table and put it
>into a varray? This is the setup:
>
>declare
> person_clubs array_type;
>begin
> SELECT ______ INTO person_clubs FROM
> clubs c WHERE c.Name = 'some_name';
>end;
Received on Mon Jun 28 1999 - 03:29:39 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US