0649-0652

Previous Table of Contents Next

Page 649

CHAPTER 26

Object-Oriented
Programming with
Packages

IN THIS CHAPTER

The Package Specification650
The Package Body652
Package Privileges659
Accessing Oracle Packages from Client Applications661
Object-Oriented Concepts663

Page 650

Although Oracle is an object-relational database (as opposed to an object-oriented database), it provides a very powerful object-oriented feature in its implementation of packages. An Oracle package is a group of procedures, functions, variables , constants, cursors , and type declarations that function as a logical unit. Packages provide many of the characteristics typically associated with object-oriented languages, including encapsulation, information hiding, and function overloading.

Packages can also provide improved performance because when a packaged object is referenced, the entire package is loaded into memory. This reduces or eliminates disk I/O for subsequent calls to objects in the package. As a result, these calls execute more quickly than similar calls to standalone functions and procedures, which must be read from disk as requested .

The two parts to a package are the package specification and the package body. The package specification provides the interface through which applications and other subprograms access packaged objects. The package body contains the actual code for objects in the specification, as well as any declarations and subprograms that are private to the package.

If a package specification has only variables, constants, and type declarations, it need not have a body at all. This independence from the body of the package enables the specification to be compiled separately, even when a body is required. This can improve the development process by enabling developers to define the application interface before writing the underlying code. Objects referencing the package are dependent only on the specification. Therefore, the package body can also be compiled independently from the specification without affecting any external references, provided that there are no changes to the interface.

The following sections demonstrate the creation of package specifications and bodies, highlighting key features. In addition to PL/SQL, an example is provided in C++ to illustrate the use of Oracle packages in object-oriented client applications.

The Package Specification

The package specification must contain all objects that are accessed by external subprograms or applications. It can be viewed as the public declarations section of the package. You can construct packages to perform all operations on an underlying database object or to perform operations on groups of similar objects. Any logical grouping of data and subprograms is an acceptable candidate for a package, as dictated by the application or applications that access the database.

Listing 26.1 shows an example of a package specification that encapsulates methods for maintaining lookup tables in the database.

Listing 26.1. A package specification with functions used to maintain lookup tables.

 CREATE OR REPLACE PACKAGE lookup_admin AS     FUNCTION add_address_type(description VARCHAR2) RETURN NUMBER;     FUNCTION add_phone_type(description VARCHAR2) RETURN NUMBER;     FUNCTION add_contact_type(description VARCHAR2) RETURN NUMBER;

Page 651

FUNCTION add_contact_method(description VARCHAR2) RETURN NUMBER; FUNCTION add_contact_reason(description VARCHAR2) RETURN NUMBER; /* add update and delete functions here */

END lookup_admin;

In addition to functions and procedures, the package specification can contain variables, constants, and user-defined exceptions and datatypes. The code example in Listing 26.2 includes a user -defined datatype based on an underlying table and provides functions to operate on the table.

Listing 26.2. A package specification with a user-defined datatype.

 CREATE OR REPLACE PACKAGE manage_individuals AS     TYPE indiv_rec IS RECORD(          ID             NUMBER(10)         ,last_name      VARCHAR2(30)         ,first_name     VARCHAR2(30)         ,notes          VARCHAR2(255)         ,date_of_birth  DATE         ,last_updt_user VARCHAR2(20)         ,last_updt_date DATE     );     FUNCTION insert_individual(indiv_in INDIV_REC) RETURN NUMBER;     FUNCTION update_individual(indiv_in INDIV_REC) RETURN NUMBER;     FUNCTION delete_individual(indiv_in INDIV_REC) RETURN NUMBER;

END manage_individuals;

Perhaps the most powerful feature of packaged functions and procedures is overloading. Overloading enables a single function or procedure to accept different sets of parameters. To overload a packaged subprogram in Oracle, simply declare it separately for each desired parameter list, as shown in Listing 26.3.

Listing 26.3. A package specification demonstrating function overloading.

 CREATE OR REPLACE PACKAGE manage_individuals AS     FUNCTION insert_individual(last_in VARCHAR2, first_in VARCHAR2)              RETURN NUMBER;     FUNCTION insert_individual(last_in VARCHAR2, first_in VARCHAR2,              notes_in VARCHAR2) RETURN NUMBER;     FUNCTION insert_individual(last_in VARCHAR2, first_in VARCHAR2,              d_o_b DATE, notes_in VARCHAR2) RETURN NUMBER;     FUNCTION insert_individual(last_in VARCHAR2, first_in VARCHAR2,              d_o_b DATE) RETURN NUMBER;     /* add update and delete functions here */

END manage_individuals;

Page 652

CAUTION

Be careful to avoid ambiguous parameter lists. For example, if the d_o_b parameter in the fourth function declaration of Listing 26.3 is defined as type VARCHAR2, it becomes indistinguishable from the second function declaration. In the context of Listing 26.3, that results in values being inserted into the wrong columns .

You should recompile the package specification as infrequently as possible. Other packaged and standalone subprograms that reference objects in the package specification are invalidated when it is recompiled. As a result, objects referencing the package specification must also be recompiled every time the specification is recompiled.

The Package Body

The package body contains the code for all subprograms defined in the specification, as well as any private variables, constants, cursors, datatypes, or subprograms. Objects declared within the package body are accessible only by other objects within the body. This enables you to use the package body to hide information and encapsulate subprograms within the package. However, objects within the package body can reference objects in other package specifications, as well as standalone objects.

A package body cannot exist without a package declaration. If the body does not contain all subprograms and cursors declared in the specification, or if declarations in the body are in conflict with declarations in the specification, compilation errors result. However, you can compile the body separately from the specification, which is extremely useful when you debug packaged subprograms.

Packaged subprograms that contain explicit commits and rollbacks cannot be accessed by triggers or other subprograms that apply transactions. You should keep this in mind when you design packages, along with the effects of any implicit commits and rollbacks that might occur. Transactions applied within a packaged subprogram are rolled back implicitly when an unhandled exception occurs. An implicit commit occurs for all uncommitted transactions when the current session is terminated . In general, packaged subprograms involving transactions should not participate in transactions with other subprograms and should not be referenced by triggers. It is usually preferable to explicitly commit or roll back transactions that occur within packaged subprograms.

Package Variables and Initialization

The first time a packaged object is referenced, the entire package is loaded into memory. It is important to note that each session gets its own instance of package variables. Packaged data cannot be shared across sessions, and all values stored for a particular session are lost when the session ends.