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 







Oracle Object Methods

Oracle Tips by Burleson Consulting

There is a huge ongoing debate about the benefits of adding objects into mainstream database management. For years, a small but highly vocal group of object-oriented bigots preached the object revolution with an almost religious fervor. However, object-oriented databases languished in the backwaters until the major relational database vendors made a commitment to incorporate objects into their relational engines. And now that object-relational databases are becoming a reality, Oracle developers are struggling to understand how Oracle's new object extensions are going to change their lives.

Oracle Corp.'s commitment to objects is not just acknowledgment of a fad; the benefits of being able to support objects are very real, and Oracle's exciting new extensions are going to create the new foundation for database systems of the 21st century.

When I first glanced at the Oracle documentation, the object extensions seemed mundane. The new features essentially consist of user-defined datatypes, pointers to rows, and the ability to couple data with behavior using methods. However, these new additions are anything but mundane - they are going to change the way that databases are designed, implemented, and used.

Objects and Relational Databases

Relational databases must be able to directly represent the real world. Advocates for object orientation argue that it does not make sense to dismantle your car when you arrive at home each night only to reassemble it every time you want to drive it. With Oracle7, you must assemble aggregates using SQL joins every time you want to see them. With Oracle8, database designers will no longer need to model their applications at their most atomic levels.

The new "pointer" construct allows for the creation of aggregate objects, and it will no longer be necessary to create Oracle "views" to see composite objects. In addition, relational designers will be able to model the real world at all levels of aggregation and not just at the third-normal-form level.

The ability to prebuild real-world objects will enable Oracle designers to model the world as it exists, without re-creating objects from their pieces each time they are needed. These real-world objects also have ramifications for Oracle's SQL. Rather than having to join numerous tables together to create an aggregate object, the object will have an independent existence, even if it is composed entirely of pieces from atomic tables. (See Figure 1.)

This modeling ability also implies a whole new type of database access. Instead of using SQL, you can "navigate" Oracle databases by going from row to row, chasing the pointer references, without ever having to join tables together. This navigational data access enables Oracle designers to create faster links between tables and avoid some of the time-consuming SQL join operations that plague some systems.

Finally, the ability of Oracle to tightly couple data and behavior will change everything. Rather than having all of your process logic in external programs, the process code will move into the Oracle database, and the Oracle engine will manage both the data and the processes that operate on the data. These "methods" were first introduced into the object-oriented model to provide encapsulation and reusability. Encapsulation refers to the requirement that all data inside an object can only be modified by invoking one of its methods. By associating pretested and reliable methods with an object, the Oracle object "knows" how to behave, and the methods always function in the same manner regardless of the target objects.

Reusability is achieved through the elimination of the "code hunt." Before methods were introduced, Oracle programmers had to scan through Pro*C programs or stored procedures searching for their desired code. With methods, the developer needs to know only the name of the class associated with the object, and the list of methods can easily be displayed. Just as the introduction of reusable parts changed the way American manufacturing functioned, the introduction of reusable code will change the way that Oracle systems are constructed and maintained.

This reusability does not come without a price, however. The structure of the aggregate objects must be carefully defined, and the Oracle developer must give careful thought to the methods that are associated with objects at each level of aggregation. Libraries of reusable code will eventually be developed by application developers as well as third-party vendors to be used within new Oracle systems.

Now that we know the compelling benefits of object-relational databases, let's take a closer look at how Oracle8 has implemented these features. Oracle has implemented the object-relational model in stages, is introducing objects in Oracle 8.0, and will introduce inheritance in Oracle 8.2. User-Defined Datatypes

The ability of Oracle to support object types (sometimes called user-defined datatypes) has profound implications for Oracle design and implementation. User-defined datatypes will enable the database designer to:

  • Create aggregate datatypes - Aggregate datatypes are datatypes that contain other datatypes. For example, you could create a type called FULL_ADDRESS that contains all of the subfields necessary for a complete mailing address.

  • Nest user-defined datatypes - Datatypes can be placed within other user-defined datatypes to create data structures that can be easily reused within Oracle tables and PL/SQL. For example, you could create a datatype called CUSTOMER that contains a datatype called CUSTOMER_DEMOGRAPHICS, which in turn contains a datatype called JOB_HISTORY, and so on.

Pointers and Oracle

One of the new user-defined datatypes in the object-relational model is a "pointer" datatype. Essentially, a pointer is a unique reference to a row in a relational table. The ability to store these row IDs inside a relational table extends the traditional relational model and enhances the ability of an object-relational database to establish relationships between tables. The new abilities of pointer datatypes include:

  • Referencing "sets" of related rows in other tables - It is now possible to violate first normal form and have a cell in a table that contains a pointer to repeating table values. For example, an EMPLOYEE table could contain a pointer called JOB_HISTORY_SET, which in turn could contain pointers to all of the relevant rows in a JOB_HISTORY table. This technique also lets you prebuild aggregate objects, such that you could preassemble all of the specific rows that comprise the aggregate table.

  • Allow "pointers" to nondatabase objects in a flat file - For example, a table cell could contain a pointer to a flat file that contains a nondatabase object such as a picture in .gif or .jpeg format.

  • The ability to establish one-to-many and many-to-many data relationships without relational foreign keys - This would alleviate the need for relational JOIN operations, because table columns could contain references to rows in other tables. By dereferencing these pointers, you could retrieve rows from other tables without ever using the time-consuming SQL JOIN operator.

Now that you have a high-level understanding of these Oracle8 features, let's take a closer look at how they are implemented. Basic User-Defined Datatypes

One of the shortcomings of the relational model is the requirement to model all data at its smallest level. For example, if you want to select all of the address information for a customer, you must manipulate STREET_ADDRESS, CITY_ADDRESS, and ZIP_CODE as three separate statements. With abstract datatyping, you can create a new datatype called FULL_ADDRESS and manipulate it as if it were an atomic datatype. Although this may seem like a huge improvement, it is interesting to note that prerelational databases supported this construct, and the Cobol language had ways to create data "types" that were composed of subtypes. For example, in Cobol you could define a full address as follows:

    07 STREET-ADDRESS      PIC X(80).
    07 CITY-ADDRESS        PIC X(80).
    07 ZIP-CODE            PIC X(5). 
You could then move the customer address as if it were an individual 

By the same token, Oracle will let you define a CUSTOMER_ ADDRESS datatype.

CREATE TYPE full_name (
  first_name        varchar2(20),
  middle_init       char(3),
  last_name         varchar2(50));

CREATE TYPE full_address (
  street_address    varchar2(20),
  city_name         varchar2(20),
  state_name        char(2);
  zip_code          varchar2(5));

You could then treat CUSTOMER_ADDRESS as a valid datatype and use it to create tables and select data:

CREATE TABLE customer (
  customer_name     full_name,
  customer_address  full_address,
  . . . 

Now that you've defined the Oracle table, you can reference CUSTOMER_ADDRESS in your SQL just as if it were a primitive datatype:


  customer_name ('ANDREW','S.','BURLESON'),
  customer_address('123 1st st.','Minot, ND','74635');

 '('171 Glenbrook Rd','Rochester','NY', '14616')
WHERE customer_name.last_name = 'Burleson';

Note that the SQL changes when you use user-defined datatypes. Following is the syntax to select a component of FULL_ADDRESS:

SELECT full_address.zip_code 
  WHERE full_address.zip_code LIKE '144%';

Nesting of User-Defined Datatypes

Now let's take this concept one step further and consider how to nest user-defined datatypes within other datatypes. As a basic example, let's create a datatype that encapsulates all of the data in a table:

CREATE TYPE    customer_stuff (
  full_name        customer_name, 
  home_address     customer_address
  business_address customer_address);

With the CUSTOMER_STUFF type defined, defining the table is simple:

CREATE TABLE customer (customer_data customer_stuff);

By using this type of user-defined datatype, you are essentially duplicating the object-oriented concept of encapsulation. That is, you are placing groups of related datatypes into a container that is completely self-contained and has the full authority of the innate relational datatypes such as INT and CHAR:

SELECT customer_stuff.customer_name.zip_code
  FROM customer
  WHERE customer_stuff.customer_name.zip_code
   LIKE '144%';

Using Pointer References with Oracle8

The ability to define datatypes that contain pointers to rows in other database tables will profoundly change the way you create and maintain databases. These extensions to the relational model will enable a cell in a table to reference a list of values or another entire table. This ability will let designers define and implement "aggregate objects" that contain pointer references to the components, rather than having to define a relational view on the data. This will also enable database designers to more effectively model the real world, reduce overhead, and provide a way to attach "methods" or behaviors to aggregate objects.

Repeating Groups and Object Types

Now let's look at how repeating values appear in Oracle8. Oracle PL/SQL uses the VARRAY construct to indicate repeating groups, so you can use the VARRAY mechanism to declare your job history item. The following creates a TYPE called JOB_HISTORY with a maximum of three values:

CREATE TYPE customer_address (
  street_address    varchar2(20),
  city_address      varchar2(20),
  zip_code          char(5));
CREATE TYPE job_details (
  job_dates          varchar2(80),
  job_employer_name  varchar2(80),
  job_title          varchar2(80)
  job_address        customer_address);

CREATE TYPE job_list (
  VARRAY(3) OF REF job_details);

Now that we've defined the datatypes, the following code creates the Oracle table using the datatypes:

CREATE TABLE customer (
  customer_name     full_name,
  cust_address      customer_address,
  prior_jobs        job_list);

CREATE TABLE job_history (
  job_stuff         job_details);

Now that we've created a repeating list within our Oracle definition, in addition to dereferencing the datatype (you dereference a user-defined datatype by prefixing the data name with all of the owner datatypes that are defined within the table), we also need to subscript the PRIOR_JOBS to tell Oracle which one we want:

SELECT customer.prior_jobs.job_title(3)
  WHERE customer.customer_name.last_name 

Establishing Data Relationships to Other Tables with Pointers Now that we have violated first normal form and upset Chris Date, let's carry this argument one step further. If it is possible to allow for repeating values within a table cell, why can't we include a reference to an entirely new table? Imagine a database that allows nesting of tables within tables such that a single cell of one table is a pointer to another whole table. This concept may seem foreign on the surface, but it's not too hard to understand if you consider that many real-world "things," or objects, are made up of subparts.

It has always been a shortcoming of the relational database that only atomic things can be represented directly, and relational "views" are required to assemble aggregate objects. Object technology professors always like to make fun of the relational model's inability to represent aggregate objects, stating that it makes no sense to rebuild objects from their components each time you want to reference the object.

At last, nested object types let Oracle users represent real-world "things" without resorting to views. Let's take a look at how this type of recursive data relationship might be represented within Oracle. The following code creates a TYPE definition for a list of orders. This list of pointers to orders might become a column within an Oracle table:

CREATE TYPE order_set
  AS TABLE OF order;

CREATE TYPE customer_stuff (
  customer_id            integer,
  customer_full_name     full_name,
  customer_full_address  customer_address,
  . . .  
  order_list             order_set);

CREATE TABLE customer OF customer_stuff;

The following shows the new style of table-creation syntax. Both of the following table declarations are identical, except that the CREATE TABLE OF syntax will establish object IDs (OIDs), so that other tables may contain references to rows in the customer table. Without OIDs:

CREATE TABLE customer (cust_data customer_stuff);

With OIDs:

CREATE TABLE customer OF customer_stuff;

In either case, we have now defined a pointer column called ORDER_LIST in the CUSTOMER table. This pointer will point to a list of pointers. Each cell of this list will contain pointers to rows in the ORDER table. (See Figure 2.) Now, here comes the cool part. We can now "prejoin" with the ORDER table to add the order for this customer:

UPDATE customer
  SET order_list (
    SELECT REF(order)/* this returns the OID's from all order rows */
      FROM order
      WHERE order_date = SYSDATE
      AND order.customer_id = (123)


The following query shows how you can navigate between tables without having to join tables together:

SELECT DEREF(order_list)
  FROM customer
  WHEREcustomer_id = 123;  

/* this will return 3 rows from the order table */

Because we have dereferenced the ORDER_LIST datatype, Oracle will access the VARRAY and return the data values for all of the OIDs that are in the array - in this case, the three rows from the order table. The important point is that we have navigated between tables without ever performing a SQL join. Consider the possibilities.

You would never need to embed the foreign key for the CUSTOMER table in the ORDER record, because you could store the pointers in each customer row. Of course, you would never be able to perform a relational join between the CUSTOMER and ORDER tables, but this would not really make any difference as long as you have maintained the ability to navigate between customers and orders with pointers.

Of course, these are one-way pointers from CUSTOMER to ORDER, and you don't have a method to get from the ORDER table to the CUSTOMER table unless you embed a pointer to point to the row that contains the customer for each order. You could do this by creating an "owner" reference inside each order row that contains the OID of the customer who placed the order. What it All Means

Although the syntax extensions for Oracle8 seem very minor, the ramifications for Oracle database design and administration are monumental. You can now design systems that support all of the major object-oriented features, including pointer-based data navigation, coupled data and behaviors, polymorphism, and-upcoming in Oracle 8.2-support for inheritance. The new object features will revolutionize the nature of Oracle systems and herald a new age of database design in which the promises of object orientation will become a reality.

Designing with Oracle Methods

Successfully coupling Oracle9i data and methods requires the use of method prototypes.  The following example shows a hierarchy of methods. If we use the data type definitions in the data dictionary and the pseudocode from the mini-spec it will facilitate our discussion. The example references a set of data flow diagrams. The fill_order process is described in level one, including all of the lower level data flow diagrams each process is listed, including the sub-methods within the 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

This hierarchy helps us visualize how methods are nested within other methods. After the hierarchy has been developed, we can translate these processes into the database classes.

The lowest level data flow diagrams represent functional primitives,  processes that cannot be decomposed into smaller processes. It is obvious that functional primitive processes become methods, but does this mean that they will never have subcomponents? With the exception of standalone methods, such as a compute_shipping_charges method, a method will never have subcomponents if the analyst has designed the process properly.

Using the primitive processes, we can design a method that accepts the same values as noted on the DFD and returns those values to the retrieving program. For example, we might have a process called compute_shipping_charges that accepts a valid_in_stock_order as input. The process gathers the weight and cost of the items, computes the charges, and returns the shipping charge and total weight.

A prototype is essentially 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, 
       . . .);

Let's review the data types that methods can use before going into further detail. These data types can return a data value or they can be an input parameter for a method:

  • INT--Integer value

  • VARCHAR--Variable length character string

  • TYPE--Pointer to a data structure (identical to an Oracle9i OID)

Object novices are often confused by TYPE because it refers to pointers. A pointer in an object database is nothing more than an OID pointing to an object that supplies method values. The various types of OIDs must be carefully differentiated because Oracle 9i supports strong data typing in the SCOPE clause of the CREATE TABLE statement.. For example, a pointer to an order (*order) is quite different from a pointer to a customer (*customer) object. It is more efficient to use pointers than the data itself because the OID pointer is more compact.

As mentioned, Oracle9i fully supports strong typing. Oracle9i uses the SCOPE verb in the CREATE TABLE statement to limit the type of a reference column to a particular OID table. For example, if a customer table is defined with a VARRAY of OIDs consisting of customer orders, the SCOPE clause can be used to insure that only OIDs from the ORDER table are stored within these columns.

In object database parlance, a prototype is designed for each process in the DFD. This is illustrated by examining how the prototype is designed for the compute_shipping_charges method. According to the DFD, compute_shipping_charges accepts a valid_in_stock_order and outputs the shipping_charge for the order. Therefore, the prototype could return an integer (the shipping charge, defined as INT) from compute_shipping_charges and accept a pointer to an order object:

INT compute_shipping_charge(valid_in_stock_order *order);

Here we see the prototype details:

  • INT – This says that the returned value will be an integer

  • compute_shipping_charge – This is the name of the procedure

  • valid_in_stock_order – This is the first parameter pass to the procedure

  • *order – This is the 2nd parameter passed, and the "*" indicates that it is a pointer datatype, pointing to an ORDER object

We assume for the purpose of this example that the valid_in_stock_order contains the following four values, which the process requires to compute the shipping charges:

  • Weight in pounds

  • Desired class of shipping

  • Origination zip code

  • Destination zip code

How can the data items be retrieved when the order is represented by an OID? The method retrieves the data by dereferencing the OID and accessing the order object. In other words, the method captures the OID and dictates SQL to retrieve data items from the object. The SQL within the compute_shipping_charges method might look like this:

    order.oid = :valid_in_stock_order;

The function above yields the shipping charge, expressed as an integer number. If the method has no pointer to the order object, the prototype for compute_shipping_charges becomes far more complicated, as the following shows:

INT compute_shipping_charge
      (weight               int,
       class                char(1),
       origination_zip_code number(9),
       destination_zip_code number(9));

Note that INT refers to the data type of the value returned by the method. If the method does not return a value, INT is replaced by 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);

Armed with this basic understanding of prototyping, we are ready to prototype some methods.  We need to know the names and data types of all input data, as well as the name and data types of the returned value.  These are generally derived from the data flow diagrams in the initial systems analysis.

*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 describe these prototypes to become more comfortable with the definitions. We see that some methods return an integer number, some return values, and others return object pointers. It is not uncommon to combine assignment statements with method calls in object-oriented databases. For example, the following process code computes the shipping charges for the order and assigns the result to a variable called my_shipping_charges:

my_shipping_charges = compute_shipping_charges(:my_order_form_OID);

In the same way, a method call can also return an OID. This means that an OID can be embedded into another object. We assume in the code below that the data type for order_OID has been defined as a pointer to an order. Two things can now be done in a single statement. The fill_order method can be initialized while simultaneously returning the OID of the new order object into the order_OID variable, as follows:

order_OID = fill_order(:cust_info);

The name and data type of every input and output variable has been specified for each method. It is required that each method be tested independently. The internal variable might  be unknown to the calling method. This is called information hiding. It is used whenever private variables are declared within a method. One of the goals of object-oriented databases is to make each method a reusable procedure that can always be counted on to function properly. This is the foundation of object method design 

Let's introduce the Oracle9i model into this system. It should be realized by now that several components are used to describe an object/relational database design. First, the object/relational model must be delineated for the base objects. Figure 6.9 displays the base classes in the order processing system and describes the indexes, tablespaces, and subclasses for each class definition.

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

Now take a look at the aggregate class diagram shown in Figure 6.10. Here, we see two aggregate class definitions, their internal pointer structures, and the index and tablespace information for all classes that are composed of pointers to other objects.

Figure 6.10 - The aggregate class diagram for the order processing system

Note that the models show both base classes, as well as the aggregate classes. The problem now is mapping the method prototypes for these classes. Since all objects are represented as tables in the object-relational model, the aggregate object allows the owner table to be coupled with aggregate methods.. The method informs the aggregate object in this way.

Automatic Method Generation

Most object-oriented and object/relational databases automatically create the basic methods for objects (e.g. new) when an object class is defined. These basic methods correspond to the INSERT, DELETE, and UPDATE SQL verbs and are used whenever objects are created. It is important to recognize that methods exist in several forms within a database engine:

  • Standalone--These methods are not associated with a database class.

  • Base class--These methods affect the lowest level objects in a database.

  • Aggregate class--These methods operate on aggregate objects and reference data within component objects.

However, more complex methods can be linked to their target objects. For example, an order_form object might contain a method called check_payment_history, which performs detailed checks into the prior payment history for a customer who is placing an order. 

Let's analyze the methods that might be associated with these objects. If a method of the same name appears in multiple class definitions, the database first references the object and then searches for the method in the class hierarchy.  The following example indicates some sample methods that might be associated with each type of student object.

Methods for student:

Methods for graduate_student; 


Methods for non_resident_students;


Methods for foreign_students;


We see that some methods unique to the subclass appear only within the subclass definition. For example, update_thesis_status would  have no meaning to an undergraduate student.

We have provided a general method for  mapping processes with their database objects. We have emphasized that careful method planning occurs before a database schema is defined. This planning is critical in the design of an object database. The importance of careful method placement cannot be overemphasized. Method planning is crucial in Oracle9i because many "types" of an object may be defined within the class hierarchies, each having identical method names, but with vastly different processing.

To illustrate, a method called compute mileage might exist for both a sailboat class and an automobile class. The internals for the sailboat would use nautical miles while the automobile would use statute miles.  Oracle9i allows a new method to be created that differentiates between the type of object used in either case. The new method will be known only to objects within that class of subclasses. Objects belonging to other classes will never know that the new method exists. This is called "overloading". Overloading is extremely powerful because new code can be introduced into a system with absolute certainty that no unintended side effects will occur.

Now that we understand the performance implications of the Oracle9i object features for our database design, let's look at the design considerations within the base Oracle engine. Bear in mind that the single most important factor in database performance is proper design. Proper design is absolutely crucial because  no amount of tuning will correct a poorly designed database. 

Stored Procedures and Oracle Tables

Objects such as stored procedures and triggers are becoming more popular, moving application code away from external programs and into the database engine. Oracle encouraged this trend in anticipation of the object-oriented features introduced in Oracle version 8. However, the Oracle DBA must be conscious of the increasing memory demands of stored procedures, and plan carefully for the eventual storage of all database access code within the database.

Most Oracle databases today have only a small amount of code in stored procedures--but this is changing rapidly. Many compelling benefits accrue from  placing all Oracle SQL inside stored procedures. These benefits include:

  • Improved Performance - Stored procedures are loaded once into the System Global Area (SGA) and remain there unless they become paged out. Subsequent runtimes of the stored procedures are far faster than external code.

  • Coupling of Data With Behavior - Relational tables can be coupled with the behaviors associated with them by using naming conventions. Oracle 9i gives us the ability to store procedures that are directly associated

with the database table through the use of methods. For example, if all behaviors associated with the EMPLOYEE table are prefixed with the table name (e.g., EMPLOYEE.hire, EMPLOYEE.give_raise), then the data dictionary can be queried to list all behaviors associated with a table (for instance, SELECT * FROM DBA_OBJECTS WHERE OWNER = ‘EMPLOYEE'), and code can be readily identified and reused.

  • Isolation Of Code - All SQL is moved out of the external programs and into stored procedures. The application program becomes nothing more than a call to a stored procedure. This feature makes it a simple matter to interchange one database for another.

Stored procedures and triggers function faster than traditional code primarily because of Oracle's SGA. Once a procedure has been loaded into the SGA, it remains in the library cache until it is paged out of memory. Items are paged out of memory according to a least-recently-used algorithm. The procedure will execute very quickly once it has been loaded into the RAM memory of the shared pool. The trick is to prevent pool-thrashing during the period when many procedures are competing for a limited amount of library cache within the shared pool memory.

Two init.ora parameters emerge as more important than all other parameters combined for tuning Oracle. They are the db_block_buffers and the shared_pool_size parameters. These two parameters define the size of the in-memory region that Oracle consumes on startup and also determine the amount of storage available to cache data blocks, SQL, and stored procedures

Oracle also provides the package construct. A package is essentially a collection stored procedures and functions that can be organized in various ways. Stored procedures and functions for employees can be logically grouped together in an employee package as in the following example:


    FUNCTION compute_raise_amount (percentage NUMBER);
    PROCEDURE hire_employee();
    PROCEDURE fire_employee();
    PROCEDURE list_employee_details();


The code above creates a "package" to encapsulate all employee "behaviors" (Oracle functions and stored procedures) into a single package that will be added into Oracle's data dictionary. Stored procedures place the SQL directly into the database and out of the external application programs. The external programs are reduced to mere procedure calls. 

The shared pool will become very important as systems increasingly place process code within stored procedures. The shared pool consists of the following subpools:

  • Dictionary cache

  • Library cache

  • Shared SQL areas

  • Private SQL areas (these exist during cursor open/cursor close)

  • Persistent area

  • Runtime area

We mentioned that the shared pool utilizes a least-recently-used algorithm to determine which objects are paged out of the shared pool. Fragments, or discontiguous chunks of memory, are created within the shared pool as this paging occurs.

This means that a large procedure that originally fit into memory may no longer fit into contiguous memory when it's reloaded after paging out. A problem can occur when the body of a package has been paged out



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 -  2017

All rights reserved by Burleson

Oracle ® is the registered trademark of Oracle Corporation.

Remote Emergency Support provided by Conversational



Oracle Training at Sea
oracle dba poster

Follow us on Twitter 
Oracle performance tuning software 
Oracle Linux poster