Structure of this tutorial

This second version has changed from the first version in a number of significant ways. Before listing the individual parts of the tutorial, I want to explain the overall restructuring.

About the Second version

We (the authors and Anonymous) are committed to providing comprehensive, useful coverage of the PL/SQL language over the life of this language. The first version of this tutorial covered most of PL/SQL's features as it existed through PL/SQL Release 2.3. With the release of Oracle8, however, we faced a challenge: how do we fit all the new technologies of PL/SQL8 into Oracle PL/SQL Developing and fill out coverage of existing elements of PL/SQL without creating a tome so unwieldy that reading the tutorial becomes as much a physical as a mental workout?

Furthermore, if we look ahead a few years, we can easily expect that Oracle will continue to roll out expanded functionality in the objects area, as well as providing new and enhanced built-in packages. Given this situation, it quickly became clear to us that it was not practical to offer a single text which covered "all things PL/SQL."

Two questions then arose: what do we cut and what do we do with the stuff that we cut? The answers are as follows:

• Move the package examples from the printed text to the disk. The second version's disk offers a Windows-based interface designed by RevealNet, Inc. (http://www.revealnet.com) allowing you rapid access to the many utilities and samples. Now, instead of having to look through a directory listing and then using an editor to view those files, you will be able to mouse-click and drill-down your way to the topics in which you are interested. We take advantage of this interface to give you an easy way to examine the many examples of packages I built for the tutorial.
• Remove the chapter on built-in packages and expand it into a complete tutorial. This chapter in the first version offered lots of good information, but it was not comprehensive and simply couldn't keep growing to absorb all the new technology issuing forth from Oracle headquarters. Moving coverage of built-in packages to its own tutorial will give us room to provide more and better information about this key element of the PL/SQL language. Appendix A, What's on the Companion Disk?, of this version of Oracle PL/SQL Developing will still provide a quick reference to the more common built-in packages.

About the Contents

The second version of Oracle PL/SQL Developing is divided into seven parts:

• Part 1, Developing in PL/SQL
• Chapters 1 through 3 explain what it means to program in PL/SQL and in Oracle-based applications in general. This part of the tutorial introduces you to the main features of the PL/SQL language and gives you some general advice on developing habits and effective coding style.
• Part 2, PL/SQL Language Elements
• Chapters 4 through 10 describe the basic PL/SQL developing components -- variables, cursors, conditional and sequential control statements, loops, exception handlers, PL/SQL records, and PL/SQL tables. These chapters contain numerous examples, along with tips on how, when, and where to apply these developing constructs most effectively. When you complete this section of the tutorial you will know how to apply all the constructs of the PL/SQL language. You will also be able to examine the complex requirements in your own applications and use the different parts of PL/SQL to implement those requirements.
• Part 3, Built-In Functions
• Chapters 11 through 14 present the many built-in (predefined) PL/SQL functions and procedures, which you can put to use immediately in your applications. One of the key barometers of your success with PL/SQL will be the extent to which you know how to leverage all of the capabilities that Oracle Corporation provides. (You don't want to have to reinvent the wheel when so many functions and procedures are provided for you.) Appendix C, Built-In Packages supplements Part 3 by summarizing the syntax of Oracle's built-in packages.
• Part 4, Modular Code
• Chapters 15 through 17 take you past the individual components of the PL/SQL language to explore modular construction in PL/SQL. You will learn how to build procedures, functions, and packages; more than that, you will learn how to build them correctly. More than with any other aspect of code, the way you design your modules has an enormous impact on your applications' development time, reusability, and maintainability.
• Part 5, New PL/SQL8 Features
• Chapters 18 through 21 contain the main discussion of the new features of Oracle8 -- in particular, object types, new types of collections (nested tables and VARRAYs), object views, and external procedures. Although we describe Oracle8 enhancements as appropriate in other sections of the tutorial, we localize the major discussion here. Doing so allows developers new to Oracle8 to learn about the features in a coordinated way. It also lets developers still working on Oracle7 systems to skip over much of the discussion until it is more relevant to them.
• Part 6, Making PL/SQL Programs Work
• Chapters 22 through 26 tell you how to manage your PL/SQL code and debug, tune, and trace the execution of your programs. This part of the tutorial also contains a summary of particularly helpful tips for effective PL/SQL developing in the real world.
• Part 7, Appendixes
• Appendixes A through C summarize what's on the companion disk, how to call stored procedures from PL/SQL Version 1.1, and how to call Oracle's built-in packages.

If you are new to PL/SQL, reading this tutorial from beginning to end should improve your skills and help your understanding widen in a gradual, natural process. If you're already a proficient PL/SQL developer, you'll probably want to dip into the appropriate sections in order to extract particular techniques for immediate application. Whether you use this tutorial as a teaching guide or as a reference, I hope that it will have a significant impact on your ability to use PL/SQL most effectively.

Long as this tutorial is, it doesn't do everything. The Oracle environment is a huge and complex one, and in this tutorial I've focused my attention on PL/SQL itself. Some topics are outside the logical scope of this tutorial. Therefore, this tutorial does not cover:

• Administration of Oracle databases. While the DBA can use this tutorial to learn how to write the PL/SQL needed to build and maintain databases, this tutorial does not explore all the nuances of the Data Definition Language (DDL) of Oracle's SQL.
• Application and database tuning. I don't cover detailed tuning issues in this tutorial, though the second version does add a chapter on PL/SQL tuning. Oracle Performance Tuning by Peter Corrigan and Mark Gurry (Anonymous, 1997) gives you all the information you need about tuning your Oracle applications.
• Oracle tool-specific technologies independent of PL/SQL. This tutorial won't teach you everything you need to know about such technologies as SQL*Forms and Oracle Forms triggers. Similarly, you won't find detailed discussions of repeating frames of Oracle Reports in here. Nevertheless, many of the techniques offered in this tutorial certainly do apply to the Oracle Developer/2000 environment.
• Third-party application development software. There are many alternatives to using PL/SQL and the tools supplied by Oracle to build your applications. This tutorial does not address these options, nor does it attempt to compare PL/SQL to these third-party products.
• National Language Support in Oracle. This tutorial does not offer comprehensive coverage of Oracle's National Language Support (NLS) capabilities for developing applications for multiple languages.
• Trusted Oracle. Oracle Corporation has developed a special version of its Oracle7 Server for high-security environments. This tutorial does not detail the additional datatypes and features available only for Trusted Oracle.