[Chapter 18] 18.6 Object Housekeeping

18.6 Object Housekeeping

In the business of working with objects, you will gain a familiarity with a number of ways of getting information about object types that you have created. These include making direct queries of the data dictionary and using the SQL*Plus "describe" command.

Another topic in the "housekeeping" category is how to deal with schema changes after you have already built tables on your object types. As the next section of the chapter explains, there are no easy answers to this question.

18.6.1 Data Dictionary

There are a few new entries in the data dictionary that will be very helpful in managing your object types. The shorthand dictionary term for object types is simply TYPE. Object type definitions and object type bodies are both found in the USER_SOURCE view (or DBA_SOURCE, or ALL_SOURCE), just as package specifications and bodies are. Table 18.4 summarizes the views.

Table 18.4: Data Dictionary Entries for Object Types
To Answer the Question...	Use This View	As In
What object types have I created?	USER_TYPES	SELECT type_name FROM user_types WHERE type_code = 'OBJECT';
What are the attributes of type Foo_t?	USER_TYPE_ATTRS	SELECT * FROM user_type_attrs WHERE type_name = 'FOO_T';
What are the methods of type Foo_t?	USER_TYPE_METHODS	SELECT * FROM user_type_methods WHERE type_name = 'FOO_T';
What are the parameters of Foo_t's methods?	USER_METHOD_PARAMS	SELECT * FROM user_method_params WHERE type_name = 'FOO_T';
What datatype is returned by Foo_t's method called bar?	USER_METHOD_RESULTS	SELECT * FROM user_method_results WHERE type_name = 'FOO_T' AND method_name = 'BAR';
What is the source code for Foo_t?	USER_SOURCE	SELECT text FROM user_source WHERE name = 'FOO_T' AND type = 'TYPE' ORDER BY line;
What is the code used in the object body of Foo_t?	USER_SOURCE	SELECT text FROM user_source WHERE name = 'FOO_T' AND type = 'TYPE BODY' ORDER BY line;
What are the object tables that implement Foo_t?	USER_TABLES	SELECT table_name FROM user_object_tables WHERE table_type = 'FOO_T';
What columns implement Foo_t?	USER_TAB_COLUMNS	SELECT table_name, column_name FROM user_tab_columns WHERE data_type = 'FOO_T';
What database objects are dependent on Foo_t?	USER_DEPENDENCIES	SELECT name, type FROM user_dependencies WHERE referenced_name = 'FOO_T';

18.6.2 SQL*Plus "Describe" Command

If you're like me and don't like to type any more than necessary, you'll appreciate a wonderful enhancement that Oracle has provided for the describe command in SQL*Plus. It will report not only the attributes of an object type, but also the methods and their arguments. To illustrate:

SQL> desc pet_t
 Name                            Null?    Type
 ------------------------------- -------- ----
 TAG_NO                                   NUMBER(38)
 NAME                                     VARCHAR2(60)
 ANIMAL_TYPE                              VARCHAR2(30)
 SEX                                      VARCHAR2(1)
 PHOTO                                    BINARY FILE LOB
 VACCINATIONS                             VACCINATION_LIST_T
 OWNER                                    REF OF PERSON_T

METHOD
------
 MEMBER FUNCTION SET_TAG_NO RETURNS PET_T
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 NEW_TAG_NO                     NUMBER                  IN

METHOD
------
 MEMBER FUNCTION SET_PHOTO RETURNS PET_T
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 FILE_LOCATION                  VARCHAR2                IN
 MEMBER PROCEDURE PRINT_ME

Although the formatting could be improved, this is much easier than SELECTing the equivalent information from the data dictionary.

18.6.3 Schema Evolution

Let's say that you have created an object type and you need to make a change to its definition. What do you do? The answer is that it depends -- on whether you have used the type, and on what type of change you want to make. Precious few modifications are easy; the rest will probably age you prematurely. Consider the implications of where you have used the type:

Type has no dependencies. Using CREATE OR REPLACE, you can change the object type to you heart's content. Or drop and recreate it; who cares? Life is good.
Type is used only in PL/SQL modules. In this case, since you don't have to rebuild any dependent tables, life is still easy. Oracle will automatically recompile dependent PL/SQL modules the next time they are called.
Type is used in one or more tables. Consider what would be a simple change to a relational table: adding a column. If you try to add a column to an object table, you get an "ORA-22856 cannot add columns to object tables." The "Action" for this message says we need to "Create a new type with additional attributes, and use the new type to create an object table. The new object table will have the desired columns." Your frustrations are beginning.

OK, if you want to add an attribute, you're out of luck. What about methods? Oracle8.0 does include an ALTER TYPE statement that allows you to recompile an object specification or body. It also allows you to add new methods. It is extremely limited, however; it does not allow you to add or remove attributes, nor does it allow you to modify the quantity or datatypes of existing method arguments. The basic syntax is:

Form I
ALTER TYPE [ BODY ] type_name COMPILE [ SPECIFICATION | BODY ];

which does not solve our problem, or:

Form II
ALTER TYPE [ BODY ] type_name REPLACE
   <the entire new type or body definition>;

Using Form II, we can, in fact, add an entirely new method to an object type, even if there are dependencies on the type.

In the case of changing a method's specification (or deleting a method) in object type Foo_t which is implemented in table foo, you would think that export/import would work, using something like:

Export the foo table.
Drop the foo table.
CREATE OR REPLACE TYPE Foo_t with the new definition.
Import the foo table.

But alas, it doesn't work, because when you CREATE OR REPLACE the type, it actually assigns a new OID to the type, and the import fails with IMP-00063 when it sees that the OID is different. Huh? What do you mean, "assigns a new OID to the type?" For reasons apparently having to do with facilitating certain operations in the Oracle Call Interface (OCI), object types themselves have an OID. See for yourself -- you can easily retrieve them from the USER_TYPES data dictionary view.

Neither can you "CREATE new_object_table AS SELECT ... FROM old_object_table." Even if you could, the REFs wouldn't match up to the OIDs of the new table.

It's even worse if you want to make any serious modifications to an object type and you have a dependency on the type from other types or tables. You cannot drop and recreate a parent object table unless you drop the child object types and object tables first. So maybe you could:

Create new object types and tables.
Somehow populate new from the old.
Drop the old object tables and types.
Rename the new types and object tables to the old names.

It is not obvious to me how to do the second step in a way that will preserve REFs to the type. The only way I see to do it in a guaranteed fashion is to rely on relational primary and foreign keys for tuple identification. That is, your schema will include not only REFs but also equivalent foreign keys. Then, when your OIDs change because you have rebuilt an object table, you can update all the REFs to that object table using foreign key values. Not a pretty picture.

Also, you cannot rename object types (number 4 above); attempting to do so fails with "ORA-03001: unimplemented feature."

WARNING: Requiring the dropping of all dependent types and objects before altering a type is not going to endear the Oracle objects option to the average database administrator (or to anyone else, for that matter). Object schema evolution is a significant area where Oracle could make a lot of improvements.


18.5 Modifying Persistent Objects		18.7 Making the Objects Option Work