Re: Object Relational Feature of Oracle 8i

From: Chris Leonard <chris_at_databaseguy.com>
Date: Mon, 7 Jan 2002 11:48:34 -0600
Message-ID: <Ubl_7.13267$75.1740090@news.uswest.net>

Hello Tapas,

As for your first question (what is gained by defining the type before creating the table): I think you named one of the largest benefits when you said you can create the type once and then reference it many times. Other than that, maybe Oracle was just following the standard object-oriented approach of having creating the class definition before instantiating it.

As for your second question, here is a little script to illustrate the basic use of these object tables:

<snip>
create or replace type t_cust_obj

   as object (cust_id number, cust_name varchar2(30)) /

create table sales_cust (salesperson_id number, cust t_cust_obj); insert into sales_cust values (1, t_cust_obj(1000, 'Customer 1000')); </snip>

Notice that you can use the type name (t_cust_obj) as a "constructor" to insert values into the column. Here are a couple of sample SELECT statements with their output:

select * from sales_cust;

SALESPERSON_ID CUST(CUST_ID, CUST_NAME)

--------------    -----------------------------------------
             1    T_CUST_OBJ(1000, 'Customer 1000')

select salesperson_id, sc.cust.cust_id as cust_id, sc.cust.cust_name as cust_name
from sales_cust sc;

SALESPERSON_ID CUST_ID CUST_NAME

-------------- ---------- ------------------------------
             1       1000 Customer 1000

I hope this is enough to get you started.

Chris

---

Chris Leonard
MCSE, MCDBA, MCT, OCP, CIW
The Database Guy at PPI
http://www.propoint.com
Brainbench MVP for Oracle Admin
http://www.brainbench.com

"Tapas Guha" <tguha_at_sympatico.ca> wrote in message news:oX5_7.2954$p04.569383_at_news20.bellglobal.com...
> Recently I took the opportunity to explore the Oracle 8i User Defined
> Datatype and object relational Database Feature.
>
> But at this point, I am little confuse, so please help me to understand
this
> feature.

>

> If I am want to define a object table and store data then first of all i
> have to define a object type and then create a table based on that type.

>

> If what i said above is correct then, What user is gaining over defining a
> type first rather than defining directly the table ? I can understand that
> type define helps use the same type several times. But as far as the
object
> relational database goes i don't understand the benefit. If anybody can
> explain please do so.

>

> how do I insert data into a table where
> table structure is like this

>


> Salesperson_id Number

> Customer       cust_obj

>

> where cust_obj is consists of several defined attributes using different
> built in data types.

>

> Thanx in advance for ur help.

>

> Cheers

>
> Received on Mon Jan 07 2002 - 11:48:34 CST