Learning Oracle PL/SQL

Most Oracle PL/SQL books on the market attempt to present most if not all of the language elements to the reader. In so doing, each one tries to be both a thorough explanation of the language, as well as a complete reference. Such books are of great value to the reader in need of full coverage of such a substantial language — whether the reader is, for instance, an experienced Oracle applications developer writing an Oracle Portal package, or a DBA who needs to review PL/SQL procedures prior to their being compiled into the database. However, such volumes rarely meet the needs of the PL/SQL neophyte, especially any programmer who does not already have one or more 3GL languages under their belt, or has never worked with any other database programming language, such as Transact-SQL.

To fill this gap in the available Oracle books, O'Reilly & Associates publishes a volume entitled Learning Oracle PL/SQL (published on 15 December 2001 under the ISBN 978-0596001803), written by Bill Pribyl with Steven Feuerstein. The authors intend to give the reader a gentle introduction to the Oracle PL/SQL programming language, and in this endeavor they have truly succeeded. In addition to explaining the fundamental aspects of the language, they offer side notes which carefully explain concepts taken for granted by more advanced books. These touches will be most welcome to the reader completely unfamiliar with PL/SQL or even any programming language. Another wise choice on their part is to use one single example application and its tables throughout the book, rather than hitting the reader with a new and somewhat contrived example for each individual concept or section, which is typically done in most programming books.

The authors begin their work with a preface in which they explain the purpose and organization of the book, as well as the more recent versions of PL/SQL, and how they correspond to the versions of the Oracle Database product. The preface's easygoing writing style is employed throughout the book, not unlike other titles bearing the imprint of Steven Feuerstein. In addition to the usual explanation of the book's conventions, contact information, and acknowledgments from the authors, it provides a useful list of all the current Oracle books published by O'Reilly, including a brief description of each book. Such a list should prove valuable to the reader who wishes to know which titles to read after completing this particular book.

The material is organized into nine chapters, spanning 359 pages. The first chapter explains what PL/SQL is all about, and why it is the language of choice when working with Oracle databases. This includes a brief comparison with Java, which can now be extensively used within Oracle9i. The authors also explain what the reader will need technically for getting started with PL/SQL programming, including the minimum operating system and hardware platform required by Oracle. They also explain how to obtain a licensed copy of Oracle, and at least where to find installation information. The book should have stressed that the beginner will want to first attempt an installation on Windows, and not Linux, as the latter process is unavoidably far more involved and fraught with difficulty. Perhaps just to be complete, mention is made of some common text editors that can be used for writing PL/SQL code.

Chapter 2 explains the fundamentals of the language, including program structure, variables, operators, conditional logic, and loop statements. The authors explain how to run PL/SQL programs using SQL*Plus, as well as how to save dynamically created scripts in text files for later use. PL/SQL program structure and datatypes are also covered, albeit rather briefly. The cursory treatment could be expanded in a future edition of the book, to good effect. Basic guidelines and language requirements for code formatting are also presented — a welcome feature not found in all such introductory technical works. The chapter concludes with some important topics: NULLs (including how to detect and convert them), identifier naming rules and scope, and user-defined datatypes.

In Chapter 3, the primary application example used throughout the book is presented, including the application's design requirements, data model, and physical database design. A clear explanation of stored procedures is followed by a discussion of PL/SQL functions. Robust coding and basic unit testing techniques are typically absent from programming books aimed at beginners. But Learning Oracle PL/SQL does not skimp in these areas. The organization of PL/SQL procedures and functions into packages is explained, as well as the many benefits of doing so. This can only help in guiding new programmers as they evolve their own coding styles and habits. Finally, code reuse and procedure overloading are briefly discussed.

The authors shift gears in the fourth chapter, in which they develop the application's front-end, using PL/SQL to create Web pages. This chapter includes an introduction to HTML and Web-based forms, which allow the application to accept data from a user. Next the authors describe how to embed HTML within PL/SQL code. After explaining how to process form data, the authors cover various techniques for handling any errors that may occur, and, perhaps more importantly, how to prevent the errors in the first place. One might argue that the topic of Web-based front ends in PL/SQL should not be presented until other more fundamental and commonly used topics are presented. But the ordering of material in the book follows the development of the sample application. Moreover, the topic of Web forms in PL/SQL, is truly independent of most non-Web topics. Hence, the ordering is not that critical. One reader review found on a bookseller's website, complained that the Web-based PL/SQL techniques are not needed by the typical beginning reader, and thus the rest of the book is apparently not worth reading. But this rather stupidly assumes that the remainder of the book is devoted to Web forms and other HTML usage in PL/SQL. Such is not the case.

Chapter 5 covers one of those fundamental topics — in this case, retrieving information from the Oracle database, one row at a time, utilizing SELECT INTO statements, and then cursors. A number of shortcuts are explained, to make the utilization of cursors easier. Using the sample application, it is shown how to display the query results via HTML. This is followed by a discussion of dynamic SQL, including a valuable explanation of how to handle quotation marks. The chapter is wrapped up with a number of advanced data retrieval topics, including locking data, embedded SELECT statements, and how to make SELECT statements more precise when searching.

The next chapter is relatively short in length, but certainly not short on useful suggestions, including one approach for better organizing PL/SQL code in files and directories, as well as the value of installation scripts and frequent backups. Those last two topics apply to all database languages, and are welcome here. Next the authors discuss how to view and search in the Oracle database for code, compilation errors, and dependencies. Various development tools are summarized in two tables. Code generation, a valuable and rarely used approach, is presented. This is followed by a brief discussion of debugging and unit testing techniques, in addition to a quick introduction to utPL-SQL, Feuerstein's freely available unit testing framework.

Chapter 7 discusses database security, including native and external database authentication. This critical topic is often neglected in language-focused database books. The authors also cover authorization, roles, granting privileges, accounts, execute privileges, synonyms, and name resolution. Returning to their sample application, they discuss security of Web-based applications, cookies, HTTP authentication, and history logs using triggers and other methods. The chapter concludes with a number of security topics more specific to PL/SQL programming, including known security problems with Oracle, batch and dynamic SQL programs, virtual private databases, and encryption.

The next chapter covers a topic one would not expect to find in an introductory volume — in this case, communicating with users and websites outside of an Oracle database. This includes how to send and receive email over the Internet from within PL/SQL, using Oracle's built-in package, as well as other methods. In addition, querying data from remote websites is explained in detail. The potential applications of these capabilities are limited only by the imagination of the database developer or DBA. The second major area covered in this chapter is the integration of PL/SQL with other programming languages, specifically Java and C.

In the final chapter of the book, the reader will find a wide variety of topics discussed, including the typical software development lifecycle, PL/SQL collections, exception-handling packages, pragmas, transaction control, the PL/SQL compiler itself, various generic components, object types, large objects, XML, code profiling, pipelined table functions, and various built-in packages and data types. Following chapter 9 is an afterword, in which the authors discuss a less technical but no less interesting topic: ethical database programming. The book concludes with a fairly complete glossary, which could be especially useful to the reader new to application development.

Overall, this title is a welcome addition to the impressive O'Reilly lineup of Oracle books. While it speaks effectively to the inexperienced PL/SQL programmer, it does not talk down to the reader in any way; nor does it neglect the essential details required to understand the material and to complete the sample application. To avoid typing in any of the sample code, the reader may download it from the O'Reilly Web page for this book. The page also includes all the errata. What this book intentionally lacks in depth and breadth of coverage of the PL/SQL programming language, it more than makes up for in its straightforward and approachable style, geared for the reader hoping to find an effective introductory book. The new PL/SQL programmer could find no better starting point for beginning their journey.