Skip to main content


Showing posts from February, 2006

The Rise and Fall (and Rise again) of Object Types

Object Types are available since Oracle 8i, but just recently I encountered a situation were they seemed to be usefull.

Picture this:
We have a product that has a certain size (length, width and heigth) of its own and a certain size when packed into a box. Ofcourse we could model this by repeating the length-, width- and heigth-attributes for the two sizes, but (mostly) the same checks are liable for both sizes. And this 'size' is also used in other tables. So my idea was : defining an Object Type could be usefull here!

So here we start, first create the Object Type "cbm_type":

SQL>create or replace
2 type cbm_type as object
3 ( length number(6,2)
4 , width number(6,2)
5 , heigth number(6,2)
6 )
7 /
Type created.

Now create the table (I defined cbm as not null, because the length, width and height should always be known):

SQL>create table product
2 (
3 description varchar2(30) not null,
4 cbm cbm_type not null
5 )
6 /
Table created.

Now add some data:

SQL>insert into product
2 va…