Oracle Consulting Oracle Training Oracle Support Development
Home
Catalog
Oracle Books
SQL Server Books
IT Books
Job Interview Books
eBooks
Rampant Horse Books
911 Series
Pedagogue Books

Oracle Software
image
Write for Rampant
Publish with Rampant
Rampant News
Rampant Authors
Rampant Staff
 Phone
 800-766-1884
Oracle News
Oracle Forum
Oracle Tips
Articles by our Authors
Press Releases
SQL Server Books
image
image

Oracle 11g Books

Oracle tuning

Oracle training

Oracle support

Remote Oracle

STATSPACK Viewer

Privacy Policy

 

 
 

Using Oracle Object-Oriented Methods

By Mike Ault

By this time, most of us have been introduced to the object-oriented features included in Oracle. While many may be conversantly familiar with the new functionality that the object extensions to Oracle have introduced, far fewer have taken advantage of this exciting departure from conventional relational design.

While it is recognized that the first deployment of the object-relational database by Oracle does not address every aspect of object-oriented methodology, most of the key elements are covered.

One of the most exciting of these key elements in the object extensions is the support for methods. A method, in the purely object-oriented paradigm, is the incorporation of a specific behavior assigned to an object or element. Fundamentally, it is recognized that the encapsulation of data with its associated behavior, is key to the effective modeling and implementation of complex systems.

Part of the tremendous appeal of the object-oriented paradigm is this ability to encapsulate data and behavior together. Oracle’s newest version of the database brings this sorely needed functionality to the object-relational paradigm. The concept behind methods is of crucial importance in the object-oriented world and will certainly prove to be equally so in implementations requiring Oracle.

Two questions should immediately come to mind in any discussion about object functionality in Oracle. The first, most obvious one is: "How does it work"? The second, but no less important one will be: "How does this impact the way that database systems will be designed"? This paper will seek to describe the ways in which methods can be effectively incorporated into a working object-relational design.

Oracle Method Basics

If one were to consider a purely object-oriented language like C++, it would be found that the underlying principle of method design is the encapsulation of data with its inherent behavior. Indeed, methods represent the actions or events that are associated with a given data object or class. Let us consider for a moment, a simple class object in C++. Recall that a class is a type that consists of data members and their functions. The associated functions comprise the class methods and are also constituent members of that class. In a language like C++ it is possible for elements to achieve this homogeneous characteristic.

In the traditional framework of relational design, behaviors have always maintained a disjoint relationship with the data. The problem of data behavior has been addressed through the use of database triggers, stored packages and procedures, external procedures, and so on. In truth, this approach never coupled behavior with data, but in all fairness, it was never designed to do so. The object-relational nature of Oracle makes great strides in remedying the disparate way that relational methodology treats the association between data and behavior.

Oracle has included a number of extensions to SQL to support the creation and administration of the object features, not the least of which are methods. Methods must be declared as PL/SQL objects but a method definition may, in turn, call any program accessible by the C language. Oracle currently supports four different types of methods: constructor, map, order, and user defined.

A constructor method is a function that creates a user defined object based on the definition stated in the object type's specification. Oracle names the new constructor method the same name as the new object type. The parameters are the same as the attributes named in the object type definition. From the standpoint of method execution, think of the use of the object type name as the invocation of the constructor method.

The creation of a type automatically creates a method (constructor) to populate the type. To call the method the original type name is used. The constructor is never called implicitly but must be called explicitly. The constructor can be used anywhere a function call is used. Here is an example insert into the employee_info table.

insert into employee_info
values(1,name_t('Michael','Ault','R'),   <--- Note use of the name_t constructor method
dependent_list(    <--- Note use of dependent_list constructor method to build insert array     
dependent_t('Wife',name_t('Susan','Ault','K'),'39'), <---- Note use of dependent_t method       
 dependent_t('Daughter',name_t('Marie','Ault','C'),23),               
dependent_t('Daughter',name_t('Michelle','Ault','E'),19))
  6* );

Notice that the format for using the constructor methods walk down the type tree. In this case the type tree for the dependent type looks like so:

dependents      <---- Attribute in table
                      dependent_list      <----- Nested Table Type
                                                    dependent_t       <----- Base Type

Dependents is the attribute so it isn't used in the type tree. Therefore, we start at the dependent_list type as the outermost method and end at dependent_t, the basic type constructor method. Since the type is for a nested table in this example, we specify the dependent_t method multiple times for each insert into the nested table. Notice that we also use a constructor method, name_t, inside the innermost type constructor to perform the insert into the dependents name attribute which is itself a type. Population of a varray type is identical to population of a nested table type with the exception that you can only insert up to the maximum specified count of the varray.

In Oracle, methods can also be used in comparative operations when used with user-defined object types. In the traditional relational model, scalar values of type VARCHAR2 and NUMBER were easy to compare and contrast. This simplicity diminishes when the data types are more complex. Map and Order methods comprise the set of comparison type methods. Map methods provide a way to compare two objects to determine a quantitative outcome. For example, let's say you defined an object type of VEHICLE, you might specify an attribute that describes the maximum speed. Map methods could perform a comparison to determine the vehicle with the fastest or slowest maximum speed. Order methods offer a different perspective. Order methods use internal logic to compare two objects. Order methods are well-suited for making comparisons of small groups of instances against a single value. An order method returned value is not quantitative but, rather, qualitative. In other words, a return value would simply state that object A is less than object B (return value of -1), objects A and B are the same/equal (return value of 0), or object A is greater than object B (return value of 1).

When declaring map and order methods for comparative use of user defined data types, it is important to note that there can be only one map or order method per type. One or the other may be specified, but not both. The following code example illustrates the use of map methods:

CREATE OR REPLACE TYPE site_t as object (
  site_id                          INTEGER,
  company                      company_t,
  address                       address_t,
  employee_id_r             REF emp_t,
MEMBER FUNCTION get_ref
   (emp_id IN integer)
   RETURN REF emp_t,
MAP MEMBER FUNCTION give_site_id
RETURN INTEGER)
/

CREATE TYPE BODY site_t AS
 MEMBER FUNCTION get_ref (emp_id IN integer)
 RETURN INTEGER IS
 status INTEGER:=0;
 BEGIN
    SELF.employee_id_r := emp_t.give_ref(emp_id);
    RETURN status;
 EXCEPTION
 WHEN others THEN
    status := 1;
    RETURN status;
 END;
 MAP MEMBER FUNCTION give_site_id
 RETURN INTEGER IS
 BEGIN
      RETURN site_id;
 END; 
END;

The previous listing will return a site_id value whenever comparison values are required from the site_t type. This allows GROUP BY, ORDER BY and other sort dependent calls against the type to function properly.

The ORDER type MEMBER function allows for ordering of entries that don't have simple solutions like returning a site_id that is already stored in the table. An ORDER function must return an integer value and is only used once per call. A MAP function is used recursively. An ORDER is most useful when you will be comparing a small number of instances against a single value. A MAP function is most useful when you are sorting large numbers of instances. An example of an ORDER function is shown below:

CREATE OR REPLACE TYPE site_t as object (
  site_id                          INTEGER,
  company                      company_t,
  address                       address_t,
  employee_id_r             REF emp_t,
MEMBER FUNCTION get_ref
   (emp_id IN integer)
   RETURN REF emp_t,
ORDER MEMBER FUNCTION give_order( site INTEGER)
RETURN INTEGER)
/

CREATE TYPE BODY site_t AS
 MEMBER FUNCTION get_ref (emp_id IN integer)
 RETURN INTEGER IS
 status INTEGER:=0;
 BEGIN
    SELF.employee_id_r := emp_t.give_ref(emp_id);
    RETURN status;
 EXCEPTION
 WHEN others THEN
    status := 1;
    RETURN status;
 END;
 ORDER MEMBER FUNCTION give_order( site INTEGER)
 RETURN INTEGER IS
 BEGIN
IF site<SELF.site 
   THEN
      RETURN 1;
ELSIF site>SELF.site
   THEN
       RETURN -1;
Else
       RETURN 0;
 END; 
END;

The code just illustrated creates an ORDER function called give_order that returns -1, 1, or 0 depending on the comparison of the input value to the value of the instance attribute. Again, an ORDER function is used on a one time basis (it can only compare the input value to the current instance value) while a MAP method "maps" all instance values into scalars when it is called.

Use of Other Built-in Methods for Collections

In addition to the constructor type Oracle also provides collection methods for use with VARRAYS and NESTED TABLEs. These methods cannot be used outside of PL/SQL. Collection methods cannot be used in DML but only in procedural statements.

EXISTS -- Used to determine if a specific element in a collection exists. EXISTS is used with nested tables

COUNT -- Returns the number of elements that a collection currently contains not including null values. For varrays, count is equal to LAST. For nested tables, COUNT and LAST may be different due to deleted values in interstitial data sites in the nested table.

LIMIT -- Used for VARRAYS to determine the maximum number of values allowed. If LIMIT is used on a nested table it will return a null.

FIRST and LAST -- Return the smallest and largest index numbers for the collection referenced. Naturally, they return null if the collection is empty. For VARRAYS FIRST always returns 1, for nested tables FIRST returns the value of the first filled spot for that entry. LAST returns the last filled instance of a VARRAY and a nested table. For a VARRAY COUNT will always equal LAST. For a nested table they can be different but LAST should always be greater than COUNT if they are different for a nested table.

PRIOR and NEXT -- Return the prior or next value based on the input value for the collection index. PRIOR and NEXT ignore deleted instances in a collection.

EXTEND -- Appends instances to a collection. EXTEND has three forms, EXTEND, which adds one null instance, EXTEND(n) which adds "n" null instances and EXTEND(n,m) which appends N copies of instance "m" to the collection. For not null specified collections forms one and two cannot be used.

TRIM -- Trim removes instances from a collection. TRIM used with no arguments removes the last instance, TRIM(n) removes "n" instances from the collection.

DELETE -- DELETE removes specified items from a nested table or all of a VARRAY. DELETE specified with no arguments removes all instances of a collection. For nested tables only DELETE(n) removes the nth instance and DELETE(n,m) deletes the nth through the mth instances of the nested table that relate to the specified master record.

Remember when using these methods that a VARRAY is a dense type, that its values start at one and go to the last filled value as far as the index is concerned with no breaks and no null values allowed. A nested table is allowed to have deleted values (i.e. it can be "sparse"). Therefore if you attempt to use a method that is inappropriate such as DELETE with an argument against a VARRAY you will receive an error.

Collection methods can raise the following exceptions:

COLLECTION_IS_NULL -- Caused when the collection referenced is atomically null.

NO_DATA_FOUND -- Subscript points to a null instance of the collection.

SUBSCRIPT_BEYOND_COUNT -- The specified subscript is beyond the number of instances in the collection.

SUBSCRIPT_OUTSIDE_LIMIT -- The specified subscript is outside the legal range (usually received from VARRAY references)

VALUE_ERROR -- Subscript is null or is not an integer.

These collection methods can be used in a variety of ways. In the case of a nested table where some of the values are null, selection using a standard cursor could result in an exception. Use the FIRST and NEXT collection methods to transverse these null values. The following code sample illustrates an interesting use of several collection methods:

j := dependents.FIRST;
WHILE j IS NOT NULL LOOP
 process dependents(j)
   j := dependents.NEXT(j);
END LOOP 

So what does this code fragment do? First, we use the FIRST collection method to get the index integer of the first valid record for the dependents nested table. Remember that a nested table is a sparse construct so the first valid value may not be one. Next, we begin loop processing of values assuming we didn’t get a null value on our call to FIRST. Once we have processed the first value we reset out counter to the appropriate NEXT value. Once NEXT evaluates to NULL we exit the loop.

Another example would be for a VARRAY where we want to only process the number of values that are in each VARRAY instance set. To do this we can use FIRST and LAST or, one (1) and COUNT.

FOR j IN addresses.FIRST..addresses.LAST LOOP

-- or ñ

FOR j in 1..addresses.COUNT LOOP

The last example shows how to determine if a specific number of instances can be added to a VARRAY using the LIMIT collection method.

IF (addresses.COUNT + 5) < addresses.LIMIT THEN
  -- add 5 more addresses

Again, let us stress, as is shown in these code fragments, the collection methods can only be used in procedural statements and not in DML.

User Defined Methods

User defined methods are, as the name implies, program units that perform some specialized action other than a comparative function. The content and action of user defined methods is at the developers discretion. User defined methods can contain embedded calls to other methods, external procedures, or even other programs. While the declarative basis for all methods is PL/SQL, a method may access any program callable by the C language. This provides a tremendous amount of flexibility to extend the programmatic control which developers may now exercise. When methods are defined for a supertype, they are inherited by all subtypes under the supertype. This points out two important aspects of object type and method design: Created behaviors are shared under an object type class, and each subtype receives the exact same behavior (method). To illustrate some of the utility found in user defined methods, the next section presents a brief but powerful example.

Creation of a REF Request and REF Passing Method Set

To properly implement an object-oriented type set where a dependent table object uses a REF referencing a master table object, two methods should be created. The first method, which for convenience we will call the get_ref method, and a second, which we will call the give_ref method will be created. The first will reside in the dependent table object type specification and the second will reside in the master table object type specification. As its name implies the whole purpose of the first method is to request the value of a specific REF from the master table. The whole purpose of the second method is to receive the request for the REF, get the requested REF from the master table object and return the REF to the calling method. For this example we have two object tables, EMPLOYEES and JOB_SITES that are related as is shown in figure 1.

Figure 1

As you can see from looking at the OOERD in Figure 1, we have the two object tables, their main types and a single method in each. The code to implement this structure is shown below.

CREATE OR REPLACE TYPE address_t (
street     VARCHAR2(30),
city        VARCHAR2(30),
state      CHAR(2),
zip         CHAR(5))
/
CREATE OR REPLACE TYPE emp_t AS OBJECT (
  employee_id                      INTEGER ,
  name                                  name_t,
  birthdate                             DATE,
  hiredate                              DATE,
  address                              address_t,
  dependents                        dependent_list,
MEMBER FUNCTION give_ref(id IN integer)
RETURN REF emp_t,
PRAGMA RESTRICT_REFERENCES (give_ref,WNPS)
)
/

rem
rem Now create the object employees which contains
rem nested tables, types and normal attributes
rem
CREATE TABLE employees OF emp_t
OIDINDEX oid_employees (TABLESPACE raw_INDEX)
NESTED TABLE dependents STORE AS dependentsv8a
       PCTFREE 10 PCTUSED 80
       INITRANS 5 MAXTRANS 255
       TABLESPACE raw_data
       STORAGE ( 
              INITIAL 20m NEXT 10m
              MINEXTENTS 1 MAXEXTENTS 10
              PCTINCREASE 0
       ) 
/

ALTER TABLE employees ADD
       CONSTRAINT PK_employees
              PRIMARY KEY (employee_id)
       USING INDEX
              PCTFREE 20
              INITRANS 5
              MAXTRANS 255
              TABLESPACE raw_index
              STORAGE ( 
                     INITIAL 10m NEXT 10m
                     MINEXTENTS 1 MAXEXTENTS 121
                     PCTINCREASE 0 FREELISTS 5
              ) 
/

ALTER TABLE employees MODIFY
   name NOT NULL
/

CREATE OR REPLACE TYPE BODY emp_t IS
MEMBER FUNCTION give_ref(id IN integer)
  RETURN REF emp_t  IS
  ref_val REF emp_t;
BEGIN
  SELECT REF(e) INTO ref_val
  FROM employees e
  WHERE e.employee_id=id;
  RETURN ref_val;
END;
END;
/

CREATE or replace TYPE company_t (
   company_name          VARCHAR2(32),
   company_business     VARCHAR2(32),
   company_contact        name_t,
   contact_home              VARCHAR2(20))
/

rem
rem The job_sites object is an independent one-to-many
rem from employees. We will REF emp_t and EMPLOYEES
rem

CREATE OR REPLACE TYPE site_t as object (
  site_id                          INTEGER,
  company                      company_t,
  address                       address_t,
  employee_id_r             REF emp_t,
MEMBER FUNCTION get_ref
   (emp_id IN integer)
   RETURN REF emp_t)
/

rem 
rem job_sites is an object table definition
rem

CREATE TABLE job_sites OF site_t
     (employee_id_r WITH ROWID
      SCOPE IS employees)
      OIDINDEX oid_job_sites (TABLESPACE raw_index)
       PCTFREE 10 PCTUSED 80
       INITRANS 5 MAXTRANS 255
       TABLESPACE raw_data
       STORAGE ( INITIAL 20m NEXT 10m
                           MINEXTENTS 1 MAXEXTENTS 10
                          PCTINCREASE 0
                        )
/
ALTER TABLE job_sites ADD
       CONSTRAINT PK_job_sites
              PRIMARY KEY (site_id)
       USING INDEX
              PCTFREE 20
              INITRANS 5 MAXTRANS 255
              TABLESPACE raw_index
              STORAGE (INITIAL 10m NEXT 10m
                                 MINEXTENTS 1 MAXEXTENTS 121
                                 PCTINCREASE 0 FREELISTS 5
                                 ) 
/

CREATE TYPE BODY site_t AS
MEMBER FUNCTION get_ref (emp_id IN integer)
RETURN INTEGER IS
status INTEGER:=0;
BEGIN
    SELF.employee_id_r := emp_t.give_ref(emp_id);
    RETURN status;
EXCEPTION
WHEN others THEN
    status := 1;
    RETURN status;
END;
END;
/

As you can see the actual methods, get_ref and give_ref are quite small and non-complex. To use the methods they are called like normal functions during insert or update operations involving the REFed row in the employees table object or the actual stored REF value in the job_sites table object.

Overloading of Methods

Method overloading could be used to provide a common name for a method that would actually be several methods that performed differently dependent upon the values passed as messages to the method. An example of this type of method would be an update method that updates a table attribute based on the value it is passed. If it were passed a date value it would do the conversion to character, same with a number. Or perhaps it would insert into a "TYPE" attribute, a code based on input value.

The technique for overloading a method procedure or function is identical to the overloading of standard procedure or function.

create type test3(type_passed varchar2(8),
member procedure input_type(in_char char),
member procedure input_type(in_varchar varchar2),
member procedure input_type(in_date date),
member procedure input_type(in_number number))
 /

This method overloading allows incorporation of polymorphism into object types.

Method Design Concepts

It is clear that moving procedures out of the application programs and into the database engine is a significant benefit of Oracle's object-relational methodology. Not only does this provide a more secure repository for the code, but it encourages the concept of code reuse. If you combine these aspects with the ability to represent aggregate objects, then you have an extremely robust framework for dealing with complex real world application designs. From a pragmatic point of view, the following reasons are at the heart of any complete method design effort:

  • Code Reusability--Process code only needs to be written once. Cleanly debugged and generated program units can be used in other modules and applications.
  • Configuration Management--Storing code in a central location in a common format, namely, the database, supports rapid code retrieval and simplifies the maintenance task. The database can also be used for tasks like code searches and code backup.
  • Proactive Tuning--Because the SQL is stored in the database, DBAs and developers can extract and test the methods used in applications. Information garnered from these tests can be used to pinpoint areas where indexes are needed, identify tables that can benefit from caching in the buffer pool, and other performance tuning techniques.
  • Application Portability--Because code is resident in the database, it (the code) is immune from platform changes like operating system changes or multiplatform execution. Portability from platform to platform is simplified because the executable code will not require recompilation.
  • Process Cross-Referencing--Documentation of a method's use in an application is simplified through the use of a data dictionary. Method calls can be examined to identify data and program dependencies. Maintenance is also simplified because all applications that reference a particular entity can be identified easily.

Designing a Method Network (Hierarchy)

The concept of developing a method network or hierarchy can be compared to planning a hierarchy of user defined data types. The principle is essentially the same, except that the focus of the hierarchy is on the various levels of process functionality rather than data types. However, remember that while methods may be mapped as a hierarchy, in reality they have a many-to-many relationship to other methods, and a method may have many sub-methods, while at the same time participating as a method in many super-methods. There are three types of processes to consider during the design phase that will impact the method hierarchy. The three types are:

  • Processes independent of database objects.
  • Processes attached to base-level database objects.
  • Processes attached to aggregate database objects.

You now have the ability to nest methods one inside the other. Just as data can have sub-elements and at the same time be a part of a larger data element, behaviors can be composed of sub-behaviors, while at the same time being a sub-behavior in a larger method. It is advisable to develop a series of steps to accomplish this effort of mapping data to methods. The following steps provide a good starting point for doing so.

  1. Create and refine the following essential analysis and design documents:
    • A complete set of fully decomposed data flow diagrams. This becomes the starting point for all the methods that will be generated. The data flow diagrams assist in determining method names, input and output values, and a preliminary plan for nested methods.
    • A detailed object-relational element diagram (an entity relationship diagram in the relational paradigm) used for identifying the base classes in the application system.
    • A diagram showing the aggregate objects that will be used to associate the higher-level processes with the appropriate methods
  1. Create a prototype for each process identified in the data flow diagram in order to define input and output requirements as well as to track method dependencies.
  2. Identify any standalone program units in the system, and trace their relationships to other program units and data objects.
  3. Perform an initial mapping of method prototypes to data elements.

There are two ways to proceed once the listed steps have been performed. A top-down or a bottom-up strategy can be implemented. The disadvantage of the latter is that it is difficult to ascertain early in the effort which atomic-level program units are needed to fulfill the project requirements. The top-down approach is effective because only those processes required to meet the business objectives will be targeted. As more facts surrounding the relationship between the methods are uncovered, more of the sub-methods will be moved to different parts of the network. As we stated earlier, the effort starts with the data flow diagram or functional model. The data dictionary is used in conjunction with the data flow diagram to associate the atomic-level attributes participating in the data process flow. At this stage, processes have a much broader scope, and it is fully understood that further decomposition will yield further sub-processes.

As you progress from one level of detail to the next, you should find the sub-processes required to perform the overall tasks for which they are assigned. At each level of analysis, you should perform an evaluation to identify duplicate process behaviors under different higher-level processes.

At this stage of analysis, duplicated processes are not eliminated or consolidated because further analysis at greater granularity might reveal subtle differences in the processes after all. What is done is that the two (or more) processes are labeled to identify their system duplicity and the analysis proceeds. Ultimately, you'll want to proceed until decomposition is no longer possible. At that point, the effort turns to organizing the defined processes. In which case, you start by grouping the processes into the three categories named earlier: independent, base level, and aggregate.

The next step is to revisit the duplicated processes and consolidate them. This is not to say that you will create one big package and put redundant sub-processes together. Instead, the effort is focused on organization. Later, redundant processes will be grouped categorically by function. These steps have proven to be a most effective means of code organization in terms of maintenance and deployment.

Once categorization is complete, the groundwork for the method hierarchy will be in place.

The benefits of developing a method network are threefold.

  • It serves to provide much-needed information regarding method interdependency and, toward that end, provides the stepping stone to effective documentation of the system being built.
  • It provides the mechanism for decomposing processes so that more generic and useful atomic level sub-processes can be created, thus reducing the brunt of coding activity in the long run because reuse is encouraged.
  • The process of creating a method network serves to reduce the confusion level that is sure to ensue when there is a propensity of code embedded in the database in the form of coupled behavior with data elements.

Conclusion

Methods are a powerful new tool in the Oracle developer’s arsenal. For the first time, database applications can more closely mirror the functionality of object-oriented programming by fusing data with behavior. The one aspect of modeling database applications in the real world that confounded developers was the ability to adequately combine the behavior (business rule) of an element with the data itself. The encapsulation of data with its associated behavior, as is commonly done in the object-oriented paradigm, is key to the effective modeling and implementation of complex systems. The exciting new object extensions in Oracle bring this sorely needed functionality to the object-relational paradigm. Along with this exciting new functionality, however, comes added complexity in planning and design.

 

 

   

 Copyright © 1996 -2016 by Burleson. All rights reserved.


Oracle® is the registered trademark of Oracle Corporation. SQL Server® is the registered trademark of Microsoft Corporation. 
Many of the designations used by computer vendors to distinguish their products are claimed as Trademarks