Oracle PL/SQL 101

The majority of the Oracle books available on the market appear to be aimed at the experienced Oracle database developer and/or DBA. A large portion of them are intended to prepare the reader for one or more of the Oracle certification exams. The remaining titles assume that the reader has a basic understanding of databases, SQL, and computer programming in general. As a result, anyone who is new to the Oracle world may find it challenging to locate a truly introductory Oracle database programming book. This would make life difficult for a student new to computer science, enrolled in an introductory Oracle course, for which the instructor has selected one of the more common intermediate level texts. Or consider a veteran server-side applications developer, conversant in a number of 3GL languages, but completely new to database programming, regardless of the particular RDBMS. In the same predicament would be a senior Webmaster, fluent in HTML and Perl, and yet with no prior exposure to Oracle PL/SQL procedures and databases to which her website must now interface. All of these individuals may be as intelligent as they are eager to learn, but they will likely struggle when armed with even the best-selling Oracle books, which assume an understanding of relational databases, basic SQL commands, and even Oracle fundamentals.

McGraw-Hill Osborne Media, publisher of the authorized Oracle Press editions, has created a new "101 Series" of Oracle titles, aimed at the Oracle newcomer. One of these books, entitled Oracle PL/SQL 101 (published on 1 December 2000 under the ISBN 978-0072126068), is authored by Christopher Allen, an experienced writer, with seven previously published works. In his very short introduction, the author makes it clear that the purpose of this new monograph is to explain SQL and PL/SQL in a clear and comprehendible manner, accompanied by examples which demonstrate the real world reasons for using the features shown. He also invites readers to contact him through an email address, in order to provide all manner of feedback, and to obtain the source code of the scripts found in the book. His willingness to respond to reader query is admirable, and genuine, as evidenced by the timely and cheerful reply received by this reviewer's request for the scripts. Within 24 hours he had emailed the most up-to-date errata (covering the third printing), which is more current than the corresponding errata file available on the Oracle Press website, which only covers the first and second printings. Another reader, reporting on a bookseller's website, had a similarly impressive experience.

The material of the book, filling 420 pages, is organized into nine chapters (plus a glossary and an index), and grouped into four parts. Part I, Database Basics, contains four chapters, the first of which is a fairly brief introduction to databases. The author explains the key concepts of table, row, and column. He effectively ties this in with the prior knowledge of even the most inexperienced reader, by comparing databases with spreadsheets, and delineating their differences. He also briefly explains why people filling various roles within an organization would benefit from a basic understanding of the functioning and purpose of databases. Next he presents a short history of the de facto standard database language, SQL, as well as the major types of commands found within that language. This first chapter, like all those that follow, concludes with a chapter summary, a handful of questions to test the reader's comprehension, and the answers to those questions. Unfortunately, having the answers so close to the questions themselves (just one page away), makes it too easy for the reader to glance at the answers without first attempting to figure them out. For this reason alone, the answers would be better located all together, at the back of the book, which is standard practice for books containing answers to their questions.

In chapter 2, the author explains the most basic operations on tables, namely, creating a table, inserting rows in a table, selecting all of the rows, and dropping a table. The first three of these topics are explored in greater detail, beginning with the creation of new tables. The author presents some basic guidelines for naming tables and their columns, including Oracle-specific limitations and reserved words. Ironically, however, the table listing all of the Oracle commands and reserved words does not follow the basic English guideline of reading a page from top to bottom, but is instead rotated 90 degrees counterclockwise, thereby forcing the reader to turn the book on edge in order to read the table's contents. There does not appear to be any reason or advantage to this orientation. Future editions of the book would benefit by rotating the table to match the rest of the text in the book, so it has standard portrait orientation. The author then covers basic Oracle datatypes, how to determine the structure of an existing table, and the NULL value. This last topic can be especially problematic for beginners, which is probably why the author takes the time to explain not only the meaning of NULL values, but the frequent need for their usage. The next part of the chapter discusses other aspects of inserting rows into a table, specifically, how to insert NULL values and how to insert strings containing apostrophes — which can stump even a veteran Oracle programmer who has never had to do it before. The third topic explored in greater detail is how to perform SELECT commands on specific columns, how to perform simple mathematical calculations using column values, how to concatenate text columns and literals, and how to change column headings from their default values to ones selected by the user.

The third chapter covers various aspects of more advanced data manipulation language (DML) operations. It should be kept in mind by the experienced developer (admittedly, not the target audience of this book) that "advanced" in this context simply means, more complex than the most basic DML statements. The first and most useful such operation is how to limit the rows returned from SELECT commands through the use of the WHERE clause. As is typical throughout this book, the examples are clear-cut and intelligible. Next the author explains how to order the resultant rows utilizing the ORDER BY clause, how to limit the results to just the unique rows using the DISTINCT and UNIQUE keywords, and finally the purpose of Oracle's DUAL table. The last topic could otherwise prove rather confusing to a database developer versed in Transact-SQL, which has no such artificial need for a dummy table in SELECT commands that do not reference any real tables. The process of modifying rows using the UPDATE table is presented very briefly, followed by the process of removing rows from a table using the DELETE command. The chapter concludes with the important topic of transaction control, specifically, undoing one's previous disastrous command, or, depending upon one's luck, how to make the mistake permanent by use of the COMMIT command.

Chapter 4 provides an overview of SQL*Plus, the text-based command line utility that allows the user to execute and save Oracle SQL commands. The author demonstrates the two methods of retrieving and modifying previously entered commands, in addition to copying and pasting text, and erasing the SQL*Plus screen. Quite helpful is his discussion of how the user can customize the SQL*Plus environment (via either the Environment menu command under the Options menu, or from the command line), and how to save these environment changes for future use. Next on the agenda are some methods of formatting numerical and textual output, as well as setting column headings. SQL*Plus allows one to direct command output to a file. In addition, SQL commands can be saved in script files, which can utilize variables. The author provides brief descriptions of these capabilities.

In Part II, Advanced SQL, the reader learns about SQL functions, indexes, constraints, views, as well as how to copy and rename tables, and how to alter their structure. Chapter 5 presents some of the more commonly employed SQL functions, of which there are two types: single-row functions, and group functions. The single-row functions include system variables (which return the current system date, user, and environment), as well as numeric, textual, temporal, and data conversion functions. The discussion of group functions is rather light, covering five of the aggregate functions, before illustrating the GROUP BY and HAVING clauses used in conjunction with SELECT commands. While brief in coverage, the explanations and examples fully describe the functions' capabilities.

In chapter 6, the author discusses the use of indexes on columns, as well as the reasons for using them. He then explores the important topics of data integrity among tables, and how to enforce such integrity using constraints. This naturally leads into a brief explication of data modeling, and, in turn, querying multiple tables using inner and outer joins. The introductory nature of the book is made evident by the fact that the joining of two tables is not seen until during the second half of the book. The chapter is wrapped up with an explanation of subqueries, of which the author identifies three types: single-row, multi-row, and multi-column.

Chapter 7 is a bit of a catchall section, in that it presents some miscellaneous — and yet crucial — topics, beginning with the process of copying data from one table into another, as well creating a new table based on an existing one, using the INSERT command. After explaining how to rename a table in Oracle, the author discusses how the reader could go about changing the structure of a table in place, specifically, adding one or more new columns, changing the data types of existing columns, and altering the "null-ability" of some columns. This is followed by a discussion of table views, including creating and dropping them, changing their definitions, and using them for analysis. This topic is worthy of inclusion in an introductory volume, because of the power inherent in views, and their lack of usage by many database developers, even those who are relatively senior. Various other Oracle database objects are explained, including sequences, synonyms, and the all-important Oracle data dictionary.

Part III, entitled Creating Programs Using PL/SQL, comprises only two chapters. This may strike the reader as being a case of "too little, too late", in a book supposedly devoted to PL/SQL, regardless of its introductory nature. But the bulk of the book may indeed have been required in order to prepare the neophyte database programmer for the primary topic itself. Nonetheless, the reader would need the patience to plow through all of the previous material in order to reach these last two chapters, which focus on the declared subject of the book. Chapter 8 is a basic introduction to PL/SQL, beginning with a description of the language and its packaging elements: stored procedures, stored functions, triggers, and SQL scripts. This is followed by an examination of a PL/SQL block and the four sections that compose one. After explaining how to call a PL/SQL procedure or function, the author describes how to declare and use PL/SQL variables and constants. Then he covers the major control structures available to the PL/SQL programmer, such as conditional statements, loops, and cursors. Lastly, the author explores basic error handling through the use of exceptions, both those defined by the programmer and those generated by Oracle itself.

The ninth and final chapter of the book briefly covers a variety of topics, specifically, PL/SQL coding conventions, dynamically declared variable types and records, DML statements within PL/SQL code, implicit and explicit cursors, methods of measuring program time and clock time, PL/SQL packages (an essential topic that probably should have been covered earlier in the chapter), triggers assigned to DML and DDL (data definition language) statements, and server events independent of any table. The chapter is wrapped up with a brief overview of ODBC (Open Database Connectivity), a commonly used interface designed to access databases from within applications, using code independent of a particular RDBMS product.

The final section of the book is labeled "Part IV", suggesting that it is a part of the main text. But it is also entitled Appendix, implying that it contains exactly one appendix. Instead, it contains a glossary and an index, neither of which should be termed an "appendix", nor considered part of the main text. This confusion could be eliminated in future editions by dropping the labels "Part IV" and "Appendix", and simply listing the sections as "Glossary" and "Index", separate from Parts I through III. This part of the book, as previously noted, would also be the ideal home for the answers to the chapter questions.

Early on, the reader will see that the book hews closely to its intended introductory charter: Not a great deal of material is discussed in any given chapter, and the concepts are presented in a very simple and unassuming manner. To the beginning database developer, this style should be a pleasant change of pace from the typical non-introductory level Oracle offering. To the intermediate programmer and DBA, the presentation could be disappointingly lacking in breadth and depth, even if he or she has never worked with Oracle before. But this is as intended, and is true to the "101" naming of the series. Consistent with this light style, the pages of the book are physically somewhat shorter than the common technical volume, with a font size seemingly larger than is typically used. Even though this enhances the readability of the text, it does reduce the amount of material that can be presented on each page. The available real estate is also consumed by a sizable number of figures; but these are well utilized, for they truly help to clarify the ideas, more so than in most Oracle books. By seeing the exact expected output of the sample commands within SQL*Plus, the reader will know whether he or she has entered the commands correctly. An additional benefit is that one can easily read the book and see the commands "in action", without having to be seated at a computer with access to an Oracle database.

The downloadable code for the book, a sample chapter (Chapter 1) in PDF format, and all the errata, may be obtained from the Oracle Press Web page for this title. As noted above, Oracle PL/SQL 101 may seem too lightweight to the intermediate database programmer, or at least too slow paced for even an experienced non-database techie. But to the new Oracle developer, especially one who has never worked with PL/SQL before, this book should prove useful. Even though this volume does not present the reader with a large amount of material, the writing style and examples are clear and approachable. This particular title, and the new 101 Series in general, may become favorites of beginning Oracle programmers.