Oracle Objects in O.R.DBs flawed to uselessness

The beauty of the Objects philosophy in any system or design is that they inherently lend themselves to easy extensibility. This isn't the case for Oracle Objects used in an Object Relational System. From my experience, several extremely serious flaws are evident:

1. You cannot modify attributes, member subprograms, map or order functions of an object after instantiation as a VARRAY or Nested Table.
2. You cannot modify attributes, member subprograms, map or order functions of an object of type object table after instantiation which may have many dependencies.

Effectively you must get your Object Relational Schema exactly right first time. Otherwise you will face difficulties - never before experienced - if you ever need to change anything about your objects where dependencies exist (which of course they will). How many Oracle based systems are correct from day one anyway?

Imagine, after releasing a system, you realise that an attribute of an object in your system defined as say Number(15, 2) - is not large enough. Or a member subprogram used in a complex calculation has a bug in it. Your customers' live systems needs sorting out, they are not happy. Welcome to Hell....you are completely screwed.

You cannot export the data and re-import (there may be object refs involved or objects of type object table causing headaches) You cannot use the "ALTER TYPE" command. You get an ORA-22319 ("type attribute information altered in ALTER TYPE") as the attribute list does not exactly match the original design. Moreover ALTER TYPE doesn't alter objects anyway, it effectively tries to recreate them. You cannot drop and re-add the object because there are many dependencies.
You cannot drop dependencies and rebuild everything, because what do you do with your customers' data?

If the object in question was an instantiated VARRAY or Nested Table you might be able to (Oracle's suggestion): 1. Perform collection unnesting, moving parent row primary key and nested table rows into a temporary table.

2.  Drop the nested table column from the parent table.
3.  Drop the nested table type.
4.  Drop the object type.
5.  Re-create the object type with attribute changes
6.  Re-create the nested table type.
7.  Add the nested table to the parent table.
8.  Populate the nested table from the temporary table.

A lot of work for a simple change, which only works in the most simple simple Object relational situation. But what would you do if the object was as object of type object table, which in turn had refs to other nested objects / object table type instances? You'd end up spending a month or two writing some code to perform the task, so you could safely drop the whole schema and rebuild it. For what? Simply to change a Number(15, 2) to Number(16, 2) or fix a member function bug!

Objects within any system should allow "evolution" i.e. Altering / adding new attributes and methods to existing system should be possible, as long as types fulfill the size requirement of the data being stored, or the member subprogram's parameters aren't violated. After all developers and system architects don't always foresee every situation, and they make mistakes.

Oracle's objects are extremely static, which completely goes against the intended dynamic nature of objects. Oracle's objects / object relational idea is nice in theory, and I'm sure the architecture is extremely complicated, but realistically it doesn't lend itself to real world maintainable large scale DBs.

Until Oracle facilitates easy non-destructive alterations to objects / objects of object table types in live data systems, (like they do to purely relational tables using "ALTER TABLE") then I would say Oracle object relationality is virtually useless. Unless someone else has disagreements which they can justify.

Paul Scott
Senior Developer / Team Leader,
David Henley Systems Ltd, UK.
aspscott_at_tcp.co.uk
^^ remove 'as' anti-spam prefix to E-mail Received on Thu Dec 02 1999 - 00:00:00 CET