1.5.1 Builtin Packages

Table 1.1 shows a partial list of builtin packages provided by Oracle Corporation. Unless otherwise noted, these packages are available in PL/SQL Release 2.1 and beyond. Most of these packages are installed by default when you create a database instance. In some cases, you may have to grant execute privileges on specific packages (such as DBMS_LOCK and DBMS_SQL) in order to make them available to your user community.

Table 1.1: Some of the Builtin Packages Stored in the Oracle Database

Package Name	Description
DBMS_ALERT	Provides support for notification of database events on an asynchronous basis. Registers a process with an alert and then waits for a signal from that alert.
DBMS_DDL	Provides a programmatic access to some of the SQL DDL statements.
DBMS_JOB	Used to submit and manage regularly scheduled jobs for execution inside the database.
DBMS_LOCK	Allows users to create their own locks using the Oracle Lock Management (OLM) services in the database.
DBMS_MAIL	Offers an interface to Oracle Office (previously known as Oracle Mail).
DBMS_OUTPUT	Displays output from PL/SQL programs to the terminal. The "lowest common denominator" debugger mechanism for PL/SQL code.
DBMS_PIPE	Allows communication between different Oracle sessions through a pipe in the RDBMs shared memory. One of the few ways to share memory-resident data between Oracle sessions.
DBMS_SESSION	Provides a programmatic interface to several SQL ALTER SESSION commands and other session-level commands.
DBMS_SNAPSHOT	A programmatic interface through which you can manage snapshots and purge snapshot logs. You might use modules in this package to build scripts to automate maintenance of snapshots.
DBMS_SQL	Full support for dynamic SQL within PL/SQL . Dynamic SQL means SQL statements that are not prewritten into your programs. They are, instead, constructed at runtime as character strings and then passed to the SQL Engine for execution. ( PL/SQL Release 2.1 only)
DBMS_TRANSACTION	A programmatic interface to a number of the SQL transaction statements, such as the SET TRANSACTION command.
DBMS_UTILITY	The "miscellaneous" package. Contains various useful utilities, such as GET_TIME, which calculates elapsed time to the hundredth of a second, and FORMAT_CALL_STACK, which returns the current execution stack in the PL/SQL runtime engine.
UTL_FILE	Allows PL/SQL programs to read from and write to operating system files. ( PL/SQL Release 2.3 only)

All of the packages in Table 1.1 are stored in the database and can be executed by both client and server-based PL/SQL programs. In addition to these packages, many of the development tools, like Oracle Forms, offer their own specific package extensions as well, such as packages to manage OLE2 objects and DDE communication.

It is no longer sufficient for a developer to become familiar simply with the basic PL/SQL functions like TO_CHAR and ROUND. Those functions have now become simply the inner layer of useful functionality. Oracle Corporation has built upon them, and you should do the same. (To take full advantage of the Oracle technology as it blasts its way to the 21st century, you must be aware of these packages and how they can help you.)

Builtin packages can and should revolutionize the code you write. With the last few releases of the Oracle Corporation's CDE tools, Oracle Server, and PL/SQL itself, the software vendor has shifted course. As Oracle developed the code it needed to implement new features, it no longer hid that code from the rest of the world. Instead, Oracle has exposed that code -- invariably structured as one or more packages -- so that all developers can also take advantage of those same techniques it employs. The next section gives you an example of this process.

1.5.2 Prebuilt Packages

Prebuilt packages are the newest type of package in the PL/SQL development arena, and in many ways offer the most promise to PL/SQL developers. "Prebuilt" (my own terminology) refers to a package that is designed, built, and tested by a third party and then made available to you, either as free shareware or as a licensed product.

Prebuilt packages will most likely come in two forms: miscellaneous utilities and libraries. A utility package might be a single package that supplies functionality in a specific area, such as a package that makes it easier to work with the job queue of the Oracle Server (interfacing with DBMS_JOB, in other words). A package library is, on the other hand, a coherent set of packages that work together and offer an entire layer of reusable code.

PL/Vision is an example of a package library and is, to my knowledge, one of the first -- if not the first -- such library to be made available to PL/SQL developers. I hope the time will come when third-party PL/SQL developers regularly publish prebuilt packages, whether standalone utilities or libraries, to help the entire PL/SQL community. In the meantime, PL/Vision and this book will offer PL/SQL developers an extensive set of prebuilt utilities and functionality to enhance their development environments.

1.5.3 Build-Your-Own Packages

The third type of package is the build-your-own (BYO) package. This is a package whose specification and body you write yourself. You decide what programs and data are publicly available and how those programs should be called.

The BYO package is, of course, the most common kind of package. You will, I hope, create many, many packages during your PL/SQL development career. Some of those packages might even evolve into prebuilt packages used by other developers. Some will remain at the core of your very business-specific applications.

However your package is used, it is critical that you learn how to build packages that can be easily debugged, maintained, and enhanced. The rest of this chapter explores aspects of package syntax and features. The next chapter, Chapter 2, Best Practices for Packages , provides advice about how best to design and build your packages.