DataCraft, Inc.
DataCraft logo
.
.

DataCraft -> Publications -> Rebuilding Oracle8 object types

How can I rebuild an Oracle8 object type if it has table dependencies?

Hey, that's a great question.  Unfortunately, I haven't found a great answer other than "upgrade to Oracle9i," which offers quite a number of ways to modify object types.  The basic problem is that Oracle8 offers two schema modification tools for object types, neither of which goes far enough.  The tools are:
ALTER TYPE -- useful for adding or changing object type methods
and
DBMS_DDL.ALTER_TABLE_REFERENCEABLE -- useful for rebuilding an object table on another tablespace or with different storage characteristics.
Those are occasionally useful, but not interesting.  To address something interesting, let's consider a situation where where have two object types, one for animals, and one for mascots.  A mascot is a type of animal, so the mascot type will have a REF to the animal type.
CREATE TYPE Animal_t AS OBJECT (
  genus VARCHAR2(40),
  species VARCHAR2(40)
);
/

CREATE TYPE Mascot_t AS OBJECT (
   name VARCHAR2(30),
   animal_ref REF Animal_t
);
/

Now let's create some tables and throw some data in.  Our assertion here is that "reveille" is a mascot of genus "canus" and species "major."
CREATE TABLE animals OF Animal_t (
   CONSTRAINT animals_pk PRIMARY KEY (genus, species),
   CONSTRAINT genus_not_null CHECK (genus IS NOT NULL),
   CONSTRAINT species_not_null CHECK (species IS NOT NULL)
);

CREATE TABLE mascots OF Mascot_t (
   CONSTRAINT mascots_pk PRIMARY KEY (name),
   CONSTRAINT mascot_name_not_null CHECK (name IS NOT NULL)
);

INSERT INTO animals VALUES ('canus', 'major' );
INSERT INTO animals VALUES ('homo', 'sapiens');

INSERT INTO mascots
   SELECT 'reveille', REF(a)
     FROM animals a
    WHERE a.genus = 'canus'
      AND a.species = 'major';

So far, so good.  Now the fun begins.  Imagine that we need to add an attribute to the Animal_t type.  We can't use CREATE OR REPLACE TYPE because it will complain about the dependencies.  Neither can we use ALTER TYPE or DBMS_DDL.ALTER_TABLE_REFERENCEABLE to accomplish this.  Instead we must completely rebuild all dependent data. Ugly but true.  The core of the problem is that it Oracle provides no way to preserve persistent REFs across a schema change.  (As an aside, I did figure out a way to keep the OIDs, but it doesn't do any good because we can't rebuild the object type with the same type identifier...hence the REFs to those OIDs still think they're of the old type.)

The following script demonstrates the basic pattern.

First, preserve the mascot data and the animal data in non-object tables.  The table names are irrelevant, since they will get thrown away at the end.

CREATE TABLE mascots_flattened (
  name VARCHAR2(30),
  genus VARCHAR2(40),
  species VARCHAR2(40)
);

CREATE TABLE animal_holder (
   genus VARCHAR2(40),
   species VARCHAR2(40)
);

INSERT INTO animal_holder SELECT genus, species FROM animals;

INSERT INTO mascots_flattened
SELECT m.name, m.animal_ref.genus, m.animal_ref.species
  FROM mascots m;

Now, drop the original tables and types:
DROP TABLE mascots;
DROP TYPE mascot_t;
DROP TABLE animals;
DROP TYPE animal_t;
At this point we are of course free to rebuild the schema any way we want it.  Let's add a "habitat" attribute to the Animal_t type.
CREATE OR REPLACE TYPE Animal_t AS OBJECT (
  genus VARCHAR2(40),
  species VARCHAR2(40),
  habitat VARCHAR2(40)
);
/
We'll just leave this one alone:
CREATE OR REPLACE TYPE Mascot_t
AS OBJECT (
   name VARCHAR2(30),
   animal_ref REF Animal_t
);
/
And now we rebuild the object tables:
CREATE TABLE animals OF Animal_t (
   CONSTRAINT animals_pk PRIMARY KEY (genus, species),
   CONSTRAINT genus_not_null CHECK (genus IS NOT NULL),
   CONSTRAINT species_not_null CHECK (species IS NOT NULL)
);

CREATE TABLE mascots OF Mascot_t (
   CONSTRAINT mascots_pk PRIMARY KEY (name),
   CONSTRAINT mascot_name_not_null CHECK (name IS NOT NULL)
);

And, finally, we can populate the new tables from the old data:
INSERT INTO animals (genus, species)
SELECT genus, species
  FROM animal_holder;

INSERT INTO mascots (name, animal_ref)
   SELECT name, REF(a)
     FROM animals a,
          mascots_flattened f
    WHERE a.genus = f.genus
      AND a.species = f.species;

And we can now flush our temporary tables:
DROP TABLE mascots_flattened;
DROP TABLE animal_holder;

Whew!  What a load of work!  Hey Oracle, about about some relief here?

.
.
Last modified September 27, 2002 5:13 PM .