The AddProduct() method is a fairly simple piece of code that adds a product. It performs an insert into the product table. The designer may later decide to change the code of the AddProduct() function. First, the code is rewritten in the body of the type. If the specifications of the type do not change, the designer can issue a CREATE or REPLACE statement. In Listing 15.2, the designer has added a line of code into the AddProduct() method.

Listing 15.2. Adding code to the AddProduct() method.

— First, we create the audit table

CREATE TABLE product_audit_table (

product_name varchar2(200),

product_description varchar2(4000));



— Next, we add a line in the AddProduct() function that inserts data

into the audit table

CREATE OR REPLACE TYPE  BODY product_type AS

 MEMBER PROCEDURE AddProduct

 (p_product_name varchar2, p_product_description varchar2,

p_product_price

 number, p_product_price_date date) AS

BEGIN

   insert into product_audit_table values (p_product_name, sysdate);

 —

 This will insert an entry into the audit table for each product

that is added insert into product_table values (p_product_name,

p_product_description, p_product_price, p_product_price_date);

 END AddProduct;

MEMBER FUNCTION DaysSincePriceChange

RETURN NUMBER IS

BEGIN

   RETURN sysdate-SELF.product_price_date;

END DaysSincePriceChange;

END;

/

However, the designer may need to make a change in the specification of the type. If a table or type has been created that is based on the product type, the table or type is a dependant of that type. Any type that has dependants cannot be dropped or changed. Thus, the product type specification cannot be changed without dropping the dependants. Needless to say, this makes it difficult for a designer to change the specification. In Listing 15.3, the designer has decided to delete the parameter product_description from the AddProduct procedure.

Listing 15.3. Deleting product_description from AddProduct.

— The analyst attempts to change the type specification.

SQL > CREATE OR REPLACE TYPE product_type

AS OBJECT(

product_name varchar2(200),

product_description varchar2(4000),

product_price number,

product_price_date date,

— The p_product_description has been removed from this procedure

MEMBER PROCEDURE AddProduct (p_product_name varchar2, p_product_price

 number),

MEMBER FUNCTION DaysSincePriceChange

RETURN NUMBER

);

/

CREATE OR REPLACE TYPE product_type

*

ERROR at line 1:

ORA-02303: cannot drop or replace a type with type or table dependents

— An error is returned notifying the user of a dependency. Since,

the product_table table is a dependent, it must be dropped.

This is usually not a viable option. Standard types such as first_name,

last_name and address may have hundreds of dependent tables and types.

In order to change one of these types, the dependant tables would need

to be dropped, recreated, and reloaded with data. In addition, the 

permissions would need to be re-granted and objects that are dependent 

on these tables would need to be re-compiled.

SQL> DROP TABLE product_table;

SQL> CREATE OR REPLACE TYPE product_type

AS OBJECT(

product_name varchar2(200),

product_description varchar2(4000),

product_price number,

product_price_date date,

— The p_product_description parameter has been removed from this

 procedure

MEMBER PROCEDURE AddProduct (p_product_name varchar2, p_product_price

 number),

MEMBER FUNCTION DaysSincePriceChange

RETURN NUMBER

);

/

Type created.

— After the dependent table was dropped, the replacement of TYPE 

product is successful.

SQL>

Due to these dependencies, the designers should ensure that changes will not need to be made in the TYPE specification. This requires an extra look at each method before releasing the type for general use. The designer should ensure that the specification will be sufficient for long-term use. One way to prevent changes to the specification is to specify methods that may be used in the future. The benefits of a flexible type will far exceed the overhead of unneeded methods in memory and system tables.

Object Ordering

Since objects are composed of several pieces of information, the database must determine how to order the objects. Oracle internal datatypes have predefined rules of ordering. For example, the varchar datatype has a rule to list values that start with an A before values that start with a B. Objects, however, are ordered according to rules that are specified by the creator of the type. If a query requests the objects from a table in descending order, the database must have a way of ordering the objects. Figure 15.2 illustrates this dilemma.

Figure 15.2.  Object ordering.

There are two comparison methods that can perform the ordering function: map methods and order methods.

Map Methods

Map methods return a single value from the object. They return an internal datatype that Oracle can order according to internal rules. For example, a map method may return a Number datatype. Oracle will execute this map method against each object requested in the query. Each object returns a number that Oracle will store in a temporary structure. Once all the relevant objects have return a number as a map value, Oracle orders the numbers by the normal number ordering rules (1 comes before 2, 2 before 3, and so on). Each object is then returned to the user in order of the map values. The value returned by the map method often uniquely identifies each row. This type of map value is the equivalent of a primary key in a relational database. The following is an example of a typical map method. It sorts the objects based on the value of the customer id.

CREATE OR REPLACE TYPE customer_type

AS OBJECT(

customer_id number,

first_name first_name_type,

last_name last_name_type,

address address_type,

MAP MEMBER FUNCTION CustomerMapFunction RETURN NUMBER

);

/



CREATE OR REPLACE TYPE BODY customer_type AS

MAP MEMBER FUNCTION CustomerMapFunction RETURN NUMBER IS

    BEGIN

        RETURN customer_id;

    END;

END;



/

Map methods are the simplest and fastest comparison method. They are easy to understand and execute quickly on the database. Map methods are preferred over order methods. However, a map method is not always robust enough to handle object ordering. Order methods are used for complex object ordering.