Database Reference
In-Depth Information
EODA@ORA12CR1> desc people
Name Null? Type
---------------------------------------- -------- ----------------------------
NAME VARCHAR2(30)
DOB DATE
HOME_ADDRESS ADDRESS_TYPE
WORK_ADDRESS ADDRESS_TYPE
In a nutshell, that's all there is to it. We create some type definitions, and then we can create tables of that type.
The table appears to have four columns representing the four attributes of the
PERSON_TYPE
we created. We are at the
point where we can now perform DML on the object table to create and query data:
EODA@ORA12CR1> insert into people values ( 'Tom', '15-mar-1965',
2 address_type( 'Denver', '123 Main Street', 'Co', '12345' ),
3 address_type( 'Redwood', '1 Oracle Way', 'Ca', '23456' ) );
1 row created.
EODA@ORA12CR1> select name, dob, p.home_address Home, p.work_address work
2 from people p;
Tom 15-MAR-65
ADDRESS_TYPE('Denver', '123 Main Street', 'Co', 12345)
ADDRESS_TYPE('Redwood', '1 Oracle Way', 'Ca', 23456)
EODA@ORA12CR1> select name, p.home_address.city from people p;
NAME HOME_ADDRESS.CITY
------------------------------ ------------------------------
Tom Denver
We're starting to see some of the object syntax necessary to deal with object types. For example, in the
INSERT
statement we had to wrap the
HOME_ADDRESS
and
WORK_ADDRESS
with a
CAST
. We cast the scalar values to be of an
ADDRESS_TYPE
. Another way of saying this is that we create an
ADDRESS_TYPE
instance for that row by using the default
constructor for the
ADDRESS_TYPE
object.
Now, as far as the external face of the table is concerned, there are four columns in our table. By now, after seeing
the hidden magic that took place for the nested tables, we can probably guess that there is something else going on.
Oracle stores all object relational data in plain old relational tables—at the end of the day, it is all in rows and columns.
If we dig into the real data dictionary, we can see what this table really looks like:
EODA@ORA12CR1> select name, segcollength
2 from sys.col$
3 where obj# = ( select object_id
4 from user_objects
5 where object_name = 'PEOPLE' )
6 /
