16.8 How do I…Use MAP and ORDER methods to compare objects?

Problem

Oracle8 is not able to sort or compare objects in my database. How do I configure objects so that the Oracle8 server knows how to compare them?

Technique

Previous How-To’s have used constructor methods to instantiate objects. These methods are system defined; Oracle8 creates a constructor method whenever a user defines an object type. The Oracle8 object implementation also enables users to create methods of their own. The CREATE TYPE and CREATE TYPE BODY commands define member methods; these methods are actually PL/SQL functions and procedures that developers create at object type definition time.

Developers define MAP and ORDER methods to define rules for Oracle8 to use to compare object types. Without these rules, the only object comparisons that Oracle8 will accept are equality conditions, which is a serious limitation in an object relational database.

We will again make use of object analogs of some of the familiar database objects traditionally residing in SCOTT’s schema. This How-To, however, will also make use of MAP and ORDER methods so that Oracle8 will accept requests to sort or compare objects.

Steps

1. Run SQL*Plus and connect as the WAITE user. Use the START command to load and execute the script CHP16_24.SQL as shown in Figure 16.22.

The script creates a new object type with three attributes to describe any particular department.

2. Use the START command to load and execute the script CHP16_25.SQL. The script and its output appear in Listing 16.6.

Listing 16.6 Script CHP16_25.SQL creates and populates the EMP16 table

SQL> start chp16_25

SQL>

SQL> CREATE TABLE emp16

2 (

3 empno number(4),

4 ename varchar2(10),

5 job varchar2(9),

6 mgr number(4),

7 hiredate date,

8 sal number(10,2),

9 comm number(7,2),

10 dept dept_typ16

11 );

Table created.

SQL>

SQL>

SQL> INSERT INTO emp16

2 VALUES (1, ‘SAL’, ‘PHYSICIAN’, 2, ‘17-SEP-55’,

3 115000, null,

4 dept_typ16(1, ‘PEDIATRICS’, ‘ROCHESTER’));

1 row created.

SQL>

SQL> INSERT INTO emp16

2 VALUES (2, ‘JOAN’, ‘NURSE’, null, ‘17-SEP-76’,

3 300000, null,

4 dept_typ16(2, ‘ER’, ‘ROCHESTER’));

1 row created.

In line 10 of the of the CREATE TABLE command the script makes use of the object type created in Step 1. The INSERT statements use the system-defined constructor method to populate the EMP16 table.

3. Use the START command to load and execute the CHP16_26.SQL script. It appears with its result in Figure 16.23.

The script consists of two SELECT statements. The first attempts to select all rows from the EMP16 table where the value of the DEPT object is less than the value of a DEPT object in which the DEPTNO has a value of two, and the other two attributes are null. The second SELECT attempts to extract all the rows from the EMP16 table and present them in order of descending DEPTNO. Both of the statements fail with ORA-22950 errors because Oracle8 cannot compare the DEPT objects.

4. Drop the current definitions of the DEPT_TYP16 object type and the EMP16 table by using the START command to execute the script CHP16_27.SQL. The script and its output appear in Figure 16.24.

5. Use the START command to load and execute the script CHP16_28.SQL as pictured in Figure 16.25.

The CREATE TYPE command in Figure 16.25 is similar to the version in Step 1 except for line 5, which specifies that there will be a user-defined MAP function for this object type. The following CREATE TYPE BODY command creates a very basic MAP function. This function projects a DEPT_TYP16 object into a scalar quantity by returning the DEPTNO attribute. Oracle8 automatically uses this scalar value whenever a user or application asks Oracle8 to compare objects.

6. Use the START command to execute the CHP16_25.SQL script again. This script creates and populates the EMP16 table and appears previously in Listing 16.6.

7. Use the START command to load and execute the CHP16_26.SQL script. This time it succeeds because Oracle8 knows how to compare objects of the DEPT_TYP16 type by using the MAP function created in Step 5.

How It Works

Steps 1 and 2 create an object type definition and embed it within a relational table. This is a common object construction for representing master-detail relationships. Step 3 shows how object comparisons fail in the absence of a MAP or ORDER method because Oracle8 does not know how to compare objects. Step 5 creates a new version of the DEPT_TYP16 object and defines a MAP method as part of the object definition. Step 6 embeds this map-enabled object type into a relational table and Step 7 shows how Oracle8 can compare objects of the DEPT_TYP16 type with help from the MAP method.

Comments

MAP methods are PL/SQL functions embedded within object type definitions. This type of method uses the attributes of its home object to calculate a scalar value. An ORDER method serves the same purpose for objects where projection to a scalar is difficult or impossible. Instead of the MAP method, Step 5 could have defined an order method within the DEPT_TYP16 declaration as shown in Listing 16.7.

Note-If you decide to run the CHP16_29.SQL script in Listing 16.7, execute CHP16_27.SQL first to drop the existing definitions of the DEPT_TYP16 object type and the EMP16 table.

Listing 16.7 Script CHP16_29.SQL creates the DEPT_TYP16 object with an ORDER member function

SQL> start chp16_29

SQL>

SQL> CREATE TYPE dept_typ16

2 ( deptno number(2),

3 dname varchar2(14),

4 loc varchar2(23),

5 ORDER MEMBER FUNCTION order_dept (d dept_typ16) RETURN NUMBER

6 );

Type created.

SQL>

SQL> CREATE TYPE BODY dept_typ16 AS

2 ORDER MEMBER FUNCTION order_dept (d dept_typ16) RETURN NUMBER IS

3 retval number(2);

4 BEGIN

5 if self.deptno < d.deptno then

6 retval := -1;

7 elsif self.deptno = d.deptno then

8 retval := 0;

9 else

10 retval := 1;

11 end if;

12 RETURN retval;

13 END order_dept;

14

14 END;

15 /

Type body created.

ORDER methods compare two instances of their home object type. Like all methods, ORDER methods accept an instance of their home types as their first parameter and refer to this object using the keyword SELF. In Listing 16.7, the SELF keyword appears in lines 5 and 7. Type body definitions can declare this parameter explicitly or implicitly (in Listing 16.7, the SELF parameter is implicitly declared). The method also accepts one explicit parameter containing the second object for the comparison. In the example in Listing 16.7, this passed parameter is named D. ORDER methods return -1, 0, or 1 to indicate that SELF is less than, equal to, or greater than the second parameter. Object type definitions can include one MAP method or one ORDER method but may not contain both.