1.2 What Are the Types and Layers of Packages?

Chapter 1
PL/SQL Packages

 

---

Although any PL/SQL package must have the same structure and follow the same rules, there are different types of packages that will play different roles in your application.

Types of Package	Description
Builtin	A builtin package is provided to you by Oracle Corporation, built right into the PL/SQL language as installed. (A builtin package could reside in the database as stored code or could instead be imbedded in a client tool, such as Oracle Developer/2000.)
Prebuilt	Prebuilt packages are libraries of packages that are built by third parties or other developers that are installed in and used by your PL/SQL environment.
Build-Your-Own (BYO)	The very best kind of package: the one you build yourself, or is built by your application development team.

Figure 1.1 shows how these different types of packages interact as layers of PL/SQL code and packages that are available to you. We describe the types further in "Types of Packages" later in this chapter.

Figure 1.1: The layers of PL/SQL code and packages

The lowest layer of code upon which you build your PL/SQL applications is the SQL language. PL/SQL was designed explicitly as a procedural language extension to SQL; most of your PL/SQL programs will function as an interface between a user of some kind and the database.

The next layer of code is the core PL/SQL language. At the center of the PL/SQL universe, we have the STANDARD and DBMS_STANDARD packages, which define the basic elements of the language. When you execute the TO_CHAR function or even use the LIKE operator, you are actually calling elements of the STANDARD package. Since these two packages are the default in PL/SQL, however, you do not have to explicitly reference the package name.

Many PL/SQL developers make use of only these two layers of code, but as you can tell from Figure 1.1, there is much more for you to take advantage of in PL/SQL, in terms of both builtin functionality and code reusability. Oracle Corporation provides a vast suite of builtin packages which extend the PL/SQL language itself and which, from a layering standpoint, sit directly above the core language elements. These packages have either the DBMS_ or UTIL_ prefix on their names. At the same level -- because they are used in precisely the same manner -- are prebuilt packages (and other kinds of prebuilt stored code, such as procedures and functions). PL/Vision is an example of prebuilt code: a library of packages. Once they have been installed and access has been granted to them, all of these packages are available for your use.

The next three layers of code represent the different kinds of reusable code you can and should build into your own environment. The enterprise-wide stored code is very similar to prebuilts: packages and other program units that are shared throughout an entire enterprise (i.e., the corporation). The application-wide stored code is the body of PL/SQL programs that are reused throughout a particular application. The developer's toolbox is the individual developer's set of code that he uses to enhance his own development efforts.

The enterprise-wide, application-wide, and developer toolbox packages are all examples of "build-your-own" packages.

All of those different, reusable layers of code (colored in white) exist to help developers build their custom applications as rapidly as possible. In Figure 1.1, the custom code is represented by the gray areas. Notice that the gray areas are in contact with all layers of the reusable code. This makes sense because your own code will undoubtedly include SQL statements, calls to the builtin functions and PL/SQL operators, and so on, right up through the layers. While it is important to be able to access those lower levels of code, however, you should always leverage programs from the highest level possible. This principle is illustrated by the dashed-line triangle in the figure; I call it the "iceberg" approach to writing PL/SQL code.

1.2.1 The Iceberg Approach to Coding

The tip of the iceberg appears in the custom code section. This portion of the triangle represents our own program, for example, procedure calc, which performs calculations for an order entry system. Only a small portion of the triangle resides in the custom code area. That is because it makes use of all of the other layers of reusable PL/SQL code, keeping the custom code to an absolute minimum. The triangle broadens as it descends through the layers because each higher-level program typically utilizes many elements in the layers below it.

If you take fullest possible advantage of the many layers of reusable code in PL/SQL, your own custom programs will resemble an iceberg floating heavily in the sea. A person looking at your code will wonder at its brevity and clarity. He or she will wonder: how does this little program get the job done? The answer is that the custom program is just the tip of the iceberg. The visible portion is just a hint at the bulk of code below the surface, powerful and solid.