Oracle8i Advanced PL/SQL Programming

Both Oracle and PL/SQL, its primary database language, have grown increasingly powerful and complex over the years. As a result, they have become more challenging to learn and master. For some database programmers, the most effective way to learn a new language such as PL/SQL, is to spend a fair amount of "quality time" with a technical book, a computer, and a compiler/interpreter. This approach has the advantages of allowing one to learn in the comfort of one's home, and to be able to progress at one's own pace. But for most people, lacking somewhat in discipline, this method of learning may prove ineffective, as they discover that the responsibilities and/or comforts of home can prove rather tempting, and easily overwhelm their best intentions to study books not known for gripping entertainment. A different approach is to take one or more courses, in a traditional classroom, perhaps at a local community college or through the extension program of a nearby university. This approach has the advantage of compelling the student to read the required material, complete the assigned projects, and attend lectures. Though still lacking in entertainment value, academic courses have the advantage of being more difficult to postpone once started.

Regardless of the method of learning chosen, the IT professional who has decided to expand his or her database programming skills by learning PL/SQL, will more than likely end up reading one of the more popular PL/SQL treatises available on the market. There is certainly no dearth of texts to choose from, with each one on the bookseller's shelf or website claiming to be complete, authoritative, and up-to-date. But as any experienced reader of technical books can attest, this is one area in which a book cannot be judged by its cover, nor specifically by the sweeping claims printed in large font upon that glossy cover. Many such texts turn out, upon careful perusal, to be incomplete, full of syntactical and logical errors, and of little more value than the mostly turgid and useless vendor documentation. In turn, the reader's disappointment is not alleviated by the presence of a CD attached to the back cover, nor by the large number of pages packed between it and the distant front cover — especially given the rather high price of most programming books at this time.

Fortunately, there are a few talented technical authors in the computer science arena, dedicated to producing useful works, and not just churning out expensive book stops and monitor stands. Among such appreciated writers is Scott Urman, who has been assisting Oracle developers the world over with his series of PL/SQL programming books, published by McGraw-Hill Osborne Media. In fact, he may have more years of experience writing such books than any other author, given that his first one, entitled Oracle PL/SQL Programming, came out in 1996, at the time that Oracle7 Release 7.3 incorporated PL/SQL version 2.3. The following year saw the publication of the second edition of that book, bringing it up to Oracle8 release 8.0.

In the year 2000, a new version was brought out, but this one with a questionable twist: Instead of simply bringing the material up-to-date with Oracle8i release 8.1 (the then-current version), Oracle8i Advanced PL/SQL Programming (published on 24 April 2000 under the ISBN 978-0072121469) focused on the advanced topics of PL/SQL, thereby forcing the PL/SQL newcomer to also purchase the second edition, in order to obtain the material on the non-advanced topics. The reason for this questionable change, given in the Introduction of the book under review, is that the resulting publication would have been too large for one volume. In the opinion of this reviewer, the logical and better choice would have been to include all of the new Oracle8i topics, including the advanced ones covered in this book, while dividing the material into two volumes, one Beginning and one Advanced. This would have made it more clear to new readers that they would be able to get all of the needed material — both advanced and otherwise — by purchasing the two volumes. The reader thus could have been assured that all of the material (including the non-advanced topics) would have been up-to-date with Oracle8i, and that there would be no overlap between the two volumes. Unfortunately, the confusion and repetition has continued even up to the publication of Urman's Oracle9i PL/SQL Programming, which duplicates material in his Oracle8i book.

Nonetheless, his Oracle8i title is worthy of consideration, particularly by the database programmer interested in learning the more advanced subjects within PL/SQL. These will be discussed in further detail below, but briefly they include: 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 book's CD-ROM). The text is divided into three parts, comprising 14 chapters and 772 pages. The first part, entitled Introduction and Development Environments, contains three chapters, the first of which is an introduction to the PL/SQL programming language, including a brief overview of its basic as well as advanced features. In addition, the author mentions the six built-in packages that are covered in the book. The introduction includes an explanation of the conventions used in the book, the PL/SQL versions and corresponding Oracle Database versions, the documentation available from Oracle, and the book's companion CD-ROM. Lastly, the author presents 11 sample tables and their data, which are used in the book for illustrating various ideas and PL/SQL commands.

As with the Oracle9i title, the data in those tables is not presented in a clear format — such as a matrix for each table. Rather, there are numerous pages filled with all of the INSERT INTO commands that would be used to populate the tables, with of course one for every row in every table. Unfortunately, this format makes it very difficult to easily read the values in the tables. In addition, it requires repetition of the column names for every single row. INSERT INTO commands such as these would naturally be at home in an SQL script that could be utilized by the reader to populate the sample tables that they may wish to create in their own database. (This wise practice would of course be valuable for executing the commands found in the book, and thereby verifying the output of those commands.) However, embedding the data in repetitious SQL commands printed in a book serves no such purpose, makes it difficult to find data of interest, and wastes space, which could otherwise be used for material left out due to space limitations. If the reason for including all of those SQL commands is to show the syntax of the INSERT INTO statement, then one occurrence alone of the statement would have been sufficient.

The first chapter, like all the others in the book, concludes with a chapter summary — as is done in his later Oracle9i title. A chapter summary in an introductory volume may serve the purpose of allowing the reader to confirm that he or she understands all of the material in that chapter. But an objective such as that would be better gained by utilizing questions and answers, best placed at the back of the book. Chapter summaries are typically not worth the space consumed, particularly in intermediate or advanced technical books. Furthermore, detailed and complex material such as this generally tends to be oversimplified when summarized. A serious reader would go straight to the full and illustrated discussion within the chapter, rather than relying upon a brief summary. In general, chapter summaries just add to the unfortunate bloat now found in the majority of technical books — not unlike the bloat found in most commercial software nowadays.

Chapter 2 examines the two most prevalent application models utilized in distributed systems: the two-tier "client/server" model, in which the user interface client is distinct from the application/database server, and the "three-tier" model, in which the application/database layer is separated into its constituent application logic and database services. The chapter also includes an overview of six of the most commonly used PL/SQL development tools, including SQL*Plus, which is the one included with the Oracle Database product. The five other development tools are more complex and graphical; they are offered by companies other than Oracle. The book's companion CD-ROM contains trial versions of those five tools, which gives the reader an opportunity to try them. Of course, they probably could all be downloaded from the vendors' websites, and would be more up-to-date.

In chapter 3, Urman explores the less glamorous area of tracing through code for the purpose of debugging it. After presenting some basic debugging guidelines, he explains a few non-graphical techniques, such as inserting the values of local variables into a temporary table, and then using the Debugging package included on the CD-ROM. A second version of the Debugging package is demonstrated — this one using the built-in package DBMS_OUTPUT, in order to print values to the screen while the PL/SQL program is running. Then the author surveys the five PL/SQL development tools examined earlier, but this time focusing on their capabilities for interactively debugging PL/SQL code and stepping through that code line by line. He compares the tools with one another, on the basis of eight different feature points. This discussion includes screenshots of the tools, showing how they could be utilized to examine the sample code, as well as illustrating various pitfalls that can lead to bugs in PL/SQL code. The second section of the chapter explores methods of tracing (i.e., recording which subprograms are called and which exceptions are raised when running an application) and profiling (i.e., tracing, supplemented with timing information for each step, typically for the purpose of detecting performance bottlenecks). This discussion covers both SQL tracing using the SQL_TRACE flag within Oracle SQL, and PL/SQL-based tracing, which was introduced with Oracle8, and which uses the DBMS_TRACE package. Oracle8i introduced PL/SQL-based profiling, utilized via the DBMS_PROFILER package. This valuable tool can provide the developer with information concerning the maximum, minimum, and total time that the database spends executing each particular line of PL/SQL code.

Some readers may question the author's choice of surveying PL/SQL development tools — as well as debugging and profiling techniques — so early in the book, and before explaining even how to write the code to be debugged. But this was a wise decision, because it provides the reader with the tools and methods he or she will need for debugging the code they may write as they work their way through the book. In fact, by arming the reader with this knowledge, he or she may be more likely to run and test the code, and feel more confident in making modifications to the author's sample code and creating their own source code from scratch — secure in the knowledge that they have a good understanding of how to locate any errors introduced in the code.

The bulk of Oracle8i Advanced PL/SQL Programming is divided between Parts II and III, entitled Non-Object Features and Object Features and LOBs, respectively. The former is by far the longest of the two parts, which should come as no surprise, given that Oracle was originally and is still primarily a non-object (i.e., relational) database. This part is composed of eight chapters, the first of which describes how to create subprograms and packages. In particular, the author shows how to create as well as drop subprograms (procedures and functions), how to pass parameters to these named blocks of code, and the differences between procedures and functions. The second half of the chapter explains how to group subprograms into packages, which is a valuable practice that should be consistently employed by all PL/SQL programmers — and yet sadly is often not done. But of course, some individuals only learn things the hard way, especially when they take on the role of maintaining convoluted and unorganized Oracle code developed by previous team members who did not follow good practices. The author next explains the purpose and structuring of package specifications and bodies, and then illustrates the scope of package objects, depending upon their type of declaration. The chapter is wrapped up with an explanation of how to overload package subprograms, as well as the concept of package instantiation.

The discussion of creating subprograms and packages leads naturally to the topic of Chapter 5: using those units of code effectively. The author explains that subprograms and packages are normally located within the Oracle data dictionary, but can also be defined within the declarative section of a block, in which case they are local subprograms. Moreover, they cannot be stored in packages, since all packages must be stored within the data dictionary. Next the author explores the concept of subprograms' dependencies, in which a subprogram is dependent upon all of the objects referenced within it. This discussion covers the invalidation of dependent objects, as well as automatic recompilation. Following this is an explication of package runtime states, privileges upon stored subprograms, and the rights of those invoking as opposed to defining subprograms. With the introduction of PL/SQL version 2.1, stored functions could be called from Oracle SQL statements, provided that they meet certain criteria, which are defined in terms of four distinct "purity levels". These restrictions are fully discussed, including the change with Oracle8i, in which the RESTRICT_REFERENCES pragma is no longer required, thereby allowing each subprogram's purity level to be checked at runtime. This allows for calling external routines written in languages not compiled by the PL/SQL compiler (such as routines written in Java or C). Chapter 5 concludes with some miscellaneous package topics, including pinning in the shared pool, limitations on the size of package bodies, and how to specify hints to the Oracle optimizer for better performance when it runs functions.

Another type of named PL/SQL block, the trigger, is the topic of the sixth chapter, in which the reader learns that there are three major types of triggers: DML triggers (in which case the trigger is fired by the execution of a DML statement, i.e., INSERT, UPDATE, or DELETE), instead-of triggers (defined on relational or object views only), and system triggers (fired by a system event, such as the starting or stopping of a database). The author provides full explanations of how to create these three different kinds of triggers, and illustrates the first two kinds using the sample tables introduced earlier. Helpful is his brief discussion of how to drop and disable triggers, which follows his noting the data dictionary view that describes all of the triggers compiled into the data dictionary. The chapter concludes with a section on mutating and constraining tables, which are quite pertinent to the topic of triggers, given that there are limitations on the tables and columns which may be accessed by a trigger's code. Lastly, the author presents a workaround for the restriction of triggers on mutating tables only being allowed as row-level.

The author shifts gears in Chapter 7, in which he examines how to run database jobs using the DBMS_JOB built-in package. As part of this, he explains job numbers, job definitions, and execution intervals. The DBMS_JOB package contains a number of routines that can be utilized, and each one is briefly described in turn. The second half of the chapter explains how to perform file input/output operations, by using the UTL_FILE built-in package, on both the client and the server side. This includes opening and closing files, writing strings and complete lines to files, and reading lines from existing files on disk.

The next four chapters explore topics within PL/SQL which some Oracle application developers may never encounter, but are nonetheless good to know. The first topic, dynamic SQL, allows the compilation of PL/SQL code without binding it to variables until run-time. Even though this can result in performance degradation (since the storage locations for the variables must be assigned at run-time, having not been assigned at compile time), there are clearly many advantages to such on-the-fly variable binding, because it allows the development of dynamically generated SQL. Most if not all major RDBMS products support dynamic SQL, and Oracle is no exception. This is done through the use of the DBMS_SQL built-in package. The author explains and illustrates the use of this package, including how to execute DML (data manipulation language), DDL (data definition language), and ALTER SESSION statements using this package. Of interest is a flow diagram showing the path of execution within the DBMS_SQL package. The second major use of the package is for executing queries, which initially is identical to the usage for DML, DDL, and ALTER SESSION statements. But in this case, the developer can fetch the rows one at a time, and retrieve the column values from the results set. The third and last kind of statement which can be executed using dynamic SQL is the anonymous PL/SQL block. Unlike executing queries, there is no fetch loop when calling an anonymous PL/SQL block; but it can assign values to the bind variables within the block. The author also discusses some of the new features introduced to the DBMS_SQL package with the releases of Oracle8 and Oracle8i, including array processing, binding large objects, the RETURNING clause, and the CALL statement. The last portion of the chapter explores the topic of native dynamic SQL, which, in contrast to the DBMS_SQL package, has only two execution models: query statements, and non-query statements (which would include DML, DDL, ALTER SESSION, transaction control, and anonymous PL/SQL blocks).

Most major operating systems, such as Unix, support the use of pipes, which are mechanisms for sending messages asynchronously from one process to another. Oracle also supports its own pipes, with a more powerful kind having been introduced with Oracle8 Advanced Queuing. The most basic pipes are available through the DBMS_PIPE and DBMS_ALERT built-in packages. These are covered in Chapter 9, on intersession communication within Oracle instances. It should be noted that Oracle pipes are implemented entirely within the Oracle Database product, and do not rely upon any operating system's piping facilities, and thus are completely independent of the operating system. As long as the sending and receiving sessions are able to execute PL/SQL blocks, then the sender can transmit an asynchronous (and non-recallable) message to the receiver. As noted above, this can be accomplished using the DBMS_PIPE package, for sending the message. The author describes the procedures used for sending and receiving messages, the differences between public and private pipes, and other issues such as privileges, security, message formatting and addressing, and the use of the provided Debug package when working with pipes. The latter part of the chapter explains how to send and receive database alerts (messages that are sent when a transaction is committed) utilizing the DBMS_ALERT package. As would be expected, these alerts are tied to the transactions, and are thus synchronous. While pipes are needed for two-way communication, alerts are typically sufficient for one-way communication. Concluding the chapter is a brief comparison between Oracle pipes and alerts.

One of the most exciting developments in Oracle application development during the past few years has been the ability, from within the database, to call routines outside of the database. These external routines can be written in third generation languages such as C and Java. Even though PL/SQL is still the primary programming language within Oracle Database — offering all the advantages of its extensive features, such as compiled routines — there are still countless situations in which external routines are the superior or only choice. For instance, external routines written in C are far better suited for computationally demanding processes, and will outperform the equivalent PL/SQL subprograms for most if not all non-trivial numerical tasks. In addition, PL/SQL was never intended or designed for extensive file system or device access. Urman presents a useful example, in both C and then Java, illustrating how to send an email message from within the database. After explaining the architecture of external routine calls, with illustrations, he explores the detailed steps for developing C and Java external routines and functions, and how to map the data types from those languages to their equivalents in PL/SQL. He also covers the critical topics of: mapping parameters, the different input and output modes of parameters among the languages, and how to pass NULL values. Next the author covers Oracle's product JServer, and issues relating to Java stored procedures. The last part of this lengthy chapter shows how external routines can make callbacks to the database. This capability can be utilized for executing SQL commands and raising exceptions as needed. With Oracle8, callbacks written in C had to use the Oracle8 OCI interface. But with the introduction of Oracle8i, Pro*C can be used instead. Java routines, on the other hand, must utilize JDBC or the SQLJ preprocessor. The chapter ends with a discussion of various issues related to C and Java external routines, including their locations, needed rights (for invoking and defining them), their dependencies, and difficulties in debugging them.

The last chapter of Part II covers a number of miscellaneous improvements to PL/SQL made available in Oracle8i. The author groups these into three areas: performance, application development, and integration with the Web and external routines. In the area of performance enhancements, Oracle8i PL/SQL introduced profiling and tracing, bulk binds, and NOCOPY parameters, in addition to features not visible to the user — specifically, optimization of the STANDARD package, anonymous block execution, RPC execution, and the RETURNING clause. In the area of application development, Urman identifies 15 enhancements to PL/SQL, ranging from improvements to the built-in debugging APIs, to constrained subtypes, and a host of other useful enhancements in between. The third area of PL/SQL advancement deals with integrating PL/SQL more closely with external routines (written in Java and C) and the Web. In fact, the Oracle8i Database product contains a full Java virtual machine, thus making possible the loading and execution of Java procedures within the database itself. C routines, being compiled to binary code, are of course still run as separate processes outside of the database.

The second major portion of the book, Part III, is entitled Object Features and LOBs. It begins with a chapter that surveys the use of objects, which were introduced with the release of Oracle8 and its corresponding PL/SQL version 8. In this chapter, the reader is given a simple object model of the major elements of a university, and learns how to define and initialize object types. This is logically followed by a discussion of how to declare and call methods, as well as how to pass objects to methods. Next the author explains and illustrates some more specialized types of methods, including static, MAP, and ORDER methods. The chapter concludes with a brief explanation of how to alter and discard object types, and how these processes can be affected by object dependencies.

In Chapter 13, having established a basis of PL/SQL objects, the author explores the topic of objects in the database. He begins by describing the various locations within an Oracle8 application where objects can be utilized. Specifically, there are three types of objects in Oracle8: transient, persistent, and client. Each transient object is local to a given PL/SQL block, and thus becomes deallocated when the variable containing that object goes out of scope. Transient objects exist only in memory, and are never stored in the database; hence their lifetime is limited to that of their hosting database session. Persistent objects, on the other hand, are stored in database tables (similar to the way predefined scalar types are stored), and exist within the database until they are deleted, if ever. They take the form of column objects or row objects, and are manipulated using DML statements. The third and final type of objects are client objects, which live only in the memory caches of client programs written in OCI or Pro*C. As such, they exist until explicitly flushed out of their cache. The second portion of the chapter delineates the use of objects via DML commands. The author illustrates how to access the attributes and methods of column objects, and then row objects. He then explains the differences between row and column objects — such as object references — and the SQL operators that can make use of them. The chapter is wrapped up with a discussion of some issues often encountered when working with persistent objects, specifically, the RETURNING clause, and the MAP and ORDER methods.

The fourteenth chapter of the book teaches the reader how to utilize collections, which in many instances can be a very powerful form of data storage, because they allow the user to manipulate many values as one. In a sense, they bring the relational concept of a set of rows, to PL/SQL variables. There are three types of collections available in Oracle8i: index-by tables, nested tables, and varrays. Index-by tables were originally introduced in Oracle8, and were referred to as PL/SQL tables (arguably a better name). But with Oracle8i, they were renamed and given considerably more capabilities. In order to declare such a table, the user defines a table type, which is indexed by a BINARY_INTEGER, and then declares one or more variables of that type. Elements are referenced using the variable name immediately followed by an element number in parentheses. On the surface, index-by tables may appear to be the PL/SQL equivalent of the arrays so commonly used in third generation languages such as C and Java. However, the astute reader will see that they are much more similar to the hashes used in Perl, partly in that their elements are not stored in any particular order, and thus must be accessed one at a time using valid key values. This points up the similarities between regular relational tables in the database, and index-by tables in PL/SQL blocks. Nested tables are similar to index-by tables, but unlike index-by tables, they are stored in the database, and must be declared to use sequential keys. Nonetheless, elements can still be removed from within a nested table — making it more sparse — as can be done with index-by tables. The reader is shown how to initialize a nested table using a constructor, similar to the constructor of an object type. Unlike the aforementioned collection types, varrays have fixed upper bounds on their size, which is specified when the variable type is declared. They are more similar to the arrays in C and Java. Index-by tables are technically not object types, and thus do not have methods defined on them. Instead, they have attributes. Nested tables and varrays are object types, and do have methods. The author explains and illustrates the ten methods that have been defined on these collection types in Oracle8i: EXISTS, COUNT, LIMIT, FIRST, LAST, NEXT, PRIOR, EXTEND, TRIM, and DELETE. The chapter concludes with an explanation of how nested tables and varrays can be stored in database tables, and how to manipulate entire collections as well as their individual elements.

The last two chapters of the book have been stored on the CD-ROM that accompanies the book, apparently due to space limitations of the printed book. The two chapters address large objects, including the basics of LOBs (Chapter 15) and more advanced topics (Chapter 16), including the built-in package DBMS_LOB. Of all the subjects that could have been relegated to the CD-ROM, LOBs was probably the right choice, given that the typical Oracle application developer will likely need to work with LOBs far less frequently than any of the other topics covered in the book. The CD-ROM also contains three appendices. The first one explains how to use the packages supplied on the CD-ROM. The second lists all of the PL/SQL reserved words. The third appendix comprises a description of the Oracle8i data dictionary. Finally, the printed book itself ends with an index. The errata and PL/SQL code used in the text, as well as a sample chapter (Chapter 1) in PDF format, can be downloaded from the Oracle Press Web page for this book.

Aside from the aforesaid poor formatting of the sample data, and the use of wasteful chapter summaries, the only other complaint that this reviewer has of this particular book, is the confusion for potential readers as to which volumes in Scott Urman's series one needs to purchase in order to have all of the latest information on PL/SQL, without any redundant material. Reader comments spotted on one bookseller's website clearly indicate that potential customers are still unsure as to which volume(s) are sufficient for learning all of the most current PL/SQL. Admittedly, it is possible that at the time of Oracle8i's release, the chosen arrangement of material was the best approach. But going forward, Oracle Press could eliminate all such confusion and avoid future duplication of contents by implementing a suggestion first presented in this reviewer's article on the Oracle9i PL/SQL Programming title — namely, publishing an Oracle9i advanced PL/SQL programming volume, to include all of the topics not found in the non-advanced Oracle9i book

But this organizational flaw in the Oracle Press PL/SQL series may not even be the author's doing. In general, this book is well written, and offers the reader countless examples to illustrate most if not all of the advanced PL/SQL features made available in Oracle8i. Oracle programmers at all levels would be well served by reading Scott's Oracle9i title, and then this Oracle8i predecessor. Regardless of one's expertise, there is much to be learned — and this offering from Oracle Press will help you learn the more advanced aspects of PL/SQL.