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 values
3 ( 'Cubic'
4 , cbm_type(1.6,1.6,1.6)
5 )
6 /
1 row created.
SQL>insert into product
2 values
3 ( 'Empty'
4 , cbm_type(null,null,null)
5 )
6 /
1 row created.

Hm, that's not quite what I expected..
Let's have a look what is in the table right now:

SQL>select * from product;
DESCRIPTION CBM(LENGTH, WIDTH, HEIGTH)
----------- ------------------------------
Cubic       CBM_TYPE(1.6, 1.6, 1.6)
Empty       CBM_TYPE(NULL, NULL, NULL)

So the NOT NULL constraint on the column CBM doesn't seem to work!
Adding a check constraint (not null constraint) to the Object Type definition would be the solution, but for one reason or the other that's not possible. I also liked to add more check constraints to the Object Type (like length>0), but that's also not possible! Don't ask me why, for imho that would really improve the use of Object Types...

Now we have to look for a workaround by adding some code to the Object Type (after dropping the product table):

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 , member function content return number
7 , member procedure check_constraints
8 , pragma restrict_references(content, rnds, wnds)
9 )
10 /
Type created.

The 'content' function gets a pragma in order to make it possible to use in a SQL SELECT statement. Now create the Object Type Body with the necessary check constraints:

SQL>create or replace type body cbm_type as
2 member
3 function content return number is
4 begin
5 return (length * width * heigth );
6 end content;
7 member
8 procedure check_constraints is
9 begin
10 if length is null
11 or width is null
12 or heigth is null
13 then
14 raise_application_error(-20000, 'Length, width or heigth cannot be NULL');
15 end if;
16 if length <> 10
23 then
24 raise_application_error(-20000, 'Too big!');
25 end if;
26 end check_constraints;
27 end;
28 /
Type body created.
SQL>

Now after recreating the table, we add some data:

SQL>insert into product
2 values
3 ( 'Box 1'
4 , cbm_type(1.3,2.4,3.5)
5 )
6 /
1 row created.
SQL>insert into product
2 values
3 ( 'Box 2'
4 , cbm_type(1.6,2.6,1.7)
5 )
6 /
1 row created.
SQL>insert into product
2 values
3 ( 'Cubic'
4 , cbm_type(1.6,1.6,1.6)
5 )
6 /
1 row created.
SQL>insert into product
2 values
3 ( 'Negative'
4 , cbm_type(-1.0,1.6,1.6)
5 )
6 /
1 row created.
SQL>insert into product
2 values
3 ( 'Empty'
4 , cbm_type(null,null,null)
5 )
6 /
1 row created.
SQL>

The check_constraints procedure didn't do anything ofcourse, but we could look what's in the table rigth now:

SQL>select description
2 , cbm
3 , p.cbm.length length
4 , p.cbm.content() content
5 from product p
6 /
DESCRIPTION CBM(LENGTH, WIDTH, HEIGTH) LENGTH CONTENT()
----------- -------------------------- ------ ---------
Box 1       CBM_TYPE(1.3, 2.4, 3.5)       1.3     10.92
Box 2       CBM_TYPE(1.6, 2.6, 1.7)       1.6     7.072
Cubic       CBM_TYPE(1.6, 1.6, 1.6)       1.6     4.096
Negative    CBM_TYPE(-1, 1.6, 1.6)        -1      -2.56
Empty       CBM_TYPE(NULL, NULL, NULL)

There are two things worth to mention. First we need to use a table alias to get the length from the cbm_type and second we need to use parenthesis if we want to use the content-function.
Now let's get the check_contraints procedure at work by adding a trigger to the products table:

SQL>create or replace trigger check_contraints
2 before insert or update
3 of cbm
4 on product
5 for each row
6 begin
7 :new.cbm.check_constraints();
8 end check_constraints;
9 /
Trigger created.

And now try to add some data:

SQL>insert into product
2 values
3 ( 'Box 1'
4 , cbm_type(1.3,2.4,3.5)
5 )
6 /
insert into product
*
ERROR at line 1:
ORA-20000: Too big!
ORA-06512: at "CBM_TYPE", line 24
ORA-06512: at "CHECK_CONTRAINTS", line 2
ORA-04088: error during execution of trigger 'CHECK_CONTRAINTS'
SQL>insert into product
2 values
3 ( 'Box 2'
4 , cbm_type(1.6,2.6,1.7)
5 )
6 /
1 row created.
SQL>insert into product
2 values
3 ( 'Cubic'
4 , cbm_type(1.6,1.6,1.6)
5 )
6 /
1 row created.
SQL>insert into product
2 values
3 ( 'Negative'
4 , cbm_type(-1.0,1.6,1.6)
5 )
6 /
insert into product
*
ERROR at line 1:
ORA-20000: Length, width or heigth cannot be negative
ORA-06512: at "CBM_TYPE", line 20
ORA-06512: at "CHECK_CONTRAINTS", line 2
ORA-04088: error during execution of trigger 'CHECK_CONTRAINTS'
SQL>insert into product
2 values
3 ( 'Empty'
4 , cbm_type(null,null,null)
5 )
6 /
insert into product
*
ERROR at line 1:
ORA-20000: Length, width or heigth cannot be NULL
ORA-06512: at "CBM_TYPE", line 14
ORA-06512: at "CHECK_CONTRAINTS", line 2
ORA-04088: error during execution of trigger 'CHECK_CONTRAINTS'

OK, that's more like it! So the work around is adding a call to the Object Type procedure in the trigger(s) on every table that uses the Object Type. I think I can live with that - for the moment...
So now I can simply add more checks in de Object Type Body and those checks will be evaluated for every insert or update on every cbm_type object.