Call now: 252-767-6166  
Oracle Training Oracle Support Development Oracle Apps

 E-mail Us
 Oracle Articles
New Oracle Articles

 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog

 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Oracle Support

 SQL Tuning

 Oracle UNIX
 Oracle Linux
 Remote s
 Remote plans
 Application Server

 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S


 Consulting Staff
 Consulting Prices
 Help Wanted!


 Oracle Posters
 Oracle Books

 Oracle Scripts

Don Burleson Blog 







Creating the Method Prototypes

Oracle Database Tips by Donald Burleson

For the purposes of this example, we will take the data type definitions from the data dictionary and the psuedocode from the mini-spec to complete our understanding of the hierarchical mapping of methods.  If we have performed our analysis properly, we will reference our set of data flow diagrams, beginning at level one (describing our fill_order process), and including all of the lower-level data flow diagrams.

Here we can begin by listing each process, and showing the sub-methods within each process:

1 - fill_order 

         1.1 - check_customer_credit 

         1.2 - check_inventory

                     1.2.1 - check_stock_level
                     1.2.2 - generate_backorder_notice
                     1.2.3 - decrement_inventory
                     1.2.4 - prepare_packing_slip

         1.3 - prepare_invoice

                     1.3.1 - compute_order_cost
                     1.3.2 - compute_shipping_charges
                     1.3.3 - add_handling_charge
                     1.3.4 - assemble_invoice

We should now be able to see how all of the methods are nested within other methods. Once this natural hierarchy has been developed we are ready to define the mapping of these processes to our database classes.

As we know, the lowest level data flow diagrams represent "functional primitives", or processes that cannot be decomposed into smaller processes.  Of course, the functional primitive processes will become methods, but does this mean that they will never have sub-components?  If the analyst has performed their job properly there will be no sub-methods in these processes with the exception of standalone methods, such as a compute_shipping_charge method.

Beginning with the these primitive processes, we design a method that accepts the same values as noted on the DFD, and returns the same values to the program that invokes the method.  For example, in Figure 8.5 we see that the complete-shipping-charges process accepts a valid-in-stock-order as input.  Inside this process, it gathers the weight and cost of the items, computes the charges, and returns the shipping charge and total-weight.

Essentially, a prototype is a formal definition of a method that describes all of the input and output data flows.  The accepted form for a prototype is:

return_data_type Method_name
      (input_data_name_1  input_data_type_1,
       input_data_name_2 input_data_type_2, 
       . . .);

Before going into more detail, let's review the possible data types that are used by methods.  These data types may be used to return a data value or they may be accepted by the method as an input parameter.

int - an integer value

varchar - a variable length character string 

*type - a pointer to a data structure

The *type is the most confusing data type for most object novices since it refers to pointers.  A pointer in an object database is an object identifier (OID) that points to the object that will supply the values to the method.  Because most object-oriented databases support 'strong data typing?, we must differentiate between the different types of OIDs.  For example, a pointer to an order (*order), is quite different from a pointer to a customer (*customer) object.  As a practical matter it is more efficient to pass a pointer to the data than it is to pass the data itself, since the pointer (OID) is more compact.

In object database parlance, we design the "prototype" for each process on our DFDs. For example, let's begin by examining how we design the prototype for the compute_shipping_charge() method.

From our DFD, we see that compute_shipping_charges accepts a valid_in_stock_order, and outputs the shipping_charge for the order.  Therefore, we could create a prototype that shows compute_shipping charges as returning an integer (the shipping charge), and accepting a pointer to an order object.

int compute_shipping_charge(valid_in_stock_order *order);

Returning to our data dictionary (not shown) we could see that valid_in_stock_order contains four values that are required for this process to compute the shipping charges:

1. The objects weight in pounds.

2. The desired class of shipping.

3. The origination zip code.

4. The destination zip code.

So, how do we get these items, when we are only giver a pointer to an order?  The method will de-reference the pointer to the order object and gather the required information.  This means that the method will grab the OID and issue the appropriate SQL to accept all of the data items from the object.  Here is what the SQL within the compute_shipping_charges method might look like:


    ORDER.OID = :valid_in_stock_order;

This function returns the shipping charge, expressed as an integer number.

If we did not pass the pointer to the order object to this method, the prototype for compute_shipping charges becomes far more complicated:

int compute_shipping_charge
      (weight int, class char(1),

       origination_zip_code number(9),

       destination_zip_code number(9));

Note that the first token "int" refers to the data type of the value that is returned by the method.  For methods that do not return a value the first token in the prototype is "void".  For example, a method called give_raise would not return a value, and could be prototyped as:

void give_raise(emp_ID number(9), percentage int);

Now that we understand the basics of prototyping, let's prototype every method from our example data flow diagrams.

*order             fill_order(cust_info *customer); 

int                check_customer_credit(cust_info *customer);

int                check_inventory(item_number int);

*invoice           prepare_invoice(valid_in_stock_order *order_form);

int                check_stock_level(item_number int);

*backorder         generate_backorder_request(item_number  int);

void               decrement_inventory(item_number int);

*packing_slip prepare_packing_slip(valid_in_stock_order *order_form);

int                compute_order_cost(valid_in_stock_order *order_form);

int                compute_shipping_charges(valid_in_stock_order *order_form);

int                add_handling_charge(total_weight int);

*invoice           assemble_invoice(item_total_cost   int,
                                                  shipping_charge int,
                                                  handling_charge int);

Let's now describe these prototypes, so we are comfortable with the definitions.  In these prototypes we see that some methods return an integer number, some return on values, and others return pointers to objects.  In object-oriented databases, It is not uncommon to combine assignment statements with method calls.  For example, the following process code will do two things, it will compute the shipping charges for the order and assign the result to a variable called my_shipping_charges:

my_shipping_charges = compute_shipping_charges(:my_order_form_OID);

By the same token (excuse the pun), we can also return an OID in a method call, so we can embed the OID into another object.  In the following code, assume that we have defined the data type for order OID as a pointer to order.  We can now do two things in a single statement.  Below we are invoking the fill_order method and at the same time returning the OID of the new order object into our order_OID variable:

order_OID = fill_order(:cust_info);

What we see is that we have created a complete specification for each method, stating the name and data type of every input and output variable.  Remember from chapter 2, each of these methods will be independently tested, and the internal variable may not be known to the calling method.  This is known as "information hiding", and is used when "private" variables are declared and used within the method.  Remember, our goal is to make each of these methods into re-usable black-boxes that can always be counted on to function properly.  This is the very foundation of object method re-usability.

Let's now introduce the object/relational model that we have prepared for this system.  As we recall from chapter 3, we know that there are several components that are used to describe an object/relational database design.  First, we have the object/relational model for the base objects (Figure 8.6).  This diagram describes all of the base classes in our system, and describes the indexes, tablespaces and the sub-classes for each class definition.

Figure 8.6 - An object/relation diagram for the order processing system. 

Next, we take a look at the aggregate class diagram (Figure 8.7).  Here we see two aggregate class definitions, their internal pointer structures and the index and tablespace information for all classes that are entirely composed of pointers to other objects.

Figure 8.7 - The aggregate class diagram for the order processing system.

Note that in the models we show both the base classes, as well as the aggregate classes.  The question become, how do we map our method prototypes to these classes?  Since the object-relational model represents all objects as tables, the availability of aggregate objects will now allow the coupling of aggregate methods with the "owner" table.  In this fashion, an aggregate object will know how to behave based on these methods. 





Oracle Training at Sea
oracle dba poster

Follow us on Twitter 
Oracle performance tuning software 
Oracle Linux poster


Burleson is the American Team

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum.

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:  

and include the URL for the page.


Burleson Consulting

The Oracle of Database Support

Oracle Performance Tuning

Remote DBA Services


Copyright © 1996 -  2020

All rights reserved by Burleson

Oracle ® is the registered trademark of Oracle Corporation.