 | Chapter 1 PL/SQL Packages |  |
The different types of packages are determined by who wrote them and by where they lay in the layers of PL/SQL code. As we mentioned earlier, the lowest-level and therefore most broadly available packages are the builtin packages, provided by Oracle Corporation. The next level of packages are the prebuilt packages, written by a third party and made available to you for inclusion in your applications. Finally, there are packages you build yourself.
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.
Package Name | Description |
|---|---|
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. | |
Provides a programmatic access to some of the SQL DDL statements. | |
Used to submit and manage regularly scheduled jobs for execution inside the database. | |
Allows users to create their own locks using the Oracle Lock Management (OLM) services in the database. | |
Offers an interface to Oracle Office (previously known as Oracle Mail). | |
Displays output from PL/SQL programs to the terminal. The "lowest common denominator" debugger mechanism for PL/SQL code. | |
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. | |
Provides a programmatic interface to several SQL ALTER SESSION commands and other session-level commands. | |
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. | |
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) | |
A programmatic interface to a number of the SQL transaction statements, such as the SET TRANSACTION command. | |
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. | |
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.
Oracle Corporation called the Oracle7 Server Version 7.1 the "Parallel Everything" server. It offered parallel query, parallel index updates, and many other features that take advantage of the symmetric multiprocessors readily available today. The parallelization of the RDBMs is an important advance in raw performance, but Oracle Corporation didn't stop there. It also "made public" (i.e., available to outside developers) a package of procedures and functions -- DBMS_PIPE -- used by its developers to support these parallel operations.
The DBMS_PIPE package provides a means to communicate between different Oracle processes directly through the SGA, outside of any particular data transaction. When the RDBMs receives a query request, it can determine whether any of the individual components of the query can be processed independently. If so, the RDBMs issues a call to DBMS_PIPE.SEND_MESSAGE to send the various query components to waiting processes in order to execute those chunks of SQL -- simultaneously.
Now here's the really exciting part: the advantages of DBMS_PIPE are not confined to the Oracle RDBMs. You also can use DBMS_PIPE in all sorts of new and creative ways. You can parallelize your own programs. You can communicate between a client program in Oracle Forms and a server-based process, without having to commit any data. You can build a debugger for your server-side PL/SQL programs.
The DBMS_PIPE package is just one of many such mind- and functionality-expanding new resources. Do you need to issue your own locks? Do you need to detect whether another process in your current session has committed data? Use the DBMS_LOCK package. Do you want to issue messages from within your PL/SQL programs to help trace and debug your program? Check out the DBMS_OUTPUT package. Would you like to schedule jobs within the RDBMs itself? Explore the DBMS_JOB package. The list goes on and on, and is constantly growing. With the Oracle-supplied packages, you have at your disposal many of the same tools used by the internal Oracle product developers. With these tools, you can do things never before possible!
Chapter 15 of Oracle PL/SQL Programming provides details on many of the stored packages of the Oracle Server.
NOTE: Each time you install a new version of the Oracle Server, you should peruse the dbms*.sql and utl*.sql files (usually found in an operating system-specific variant of the rdbmsN/admin directory, where N is the release number, as in: rdbms73/admin). See what is new in terms of both entirely new builtin packages and also changes to existing builtin packages. Don't rely solely on reading the New Features section in the Oracle Server documentation.
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.
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.
|  | |
| 1.4 Using Packages |  | 1.6 Building Packages |
Copyright (c) 2000 O'Reilly & Associates. All rights reserved.
| This HTML Help has been published using the chm2web software. |