Skip to Main Content
  • Questions
  • Object Relational Features: Column validation for TYPEs and storing data for subtypes.


Question and Answer

Tom Kyte

Thanks for the question, Ramesh.

Asked: April 14, 2010 - 9:55 pm UTC

Last updated: April 15, 2010 - 2:11 pm UTC

Version: 9i/10g

Viewed 1000+ times

You Asked

Hi Tom:

I have a couple of questions related to Object Relational Features of Oracle.

I have following structures:

(line1 VARCHAR2(20), city VARCHAR2(20), COUNTRY VARCHAR2(20));

CREATE TYPE phone_typ AS OBJECT (area_cd VARCHAR2(10), phone# VARCHAR2(10));

CREATE TYPE phone_list_typ AS TABLE OF phone_typ;

(lname VARCHAR2(20), fname VARCHAR2(20),
address address_typ,
phones phone_list_typ) NOT FINAL;

CREATE TYPE salesperson_typ UNDER person_typ
(salesperson_id NUMBER, salary NUMBER, NoOfSale NUMBER);

CREATE TYPE customer_typ UNDER person_typ
(customer_id NUMBER, TotalPurchase NUMBER);

CREATE TABLE person_obj OF person_typ
NESTED TABLE phones STORE AS phones_tab;

My questions are:

1. I have created an object table person_obj of person_typ.

I have not created object tables of subtypes salesperson_typ and customer_typ and want to store data related to both in person_obj.

What is the difference between storing data in an object table of parent type and object table of subtypes?

In which case should I consider storing data of all subtypes in an object table of parent type instead of object tables of individual subtypes?

2. Second question is regarding validating the column values before the creation of object instance.

Suppose before creating an instance of salesperson_typ, I want to check if salary is NOT NULL and greater than 0.

I guess constructor is the only option to achieve this but how full-proof it is if constructors are used for this purpose?

Are there any alternate ways?

I didn't get answers to these questions anywhere hence thought of asking you.

Thanks in advance for your time.


and Tom said...

I strongly encourage you to NOT do this, use real relational tables and create an OR-view (object relational) on top of them if you want, but do not use nested tables, object types as TABLES. There is a ton of unnecessary overhead to give you this syntactic sugar.

A nested table for example makes it so you have an extra 16 byte raw added to both parent and child, along with a unique constraint on parent on this raw column - and makes you index the raw in the child. You just do not need any of those.

So, develop a proper relational model - and use it.

I am 100% positive that you will want to query the address table some time (queries like "find me everyone that lives in the state of ?"). If you use a nested table - you don't have a real table anymore. Object models are excessively myopic - they present a single view of the data - and we always need many many views of the data.

How about doing a reverse phone number look up? Not so easy with the OR-model - trivial with a proper data model.

So, this is a big non-answer to your questions, but consider that a good thing - you were about to make a big mistake in my opinion.


  (2 ratings)

Is this answer out of date? If it is, please let us know via a Comment


Thank you very much Tom.

Ramesh Samane, April 15, 2010 - 1:23 pm UTC

This is very useful information indeed and I am sure I could not have got it from anywhere except from you.

Well, I have few more questions related to this topic and I will post them soon.

And I have a feeling that Object Oriented Model and Relational Model do not play well with each other. We need to check the design carefully before mixing these two models.

Thanks much for your time.

Tom Kyte
April 15, 2010 - 2:11 pm UTC

... And I have a feeling that Object Oriented Model and Relational Model do not
play well with each other...

No no no, not at all - they do.

But - and this is important - you have to start with a good solid relational base and ON TOP of that build your myopic OO view.

I keep calling OO views "myopic", why? Because they are very application focused, not data focused.


now I know...

Ramesh Samane, April 15, 2010 - 3:14 pm UTC


The link you had mentioned answered the most of my questions I was about to ask and many more that I didn't imagine.

Thank you very much.