Object-Oriented Oracle with Substitutable Types

Share Button

Have you ever been forced to create many tables to handle a single functionality? Or make a table that’s horribly vague because there were so many different ways one row could come in?

Let me give you an example. Let’s say you had to create a table that could support every different kind of electronic computing device. At first, you may think, easy! We’ll create a table with CPU, RAM, VIDEO_CARD… wait, what about iPods? Those are computers too. Video game consoles, like the new XBOX360, can run Linux. PDAs, cell phones, mainfraimes, robotics, entertainment systems, all of these are types of computing devices! How can you make a table that holds them all?

At this point, you feel you have four options. You can make a single table with hundreds of columns, most of them nullable, that can handle all of it. Or you can make a table with tons of columns like STRING1, STRING2, NUMBER1, NUMBER2 to handle any eventuality. Or, you can just make a different table for every single device out there. Lastly, you can make other tables that have two vague columns, NAME and VALUE, so you can have as many rows as you want to describe the object in any way you like… but what happens when you have to do an AND or OR where clause against it? You’re stuck with UNIONs and such and performance goes downhill.

Enter object oriented Oracle, using substitutable types. How would you like a single column that can hold all of these columns and more that’s extremely scalable and easy to manage? Yes please.

What we will do is create a base table to hold all these items with columns that pertain to the overall concept. For instance, it will have the columns ITEM_ID and QUANTITY.

But where’s the details? We will put all of them, every single on, in a column called ITEM. And the data type of ITEM will be ITEM_TYPE.

Yes, we’re going to make our own data type!

Now that we have a data type of our own, we can assign it to a column of a table. But before we do that, we’re going to find out just how powerful these things are with substitutable data types.

Notice that even though we didn’t put it in our create statement, there’s an ITEM_NAME column in the data type. That’s because PDA_TYPE extends ITEM_TYPE, which contains ITEM_NAME! This is what a substitutable type truly is: it’s a datatype that takes pieces of other datatypes and extends them to form new and interesting things.

Let’s make one more for our experiment.

Now, we’re at last ready to create our table.

And here we are, ready to go now. Let’s put some records into our new table. This is where it gets a bit interesting.

First, we’re going to insert a PDA.

Let’s do one more, this time a PC.

Did you notice that our insert had entirely different columns? That’s because the data type changed! It’s the beauty of substitutable types. Now, we have all our data in that column called ITEM. Let’s see what we can do with it.

Would you like to get all the devices that are PCs?

Here’s the one that’s probably on ALL of your minds. How are we going to specifically pull a single column? Like CPU from ITEM where it’s a PC? This is where the TREAT function comes in. TREAT allows us to translate an item’s datatype into one of its substitutes. Look below:

And there we go! We can actually pull the columns as we please out of the system. If you create your code properly, you can make it very simple to dynamically build queries.

Lastly, we may want to update some rows. This is the only place I find these abilities lacking. You will actually have to do an inline view to perform the update.

The other option is to create an actual view that is updateable for each of your sub-types, which can be very convenient.

And now we can update and select easily any time we choose. One note: notice when we created the view, we TREATed item as ITEM_TYPE for the ITEM_NAME column. This is because we need to use the highest possible data type in order to make the column updateable.

It is somewhat confusing, but extremely powerful for scalable systems. Please leave a comment here if you have questions!

Share Button


  1. Steve,

    Great example and demo. I use OO in my PL/SQL quite frequently but in my SQL less so. This example is something to keep in mind.



  2. Great example! Thank you for clarifying several questions I had about substitutable types. Keep up the great site and work!


  3. Excellent article with good examples.
    I wanted to share one issue we ran into while implementing substitutable object types.
    When we declare a column in a table as an object type, Oracle stores all the attributes of the type and all its sub-types as hidden columns in the table. If we have lots of sub-types, we could run into an oracle error “maximum number of columns exceeded” since there is a 1000 column limit for an Oracle table.
    We ran into this situation recently where we needed to add more sub-types and it will not let us do that. I am currently trying to figure out a work-around for this problem


  4. Hope nobody minds me posting up to an old thread.

    Why doesn’t 10g allow types to reference a table column type for it’s children?

    create or replace type car as object (
    model dbo_space.car_table.modle%type) not final;

    Or does it and I’m just going about it all wrong?

    thanks a lot for info

    there are really too many things to learn inside oracle,
    and you had help me with this argument


Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.