12.1 Object Types

An object type is a user-defined data type that combines data and related methods to model complex entities. In this regard, they are similar to class definitions in an object-oriented language such as C++ or Java. However, unlike Java and C++, Oracle object types have a built-in persistence mechanism, since a table can be defined to store an object type in the database. Thus, Oracle object types can be directly manipulated via SQL.

The best way to define the syntax and features of an object type is with an example. The following DDL statement creates an object type used to model an equity security such as a common stock:

CREATE TYPE equity AS OBJECT (

  issuer_id NUMBER,

  ticker VARCHAR2(6),

  outstanding_shares NUMBER,

  last_close_price NUMBER(9,2),

MEMBER PROCEDURE 

  apply_split(split_ratio in VARCHAR2)

)

NOT FINAL;

The equity object type has four attributes and a single member procedure. The NOT FINAL declaration at the end of the type definition allows for subtypes to be defined in the future (more on this later). The body of the apply_split procedure is defined within a CREATE TYPE BODY statement. The following example illustrates how the apply_split member procedure might be defined:

CREATE TYPE BODY equity AS

  MEMBER PROCEDURE apply_split(split_ratio in VARCHAR2) IS

    from_val NUMBER;

    to_val NUMBER;

  BEGIN

    /* parse the split ratio into its components */

    to_val := SUBSTR(split_ratio, 1, INSTR(split_ratio, ':') -- 1);

    from_val := SUBSTR(split_ratio, INSTR(split_ratio, ':') + 1);



    /* apply the split ratio to the outstanding shares */

    SELF.outstanding_shares := 

     (SELF.outstanding_shares * to_val) / from_val;



    /* apply the split ratio to the last closing price */

    SELF.last_close_price := 

     (SELF.last_close_price * from_val) / to_val;

  END apply_split;

END;

In this example, the SELF keyword is used to identify the current instance of the equity object type. Although it is not required, we recommend using SELF in your code so that it is clear that you are referencing or modifying the current instance's data. We will explore how to call member functions and procedures a bit later in the chapter.

Instances of type equity are created using the default constructor, which has the same name as the object type and expects one parameter per attribute of the object type. The following PL/SQL block demonstrates how an instance of the equity object type can be created using the default constructor:

DECLARE

  eq equity := NULL;

BEGIN

  eq := equity(198, 'ACMW', 1000000, 13.97);

END;

Object type constructors may also be called from within DML statements. The next example queries the issuer table to find the issuer with the name 'ACME Wholesalers', and then uses the retrieved issuer_id field to construct an instance of the equity type:

DECLARE

  eq equity := NULL;

BEGIN

  SELECT equity(i.issuer_id, 'ACMW', 1000000, 13.97)

  INTO eq

  FROM issuer i

  WHERE i.name = 'ACME Wholesalers';

END;

12.1.1 Subtyp es

Oracle9i introduced several notable features to the object-oriented toolset, including inheritance (release 1) and user-defined constructors (release 2). The following type definition demonstrates both of these features:

CREATE TYPE preferred_stock UNDER equity (

  last_dividend_date DATE,

  last_dividend_amount NUMBER(9,2),

  CONSTRUCTOR FUNCTION preferred_stock(last_div_dt DATE,

    last_div_amt NUMBER, eq equity) RETURN SELF AS RESULT

)

FINAL;

Because preferred stock is a special type of equity, you can create a subtype that includes all of the attributes and methods of the equity type along with additional attributes to hold dividend information. In this case, we have added two attributes to hold information about the last dividend payment, along with a user-defined constructor for the preferred_stock type, which is defined in the type body:

CREATE TYPE BODY preferred_stock AS

  CONSTRUCTOR FUNCTION preferred_stock(last_div_dt DATE,

    last_div_amt NUMBER, eq equity) RETURN SELF AS RESULT IS

  BEGIN

    SELF.last_dividend_date := last_div_dt;

    SELF.last_dividend_amount := last_div_amt;

    SELF.issuer_id := eq.issuer_id;

    SELF.ticker := eq.ticker;

    SELF.outstanding_shares := eq.outstanding_shares;

    SELF.last_close_price := eq.last_close_price;

    RETURN;

  END preferred_stock;

END;

For the constructor, we chose to pass in an instance of equity rather than pass in the four equity attributes individually. The next example shows how to create an instance of preferred_stock by creating an instance of equity and passing it into the user-defined constructor:

DECLARE

  eq equity := NULL;

  ps preferred_stock := NULL;

BEGIN

  eq := equity(198, 'ACMW', 1000000, 13.97);

  ps := preferred_stock(SYSDATE, 0.1, eq);

END;

Because preferred_stock is a subtype of equity, an instance of preferred_stock is also an instance of equity. The next example uses the IS OF function to demonstrate this:

SELECT equities.eq

FROM

 (SELECT equity(198, 'ACMW', 1000000, 13.97) eq FROM DUAL 

  UNION ALL

  SELECT preferred_stock(SYSDATE, 0.1, 

    equity(198, 'ACMW', 1000000, 13.97)) eq FROM DUAL

  ) equities

WHERE equities.eq IS OF (equity);



EQ(ISSUER_ID, TICKER, OUTSTANDING_SHARES, LAST_CLOSE_PRICE)

-------------------------------------------------------------

EQUITY(198, 'ACMW', 1000000, 13.97)

PREFERRED_STOCK(198, 'ACMW', 1000000, 13.97, '03-DEC-03', .1)

The equities inline view constructs an instance of equity and an instance of preferred_stock, and the containing query returns those objects that are of type equity; as you can see, both instances, one of equity and one of preferred_stock, are returned. However, an instance of equity is not an instance of preferred_stock, as demonstrated in the next example:

SELECT equities.eq

FROM

 (SELECT equity(198, 'ACMW', 1000000, 13.97) eq FROM DUAL UNION ALL

  SELECT preferred_stock(SYSDATE, 0.1, 

    equity(198, 'ACMW', 1000000, 13.97)) eq FROM DUAL

  ) equities

WHERE equities.eq IS OF (preferred_stock);



EQ(ISSUER_ID, TICKER, OUTSTANDING_SHARES, LAST_CLOSE_PRICE)

-------------------------------------------------------------

PREFERRED_STOCK(198, 'ACMW', 1000000, 13.97, '03-DEC-03', .1)

In this case, the equity instance is not returned, since it is not of type preferred_stock. This distinction will be important when we look at collections later in the chapter.

12.1.2 Object Attributes

So far, we have created several object types and generated several non-persistent (not stored in a database) instances of those object types. If you want to store instances of your object types in a database, you have two choices: create a column in a table to store instances of your object type, or create a special type of table, called an object table, to store instances of your object type. This section will explore how to generate object-based columns, while the following section will explore object tables.

The following table definition shows how an equity object type may be used as an attribute of the fund_holding table:

CREATE TABLE fund_holding (

  fund_id VARCHAR2(10) NOT NULL,

  security equity NOT NULL,

  num_shares NUMBER NOT NULL);

While the fund_id and num_shares columns are defined using standard built-in data types, the security column is defined as type equity. When adding records to the table, you must utilize the object type constructor, as illustrated by the following INSERT statement:

INSERT INTO fund_holding (fund_id, security, num_shares)

VALUES ('101A', equity(198, 'ACMW', 1000000, 13.97), 20000);

To see the attributes of the equity object, you must provide an alias for the table and reference the alias, the name of the column containing the object type, and the object type's attribute. The next query retrieves the fund_id, which is a column in the fund_holding table, and the ticker, which is an attribute of the equity object within the fund_holding table:

SELECT fh.fund_id fund_id, 

  fh.security.ticker ticker

FROM fund_holding fh;



FUND_ID    TICKER

---------- ------

101A       ACMW

Since preferred_stock is a subtype of equity, you can substitute an instance of preferred_stock:

UPDATE fund_holding fh

SET fh.security = preferred_stock(SYSDATE, 0.1, 

  equity(198, 'ACMW', 1000000, 13.97));

The ability to substitute a subtype for its parent type is turned on by default. If you do not want this behavior, you can turn it off when creating or altering your object table. For the fund_holding table, you would issue the following statement:

ALTER TABLE fund_holding MODIFY COLUMN

   security NOT SUBSTITUTABLE AT ALL LEVELS;


However, as shown in the next example, you will not be able to query attributes of the preferred_stock type explicitly, because the table definition specifies type equity:

SELECT fh.fund_id fund_id,

  fh.security.last_dividend_amount div_amt

FROM fund_holding fh;



ERROR at line 2:

ORA-00904: "FH"."SECURITY"."LAST_DIVIDEND_AMOUNT": invalid identifier

Even though you can substitute an instance of a subtype for an instance of a parent type, you are limited to using the attributes and methods defined for the parent type. In the most recent example, the SELECT statement "sees" a table of equity objects. That does not change just because one of those objects happens to be a specific subtype of equity.

12.1.3 Object Tables

In addition to creating object type columns, you can also build an object table specifically for holding instances of your object type. You create these tables by referencing the object type in the CREATE TABLE statement using the OF keyword:

CREATE TABLE equities OF equity;

You can populate the equities table using the constructor for the equity object type, or you may populate it from existing instances of the equity object type. For example, the next statement populates the equities table using the security column of the fund_holding table, which is defined as an equity type:

INSERT INTO equities

SELECT fh.security FROM fund_holding fh;

You can also store any subtype of the equity type in the table:

INSERT INTO equities

VALUES (preferred_stock(SYSDATE - 20, 0.05, 

  equity(199, 'XYZ', 2000000, 8.87)));

When querying the equities table, you can reference the object type's attributes directly, just as you would an ordinary table:

SELECT issuer_id, ticker 

FROM equities;



ISSUER_ID TICKER

---------- ------

       198 ACMW

       199 XYZ

If you want to retrieve the data in the equities table as an instance of an equity object rather than as a set of attributes, you can use the VALUE function to return an object. The following query retrieves the object having a ticker equal to 'ACMW' from the equities table:

SELECT VALUE(e)

FROM equities e

WHERE e.ticker = 'ACMW';





VALUE(E)(ISSUER_ID, TICKER, OUTSTANDING_SHARES, LAST_CLOSE_PRICE)

-----------------------------------------------------------------

PREFERRED_STOCK(198, 'ACMW', 1000000, 13.97, '20-MAR-04', .1)

Since you can treat an object table as either a relational table or as a set of objects, object tables represent the best of both worlds.

You must use a table alias with the VALUE function. You cannot pass a table name to VALUE.


Now that you have an object stored in the database, we can explore how to call the apply_split member procedure defined earlier. Before you call the procedure, you need to find the target object in the table and then tell the object to run its apply_split procedure. The following PL/SQL block expands on the previous example, which finds the object in the equities table with a ticker of 'ACMW', by finding an equity object, invoking its apply_split method, and saving it back to the table again:

DECLARE

  eq equity := NULL;

BEGIN

  SELECT VALUE(e) 

  INTO eq

  FROM equities e

  WHERE ticker = 'ACMW'

  FOR UPDATE;



  /* apply a 2:1 stock split */

  eq.apply_split('2:1');



  /* store modified object */

  UPDATE equities e

  SET e = eq

  WHERE ticker = 'ACMW';

END;

It is important to realize that the apply_split procedure is not operating directly on the data in the equities table; rather, it is operating on a copy of the object held in memory. After the apply_split procedure has executed against the copy, the UPDATE statement overwrites the object in the equities table with the object referenced by the local variable eq, thus saving the modified version of the object.

Specifying FOR UPDATE at the end of the SELECT statement signifies your intent to modify the data being selected at some point in the future, which causes Oracle to place a lock on the data on your behalf. If you do not specify FOR UPDATE in this case, it is possible for another user to modify or delete the data between when you first select the data and when you issue the UPDATE statement.


12.1.4 Object Parameters

Regardless of whether you decide to store object types persistently in the database, you can use them as vehicles for passing data within or between applications. Object types may be used as input parameters and return types for PL/SQL stored procedures and functions. Additionally, SELECT statements can instantiate and return object types even if none of the tables in the FROM clause contain object types. Therefore, object types may be used to graft an object-oriented veneer on top of a purely relational database design.

To illustrate how this might work, let's build an API for our example database that both accepts and returns object types to find and build customer orders. First, we'll identify the necessary object types:

CREATE TYPE customer_obj AS OBJECT

 (cust_nbr NUMBER,

  name VARCHAR2(30));



CREATE TYPE employee_obj AS OBJECT

 (emp_id NUMBER,

  name VARCHAR2(50));



CREATE TYPE order_obj AS OBJECT

 (order_nbr NUMBER,

  customer customer_obj,

  salesperson employee_obj,

  order_dt DATE,

  price NUMBER,

  status VARCHAR2(20));



CREATE TYPE line_item_obj AS OBJECT 

 (part_nbr VARCHAR2(20),

  quantity NUMBER(8,2));

Using these object definitions, we can now define a PL/SQL package containing procedures and functions that support the lifecycle of a customer order:

CREATE PACKAGE order_lifecycle AS

  FUNCTION create_order(v_cust_nbr IN NUMBER, v_emp_id IN NUMBER) 

    RETURN order_obj;

  PROCEDURE cancel_order(v_order_nbr IN NUMBER);

  FUNCTION get_order(v_order_nbr IN NUMBER) RETURN order_obj;

  PROCEDURE add_line_item(v_order_nbr IN NUMBER, 

    v_line_item IN line_item_obj);

END order_lifecycle;



CREATE PACKAGE BODY order_lifecycle AS

  FUNCTION create_order(v_cust_nbr IN NUMBER, v_emp_id IN NUMBER) 

    RETURN order_obj IS

    ord_nbr NUMBER;

  BEGIN

    SELECT seq_order_nbr.NEXTVAL INTO ord_nbr FROM DUAL;



    INSERT INTO cust_order (order_nbr, cust_nbr, sales_emp_id,

      order_dt, expected_ship_dt, status)

    SELECT ord_nbr, c.cust_nbr, e.emp_id,

      SYSDATE, SYSDATE + 7, 'NEW'

    FROM customer c CROSS JOIN employee e

    WHERE c.cust_nbr = v_cust_nbr

      AND e.emp_id = v_emp_id;



     RETURN order_lifecycle.get_order(ord_nbr);

  END create_order;



  PROCEDURE cancel_order(v_order_nbr IN NUMBER) IS

  BEGIN

    UPDATE cust_order SET cancelled_dt = SYSDATE, 

      expected_ship_dt = NULL, status = 'CANCELED'

    WHERE order_nbr = v_order_nbr;

  END cancel_order;



  FUNCTION get_order(v_order_nbr IN NUMBER) RETURN order_obj IS

    ord order_obj := NULL;

  BEGIN

    SELECT order_obj(co.order_nbr, 

      customer_obj(c.cust_nbr, c.name),

      employee_obj(e.emp_id, e.fname || ' ' || e.lname),

      co.order_dt, co.sale_price, co.status)

    INTO ord

    FROM cust_order co INNER JOIN customer c

      ON co.cust_nbr = c.cust_nbr

      INNER JOIN employee e

      ON co.sales_emp_id = e.emp_id

    WHERE co.order_nbr = v_order_nbr;



    RETURN ord;

  EXCEPTION

    WHEN NO_DATA_FOUND THEN

      RETURN ord;

  END get_order;



  PROCEDURE add_line_item(v_order_nbr IN NUMBER, 

    V_line_item IN line_item_obj) IS

  BEGIN

    INSERT INTO line_item (order_nbr, part_nbr, qty)

    VALUES (v_order_nbr, v_line_item.part_nbr, 

      v_line_item.quantity);

  END add_line_item;

END order_lifecycle;

From the API user's standpoint, objects are being stored and retrieved from the database, even though the database behind the API is purely relational. If you are squeamish about using object types in your database schema, this approach can be an attractive alternative to asking your Java coders to directly manipulate relational data.