0661-0663

Previous Table of Contents Next

Page 661

 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 26.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 are granted, however, the user cannot 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 cannot access address_type_info. get_next_address_type directly or even use SELECT * FROM address_type. This is an example of how you can use packages 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. You should take this advantage into consideration when you design packages. For example, if a particular role should have read-only access to the lookup tables referenced in Listing 26.9, you should create a separate package that does not include the insert functions.

Accessing Oracle Packages from Client Applications

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 26.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 26.6. The code example in Listing 26.10 is not intended 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.

Page 662

Listing 26.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 to call the proper function in Oracle.

Perhaps a better implementation of the Individual class includes an overloaded constructor to perform the insertion so that the data members are 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 26.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. To access packaged objects, you must create standalone functions and subprograms to call the packaged objects within Oracle. Listing 26.10 is an example of a stub that you can use to access packaged functions. Because overloading is not allowed in standalone functions and procedures, you must create separate subprograms to access each form of the overloaded packaged subprogram.

When you develop 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 datatypes 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

Page 663

packages when the database is 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 26.11.

Listing 26.11. A standalone function 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;

You can use products that communicate with SQL*Net and the Oracle Call Interface directly 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.

Object-Oriented Concepts

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 new and delete. A call to new allocates memory for the new instance of the object and loads it, whereas a call to delete 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.

Previous Table of Contents Next