|
Although Oracle is a 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 stand-alone functions and procedures, which must be read from disk as requested.
There are two parts to a package: 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 must contain all objects that will be 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 will be accessing the database.
Listing 18.1 shows an example of a package specification that encapsulates methods for maintaining lookup tables in the database.
Listing 18.1. This package specification contains 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;
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 data types. The code example in Listing 18.2 includes a user-defined data type based on an underlying table and provides
functions to operate on the table.
Listing 18.2. This package specification contains a user-defined data type.
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 18.3.
Listing 18.3. This package specification demonstrates 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;
 Be careful to avoid ambiguous parameter lists. For example, if the d_o_b parameter in the fourth function declaration of Listing 18.3 was defined as type VARCHAR2, it would become indistinguishable from
the second function declaration. In the context of Listing 18.3, that would result in values being inserted into the wrong columns.
You should recompile the package specification as infrequently as possible. Other packaged and stand-alone subprograms that reference objects in the package specification will be 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 contains the code for all subprograms defined in the specification, as well as any private variables, constants, cursors, data types, 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 stand-alone
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 are debugging 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 are designing 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.
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.
Variables declared within the package body, but outside of subprograms, hold their values for the life of the session. As with stand-alone functions and procedures, variables declared within packaged subprograms persist only within the scope of the
subprograms in which they are declared.
Variables and cursors declared at the package level can be accessed by all subprograms within the package body. Any code in the body of the package itself is executed only once, when the package is first loaded. For this reason, package code is
typically used only to initialize package variables. Listing 18.4, which is a portion of the package body for the specification in Listing 18.1, uses only one statement in the package body.
Listing 18.4. This package body provides functions to insert records into lookup tables.
CREATE OR REPLACE PACKAGE BODY lookup_admin AS
user_id VARCHAR2(20);
FUNCTION add_address_type(description VARCHAR2) RETURN NUMBER
IS
BEGIN
INSERT INTO address_type VALUES(address_type_ids.nextval,
description, user_id, sysdate);
COMMIT;
RETURN(0);
EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
RETURN(1);
END add_address_type;
/* all functions in the specification must be defined in the body */
BEGIN
SELECT user INTO user_id FROM dual;
END lookup_admin;
Packaged subprograms and data are accessed using owner.package_name.object_name notation. You can create public synonyms for packages, as with other objects, to eliminate the need for the owner prefix.
Note that the SELECT statement in the package body is executed only once, which is a somewhat of an optimization when multiple transactions are applied using the functions in the package. For example, the SELECT statement stores the user_id upon package
instantiation (first function call). All subsequent calls do not execute the SELECT statement.
To this point, the code listings in this chapter have included functions in preference to procedures. Each of these functions returns a value that indicates the success or failure of the operation it performs. The same result can be achieved by using an
output parameter in a procedure, as illustrated by the package specification in Listing 18.5, which simply redefines the functions declared in Listing 18.3 as procedures.
Listing 18.5. This package specification demonstrates the use of an output parameter in an overloaded procedure.
CREATE OR REPLACE PACKAGE manage_individuals AS
PROCEDURE insert_individual(ret_code OUT NUMBER,
last_in IN VARCHAR2, first_in IN VARCHAR2);
PROCEDURE insert_individual(ret_code OUT NUMBER,
last_in IN VARCHAR2, first_in IN VARCHAR2,
notes_in IN VARCHAR2);
PROCEDURE insert_individual(ret_code OUT NUMBER,
last_in IN VARCHAR2, first_in IN VARCHAR2,
d_o_b IN DATE, notes_in IN VARCHAR2);
PROCEDURE insert_individual(ret_code OUT NUMBER,
last_in IN VARCHAR2, first_in IN VARCHAR2,
d_o_b IN DATE);
/* add update and delete functions here */
END manage_individuals;
The use of functions instead of procedures is merely a design consideration based on the assumption that it is better to clearly distinguish return codes from actual data.
The capability to overload a subprogram is one of the primary advantages of packages. This feature is not available to stand-alone procedures and functions. Overloading is particularly useful when you are inserting records into tables with
optional fields, or when you are updating existing records. When overloading is implemented correctly, you can minimize the data passed between the application and the database and reduce the possibility of error. Listing 18.6 shows an
example of function overloading in the package body, based on the package specification in Listing 18.3.
Listing 18.6. This package demonstrates function overloading.
CREATE OR REPLACE PACKAGE BODY manage_individuals AS
user_id VARCHAR2(20);
FUNCTION insert_individual(last_in VARCHAR2, first_in VARCHAR2)
RETURN NUMBER
IS
new_id NUMBER;
BEGIN
SELECT individual_ids.nextval INTO new_id FROM dual;
INSERT INTO individual (id, last_name, first_name,
last_updt_user, last_updt_date)
VALUES (new_id, last_in, first_in, user_id, sysdate);
COMMIT;
RETURN(new_id);
EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
RETURN(1);
END insert_individual;
FUNCTION insert_individual(last_in VARCHAR2, first_in VARCHAR2,
notes_in VARCHAR2) RETURN NUMBER
IS
new_id NUMBER;
BEGIN
SELECT individual_ids.nextval INTO new_id FROM dual;
INSERT INTO individual (id, last_name, first_name, notes,
last_updt_user, last_updt_date)
VALUES (new_id, last_in, first_in, notes_in, user_id,
sysdate);
COMMIT;
RETURN(new_id);
EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
RETURN(1);
END insert_individual;
FUNCTION insert_individual(last_in VARCHAR2, first_in VARCHAR2,
d_o_b DATE, notes_in VARCHAR2) RETURN NUMBER
IS
new_id NUMBER;
BEGIN
SELECT individual_ids.nextval INTO new_id FROM dual;
INSERT INTO individual (id, last_name, first_name,
date_of_birth, notes, last_updt_user, last_updt_date)
VALUES (new_id, last_in, first_in, d_o_b, notes_in,
user_id, sysdate);
COMMIT;
RETURN(new_id);
EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
RETURN(1);
END insert_individual;
FUNCTION insert_individual(last_in VARCHAR2, first_in VARCHAR2,
d_o_b DATE) RETURN NUMBER
IS
new_id NUMBER;
BEGIN
SELECT individual_ids.nextval INTO new_id FROM dual;
INSERT INTO individual (id, last_name, first_name,
date_of_birth, last_updt_user, last_updt_date)
VALUES (new_id, last_in, first_in, d_o_b, user_id,
sysdate);
COMMIT;
RETURN(new_id);
EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
RETURN(1);
END insert_individual;
BEGIN
SELECT user INTO user_id FROM dual;
END manage_individuals;
Consider how you might accomplish this insert by using a user-defined record type or a single function that accepts all values. Using either alternative, applications calling the packaged insert function would have to ensure
that null values are supplied for the fields for which no data exists. It is a much better programming practice to encapsulate all default values within the packaged routines rather than in various calling routines.
The potential for problems is magnified for update operations. In update operations, the function would need logic to determine which fields are actually being updated or would have to update all columns in the table. In the latter case, the application
would then be responsible for supplying all values accurately to avoid accidental column updates. Function overloading simplifies application development by enabling applications to supply only the values required for each transaction. Passing only the
values needed to perform the update can improve performance through minimizing disk writes of unnecessary data.
Oracle stored procedures and functions currently do not support the retrieval of result sets. However, you can overcome this limitation by using a packaged subprogram. Remember that cursors declared at the package level persist for the duration of the
session. This enables a set of functions to open a cursor and perform operations on it, maintaining the current position within the cursor from one call to the next. Output parameters can be used to pass data from packaged functions to the calling
application. Listing 18.7 shows an example of how these features can be used to return result sets to an application from a packaged subprogram.
Listing 18.7. This code example uses a packaged cursor and functions to retrieve a result set.
CREATE OR REPLACE PACKAGE address_type_info AS
FUNCTION get_next_address_type(id_out OUT NUMBER,
description_out OUT VARCHAR2) RETURN NUMBER;
FUNCTION close_address_type RETURN NUMBER;
FUNCTION reopen_address_type RETURN NUMBER;
END address_type_info;
CREATE OR REPLACE PACKAGE BODY address_type_info AS
last_id NUMBER(10);
CURSOR c1 IS SELECT id, description FROM address_type;
FUNCTION get_next_address_type(id_out OUT NUMBER,
description_out OUT VARCHAR2) RETURN NUMBER
IS
end_of_cursor EXCEPTION;
temp_id NUMBER(10);
temp_desc VARCHAR2(40);
BEGIN
FETCH c1 INTO temp_id, temp_desc;
IF (temp_id = last_id) THEN
RAISE end_of_cursor;
ELSE
last_id := temp_id;
id_out := temp_id;
description_out := temp_desc;
END IF;
RETURN(0);
EXCEPTION
WHEN end_of_cursor THEN
RETURN(1);
WHEN OTHERS THEN
RETURN(1);
END get_next_address_type;
FUNCTION close_address_type RETURN NUMBER
IS
BEGIN
CLOSE c1;
RETURN(0);
EXCEPTION
WHEN OTHERS THEN
RETURN(1);
END close_address_type;
FUNCTION reopen_address_type RETURN NUMBER
IS
BEGIN
OPEN c1;
RETURN(0);
EXCEPTION
WHEN OTHERS THEN
RETURN(1);
END reopen_address_type;
BEGIN
OPEN c1;
END address_type_info;
Note that the cursor is opened in the body of the package itself. To retrieve the first row, an application need only call address_type_info.get_next_address_type to retrieve the first row. When this function returns 1, it informs the calling
application that the end of the cursor has been reached. The application should then call address_type_info.close_address_type. The OPEN c1 statement in the body of the cursor will be executed only once, when the package is first loaded. In order to access
the cursor a second time, the application must first call address_type_info.reopen_address_type. Subsequent calls to address_type_info.get_next_address_type can then be used to retrieve rows.
Although this approach might be somewhat cumbersome, it might be acceptable for retrieving small result sets. This method could also be useful in producing reports that require breaks and subtotals. You could employ additional package-level variables to
determine breakpoints and hold summary information as each row is returned to the application. This is just one example of how packages can be used to overcome many of the limitations of PL/SQL.
Oracle provides many predefined exceptions, and a number of functions and procedures that can be used to handle them. Oracle implicitly raises predefined exceptions when they occur in PL/SQL blocks. Among these, the OTHERS exception is extremely
valuable because it can be used as a catch-all (all other exceptions that are not explicitly handled), which in many cases is all that is needed. Even when specific handlers are used, using the OTHERS exception is a good idea. Using this exception prevents
an application from bombing because of an unhandled error in a subprogram.
In some cases, defining an exception that does not exist in Oracle might be useful. User-defined exceptions are declared in much the same way as variables. For example, in Listing 18.7, the user-defined exception end_of_cursor is declared in the
get_next_address_type function. Control is passed to the exception handler using the RAISE statement. User-defined exceptions are particularly useful in performing sanity checks within PL/SQL blocks. You can use a package variable to associate user-defined
text with an exception, which can be accessed by the application through an additional packaged subprogram. Listing 18.8 demonstrates how packaged constructs can be used to give to an application additional information concerning a user-defined error.
Listing 18.8. A demonstration of user-defined exception handling.
CREATE OR REPLACE PACKAGE manage_individuals AS
FUNCTION insert_individual(last_in VARCHAR2, first_in VARCHAR2,
d_o_b DATE, notes_in VARCHAR2) RETURN NUMBER;
FUNCTION get_error_text(text_out OUT VARCHAR2) RETURN NUMBER;
END manage_individuals;
CREATE OR REPLACE PACKAGE BODY manage_individuals AS
user_id VARCHAR2(20);
invalid_b_day EXCEPTION;
error_text VARCHAR2(255);
FUNCTION insert_individual(last_in VARCHAR2, first_in VARCHAR2,
d_o_b DATE, notes_in VARCHAR2) RETURN NUMBER
IS
new_id NUMBER;
temp_bd VARCHAR2(20);
temp_today VARCHAR2(20);
BEGIN
temp_bd:=TO_CHAR(d_o_b, 'MMDDYYYY',
'nls_date_language = American');
SELECT TO_CHAR(sysdate, 'MMDDYYYY',
'nls_date_language = American')
INTO temp_today FROM dual;
IF ((to_date(temp_bd, 'MMDDYYYY',
'nls_date_language = American') >
to_date(temp_today, 'MMDDYYYY',
'nls_date_language = American')) OR
((SUBSTR(temp_today, 7, 4) SUBSTR(temp_bd, 7, 4))
> 100)) THEN
RAISE invalid_b_day;
ELSE
SELECT individual_ids.nextval INTO new_id FROM dual;
INSERT INTO individual (id, last_name, first_name,
date_of_birth, notes, last_updt_user,
last_updt_date) VALUES (new_id, last_in,
first_in, d_o_b, notes_in, user_id,
sysdate);
error_text:= ' ';
RETURN(new_id);
END IF;
EXCEPTION
WHEN invalid_b_day THEN
error_text:= 'Date of birth outside normal range.';
RETURN(11);
WHEN OTHERS THEN
error_text:=SUBSTR(SQLERRM, 1, 255);
RETURN(1);
END insert_individual;
FUNCTION get_error_text(text_out OUT VARCHAR2) RETURN NUMBER
IS
BEGIN
text_out:=error_text;
RETURN(0);
EXCEPTION
WHEN OTHERS THEN
text_out:='Unable to retrieve error information.';
RETURN(1);
END get_error_text;
BEGIN
SELECT user INTO user_id FROM dual;
END manage_individuals;
The example in Listing 18.8 uses a package-level variable to store error text and provides a function to retrieve error text. Note the use of the predefined function SQLERRM in the OTHERS handler. In this context, SQLERRM is used to copy the Oracle
error message into the package variable.
The example in Listing 18.8 is just one way to deal with exceptions in packages. Oracle includes many other predefined functions used to handle exceptions, including SQLCODE, EXCEPTION_INIT, and RAISE_APPLICATION_ERROR. SQLCODE returns the Oracle error
number associated with an exception; EXCEPTION_INIT enables the developer to associate a name with an Oracle error number; and RAISE_APPLICATION_ERROR raises a user-defined exception, accepting an error number and error text as parameters. The way in which
exceptions are handled depends entirely on the nature of the application. What is most important is that all exceptions are handled. As a general rule, the OTHERS handler should always be used to trap all exceptions that do not have specific handlers.
Using packages can greatly simplify the process of granting rights to users and roles. When you grant a user the EXECUTE privilege for a package, the user can access any data and subprograms in the package specification. In the package body,
subprograms can access other packaged or stand-alone subprograms and other database objects. The user to which EXECUTE was granted does not need to have any rights to the external objects referenced in the package body. This is another way in which
packages can be used for information hiding. In Listing 18.9, the lookup_admin package from Listing 18.4 is redefined to hide the implementation of address_type_info from Listing 18.7.
Listing 18.9. A demonstration of indirect function calling.
CREATE OR REPLACE PACKAGE lookup_admin AS
FUNCTION get_next_address_type(id_out OUT NUMBER,
description_out OUT VARCHAR2) RETURN NUMBER;
FUNCTION close_address_type RETURN NUMBER;
FUNCTION reopen_address_type RETURN NUMBER;
/* add get_next, close, and reopen functions */
/* for other lookups here */
FUNCTION add_address_type(description VARCHAR2) RETURN NUMBER;
FUNCTION add_phone_type(description VARCHAR2) RETURN NUMBER;
FUNCTION add_contact_type(description VARCHAR2) RETURN NUMBER;
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;
/
CREATE OR REPLACE PACKAGE BODY lookup_admin AS
user_id VARCHAR2(40);
temp_id NUMBER(10);
temp_desc VARCHAR2(40);
FUNCTION get_next_address_type(id_out OUT NUMBER,
description_out OUT VARCHAR2) RETURN NUMBER
IS
ret NUMBER(10);
BEGIN
ret:=address_type_info.get_next_address_type(id_out, description_out);
RETURN(ret);
EXCEPTION
WHEN OTHERS THEN
RETURN(1);
END get_next_address_type;
FUNCTION close_address_type RETURN NUMBER
IS
ret NUMBER(10);
BEGIN
ret:=address_type_info.close_address_type;
RETURN(ret);
EXCEPTION
WHEN OTHERS THEN
RETURN(1);
END close_address_type;
FUNCTION reopen_address_type RETURN NUMBER
IS
ret NUMBER(10);
BEGIN
ret:=address_type_info.reopen_address_type;
RETURN(ret);
EXCEPTION
WHEN OTHERS THEN
RETURN(1);
END reopen_address_type;
BEGIN
SELECT USER INTO user_id FROM dual;
END lookup_admin;
When a user is granted the EXECUTE privilege on lookup_admin, as defined in Listing 18.9, the user gains indirect access to address_type_info, as well as the sequences and lookup tables referenced in the insert functions. Unless other privileges have
been granted, however, the user will not be able to access the objects directly. For example, the user can read a row from the address_type table using lookup_admin.get_next_address_type, but will not be able to access
address_type_info.get_next_address_type directly, or even use SELECT * FROM address_type. This is an example of how packages can be used to abstract the details of implementation from users and application interfaces.
Granting privileges at the package level has the additional advantage of simplifying the entire process of granting rights to users and roles. This should be taken into consideration when you design packages. For example, if a particular role should
have read-only access to the lookup tables referenced in Listing 18.9, you should create a separate package that does not include the insert functions.
Oracle's implementation of the package fits the object model used in C++ particularly well, and using packages exclusively can simplify the process of designing the client application. The development of the client application, in many cases, can begin
with the duplication of the structures and subprograms defined in the database packages.
Listing 18.9 provides an example of a C++ class that is based on database objects, including packaged constructs. The data members of the class correspond to the columns in a table, and the Insert() member function of the Individual class is mapped to
the overloaded insert functions in the manage_individuals package from Listing 18.6. The code example in Listing 18.10 is intended not to illustrate good C++ programming technique, but rather to demonstrate how overloaded C++ member functions can be mapped
directly to overloaded functions in Oracle packages.
Listing 18.10. A C++ class illustrating overloading using a host language.
class Individual
{
public:
long ID;
char LastName[30];
char FirstName[30];
char DateOfBirth[10]; /* DDMONYY */
char Notes[255];
char LastUpdateUser[20];
char LastUpdateTS[20]; /* DDMMYYYY HH:MI:SS */
int Insert(OSession SessionHandle, char* Last, char* First);
int Insert(OSession SessionHandle, char* Last, char* First,
char* Notes_Or_DOB);
int Insert(OSession SessionHandle, char* Last, char* First,
char* DateOfBirth, char* Notes);
};
The data members of the Individual class are identical to the columns of the Individual table in Oracle, with one exception. The date of birth is stored as a string, requiring the overloaded form Individual::Insert(char*, char*, char*) to be able to
distinguish a date from ordinary text in order to call the proper function in Oracle.
Perhaps a better implementation of the Individual class would include an overloaded constructor to perform the insertion so that the data members could be protected. Declaring the data members as public is analogous to declaring variables in an Oracle
package specification.
Despite the shortcomings of the example in Listing 18.10, it demonstrates the point that if Oracle packages are designed properly they can be replicated in the client application. This can simplify the design of the client application, as well as ensure
consistency in the object models being used throughout the system.
Many Windows development tools use ODBC to communicate with the database. Unfortunately, the current Oracle ODBC driver does not support the access of packaged objects through ODBC. In order to access packaged objects, you must create stand-alone
functions and subprograms to call the packaged objects from within Oracle. Listing 18.10 is an example of a stub that can be used to access packaged functions. Because overloading is not allowed in stand-alone functions and procedures, you must create
separate subprograms to access each form of the overloaded packaged subprogram.
When you are developing ODBC applications, you should carefully consider this limitation in the design process. The necessity of external stubs might nullify many of the advantages to using packages. User-defined data types and exceptions, variables,
and cursors cannot be accessed from package specifications, and overloading is nullified by the requirement of separate external stubs corresponding to each form of the overloaded function. In addition, you must grant rights to each external stub that
accesses the package. In some cases, there is no advantage to using packages when the database is being accessed through ODBC. The exception is when the application needs user-defined types or persistent variables. These can be packaged and accessed
indirectly through the external stubs such as the example in Listing 18.11.
Listing 18.11. This stand-alone function is needed to access a packaged function through ODBC.
CREATE OR REPLACE FUNCTION ins_indiv_stub1
(last_in IN VARCHAR2
,first_in IN VARCHAR2)
RETURN NUMBER
IS
ret NUMBER;
BEGIN
ret:=manage_individuals.insert_individual(last_in, first_in, SYSDATE, 'new individual');
RETURN(ret);
END ins_indiv_stub1;
Products that communicate with SQL*Net and the Oracle Call Interface directly can be used to overcome this ODBC-specific limitation. Using packages in an ODBC application is also inconsistent with one of the primary goals of ODBC, which is to provide
database independence.
As mentioned previously, Oracle packages provide several features that are typically associated with object-oriented programming. Among these are encapsulation, information hiding, and function overloading. In this section you will learn additional
object-oriented features that apply not to the database itself, but to several of Oracle's newest development tools. C++, in particular, is used to illustrate these concepts.
Encapsulation is simply the grouping of related data, procedures, and functions to form a collection. An object, or a package, is simply a name for this encapsulated data and methods for operating on it. In C++, an object is implemented as
a class or an instance of a class. The class itself defines the object's data and methods, whereas an instance contains the data specific to one particular object belonging to the class. In terms of Oracle packages, the package specification and
package body make up the class, whereas each session gets a specific instance of the class.
In C++ terminology, objects are created and destroyed using constructors and destructors. A constructor allocates memory for the new instance of the object and loads it, whereas a destructor unloads the object and frees memory allocated to it. You have
the option of placing code in the constructor and destructor of an object. In Oracle, an instance of a package is constructed when it is first referenced in a session and destructed when the session ends. Code in the body of the package itself is fired
when an instance is constructed. No code can be specified for the destructor of a package.
Listing 18.12 provides a simple example of an object in C++, with a single constructor and a single destructor. Note that in C++ the constructor has the same name as the class and returns a pointer to an instance of an object belonging to the class.
Although the arguments to the constructor can be redefined, the return type cannot be. If no constructor is specified, the compiler creates a default constructor that simply allocates memory for the new instance and loads it. Similar rules apply to the
destructor, which always has the name of the class preceded by a tilde and returns void, (nothing). In Listing 18.12, the destructor is named ~Car().
Listing 18.12. A simple class, with a constructor and destructor as the only member functions.
class Car {
public:
char *Make;
char *Model;
unsigned Year;
Car(char* CarMake, char* CarModel, unsigned CarYear);
~Car();
};
Car::Car(char* CarMake, char* CarModel, unsigned CarYear)
{
Make = strdup(CarMake);
Model = strdup(CarModel);
Year = CarYear;
}
Car::~Car()
{
free(Make);
free(Model);
}
 The free statements in the destructor are very important. When the class is instantiated, additional memory is allocated for these data members. The default destructor will only free memory allocated for the
object itself, which includes only the pointers.
To create an instance of car, declare a pointer to Car, which will receive the return value of the constructor:
Car *MyCar;
MyCar = new Car(ÓFordÓ, ÓMustangÓ, 1967);
// To destroy the object, use the delete operator:
delete MyCar;
This simple example illustrates the encapsulation of data and methods in an object and the instantiation and destruction of an instance of the object. These concepts are the very foundation of object-oriented programming techniques.
Information hiding is a form of encapsulation in which data elements or methods can be accessed only by the methods of the object. This point was illustrated in the context of Oracle packages in several ways. In Listing 18.6, the user who last
updated a record and the timestamp indicating when the record was last updated were inserted by a function, without any intervention by the user or the calling application. Tables, functions, procedures, and other database objects can also be hidden by
Oracle packages as illustrated in Listing 18.9. In general, variables and constructs declared in the package specification are visible, or public. Variables and constructs declared within the package only are hidden, or private.
In C++, variables and functions can be declared as public, private, or protected in the class definition. Public constructs can be accessed anywhere in a program, whereas private and protected data and methods can be accessed only through member
functions and member functions of friend classes. These subjects are discussed in greater detail in the explanation of Listing 18.14. At this point, it is only important to recognize that this is the how C++ hides information. For example, if the Car class
from Listing 18.12 were redefined as in Listing 18.13, the Mileage data member could only be accessed by the constructor and the member functions GetMileage and IncrementMileage.
Listing 18.13. A redefinition of the car class, illustrating the use of the protected keyword.
class Car {
public:
char *Make;
char *Model;
unsigned Year;
Car(char* CarMake, char* CarModel, unsigned CarYear
,unsigned long Mileage);
~Car();
unsigned long GetMileage();
void IncrementMileage(unsigned Miles);
protected:
unsigned long Mileage;
};
If this were the extent of the implementation of the Car class, Mileage could only be increased after the instance is constructed. If Mileage were declared as public, however, it could be modified at any time through an assignment, such as
MyCar->Mileage = 10;
Protected data and functions can also be used to abstract implementation details, such as database transactions. The SQL used to insert a car could be declared protected, parameterized, and initialized when an instance is constructed. The application
could then add a car to the database by accessing a public member function without knowing the SQL syntax, or that it even exists.
An extremely important feature of the object-oriented model is the concept of inheritance. Inheritance defines a class hierarchy in which a descendent class receives the member functions and data elements of the parent class to which it belongs.
For example, you can create a base class without any intention of constructing the object. Base classes are often created only to be inherited from. Listing 18.14 illustrates this point in the context of the simple example of the Car class.
Listing 18.14. This implementation of the Car class illustrates the concept of inheritance.
class Vehicle {
public:
char *Make;
char *Model;
unsigned Year;
};
class Car : public Vehicle {
public:
Car(char* CarMake, char* CarModel, unsigned CarYear
,unsigned long Mileage);
~Car();
unsigned long GetMileage();
void IncrementMileage(unsigned Miles);
protected:
unsigned long Mileage;
};
Note that the base class Vehicle enables the compiler to generate the default constructor and destructor, and that it now contains the public data members. They still exist in the Car class because they are inherited from Vehicle. The keywords private
and protected are important to the behavior of inheritance. Although protected member functions can be accessed by derived classes, private members cannot be. A friend class can access both private and protected members. Friend classes do not inherit from
the base class, but have full access rights to member functions of the base class.
The virtual keyword is also important to inheritance. Class functions declared as virtual can be redefined by descendants, without changing the call interface (overloading the function). If the function is redefined in the descendant with different
arguments or return types, the virtual keyword is ignored, and the function is, in effect, overloaded.
Base classes commonly contain virtual functions, with the intention of enabling descendants to override them rather than overload them. Although descendants can override base class functions without the virtual keyword, pure virtual functions can be
used to force descendants to redefine functions. A pure virtual function has a void return type and no arguments. Listing 18.15 is an example of a base class with pure virtual functions and two derived classes that override the virtual functions, or
redeclare them.
Listing 18.15. These class definitions illustrate the use of virtual functions.
class Transaction {
public:
virtual void Insert();
virtual void Update();
virtual void Delete();
protected:
int GetConnection();
};
class AddressHistoryTrans : public Transaction
{
public:
int Insert(Address* last);
void Update(); /* Overrides base class */
void Delete(); /* Overrides base class */
};
class AddressTrans : public Transaction
{
public:
int Insert(Address* last);
/* int Update(); ILLEGAL--only return type is different */
int Update(Address* last);
int Delete(unsigned long OldID);
};
In Listing 18.15, the protected member function GetConnection is inherited normally by the derived classes. However, the functions declared as virtual must be redefined in the derived classes. In both derived classes, the Insert function is treated as
overloaded because it differs in arguments and return type from the base classes Insert function. However, the redeclaration of Update and Delete in AddressHistoryTrans completely hides the base class implementations of these functions because they match
exactly in terms of arguments and return type. Note that the arguments must change in the derived class to overload a base class function. It is illegal to change only the return type of a pure virtual function, except when the base class function returns
a pointer to the base class and the derived class function returns a pointer to the derived class. In this case, the compiler will cause the derived classes function to override the base classes function.
Class objects can also inherit from multiple bases. For example, the previously defined classes can be used as bases to derive a Transaction class for the Car class, as illustrated in Listing 18.16.
Listing 18.16. An illustration of multiple inheritance and overriding virtual base functions.
class CarTransaction : public Car, public Transaction {
public:
virtual void Insert();
virtual void Update();
virtual void Delete();
protected:
void Commit();
void Rollback();
};
The class CarTransaction provides only two new member functions: Commit and Rollback. It inherits all of the data and methods of the Car class, overrides Delete, inherits GetConnection, and overloads the Insert and Update member functions of the
Transaction class. The technique of inheriting from multiple bases and overriding base class implementations in derived classes is often referred to as polymorphism, which combines many of the features of object-oriented programming.
Inheritance is an extremely powerful, yet dangerous, feature of object-oriented programming. Changes made to a base class are proliferated to all descendants that inherit from the base. This is very powerful when you are fixing a bug in a base class or
adding new data or methods that should apply to all descendants. However, you should always be very careful when you make changes to base classes. If a bug is introduced in a base class member function, it will affect every descendant that relies on the
member function. Also, removing data and member functions from base classes is very difficult. When a data member is removed from a base class, it invalidates references to that data member in every member function of the base class and every member
function of descendant classes where it is referenced.
Always remember that when you inherit from a class to derive a new one, all data members are inherited whether they are used or not. When you define base classes think small, and use inheritance carefully. As critics of C++ often point out, poor design
of class hierarchies and overuse of inheritance results in bloated code.
Although C++ was used in this chapter to illustrate the concepts presented in this section, an increasing number of development tools are embracing the object-oriented model. The implementation of object-oriented features varies greatly among these
tools, but the basic concepts are essentially the same. Visual Basic, PowerBuilder, SQLWindows, and more recently, Delphi, have all implemented the object-oriented paradigm with varying degrees of success. Regardless of which development tool you use, you
need to understand the basic concepts of the object-oriented model.
Although Oracle is a relational database, the Oracle package provides the object-oriented capabilities of encapsulation, information hiding, and function overloading. Inheritance is not supported in version 7.1, but because Oracle seems to be moving in
an object-oriented direction, it cannot be ruled out as a possibility in version 8. The newest of the Oracle development tools, including Power Objects and Oracle Objects for OLE, provide true object-oriented capabilities for developing Oracle client
applications. However, the capabilities of the object model will not be fully exploited until there are more object-oriented features in the database itself. This will simplify the process of designing database applications and provide consistency by
enabling a single object model to exist in both sides of the application.
| 
