First Edition (Steven)

As a teenager and young man, I wrote many poems, works of short fiction, and uncompleted novels. I dreamed of being an author and sent out my share of manuscripts and self-addressed, stamped envelopes. I never thought that my first book would be on software programming techniques, but here it is! I have tried to keep the fictional content down to an absolute minimum.

I was assisted by many people as I conceived of, wrote, and then polished this text. I would like to thank the following individuals for reviewing draft versions of this book: Jennifer Blair of Blair Technical Training, Eric Camplin of SSC, Joe Celko of OSoft Development Corporation, Avery Cohen of Synectics, Thomas Dunbar, Computer System Engineer at Virginia Tech, R. James Forsythe of PCS Technologies, Mike Gangler of Sharidionne, Gabriel Hoffman of PCS Technologies, Karen Peiser of SSC, Pete Schaffer of SSC, and David Thompson of Spectrum Group.

While all of the people listed above made a contribution, I received particularly detailed and insightful feedback from three people: David Thompson, who saved me much embarrassment from technical glitches in Part 3 and singlehandedly recoded an obtuse example into a well-thought-out illustration of concepts; Thomas Dunbar, who helped me liberate this book from the confines of the Oracle development tools; and Avery Cohen, whose sharp eye and organized mind greatly improved Part 4 .

One of the most dramatic improvements at Oracle Corporation in the last few years is its eagerness to work with those of us outside of the company to help utilize fully its technology. Many individuals at Oracle Corporation have supported the writing of this book. Technical reviewers from Oracle Corporation included Cailein Barclay, Sunil Bhargava, Boris Burshteyn, Gray Clossman, Radhakrishna Hari, James Mallory, Nimesh Mehta, Jeff Muller, Dave Posner, Chris Racicot, Peter Vasterd, and Zona Walcott. These reviews were especially helpful in clarifying language and concepts and in improving technical accuracy. I am grateful for their time and attention.

Two Oracle employees made a special effort on my behalf and I offer them my warmest thank-yous: Peter Vasterd, Product Manager of Server Technologies, who responded rapidly to each of my questions, coordinated the technical review of my book by Oracle Corporation employees, and was especially helpful in the final stages of the book; and Nimish Mehta, Vice-President of the Desktop Products Division and a friend from the early days of SQL*Reportwriter, who was more than generous in providing early releases of software I needed to test and develop the code for this book.

In addition to the reviewing process, many other Oracle employees gave of their time, knowledge, and resources to strengthen this book, including Sohaib Abassi, Per Brondum, Ivan Chong, Bill Dwight, Steve Ehrlich, Bushan Fotedar, Ken Jacobs, Nimish Mehta, Steve Muench, Sri Rajan, and Mark Richter.

Of course, the universe of people who provide a support network for this kind of project goes far beyond those directly involved in the writing and reviewing. For the past three years I have been very fortunate to have been a consultant in SSC, a systems management consulting firm led by Bill Hinman. Bill has created a unique, empowering professional environment. Rather than try to bind his employees to SSC, Bill has sought to offer a guild-like atmosphere in which we each are encouraged to cultivate our own interests, develop a vision, and, of course, hone our technical skills. My time with SSC has exceeded all expectations and we are still just cranking up the model! Hugo Toledo, a fellow SSC consultant, has also been especially supportive of my work. He is one of the most helpful, generous, and intelligent people I have ever met, with a breadth and depth of knowledge about data processing technology that continually astonishes me. For three years, he has always been ready to lend a hand and answer a question, and I am grateful to him for his friendship and commitment.

As I became more active in writing articles and papers for the Oracle User Group community, two editors provided encouragement and steady outlets for my work: Tony Ziemba, editor of Pinnacle's Oracle Developer newsletter, and Bill Pribyl, former and outstanding editor of the IOUG Select magazine. They have both had a wide and lasting impact on improving the flow of quality technical information to Oracle users around the world.

Of course, I would not have been able to write this book without the invaluable experience I gained during my years at Oracle Corporation. I want to thank, in particular, John Cordell, who brought me into Oracle Corporation and guided me through the shoals of Oracle politics and technology, and Beverly Gibson, who worked hard to create special opportunities for so many talented technicians in the sales force. I had the privilege of working with many other incredibly smart, funny, dedicated, and energetic people at Oracle between 1987 and 1992 and I will always treasure that experience.

Thanks to all the people at O'Reilly & Associates who turned my manuscript into a finished book: to Debby Russell, my time-slicing, parallel-processing editor who managed to wrestle this book down to publishable size; Mike Sierra, whose wizardry converted hundreds of pages of Microsoft Word files to FrameMaker; Gigi Estabrook, who read and edited the book several times to prepare drafts for technical review and the East Coast Developer's Conference; Edie Freedman, who designed the cover, and Nancy Priest, who designed the interior layout; Donna Woonteiler, who copyedited the final draft of the book and brought order to the fonts; Chris Reilley and Michelle Willey, who prepared the diagrams; Debby Cunha, Michael Deutsch, John Files, and Juliette Muellner, who edited the files; Cory Willing, who did a final proofreading; Seth Maislin, who prepared the index; Kismet McDonough Chan, who did the final quality control on the book; and Clairemarie Fisher O'Leary, drill sergeant, who pulled together all the pieces of final production.

Finally, I thank my wife, Veva Silva Feuerstein, for her support and tolerance of the absorption of so many of our evenings and weekends in the past year.

Second Edition (Steven)

I know! Instead of saying "Finally, I thank my wife" at the end of this section, I will do it first: Thanks, Veva, for being a fantastic partner and wonderful mother. Sure, I could have written this book without you (actually, I did write this book without you. You don't know anything about PL/SQL...), but without you my life would be empty, so I wouldn't care that I wrote the book.

Wow, what a fast and fascinating trip this has been! The first edition of this book was published in September 1995 and it certainly changed my life. Clearly, the book touched a nerve and filled a need. The book's reception showed that Oracle developers have been desperate for both clear reference information on the PL/SQL language and, perhaps more importantly, tips on how best to take full advantage of this core Oracle technology. From what my readers have told me, Oracle PL/SQL Programming has been a big help. I am very glad that all those trees were not chopped down in vain. I furthermore feel a real obligation to my readers to maintain this text, and my growing series on PL/SQL, so that you may continue to get the most out of this important language. (Who knows? It might even outlive Java!)

The human most responsible for making this second edition possible is Bill Pribyl. When it became clear that Oracle would finally release Oracle8 and that PL/SQL would be changed in fundamental ways, I started to panic. How could I learn everything I needed to learn about object-oriented programming, as well as PL/SQL8, and then write all that down to be published in the fall of 1997, on top of everything else I was doing? It just didn't seem possible. So I sent a plea for help out to a few select people whom I knew were solid writers and outstanding Oracle technicians. Bill quickly exposed a masochistic sliver of his personality by agreeing to cover many of the new features of PL/SQL8 for this edition. He also proved to be a great writer, infused with discipline and enthusiasm for the project. I believe his text (and sense of humor) meshes well with the existing content of the book; I am sure you will feel the same.

This second edition is much more a product of the worldwide PL/SQL developer community than the first edition. I have received hundreds of messages from readers over the past two years, pointing out errors in the text, asking for clarification, and offering suggestions for improvements. I love to hear from you and I do read every single critique, so please keep the comments coming. I am particularly indebted to Eric Givler, Systems Analyst, Department of Environmental Protection, State of Pennsylvania. He pored over my first edition with a devotion and fanaticism that was totally unsolicited and deeply appreciated. He uncovered many, many errors in my first text (I am embarrassed to admit to their volume), and has therefore contributed mightily to the much-improved quality of this second edition. I therefore forgive him his occasional wisecrack at my expense.

Many others helped in ways large and small. Bert Scalzo, Senior DBA/Data Architect at Analysts International Corp (AIC) provided quick reference material for Appendix C . John Beresniewicz of Savant has offered advice and support since the day I met him at the ECO96 conference in Washington, DC. Tom White and Steve Hilker of RevealNet have not only been wonderfully supportive, but they have established a viable commercial outlet for my software, PL/Vision, and for that I am deeply grateful.

At Oracle Corporation, Thomas Kurian made certain that we got the software, support, and answers we needed to make this book technically sound. Also among the contributors were some extremely helpful folks on the Oracle8 development team, approximately 15 of whom fielded our questions at one point or another. In particular, Radhakrishna Hari provided solid and helpful reviews of the Oracle8 material. Shirish Puranik and Kannan Muthukkaruppan assisted with the performance tuning tips in Chapter 25, Tuning PL/SQL Applications .

As with the first edition, Debby Russell of O'Reilly & Associates carried this book through to publication. I have greatly enjoyed working with Debby and hope to do so for a good many other books. This second edition was prepared in record time, in no small part due to Debby's professionalism and competence, not to mention the skills and devotion of the rest of her team: Jane Ellin, the production editor who also copyedited the manuscript; Mike Sierra, who converted the new files to Frame and helped in many other ways; Kimo Carter, who entered edits into the files; Madeleine Newell, who provided extensive production support; Rob Romano, who created the new figures; Edie Freedman, who designed the cover, and Nancy Priest, who designed the interior format; and Seth Maislin, who prepared the index.

Many of the people who helped me in the first edition also stood by me for the second edition. Without repeating all of their names again, I once again thank them.

Second Edition (Bill)

When Steven asked me if I would write a few chapters for this new edition of the book, I was, for once, speechless. Not long before, my citation of Steven as "one of my all-time heroes" had appeared on the back of his second book -- and here he was on the phone, asking me for help! It took me a long time -- about 1.2 seconds -- to give him an answer.

And now, untold hours of "is this a bug or a feature?" later, I'm even more delighted to have collaborated with Steven; not only do I appreciate his willingness to assume the risk of adding a second author to his magnum opus, but I have also thoroughly enjoyed his insightful reviews and astute criticisms of my text. My first acknowledgment goes to Steven.

There is no way that the Oracle8 material would have been acceptable for publication without a brilliant supporting cast. First, fellow consultant Fred Polizo of FP Systems in San Jose, California ( [email protected] ), one of the finest C programmers I know, developed all of the C-based examples in the External Procedures chapter. He, like Steven and I, slogged through the beta, producing test after test and revision after revision of the material until it was just right -- and then came the production version! Words cannot express the respect I have for Fred's programming talents, nor the amazement I felt when I received immediate answers to questions emailed to him at 2 A.M. I think you'll find his crystal-clear C code to be both educational and immediately useful.

I second Steven's ode of gratitude to the folks at Oracle Corporation for their help with this edition, and there are two special thank yous from me. A major supporter of the entire effort was Donald Herkimer, who fielded question after question gracefully and tirelessly, even when it must have gotten way past the fun stage. Don works in Oracle's "Center of Excellence for Applications Development and Productivity with Object-oriented Technology" (they must have won the longest-name award!). When he didn't happen to have the answer himself, Don somehow knew the incantations required to extract answers from deep within the email jungle at Oracle. Ervan Darnell was a tremendous help with the chapter on Nested Tables and VARRAYs, gently convincing me to bury one of my sillier examples, and injecting a bit of precision into my treatment of some of the new SQL functions.

I'd also like to acknowledge Gary Cernosek, an old friend and OO wizard at Rational Corporation, for checking the sanity of the Object Types chapter; and my brother, Patrick Pribyl, C++ programmer extraordinaire, for doing the same. Separate conversations with longtime acquaintances Bill Watkins and Debra Luik were also very enlightening and helped shape the Object Types chapter.

Thanks also go to the staff and clients of my firm, DataCraft, who were more than patient while I went into writing mode. I appreciate their tolerance and moral support, especially from Leo and the rest of the gang.

But my most heartfelt gratitude has to go to my immediate family, for putting up with my writing into the wee hours ("Mommy, Daddy fell asleep at the computer again!") and remaining a constant source of love and good humor.