Re: Type object and object table

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Sun, 28 Nov 1999 08:57:38 -0500
Message-ID: <n4d24sc5jleorpe5mhsv11o06u5p86f4ul_at_4ax.com>

A copy of this was sent to "Zaida Chora" <jzach_at_esoterica.pt> (if that email address didn't require changing) On Sun, 28 Nov 1999 12:37:14 -0000, you wrote:

>Hi
>I need some help to work with objects.
>I have a type cpost and a related table cpostal.
>
>CREATE TYPE cpost AS OBJECT
>(codchave NUMBER(8),
> cpostal NUMBER (7),
> design VARCHAR2(50));
>
>CREATE TABLE cpostal OF cpost;
>
>I have another type person that references cpost
>
>CREATE TYPE person AS OBJECT
>(codigo NUMBER (9),
> nome VARCHAR2 (90),
> edrua VARCHAR2 (60),
> edcpost REF cpost);
>
>CREATE TABLE people OF person;
>
>How can I insert a record in people having a edcpost that exists in cpostal
>??
> thanks
>
>Zaida
>

Just like you would in a relational schema with a 1->M relationship. Here are 2 examples. the first is the "insert and then update" and the other is "insert as select"

tkyte_at_8i> insert into cpostal values (cpost( 1, 1, 'a' ) );

1 row created.

tkyte_at_8i> insert into cpostal values (cpost( 2, 2, 'a' ) );

1 row created.

tkyte_at_8i>
tkyte_at_8i> insert into people values
  2 ( person( 1, 'tom', 'x', null ) );

1 row created.

tkyte_at_8i>
tkyte_at_8i> update people
  2 set edcpost = ( select ref(c) from cpostal c where codchave=1)   3 where codigo = 1
  4 /

1 row updated.

tkyte_at_8i> 
tkyte_at_8i> 
tkyte_at_8i> insert into people

  2 select person( 2, 'mary', 'y', ref(c) )   3 from cpostal c
  4 where codchave = 2
  5 /

1 row created.

-- 
See http://osi.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 Nov 28 1999 - 14:57:38 CET