(That will teach him to ask me questions.)
There is also a PowerPoint™ presentation called Nordic – Object Relational Database Design. I will be using this presentation as the basis for this series of posts.
Oracle 10G has Oracle® Database Application Developer’s Guide – Object-Relational Features 10g Release 2 (10.2) which can be used in the free database Oracle Database 10g Express Edition. I will be using Oracle XE as the basis for the examples as I work my way through the presentations.
The programming reference I shall be using is Oracle Database 11g PL/SQL Programming By Michael McLaughlin.
Facade Interface and Object Metadata
Slide #11 in Nordic – Object Relational Database Design describes the structure of Nordic as consisting of a Facade Interface and Object Metadata. The Facade Interface is implemented as Stored Procedures, and the Metadata is stored in relational tables.
In Oracle, the Facade Interface consists of SQL verbs:
- CREATE TYPE is for the public interface of the object class: attributes and methods.
- CREATE TYPE BODY is for the private implementation of the object class: attributes and methods.
The Object Metadata is stored in various data dictionary views (ALL_,DBA_,USER_):
- ALL_TYPE_ATTRS describes the attributes of the object types accessible to the current user.
- ALL_TYPE_METHODS describes the methods of the object types accessible to the current user.
- ALL_TYPE_VERSIONS describes the versions of the object types accessible to the current user.
- ALL_TYPES describes the object types accessible to the current user.
Establish the Oracle Environment
I am going to have a user, ORDBMS, established for this demonstration. Using the
Run SQL Command program in the
Oracle Database 10g Express Edition program group to execute the following commands:
connect / as sysdba CREATE USER ordbms IDENTIFIED BY "********" -- password suppressed DEFAULT TABLESPACE users QUOTA UNLIMITED ON users / GRANT CREATE SESSION, CREATE TYPE, CREATE TABLE, CREATE VIEW TO ordbms /
Now establish a session for ORDBMS,
SQL> connect ordbms Enter password: Connected. SQL>
Basic Object Model
Slide #12 shows the Oklahoma – Sample Class Structure. To implement this in Oracle XE is going to take a few steps.
I chose the BUILDING class first because it does not depend on any class. Let’s create the class without any attributes:
CREATE TYPE building AS OBJECT (); /
And I get a warning. Not an error.
Warning: Type created with compilation errors.
To find out what the warning was:
And the errors (really warnings) are:
Errors for TYPE BUILDING: LINE/COL ERROR -------- -------------------------------------------------------- 1/1 PLS-00589: no attributes found in object type "BUILDING"
Let’s get rid of the warning by adding a single atrribute – NAME.
CREATE TYPE building AS OBJECT ( name VARCHAR2(30) ); /
At this point, we just have a definition. There are no objects of this type. Let’s create a table called BUILDINGS to hold objects of type BUILDING, and put some data in:
CREATE TABLE buildings OF building; INSERT INTO buildings VALUES( building( 'Barn' ) ); COMMIT; SELECT * FROM buildings;
The data is:
NAME ------------------------------ Barn
So we have a simple type containing one attribute. There is a table for objects of this type.