Mastering Oracle PL/SQL: Practical Solutions [Paperback]

Connor McDonald (Author), Chaim Katz (Author), Christopher Beck (Author), Joel R. Kallman (Author), David C. Knox (Author)

Book Description

Publication Date: January 2004 | Series: Oaktable Series

Do you need more from a PL/SQL book than just the correct keywords and some pretty syntax diagrams? Have you been searching in vain for real-world examples and genuine strategies for maximizing the benefits of PL/SQL within your organization? You'll find them here in Mastering Oracle PL/SQL: Practical Solutions, the inaugural title of the pioneering OakTable Press series from Apress.

This isn't a tutorial on how to code PL/SQL. It's designed to show you how to code PL/SQL well. It shows you how to write code that will run quickly and wont break in high-load, multiuser environments. It covers the vast array of the functionality that PL/SQL provides, including effective handling of relational and abstract data, security, triggers, dynamic web content presentation from within the database, creation of a DBA toolkit, and effective debugging techniques.

This book sets out to redress the unjust criticism leveled at PL/SQL in years gone by. It gives you the tools and techniques to ensure that, whatever your needs are, the PL/SQL you build will run as efficiently as possible. The practical solutions provided in this book will help you to realize the true power and functionality PL/SQL can offer your projects.

If you're brand-new to PL/SQL, then you'll want to take some time to get familiar with the language before tackling this book. It's not for the total beginner. But once youre up and running, you'll find this book an invaluable guide for ensuring that the PL/SQL solutions you build are robust, perform well, and are easy to maintain.

What you’ll learn

Who this book is for

Mastering Oracle PL/SQL: Practical Solutions is targeted primarily toward the DBA or developer charged with the implementation of effective data handling, security, and database administration mechanisms in the Oracle database. However, it will also have great appeal to any developer whose applications rely on an Oracle database and who needs a sound understanding of how to use PL/SQL effectively.

Table of Contents

1. Efficient PL/SQL
2. Package It All Up
3. The Vexed Subject of Cursors
4. Effective Data Handling
5. PL/SQL Optimization Techniques
6. Triggers
7. DBA Packages
8. Security Packages
9. Web Packages
10. PL/SQL Debugging

Editorial Reviews

About the Author

Christopher Beck has a bachelor's degree in computer science from Rutgers University and has been working in the technology industry for 11 years. Starting off as a junior Ada software developer for a government systems integrator, he has spent the past 7 years with Oracle, most recently as a principal technologist. He specializes in core database technologies and web application development. Beck is also a cocreator of Oracle Portal, a commercial product that began as, in Beck's own words, a "science project," then became known as WebView.

Joel Kallman is a web systems technologist for Oracle Corporation. His work over the past 13 years has centered around database and content management, from SGML databases and publishing systems to text and document management. He is currently developing solutions and tools for customers to easily build database-centric web applications. Joel is a proud alumnus of The Ohio State University, where he received his bachelor's degree in computer engineering. He and his wife, Kristin, reside in Powell, Ohio.

A bio is not available for this author.

Connor McDonald has worked with Oracle since the early 1990s, cutting his teeth on Oracle versions 6.0.36 and 7.0.12. Over the past 11 years, Connor has worked with systems in Australia, the U.K., southeast Asia, western Europe, and the United States. He has come to realize that although the systems and methodologies around the world are very diverse, there tend to be two common themes in the development of systems running on Oracle: either to steer away from the Oracle-specific functions or to use them in a haphazard or less-than-optimal fashion. It was this observation that led to the creation of a personal hints and tips website (http://www.OracleDBA.co.uk) and more, presenting on the Oracle speaker circuit in an endeavor to improve the perception and usage of PL/SQL in the industry.

Product Details

• Paperback: 648 pages
• Publisher: Apress; 1 edition (January 2004)
• Language: English
• ISBN-10: 1590592174
• ISBN-13: 978-1590592175
• Product Dimensions: 7.5 x 1.4 x 9.2 inches
• Shipping Weight: 2.6 pounds (View shipping rates and policies)
• Average Customer Review: 4.8 out of 5 stars  See all reviews (9 customer reviews)
• Amazon Best Sellers Rank: #981,407 in Books (See Top 100 in Books)
  • #51 in Books > Computers & Technology > Databases > Oracle

Most Helpful Customer Reviews

24 of 24 people found the following review helpful

4.0 out of 5 stars A disorganized but essential practitioners guide to PL/SQL October 20, 2004

By Odessey Talbot

Format:Paperback

I've had this book for a couple of months now. I've been waiting to finish it before writing a review, but it doesn't look like I'll be finished with it any time soon mostly because I'm using it as a reference book instead of reading it from front to back.

Clearly the book wasn't written as a reference book. Just look up almost any topic in the index and you'll find a number of entries scattered all over the place. Pipe-lined functions, for example, are written about in 4 (four) separate chapters! But the index is good and the material isn't redundant. The reason for this strange structure, I think, is as follows:

The book is divided into two equal length sections. The first half deals in general with PL/SQL strategies and techniques: how to check your code for efficiency, why code should be in packages, how to use cursors, cursor variables, cursor expressions, anchored data types and collections. The second half of the book re-visits most of the same topics in an application centric way (often using PL/SQL supplied packages). It's natural that some of the same techniques and strategies will be re-used and there will be some overlap in the application-oriented chapters.

Sometimes though, the repetition is weird: both the trigger chapter and the security chapter describe a logon trigger that's designed to prevent an ODBC user from accessing the database. The security chapter discusses the data base object grants that are necessary for the trigger to work while the trigger chapter omits all this. The trigger chapter discusses the security risk with the trigger, how a user can often rename the desktop application and bypass the trigger's intent (and purpose) while the security chapter doesn't mention that problem at all.

The book's style (but not its (lack of) organization) is clearly influenced by Tom Kyte's writing, (one of the technical reviewers). Each proposition, especially in the first 1/2 of the book is backed up with a SQL proof/example/argument (based usually on the v$stat view). The style is taken to the max in the discussion of implicit vs. explicit cursors, (in chapter 3). There the discussion stretches over 10 pages and alternates between thesis anti-thesis proof and counter proof like a debate in a Tibetan monastery.

A nice thing is that there's often more than one solution to a problem. For example the code that monitors the Oracle alert file for error messages opens the alert file as an EXTERNAL TABLE, but then an alternate approach is described, which opens the alert file as a BFILE (an external LOB). This is coupled with an explanation about why you might want to go one way or the other, which is very illuminating (although in this specific case, I'm not sure why the author didn't simply open the alert file using utl_file supplied package.)

In my opinion, the chapter describing the PL/SQL Web Toolkit and related technologies is a treasure trove by itself and probably worth the price of the whole book. I don't believe this information is available in such an engaging lucid way anywhere else. It's the only chapter that I had read without stopping.

The book came out as Oracle10g was being released, which raises an important question - is this book already outdated or not? My feeling is that the book is just relevant for 10g PL/SQL developers as 9i developers. The reason I say this is that the PL/SQL language from the developer's point of view has not changed between 9i and 10g (See the paper on OTN 'What's New in PL/SQL in Oracle Database 10g?' which emphasizes that the main changes in PL/SQL 10g - compiler and execution environment - are transparent to the developer.) Maybe, for 10g, the book could have mentioned some of the new PL/SQL supplied packages, for example, dbms_scheduler in addition to dbms_job, or utl_mail instead of utl_smtp, htmldb in addition to the web toolkit, but obviously this has no impact on the book's main message.

What is the book's main message? Demonstrate that the PL/SQL code you write is performant and scalable (chapter 1). The rest of the book is a hodgepodge of technical PL/SQL advice and techniques that should have been organized like a dictionary or an encyclopedia. But nothing is perfect and you can still learn a lot from this eccentric PL/SQL text (even if you can't read it from cover to cover).

7 of 7 people found the following review helpful

4.0 out of 5 stars A good book, worth its price March 9, 2005

By Doroszlai Attila

Format:Paperback

The book takes a bottom-up approach: the first part shows useful coding and optimizing techniques, while the second one gives real world applications and tips on program design.

I have two minor complaints, however:

Most chapters assume at least a good knowledge of PL/SQL and build on that, which I think is fair for a book titled "Mastering ...". On the other hand, two of the chapters (Triggers especially, and PL/SQL Debugging to a degree) take a different approach and start from the beginning, explaining the basics, too. It may be just me, but I think those pages are wasted.

Furthermore, there is a certain amount of overlap with Tom Kyte's Expert One-on-One Oracle, also from Apress.

5 of 5 people found the following review helpful

5.0 out of 5 stars By far, one of the best book on practical Pl/sql September 5, 2005

By Trendy73

Format:Paperback

With 15 years experience in Oracle as DBA and developer, I wrote a lot of packages and found in this book true advices and practical solutions, wich sound good to me. The best feature is that you can experiment all the code found in it and see by yourself the advantage of using the way proposed by McDonald. I like these books where autors breaks some common ideas ans show by "A + B" that the right solution is not the most common. A real useful book written by a true professional.