Re: REF's and Varrays..help!

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Sun, 27 Jun 1999 13:48:26 GMT
Message-ID: <377b2b82.23955095@newshost.us.oracle.com>

A copy of this was sent to saks1000_at_my-deja.com (if that email address didn't require changing) On Sun, 27 Jun 1999 02:24:04 GMT, you wrote:

>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;
>
>
>
>"array_type" is a varray of refs. The refs are associated to the same
>type of objects in the object tabble "clubs". Any ideas would be great!
>Thanks,
>
>Pablo

It could look like this:

SQL> create or replace type myObjectType as object   2 ( name varchar2(25) )
  3 /
Type created.

SQL> create or replace type array_type as varray(25) of ref myObjectType   2 /
Type created.

SQL> create table clubs of myObjectType; Table created.

SQL> declare
  2 l_string varchar2(25);
  3 begin

  4      for i in 1 .. 20 loop
  5          l_string := 'name-' || i;
  6          insert into clubs values ( myObjecttype( l_string ) );
  7      end loop;
  8      commit;

  9 end;
 10 /
PL/SQL procedure successfully completed.

SQL> declare
  2 person_clubs array_type;
  3 begin

  4      select cast( multiset( select ref(c)
  5                               from clubs c
  6                              where c.name like 'name%' )
  7                   AS array_type )
  8        into person_clubs
  9        from dual;

 10 end;
 11 /
PL/SQL procedure successfully completed.

>
>
>
>
>Sent via Deja.com http://www.deja.com/
>Share what you know. Learn what you don't.

--
See http://govt.us.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'... Current article is "Part I of V, Autonomous Transactions" updated June 21'st  

Thomas Kyte                   tkyte_at_us.oracle.com
Oracle Service Industries     Reston, VA   USA

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Sun Jun 27 1999 - 08:48:26 CDT