Nordic (3)


This series is a commentary of sorts on a PowerPoint™ presentation called Nordic – Object Relational Database Design. The previous posts are at

Guy, not Boy

Just realised I had the wrong type name: BOY instead of GUY. To fix this, I am going to have to use DROP TYPE followed by CREATE TYPE.

connect ordbms/******** -- password suppressed
DROP TYPE boy;
CREATE OR REPLACE TYPE guy UNDER human (
  MEMBER FUNCTION courts (
    my_girl girl
  ) RETURN courtship
);
/

If we try to use the RENAME command, we get the following error:

SQL> rename boy to guy;
rename boy to guy
       *
ERROR at line 1:
ORA-03001: unimplemented feature

Object Inheritance – Polymorphism

We are now at slide #14 of the Nordic – Object Relational Database Design.

Create Persistant Storage

Let’s start instantiating some types. First of all, we need somewhere to store them. Let’s call the table ANIMALS. Use the CREATE TABLE command:

CREATE TABLE animals OF animal;

Instantiation

Let’s use the INSERT command to instantiate some types:

INSERT INTO animals VALUES(horse('Mabel'));
INSERT INTO animals VALUES(guy('Jim Bob'));
COMMIT;

Note that we are using the default constructor for each type. Sub-types can be added to tables of the super-type without any problem.

Looking at Persistent Objects

Let’s look inside the table to see what’s there.

SQL> SELECT * FROM animals;

NAME
------------------------------
Mabel
Jim Bob

Have we lost the type information for each object in the ANIMALS table? Let’s probe further by using the VALUE function:

SQL> SELECT VALUE(a) FROM animals a;

VALUE(A)(NAME)
----------------------------------------------------------------
HORSE('Mabel')
GUY('Jim Bob')

We see that the type information is retained in the persistent object storage.

Now, can we find all humans in our little world? Let’s use the IS OF TYPE condition in conjunction with the VALUE function:

SQL> SELECT * FROM animals a WHERE VALUE(a) IS OF TYPE( human );

NAME
------------------------------
Jim Bob

Association Classes

We now turn to slide #15.

Create Persistent Storage

CREATE TABLE courtships OF courtship;

Instantiation

INSERT INTO courtships VALUES(guy('Jim Bob'),girl('Mary Lou'));
COMMIT;

Note that these two (2) objects are not in the ANIMALS table. The GUY object here is different from the one in the ANIMALS table. And the GIRL object only exists in the COURTSHIP table.

Object Retrieval

SQL> SELECT * FROM courtships;

MY_BOY(NAME)
--------------------------------------------------------------------------------
MY_GIRL(NAME)
--------------------------------------------------------------------------------
GUY('Jim Bob')
GIRL('Mary Lou')

Type Evolution

First Redesign Attempt

Instead of using objects, let’s use pointers to objects in other tables. These are called REF data types. Because we have created a table based on a type, we cannot change the definition of a type. Let’s try anyway:

SQL> ALTER TYPE courtship
  2    REPLACE AS OBJECT (
  3      my_guy REF guy,
  4      my_girl REF girl
  5  );
ALTER TYPE courtship
*
ERROR at line 1:
ORA-22319: type attribute information altered in ALTER TYPE

We have to drop the table and other dependents. If we were to try to drop the type, we would get:

SQL> drop type courtship;
drop type courtship
*
ERROR at line 1:
ORA-02303: cannot drop or replace a type with type or table dependents

Find the Dependencies

We will use the USER_DEPENDENCIES view to find all database objects that depend on the COURTSHIP type:

SELECT name,type FROM user_dependencies WHERE referenced_name='COURTSHIP';

And the answer is:

NAME                           TYPE
------------------------------ -----------------
GUY                            TYPE
COURTSHIPS                     TABLE

The type GUY has a method called COURTS which returns the COURTSHIP type.

Removing the Dependents

Altering the GUY Type

The signature of the function has to match in function name, parameter types (include parameter name), and return value. Note that RETURN is singular, not plural.

ALTER TYPE guy DROP MEMBER FUNCTION courts( my_girl girl) RETURN courtship CASCADE;

If we do not include the CASCADE keyword, we will get the following error message:

ORA-22312: must specify either CASCADE or INVALIDATE option
Remove Other Dependents

Drop the table and the type.

DROP TABLE courtships;
DROP TYPE courtship;

Verify No Dependencies Exist

SQL> SELECT name,type FROM user_dependencies WHERE referenced_name='COURTSHIP';

no rows selected

Second Redesign Attempt

Create the New COURTSHIP Type

We have removed the old version of the COURTSHIP type. We are ready to implement the new version.

CREATE TYPE courtship AS OBJECT (
   my_guy REF  guy,
   my_girl REF  girl
);
/

Add Objects to ANIMALS Table

Let’s put the girl into the ANIMALS table where she belongs.

INSERT INTO animals VALUES(girl('Mary Lou'));
COMMIT;

Create Persistent Storage

CREATE TABLE courtships OF courtship;

Let Love Bloom!

So Jim Bob is going to court Mary Lou. What can stand in the way of true love?

INSERT INTO courtships VALUES(
    (select ref(a) from animals a where name='Jim Bob'),
    (select ref(a) from animals a where name='Mary Lou')
)
/

Well, Oracle can with the following messages:

  (select ref(a) from animals a where name='Jim Bob'),
              *
ERROR at line 2:
ORA-00932: inconsistent datatypes: expected REF ORDBMS.GUY got REF
ORDBMS.ANIMAL

Oracle thinks Jim Bob is an animal, not a guy. That may well be, but somehow Oracle is not convinced. Let’s try to convince Oracle that Jim Bob is a Guy, and Mary Lou is a girl by using Object Views through the CREATE VIEW command:

CREATE VIEW guys OF guy AS
  SELECT * FROM animals a
    WHERE VALUE(a) IS OF TYPE ( guy )
/
CREATE VIEW girls OF girl AS
  SELECT * FROM animals a
    WHERE VALUE(a) IS OF TYPE ( girl )
/

Let’s make sure.

SQL> SELECT * FROM guys;

NAME
------------------------------
Jim Bob

SQL> SELECT * FROM girls;

NAME
------------------------------
Mary Lou

That seems fine. What can go wrong now? Let’s modify the INSERT statement to read:

INSERT INTO courtships VALUES(
    (select ref(a) from guys a where name='Jim Bob'),
    (select ref(a) from girls a where name='Mary Lou')
)
/

And we get the error message:

INSERT INTO courtships VALUES(
            *
ERROR at line 1:
ORA-22979: cannot INSERT object view REF or user-defined REF

At which point, we decide that the second design attempt is a total failure.

Third Design Attempt

Clean Up

We have to do the clean-up again. See above (except for the GUY type).

DROP TABLE courtships;
DROP TYPE courtship;

An extra bit of clean up is the use of the PURGE command:

PURGE USER_RECYCLEBIN;

Third Design

They are all animals when they fall in love.

CREATE OR REPLACE TYPE courtship AS OBJECT (
  my_guy REF animal,
  my_girl REF animal
);
/

Persistent Storage

CREATE TABLE courtships OF courtship;

Let Love Bloom!

INSERT INTO courtships VALUES(
  (select ref(a) from animals a where name='Jim Bob'),
  (select ref(a) from animals a where name='Mary Lou')
)
/
COMMIT;

Peek at the Loving Couple

SELECT * FROM courtships;

Produces

MY_GUY
--------------------------------------------------------------------------------
MY_GIRL
--------------------------------------------------------------------------------
0000220208AA99CF23FF3E4F0A918C1FDC270A83690199AA9B12344248A683451EF6A6B64E
00002202083E54268F549447DE81D5A90CEC17AF150199AA9B12344248A683451EF6A6B64E

All very nice. But what does it mean? Let’s use the DEREF function to find out.

SELECT DEREF(my_guy),DEREF(my_girl) FROM courtships;

And we get the following for our troubles:

ERROR:
ORA-30757: cannot access type information



no rows selected

Conclusion to Part the Third

Agile programming and Oracle Objects do not mix.

Default Object ID is not useful for relationships.

Advertisements

One thought on “Nordic (3)

  1. Nordic (5) « Yet Another OCM

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s