9 A Sample Application Using Object-Relational Features

Example 9-1 Creating the Customer_reltab Table

CREATE TABLE Customer_reltab (
  CustNo                NUMBER NOT NULL,
  CustName              VARCHAR2(200) NOT NULL,
  Street                VARCHAR2(200) NOT NULL,
  City                  VARCHAR2(200) NOT NULL,
  State                 CHAR(2) NOT NULL,
  Zip                   VARCHAR2(20) NOT NULL,
  Phone1                VARCHAR2(20),
  Phone2                VARCHAR2(20),
  Phone3                VARCHAR2(20),
  PRIMARY KEY (CustNo));

This table, Customer_reltab, stores all the information about customers, which means that it fully contains information that is intrinsic to the customer (defined with the NOT NULL constraint) and information that is not as essential. According to this definition of the table, the application requires that every customer have a shipping address.

Our Entity-Relationship (E-R) diagram showed a customer placing an order, but the table does not make allowance for any relationship between the customer and the purchase order. This relationship must be managed by the purchase order.

Example 9-2 Creating the PurchaseOrder_reltab Table

CREATE TABLE PurchaseOrder_reltab (    
   PONo        NUMBER, /* purchase order no */  
   Custno      NUMBER references Customer_reltab, /* Foreign KEY referencing 
                                                   customer */
   OrderDate   DATE, /*  date of order */  
   ShipDate    DATE, /* date to be shipped */    
   ToStreet    VARCHAR2(200), /* shipto address */    
   ToCity      VARCHAR2(200),    
   ToState     CHAR(2),    
   ToZip       VARCHAR2(20),    
   PRIMARY KEY(PONo));   
  

PurchaseOrder_reltab manages the relationship between the customer and the purchase order by means of the foreign key (FK) column CustNo, which references the CustNo key of the Customer_reltab. The PurchaseOrder_reltab table contains no information about related line items. The line items table (next section) uses the purchase order number to relate a line item to its parent purchase order.

Example 9-3 Creating the LineItems_reltab Table

CREATE TABLE LineItems_reltab (
  LineItemNo           NUMBER,
  PONo                 NUMBER REFERENCES PurchaseOrder_reltab,
  StockNo              NUMBER REFERENCES Stock_reltab,
  Quantity             NUMBER,
  Discount             NUMBER,
  PRIMARY KEY (PONo, LineItemNo));

Example 9-4 Creating the Stock_reltab Table

CREATE TABLE Stock_reltab (
  StockNo      NUMBER PRIMARY KEY,
  Price        NUMBER,
  TaxRate      NUMBER);

Example 9-23 Creating the Customer_objtyp Type Body

CREATE OR REPLACE TYPE BODY Customer_objtyp AS
  ORDER MEMBER FUNCTION
  compareCustOrders (x IN Customer_objtyp) RETURN INTEGER IS
  BEGIN
    RETURN CustNo - x.CustNo;
  END;
END;
/

As mentioned earlier, the order method compareCustOrders operation compares information about two customer orders. It takes another Customer_objtyp object as an input argument and returns the difference of the two CustNo numbers. The return value is:

Example 9-24 Creating the Customer_objtab Table

CREATE TABLE Customer_objtab OF Customer_objtyp (CustNo PRIMARY KEY) 
   OBJECT IDENTIFIER IS PRIMARY KEY ;   

Unlike with relational tables, when you create an object table, you specify a data type for it, namely, the type of objects it will contain.

The table has a column for each attribute of Customer_objtyp, namely:

CustNo           NUMBER  /* Primmary key */
CustName         VARCHAR2(200)
Address_obj      Address_objtyp
PhoneList_var    PhoneList_vartyp

See Example 9-18, "Creating the Address_objtyp Object" and Example 9-17, "Creating the PhoneList_vartyp Type" for the definitions of those types.

Example 9-25 Creating the Stock_objtab Table

CREATE TABLE Stock_objtab OF StockItem_objtyp (StockNo PRIMARY KEY)
   OBJECT IDENTIFIER IS PRIMARY KEY ;      

Each row of the table is a StockItem_objtyp object having three numeric attributes:

StockNo    NUMBER
Price      NUMBER
TaxRate    NUMBER

Oracle creates a column for each attribute. The CREATE TABLE statement places a primary key constraint on the StockNo column and specifies that the primary key be used as the row object's identifier.

Example 9-26 Creating the PurchaseOrder_objtab Table

CREATE TABLE PurchaseOrder_objtab OF PurchaseOrder_objtyp (  /* Line 1 */
   PRIMARY KEY (PONo),                                       /* Line 2 */
   FOREIGN KEY (Cust_ref) REFERENCES Customer_objtab)        /* Line 3 */
   OBJECT IDENTIFIER IS PRIMARY KEY                          /* Line 4 */
   NESTED TABLE LineItemList_ntab STORE AS PoLine_ntab (     /* Line 5 */
     (PRIMARY KEY(NESTED_TABLE_ID, LineItemNo))              /* Line 6 */
     ORGANIZATION INDEX COMPRESS)                            /* Line 7 */
   RETURN AS LOCATOR                                         /* Line 8 */
/   

The preceding CREATE TABLE statement creates the PurchaseOrder_objtab object table. The significance of each line is as follows:

Example 9-27 Replacing the PurchaseOrder_objtyp Type Body

CREATE OR REPLACE TYPE BODY PurchaseOrder_objtyp AS 

   MAP MEMBER FUNCTION getPONo RETURN NUMBER is   
      BEGIN  
         RETURN PONo;   
      END;   
    
   MEMBER FUNCTION sumLineItems RETURN NUMBER IS  
      i          INTEGER;  
      StockVal   StockItem_objtyp;  
      Total      NUMBER := 0;
  
   BEGIN
      IF (UTL_COLL.IS_LOCATOR(LineItemList_ntab)) -- check for locator
         THEN
            SELECT SUM(L.Quantity * L.Stock_ref.Price) INTO Total
            FROM   TABLE(CAST(LineItemList_ntab AS LineItemList_ntabtyp)) L;
      ELSE
         FOR i in 1..SELF.LineItemList_ntab.COUNT LOOP  
            UTL_REF.SELECT_OBJECT(LineItemList_ntab(i).Stock_ref,StockVal);  
            Total := Total + SELF.LineItemList_ntab(i).Quantity * 
                                                            StockVal.Price;  
         END LOOP;  
      END IF;  
   RETURN Total;  
   END;  
END;     
/

The rewritten sumLineItems method checks whether the nested table attribute, LineItemList_ntab, is returned as a locator using the UTL_COLL.IS_LOCATOR function. If the condition evaluates to TRUE, the nested table locator is queried using the TABLE expression.

Example 9-28 Adding the SCOPE FOR Constraint

ALTER TABLE PoLine_ntab
   ADD (SCOPE FOR (Stock_ref) IS stock_objtab) ;

This statement specifies that the Stock_ref column of the nested table is scoped to Stock_objtab. This indicates that the values stored in this column must be references to row objects in Stock_objtab. The SCOPE constraint is different from the referential constraint in that the SCOPE constraint has no dependency on the referenced object. For example, any referenced row object in Stock_objtab may be deleted, even if it is referenced in the Stock_ref column of the nested table. Such a deletion renders the corresponding reference in the nested table a DANGLING REF.

Example 9-29 Inserting Values in Stock_objtab

INSERT INTO Stock_objtab VALUES(1004, 6750.00, 2) ;
INSERT INTO Stock_objtab VALUES(1011, 4500.23, 2) ;
INSERT INTO Stock_objtab VALUES(1534, 2234.00, 2) ;
INSERT INTO Stock_objtab VALUES(1535, 3456.23, 2) ;

Example 9-30 Inserting Values in Customer_objtab

INSERT INTO Customer_objtab
  VALUES (
    1, 'Jean Nance',
    Address_objtyp('2 Avocet Drive', 'Redwood Shores', 'CA', '95054'),
    PhoneList_vartyp('415-555-1212')
    ) ;

INSERT INTO Customer_objtab
  VALUES (
    2, 'John Nike',
    Address_objtyp('323 College Drive', 'Edison', 'NJ', '08820'),
    PhoneList_vartyp('609-555-1212','201-555-1212')
    ) ;

Example 9-31 Inserting Values in PurchaseOrder_objtab

INSERT INTO PurchaseOrder_objtab
  SELECT  1001, REF(C),
          SYSDATE, '10-MAY-1999',
          LineItemList_ntabtyp(),
          NULL
   FROM   Customer_objtab C
   WHERE  C.CustNo = 1 ;

The preceding statement constructs a PurchaseOrder_objtyp object with the following attributes:

PONo                1001
  Cust_ref            REF to customer number 1
  OrderDate           SYSDATE
  ShipDate            10-MAY-1999
  LineItemList_ntab   an empty LineItem_ntabtyp
  ShipToAddr_obj      NULL

The statement uses a query to construct a REF to the row object in the Customer_objtab object table that has a CustNo value of 1.

The following statement uses a TABLE expression to identify the nested table as the target for the insertion, namely the nested table in the LineItemList_ntab column of the row object in the PurchaseOrder_objtab table that has a PONo value of 1001.

Example 9-32 Inserting Values in LineItemList_ntab

INSERT INTO TABLE (
  SELECT  P.LineItemList_ntab
   FROM   PurchaseOrder_objtab P
   WHERE  P.PONo = 1001
  )
  SELECT  01, REF(S), 12, 0
   FROM   Stock_objtab S
   WHERE  S.StockNo = 1534 ;

The preceding statement inserts a line item into the nested table identified by the TABLE expression. The inserted line item contains a REF to the row object with a StockNo value of 1534 in the object table Stock_objtab.

The following statements follow the same pattern as the previous ones:

Example 9-33 Inserting Values in PurchaseOrder_objtab and LineItemList_ntab

INSERT INTO PurchaseOrder_objtab
  SELECT  2001, REF(C),
          SYSDATE, '20-MAY-1997',
          LineItemList_ntabtyp(),
          Address_objtyp('55 Madison Ave','Madison','WI','53715')
   FROM   Customer_objtab C
   WHERE  C.CustNo = 2 ;

INSERT INTO TABLE (
  SELECT  P.LineItemList_ntab
   FROM   PurchaseOrder_objtab P
   WHERE  P.PONo = 1001
  )
  SELECT  02, REF(S), 10, 10
   FROM   Stock_objtab S
   WHERE  S.StockNo = 1535 ;

INSERT INTO TABLE (
  SELECT  P.LineItemList_ntab
   FROM   PurchaseOrder_objtab P
   WHERE  P.PONo = 2001
  )
  SELECT  10, REF(S), 1, 0
   FROM   Stock_objtab S
   WHERE  S.StockNo = 1004 ;

INSERT INTO TABLE (
  SELECT  P.LineItemList_ntab
   FROM   PurchaseOrder_objtab P
   WHERE  P.PONo = 2001
  )
  VALUES(11, (SELECT REF(S)
    FROM  Stock_objtab S
    WHERE S.StockNo = 1011), 2, 1) ;

Example 9-34 Query Purchase Orders

SELECT  p.PONo
 FROM   PurchaseOrder_objtab p
 ORDER BY VALUE(p) ;

Oracle invokes the map method getPONo for each PurchaseOrder_objtyp object in the selection. Because that method returns the object's PONo attribute, the selection produces a list of purchase order numbers in ascending numerical order.

The following queries correspond to the queries executed under the relational model.

Example 9-35 Query Customer and Line Item Data for Purchase Order 1001

SELECT  DEREF(p.Cust_ref), p.ShipToAddr_obj, p.PONo, 
        p.OrderDate, LineItemList_ntab
 FROM   PurchaseOrder_objtab p
 WHERE  p.PONo = 1001 ;

Example 9-36 Query Total Value of Each Purchase Order

SELECT   p.PONo, p.sumLineItems()
 FROM    PurchaseOrder_objtab p ;

Example 9-37 Query Purchase Order and Line Item Data for Stock Item 1004

SELECT   po.PONo, po.Cust_ref.CustNo,
         CURSOR (
           SELECT  *
            FROM   TABLE (po.LineItemList_ntab) L
            WHERE  L.Stock_ref.StockNo = 1004
           )
 FROM    PurchaseOrder_objtab po ; 

The preceding query returns a nested cursor for the set of LineItem_obj objects selected from the nested table. The application can fetch from the nested cursor to get the individual LineItem_obj objects. The query can also be expressed by unnesting the nested set with respect to the outer result:

SELECT   po.PONo, po.Cust_ref.CustNo, L.*
 FROM    PurchaseOrder_objtab po, TABLE (po.LineItemList_ntab) L
 WHERE   L.Stock_ref.StockNo = 1004 ;

The preceding query returns the result set as a flattened form (or First Normal Form). This type of query is useful when accessing Oracle collection columns from relational tools and APIs, such as ODBC. In the preceding unnesting example, only the rows of the PurchaseOrder_objtab object table that have any LineItemList_ntab rows are returned. To fetch all rows of the PurchaseOrder_objtab table, regardless of the presence of any rows in their corresponding LineItemList_ntab, then the (+) operator is required:

SELECT   po.PONo, po.Cust_ref.CustNo, L.*
 FROM    PurchaseOrder_objtab po, TABLE (po.LineItemList_ntab) (+) L
 WHERE   L.Stock_ref.StockNo = 1004 ;

Example 9-38 Query Average Discount across all Line Items of all Purchase Orders

This request requires querying the rows of all LineItemList_ntab nested tables of all PurchaseOrder_objtab rows. Again, unnesting is required:

SELECT   AVG(L.DISCOUNT)
 FROM    PurchaseOrder_objtab po, TABLE (po.LineItemList_ntab) L ;

Example 9-39 Delete Purchase Order 1001 in an Object-Relational Model

DELETE
 FROM   PurchaseOrder_objtab
 WHERE  PONo = 1001 ;

Example 9-46 Alter PurchaseOrder_objtyp

ALTER TYPE PurchaseOrder_objtyp
    DROP ATTRIBUTE Cust_ref,
    DROP ATTRIBUTE ShipToAddr_obj
    CASCADE;

This time we were able to use the CASCADE option to let the system perform validations and make all necessary changes to dependent types and tables.

Example 9-47 Insert into LineItemList_ntabtyp with VALUE()

SQL> INSERT INTO TABLE (
  2    SELECT c.PurchaseOrderList_ntab
  3      FROM Customer_objtab c
  4      WHERE c.CustName = 'John Nike'
  5     )
  6    VALUES (1020, SYSDATE, SYSDATE + 1,
  7      LineItemList_ntabtyp(
  8        LineItem_objtyp(1, MAKE_REF(Stock_objtab, 1004), 1, 0),
  9        LineItem_objtyp(2, MAKE_REF(Stock_objtab, 1011), 3, 5),
 10        LineItem_objtyp(3, MAKE_REF(Stock_objtab, 1535), 2, 10)
 11       )
 12     );

Lines 1-5 use a TABLE expression to select the nested table to insert into—namely, the nested table of purchase orders for customer John Nike.

The VALUES clause (lines 6-12) contains a value for each attribute of the new purchase order, namely:

PONo
OrderDate
ShipDate
LineItemList_ntab

Line 6 of the INSERT statement specifies values for the three purchase order attributes PONo, OrderDate, and ShipDate.

Only attribute values are given; no purchase order constructor is specified. You do not need to explicitly specify a purchase order constructor to instantiate a purchase order instance in the nested table because the nested table is declared to be a nested table of purchase orders. If you omit a purchase order constructor, the system instantiates a purchase order automatically. You can, however, specify the constructor if you want to, in which case the VALUES clause will look like this:

VALUES (
  PurchaseOrder_objtyp(1020, SYSDATE, SYSDATE + 1,
    LineItemList_ntabtyp(
      LineItem_objtyp(1, MAKE_REF(Stock_objtab, 1004), 1, 0),
      LineItem_objtyp(2, MAKE_REF(Stock_objtab, 1011), 3, 5),
      LineItem_objtyp(3, MAKE_REF(Stock_objtab, 1535), 2, 10)
     )
  )
)

Lines 7-11 instantiate and supply data for a nested table of line items. The constructor method LineItemList_ntabtyp(…) creates an instance of such a nested table that contains three line items.

The line item constructor LineItem_objtyp() creates an object instance for each line item. Values for line item attributes are supplied as arguments to the constructor.

The MAKE_REF function creates a REF for the Stock_ref attribute of a line item. The arguments to MAKE_REF are the name of the stock table and the primary key value of the stock item there that we want to reference. We can use MAKE_REF here because object identifiers in the stock table are based on the primary key: if they were not, we would have to use the REF function in a subquery to get a REF to a row in the stock table.

Example 9-48 Query Customer_objtab for Customer John Nike

SELECT c.PurchaseOrderList_ntab
   FROM Customer_objtab c
   WHERE CustName = 'John Nike';

Example 9-49 Query Customer_objtab Using TABLE Expression

SELECT p.PONo, p.OrderDate, p.Shipdate
    FROM Customer_objtab c, TABLE(c.PurchaseOrderList_ntab) p
    WHERE c.CustName = 'John Nike';

      PONO ORDERDATE SHIPDATE
---------- --------- ---------
      2001 25-SEP-01 26-SEP-01
      1020 25-SEP-01 26-SEP-01

A TABLE expression takes a collection as an argument and can be used like a SQL table in SQL statements. In the preceding query, listing the nested table of purchase orders in a TABLE expression in the FROM clause enables us to select columns of the nested table just as if they were columns of an ordinary table. The columns are identified as belonging to the nested table by the table alias they use: p. As the example shows, a TABLE expression in the FROM clause can have its own table alias.

Inside the TABLE expression, the nested table is identified as a column of customer table Customer_objtab by the customer table's own table alias c. Note that the table Customer_objtab appears in the FROM clause before the TABLE expression that refers to it. This ability of a TABLE expressions to make use of a table alias that occurs to the left of it in the FROM clause is called left correlation. It enables you to daisy-chain tables and TABLE expressions—including TABLE expressions that make use of the table alias of another TABLE expression. In fact, this is how you are able to select columns of nested tables that are embedded in other nested tables.

Here, for example, is a query that selects information about all line items for PO number 1020:

Example 9-50 Query Customer_objtab for Purchase Order 1020

SELECT p.PONo, i.LineItemNo, i.Stock_ref.StockNo, i.Quantity, i.Discount
  FROM Customer_objtab c, TABLE(c.PurchaseOrderList_ntab) p,
    TABLE(p.LineItemList_ntab) i
  WHERE p.PONo = 1020;
 
      PONO LINEITEMNO STOCK_REF.STOCKNO   QUANTITY   DISCOUNT
---------- ---------- ----------------- ---------- ----------
      1020          1              1004          1          0
      1020          2              1011          3          5
      1020          3              1535          2         10

The query uses two TABLE expressions, the second referring to the first. Line item information is selected from the inner nested table that belongs to purchase order number 1020 in the outer nested table.

Notice that no column from the customer table occurs in either the SELECT list or the WHERE clause. The customer table is listed in the FROM clause solely to provide a starting point from which to access the nested tables.

Here is a variation on the preceding query. This version shows that you can use the * wildcard to specify all columns of a TABLE expression collection:

SELECT p.PONo, i.*
  FROM Customer_objtab c, TABLE(c.PurchaseOrderList_ntab) p,
    TABLE(p.LineItemList_ntab) i
  WHERE p.PONo = 1020;

Example 9-51 Create Corp_Customer_objtyp

CREATE TYPE Corp_Customer_objtyp UNDER Customer_objtyp
            (account_mgr_id     NUMBER(6) ) NOT FINAL;
/

When you use a CREATE TYPE statement to create a new subtype, you list only the new attributes and methods that you are adding. The subtype inherits all existing attributes and methods from its base type, so these do not need to be specified. The new attributes and methods are added after the inherited ones. For example, the complete list of attributes for the new Corp_Customer_objtyp subtype looks like this:

CustNo
CustName
Address_obj
Phonelist_var
PurchaseOrderList_ntab
Account_mgr_id

By default, you can store instances of a subtype in any column or object table that is of any base type of the subtype. This ability to store subtype instances in a base type slot is called substitutability. Columns and tables are substitutable unless they have been explicitly declared to be NOT SUBSTITUTABLE. The system automatically adds new columns for subtype attributes and another, hidden column for the type ID of the instance stored in each row.

Actually, it is possible to create a subtype of a FINAL type, but first you must use an ALTER TYPE statement to evolve the type from a FINAL type to a NOT FINAL one. If you want existing columns and tables of the altered type to be able to store instances of new subtypes, specify the CASCADE option CONVERT TO SUBSTITUTABLE in the ALTER TYPE statement.

Example 9-52 Insert Data for Ordinary Customer

INSERT INTO Customer_objtab
  VALUES (
    3, 'William Kidd',
    Address_objtyp('43 Harbor Drive', 'Redwood Shores', 'CA', '95054'),
    PhoneList_vartyp('415-555-1212'),
    PurchaseOrderList_ntabtyp()
  );

The VALUES clause contains data for each Customer_objtyp attribute but omits the Customer_objtyp constructor. The constructor is optional here because the declared type of the table is Customer_objtyp. For the nested table attribute, the constructor PurchaseOrderList_ntabtyp() creates an empty nested table, but no data is specified for any purchase orders.

Here is a statement that inserts a new corporate customer in the same table. Note the use of the constructor Corp_Customer_objtyp() and the extra data value 531 for the account manager ID:

Example 9-53 Insert Data for Corporate Customer

INSERT INTO Customer_objtab
  VALUES (
    Corp_Customer_objtyp(   -- Subtype requires a constructor
      4, 'Edward Teach',
      Address_objtyp('65 Marina Blvd', 'San Francisco', 'CA', '94777'),
      PhoneList_vartyp('415-555-1212', '416-555-1212'),
      PurchaseOrderList_ntabtyp(), 531
    )
  );

The following statements insert a purchase order for each of the two new customers. Unlike the statements that insert the new customers, the two statements that insert purchase orders are structurally the same except for the number of line items in the purchase orders:

Example 9-54 Insert Purchase Order for Ordinary Customer

INSERT INTO TABLE (
  SELECT c.PurchaseOrderList_ntab
    FROM Customer_objtab c
    WHERE c.CustName = 'William Kidd'
   )
  VALUES (1021, SYSDATE, SYSDATE + 1,
    LineItemList_ntabtyp(
      LineItem_objtyp(1, MAKE_REF(Stock_objtab, 1535), 2, 10),
      LineItem_objtyp(2, MAKE_REF(Stock_objtab, 1534), 1, 0)
     )
   );

Example 9-55 Insert Purchase Order for Corporate Customer

INSERT INTO TABLE (
  SELECT c.PurchaseOrderList_ntab
    FROM Customer_objtab c
    WHERE c.CustName = 'Edward Teach'
   )
  VALUES (1022, SYSDATE, SYSDATE + 1,
    LineItemList_ntabtyp(
      LineItem_objtyp(1, MAKE_REF(Stock_objtab, 1011), 1, 0),
      LineItem_objtyp(2, MAKE_REF(Stock_objtab, 1004), 3, 0),
      LineItem_objtyp(3, MAKE_REF(Stock_objtab, 1534), 2, 0)
     )
   );

Example 9-56 Selecting All Corporate Customers and Their Subtypes

The following query uses a WHERE clause that contains an IS OF predicate to filter out customers that are not some kind of corporate customer. In other words, the query returns all kinds of corporate customers but does not return instances of any other kind of customer:

SELECT VALUE(c)
  FROM Customer_objtab c
  WHERE VALUE(c) IS OF (Corp_Customer_objtyp);

Example 9-57 Selecting All Corporate Customers with No Subtypes

This query is identical to the preceding one except that it adds the ONLY keyword in the IS OF predicate to filter out any subtypes of Corp_Customer_objtyp. Rows are returned only for instances whose most specific type is Corp_Customer_objtyp:

SELECT p.PONo
  FROM Customer_objtab c, TABLE(c.PurchaseOrderList_ntab) p
  WHERE VALUE(c) IS OF (ONLY Corp_Customer_objtyp);

Example 9-58 Selecting PONo Just for Corporate Customers

The following query uses a TABLE expression to get purchase order numbers (from the nested table of purchase orders). Every kind of customer has this attribute, but the WHERE clause confines the search just to corporate customers:

SELECT p.PONo
  FROM Customer_objtab c, TABLE(c.PurchaseOrderList_ntab) p
  WHERE VALUE(c) IS OF (Corp_Customer_objtyp);

Example 9-59 Selecting a Subtype Attribute

The following query returns data for account manager ID. This is an attribute possessed only by the corporate customer subtype: the declared type of the table lacks it.

In this query the TREAT() function is used to cause the system to try to regard or treat each customer as a corporate customer in order to access the subtype attribute Account_mgr_id:

SELECT CustName, TREAT(VALUE(c) AS Corp_Customer_objtyp).Account_mgr_id
  FROM Customer_objtab c
  WHERE VALUE(c) IS OF (ONLY Corp_Customer_objtyp);

TREAT() is necessary here because Account_mgr_id is not an attribute of the table's declared type Customer_objtyp. If you simply list the attribute in the SELECT list as if it were, a query like the following one will return the error invalid column name error. This is so even with a WHERE clause that excludes all but instances of Corp_Customer_objtyp. The WHERE clause is not enough here because it merely excludes rows from the result.

rem Following statement returns error, invalid column name for Account_mgr_id
SELECT CustName, Account_mgr_id
  FROM Customer_objtab
  WHERE VALUE(c) IS OF (ONLY Corp_Customer_objtyp);

Example 9-60 Discovering the (Sub)Type of Each Instance

Every substitutable column or object table has an associated hidden type-ID column that identifies the type of the instance in each row. You can look up the type ID of a type in the USER_TYPES catalog view.

The function SYS_TYPEID() returns the type ID of a particular instance. The following query uses SYS_TYPEID() and a join on the USER_TYPES catalog view to return the type name of each customer instance in the table Customer_objtab:

SELECT c.CustName, u.TYPE_NAME
  FROM Customer_objtab c, USER_TYPES u
  WHERE SYS_TYPEID(VALUE(c)) = u.TYPEID;

CUSTNAME                              TYPE_NAME
------------------------------------- ---------------------
Jean Nance                            CUSTOMER_OBJTYP
John Nike                             CUSTOMER_OBJTYP
William Kidd                          CUSTOMER_OBJTYP
Edward Teach                          CORP_CUSTOMER_OBJTYP