Oracle9i PL/SQL Programming, 2nd Edition

PL/SQL — which stands for "Procedural Language/SQL" — is Oracle's primary database programming language. As a result, any technical professional working with the Oracle relational database management system (RDBMS), in any form, will likely encounter PL/SQL, and would thus profit from an understanding of the language and all it has to offer. The extent of this benefit would of course be proportional to the level of the individual's involvement with the language: An Oracle DBA may only need to comprehend PL/SQL enough to review a handful of proposed triggers to confirm that they would not adversely impact the performance of a database server. At the other end of the spectrum, senior applications developers would need strong knowledge of PL/SQL in order to create and test the stored procedures and functions to be used in accessing the Oracle databases of their project. In all cases, these individuals will be well served by one or more technical books which fully describe the language syntax and usage. There is certainly no lack of available books which claim to fill this need, as evidenced by the floor-to-ceiling bookshelves in any large bookstore, crammed with a great many Oracle volumes, many devoted to PL/SQL. So which single book best addresses the needs of the reader — in terms of breadth and depth of coverage, ease of understanding, and lack of flawed examples and other filler?

The Oracle books written by Scott Urman, and published by McGraw-Hill Osborne Media, are frequently cited as the most popular PL/SQL books available. A brief perusal of the table of contents of any of his PL/SQL titles should be enough to convince even the most jaded bookstore customer that his works cover most if not all of the topics within this involved language. His latest title, Oracle9i PL/SQL Programming, 2nd Edition, was published on 28 November 2001 under the ISBN 978-0072191479. As noted in the book's Introduction, this title complements his previous work, Oracle8i Advanced PL/SQL Programming, which built upon his popular earlier works Oracle PL/SQL Programming (for Oracle 7) and the subsequent Oracle8 PL/SQL Programming. Apparently his latest title incorporates all of the introductory material not found in his Oracle8i Advanced PL/SQL Programming, and of course includes all of the new features introduced with Oracle9i. But it does not contain the more advanced PL/SQL topics. That may explain why this volume, weighing in at 631 pages, is noticeably smaller than his Oracle8i title, which tops the scale at 772 pages. A comparison of the contents between the two books, reveals that the Oracle8i book truly does cover a number of advanced topics not found in the Oracle9i incarnation, including tracing and debugging, database jobs and file I/O, dynamic SQL, intersession communication, external routines, objects and object types, and large objects (only on the Oracle8i book's CD-ROM). So the need for two volumes is unquestionable. However, there is also a fair amount of overlap between the two books, including topics such as the obligatory PL/SQL overview, development tools, subprograms and packages, triggers, and collections. Thus it can be only hoped that Oracle Press will publish an Oracle9i advanced PL/SQL programming title, to include all of the topics not found in this non-advanced Oracle9i book (including large objects — but this time, in the book itself, and not relegated to the CD-ROM), and none of the topics contained in the non-advanced book. This would avoid duplication between the two titles, and relieve customers from having to purchase and alternate between two books with redundant material.

Oracle9i PL/SQL Programming is divided into four parts, comprising 12 chapters and three appendices. Part I, entitled Introduction and Development Environments, offers an overview of the PL/SQL programming language, including not only its raison d'être, but also some of the essential aspects of the language, namely, its block structure, error handling, datatypes, variables, conditional expressions, looping constructs, cursors, stored procedures and functions, packages, and collections. Urman next lists the most recent versions of PL/SQL and Oracle, and then presents the example tables and data used throughout the book to illustrate the ideas discussed. Unfortunately, the data in the tables is not laid out in an easy-to-read format, such as a set of matrixes, one for each table. Instead, all of the INSERT INTO commands are listed, one for every row in every table. This makes it very difficult to quickly discern the values in the tables, and also entails repeated listings of the same column names for every single row. Such INSERT INTO commands would of course make sense in an SQL script that could be used by the reader to load the sample tables into their own database — in order to follow along with the author, perform the commands presented in the book, and verify the results themselves. But embedding the data in repetitious SQL commands printed on paper serves no such purpose, makes data of interest difficult to find, and wastes space in the book. If the purpose of listing all those SQL commands is to illustrate the syntax of the INSERT INTO command, then only one instance of the command is needed. Moreover, the command itself is fully explained in Chapter 4, along with the other data manipulation commands — certainly a more appropriate location. The only other possible explanation is the same underlying reason for much that is disappointing in the I.T. world: laziness. Perhaps a future edition of the book will have rectified this problem.

The first chapter, like all the others in the book, concludes with a chapter summary. In an introductory book, a chapter summary might arguably serve the worthwhile purpose of allowing the reader to confirm that he or she comprehends all of the material in the chapter just read. But that objective is better achieved via questions (and corresponding answers, located at the back of the book). In general, chapter summaries are not worth the space consumed, especially for any intermediate or advanced programming book. Such detailed and more complex material does not well survive summarizing, and, furthermore, would be reviewed by the reader at its original place in the chapter, not at the end. Moreover, in this age of expensive technical books exceeding 1000 pages, chapter summaries look suspiciously like filler.

In chapter 2, the two most common application models that would be seen by the PL/SQL programmer, are examined: the two-tier model (a.k.a. "client/server", in which the user interface client is separated from the application/database server), and the three-tier model (in which the application logic and database services are in turn separated). The chapter concludes with an overview of seven different PL/SQL development tools, including SQL*Plus, the one included with the Oracle Database product. The six other development tools are more sophisticated, and offered by companies other than Oracle. In addition, demo versions of those six tools are included on the CD-ROM which accompanies the book.

Part II, entitled Basic PL/SQL Features, begins with a chapter explaining many of the essential aspects of the PL/SQL language, beginning with its block structure. Next the author explains how to define legal identifiers, delimiters, literals, and comments within those blocks of code. Following this is a discussion of how to declare and initialize PL/SQL variables, as well as some of its many allowed datatypes: scalars, %TYPE, column types, and user-defined subtypes. The remaining native datatypes are to be found elsewhere in the book: Reference types are covered in Chapter 6; composite types are covered in Chapter 8; and large objects (LOB types) are covered in Chapter 12, as are object types. The important topics of converting between datatypes, and the scope and visibility of variables, are explained, followed by a brief presentation of variable assignment and expressions. In the second half of Chapter 3, the reader encounters all of the PL/SQL control structures, specifically, IF and CASE statements, looping constructs, GOTOs and their labels, and pragmas. Next is a brief discussion of PL/SQL records, including how to use %ROWTYPE — strongly recommended by all maintenance programmers who must debug and modify legacy PL/SQL packages written by others. The chapter concludes with some guidelines for good coding style in PL/SQL, including the use of comments, effective choice of variable names, and common standards for capitalization of keywords as well as indentation of code blocks.

Chapter 4 addresses an area of PL/SQL often neglected in some Oracle texts: Oracle SQL and its use within PL/SQL named and unnamed blocks. The author briefly describes the four DML (data manipulation language) commands (SELECT, INSERT, UPDATE, and DELETE), the WHERE clause within SELECT statements, bulk binds, the RETURNING clause, table references, database links, and synonyms. This section is entitled DML in PL/SQL, so it is not entirely clear as to how those last five topics fall within this area. But one may argue that they are all directly or indirectly related to DML statement usage, and so this is the logical place to discuss them. The second half of the chapter addresses the use of what the author terms "pseudocolumns", such as LEVEL and ROWID. This is followed by a presentation of some common DDL (data definition language) commands, namely, GRANT and REVOKE, in addition to an explanation of privileges and roles within an Oracle database. Lastly, the author explains how to control transactions (logical units of work within the database) using COMMIT and ROLLBACK commands, savepoints, and autonomous transactions, i.e., user-defined transactions, each within a parent transaction.

In the next chapter, Urman presents all of the commonly used built-in functions within Oracle SQL. These include functions for processing character values (i.e., strings), NULL values, numeric values, and dates and times. The reader will then want to know how to convert back and forth among the various datatypes. Oracle provides a number of built-in functions to facilitate this. After briefly describing aggregate and analytic functions, the author introduces two lesser-known functions for displaying numbers in alternate bases (octal, decimal, hexadecimal, and as single characters), and for displaying information about the user's current environment.

In chapter 6, the reader is provided with a clear explanation of how to step through result sets one row at a time, using cursors, both explicit and implicit. The rows accessed by a cursor can be fetched using a number of different mechanisms: simple loops, WHILE loops, cursor FOR loops, and SELECT FOR UPDATE cursors. Urman also describes variables within cursors, including how to declare them and allocate space for them, as well as how to open and close cursors. The next chapter covers error handling through the use of exceptions. The reader learns how to declare, raise, and handle their own exceptions (as opposed to Oracle's standard exceptions). The propagation of exceptions in various sections is next explained, followed by some general guidelines on proper scoping and handling, and masking the locations of errors. The chapter concludes with an introduction to a general error handling package, whose source code is included on the book's CD-ROM. The last chapter in Part II delineates collection types within Oracle, the most commonly used being the index-by table (previously referred to as "PL/SQL tables" in earlier versions of Oracle Database). In addition, the author demonstrates how to utilize nested tables, Varrays, and multilevel collections. This naturally leads into a discussion of how to store those collections within the database, and how to manipulate the individual elements within a collection, as well as working with collections as a whole. Oracle provides a number of methods specific to collections, for doing such things as counting the number of elements, determining the first and last values, fetching the next and prior values, etc. All of these native methods are covered, with examples illustrating their use.

Part III, entitled More PL/SQL Features, is essentially a continuation of Part II. In fact, when one considers that Part I is merely an introductory section, and Part IV contains nothing but appendices, then one may wonder why the book is broken up into four separate parts, whose grouping serves no apparent purpose. Nonetheless, the four chapters within Part III cover crucial topics within PL/SQL. Chapter 9 explains how to create stored procedures and functions, how to pass parameters to them, and how to execute them using the CALL command. The author describes organizing procedures and functions in packages, including package specifications and bodies, scoping considerations, overloading, and package initialization. The next chapter extends this discussion, and thus may as well have been combined with the previous chapter. The author covers a number of important topics related to functions and procedures: their storage location (locally vs. within the database), dependencies, run-time states, privileges, usage within Oracle SQL statements, and commands for "pinning" them within the shared pool, thereby preventing Oracle from removing them from the SGA and later having to reload them again from disk, which of course reduces performance.

Chapter 11 introduces database triggers, of which there are a number of types (DML, instead-of, and system). The reader learns how to create and use each of the three types. The PL/SQL programmer, when choosing to utilize triggers, should bear in mind a number of issues, which the author explores, including their naming, Oracle restrictions and privileges, and the greater flexibility with Oracle8i in the contents of the trigger body. A final important issue related to the use of triggers is the restrictions imposed by Oracle upon which tables and columns that triggers may access. This requires an understanding of mutating and constraining tables, which the author presents, in addition to a workaround for avoiding the mutating table error.

The last chapter in the book presents some of the more advanced features in the PL/SQL language, including external routines, native dynamic Oracle SQL, bulk binds, object types, large objects (LOBs), and pipelined table functions. The chapter is wrapped up with coverage of ten Oracle packages, used for such purposes as executing dynamic SQL, piping messages between sessions, reading from and writing to files, sending email messages from the database, reading Web pages as an HTTP client, retrieving the IP address of a host server, scheduling PL/SQL jobs, and interfacing with large objects.

Part IV comprises three appendices, the first of which briefly describes 93 built-in PL/SQL packages, which provide a wide range of functionality. The second appendix lists all of the PL/SQL reserved words, so the Oracle developer will know which keywords to avoid when naming variables and other objects. Admittedly, one can use reserved words as variable names, but it is obviously not a good idea. The third and final appendix explains the Oracle data dictionary, including the naming conventions and permissions used within it, as well as the various types of views that one may encounter. This appendix also includes a very handy series of tables in which all of the data dictionary views are named, briefly described, and labeled with the Oracle release version of each view. In addition, the author describes the many partitions and subpartitions introduced in Oracle8 and Oracle8i, respectively. Further tables delineate other types of views for indexes, materialized views, summaries, snapshots, subprograms, methods, triggers, source code, compilation errors, dependencies, constraints, statistics, auditing, privileges, and grants.

The PL/SQL code for the book, as well as a sample chapter (Chapter 1) in PDF format, can be downloaded from the Oracle Press Web page for this title. Other than the formatting and organizational flaws mentioned above, this is a very solid Oracle text, and a definite contender for the coveted position of "Favorite PL/SQL Book" among veteran developers. If Oracle Press were to publish a companion volume, covering all of the advanced PL/SQL topics, with complete coverage for Oracle9i, and without any duplication of material in this particular volume, then the resultant pair of books would be tough to beat.