Ah, for the good old days of Version 1.0 of PL /SQL! Life was so simple then. No stored procedures or functions—and certainly no packages. You had your set of built-in functions, like SUBSTR and TO_DATE. You had the IF statement and various kinds of loops. With these tools at hand, you built your batch-processing scripts for execution in SQL*Plus, and you coded your triggers in SQL*Forms 3.0, and you went home at night content with a good day’s work done.
Of course, there was an awful lot you couldn’t do with PL /SQL 1.0—such as build complex, robust, large-scale applications, or read and write operating system files, or manipulate data in array structures, or debug your code effectively, or store your business formulas in reusable program units.
Sure, life was simpler back when you were stuck with PL /SQL 1.0. You just too frequently told your manager or your users that what they wanted done was, well, impossible. Fortunately, Oracle recognized that it would be so much nicer for both its users and its bottom line if its bedrock technology (most importantly, the Oracle RDBMS and PL /SQL) could actually meet user needs. So it came out with PL /SQL Version 2 (which works with Version 7 of the Oracle Server) and then, most recently, PL /SQL Version 8.0 (to accompany Oracle8). In each major version and its sub-releases (2.1, 2.2, 2.3, and, sometime in 1998, 8.1), Oracle has added major new functionality. As a result, hundreds of thousands of software programmers now use PL /SQL to implement sophisticated applications.
Easily the most important new feature of PL /SQL2 was the introduction of packages. Packages (explored in more detail in the next section) allow you to collect together related program elements and control access to those elements. Anyone who develops PL /SQL applications should employ packages at the very core of their layers of reusable code—and Oracle Corporation itself is no exception. Starting with PL /SQL 2.0 and continuing through every subsequent release, Oracle has made available to PL /SQL developers a series of built-in packages, which extend the functionality of PL /SQL in many fascinating and important directions.
It is no longer sufficient to be aware of and expert in only the core elements of PL /SQL. Getting a handle on IF statements, loops, and the built-in functions like INSTR and TO_CHAR is now only the first phase in your journey towards PL /SQL expertise. To take full advantage of the PL /SQL language, developers must now also learn how to use the programs contained in the built-in packages. And, believe me, once you make a few discoveries in these built-in packages, you will be amazed— and you will be addicted. You will eagerly troll this book for features you can apply in new and creative ways to solve your problems.
This first chapter introduces you to basic concepts of package usage in PL /SQL, shows you how to use built-in packaged functionality in your programs, and explains how to find and learn from the source code for these packages.
Most of the built-in packages extend the PL /SQL language to support features that would otherwise be unavailable to you. This is possible because when Oracle Corporation builds a package, they have the luxury of writing elements of the package in C, giving them full access to the underlying operating system and other areas of technology that are off-limits to the rest of us poor PL /SQL programmers. The result is that Oracle is making something available to us which we could not get ourselves, no matter how proficient a PL /SQL programmer we become.[1]
However, I don’t want to sound as if I’m complaining. I’m very glad Oracle took advantage of packages and C and whatever else they could (and not just because it gave me the excuse to write another book!). The result is a much more powerful and useful PL /SQL. Let’s look at an example to give you a feeling for the way Oracle used the package structure to revolutionize the code you write.
Consider Oracle7 Server Version 7.1: the “Parallel Everything” database, as Oracle Corporation called it in its marketing materials. Oracle 7.1 offered parallel query, parallel index update, and many other features that take advantage of the symmetric multiprocessors widely available today. This parallelization of the RDBMS offered significant improvements in database performance, and we should all be glad for that. But there is, fortunately, so much more for which to be thankful.
Oracle Corporation didn’t simply utilize this parallelization technology inside its own code. It also made this same technology available to us in a “safe” way: through the specification of the DBMS_PIPE package. While DBMS_PIPE is probably not used by the Oracle Server itself, that built-in package certainly accesses the same parallelization technology used by the RDBMS. The advantages for a PL /SQL developer are far-reaching.
Even if DBMS_PIPE originally grew out of a need by Oracle Corporation to enhance its own performance, the advantages of DBMS_PIPE are not confined to the Oracle RDBMS. Any developer 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.
And, given the fact that the technology accessed by DBMS_PIPE is also used by the Oracle Server, you are all but guaranteed that DBMS_PIPE will be very efficient and (relatively) bug-free.
The DBMS_PIPE package is just one of many such mind- and functionality-expanding new resources made available through the built-in packages. 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 available to the internal Oracle product developers. With these tools, you can do things never before possible!
[1] With Oracle8 and its support for external programs in PL /SQL, this is no longer quite true. With PL /SQL8, you will be able to build your own packages that, in turn, call C programs. See Chapter 21 of Oracle PL /SQL Programming, Second Edition, for more information about this feature.
Get Oracle Built-in Packages now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.
