Oracle PL/SQL Best Practices, 2nd Edition

Larger Cover

Write the Best PL/SQL Code of Your Life

By Steven Feuerstein

Publisher: O'Reilly Media

Released: October 2007

Pages: 304

In this compact book, Steven Feuerstein, widely recognized as one of the world's leading experts on the Oracle PL/SQL language, distills his many years of programming, teaching, and writing about PL/SQL into a set of best practices-recommendations for developing successful applications. Covering the latest Oracle release, Oracle Database 11gR2, Feuerstein has rewritten this new edition in the style of his bestselling Oracle PL/SQL Programming. The text is organized in a problem/solution format, and chronicles the programming exploits of developers at a mythical company called My Flimsy Excuse, Inc., as they write code, make mistakes, and learn from those mistakes-and each other.

This book offers practical answers to some of the hardest questions faced by PL/SQL developers, including:

What is the best way to write the SQL logic in my application code?

How should I write my packages so they can be leveraged by my entire team of developers?

How can I make sure that all my team's programs handle and record errors consistently?

Oracle PL/SQL Best Practices summarizes PL/SQL best practices in nine major categories: overall PL/SQL application development; programming standards; program testing, tracing, and debugging; variables and data structures; control logic; error handling; the use of SQL in PL/SQL; building procedures, functions, packages, and triggers; and overall program performance.

This book is a concise and entertaining guide that PL/SQL developers will turn to again and again as they seek out ways to write higher quality code and more successful applications.

"This book presents ideas that make the difference between a successful project and one that never gets off the ground. It goes beyond just listing a set of rules, and provides realistic scenarios that help the reader understand where the rules come from. This book should be required reading for any team of Oracle database professionals."

--Dwayne King, President, KRIDAN Consulting

Chapter 1 The Big Picture
1. Successful Applications Are Never an Accident
2. Best Practices for Successful Applications
3. Software is like ballet: choreograph the moves or end up with a mess.
4. Deferred satisfaction is a required emotion for best practices.
5. Contracts work for the real world; why not software, too?
6. Don't act like a bird: admit weakness and ignorance.
7. Five heads are better than one.
8. Don't write code that a machine could write for you instead.
9. We need more than brains to write software.
Chapter 2 Real Developers Follow Standards
1. Best Practices for Developing and Using Standards
2. It's a free country; I don't have to use carriage returns in my code.
3. Too much freedom is a very bad thing.
4. Good names lead to good code.
5. Put your checklists into your code.
6. Who needs comments? My code is self-documenting!
Chapter 3 Life After Compilation
1. Testing, Tracing, and Debugging
2. Best Practices for Testing, Tracing, and Debugging
3. Thanks, but no thanks, to DBMS_OUTPUT.PUT_LINE!
4. Assume the worst, and you will never be disappointed.
5. Users really don't want to be programmers.
6. Do you take road trips without a destination in mind?
7. For every test you can think of, there are 10 tests waiting to be performed.
8. Sherlock Holmes never had it so good.
Chapter 4 What's Code Without Variables?
1. Best Practices for Declaring Variables and Data Structures
2. That column's never going to change!
3. There's more to data than columns in a table.
4. I take exception to your declaration section.
5. Best Practices for Using Variables and Data Structures
6. This logic is driving me crazy!
7. Go ahead and splurge: declare distinct variables for different usages.
8. Didn't your parents teach you to clean up after yourself?
9. Programmers are (or should be) control freaks.
10. Best Practices for Declaring and Using Package Variables
11. Danger, Will Robinson! Globals in use!
12. Packages should have a strong sense of personal space.
Chapter 5 Developer As Traffic Cop
1. Best Practices for Conditional and Boolean Logic
2. Reading your code should not require mental gymnastics.
3. KISS (Keep it Simple, Steven).
4. Beware the hidden costs of NULL.
5. Best Practices for Loop Processing
6. There's a right way and a wrong way to say goodbye.
7. Don't take out "programmers' insurance" . . . and don't worry about SkyNet.
8. There is more than one way to scan a collection.
9. Best Practices for Branching Logic
10. Maze-like programs are never a good thing.
Chapter 6 Doing the Right Thing When Stuff Goes Wrong
1. Best Practices for Understanding Error Handling
2. Ignorance is bad exception management.
3. All exceptions are not created equal.
4. One error management approach for all.
5. Best Practices for Nitty-Gritty, Everyday Exception Programming
6. Your code makes me feel dumb.
7. Avoid programmer apathy.
8. Best Practices for Coding Defensively
9. You weren't supposed to do that with my program!
Chapter 7 Break Your Addiction to SQL
1. SQL Is Bad!
2. General SQL Best Practices
3. The best way to avoid problematic code is to not write it.
4. You may write PL/SQL code, but SQL always takes precedence.
5. When one transaction is not enough.
6. I don't always want to save my changes.
7. Best Practices for Querying Data from PL/SQL
8. It's always better to fetch items into a single basket.
9. Answer the question being asked; that is, be a good listener.
10. Your code makes my head spin.
11. Best Practices for Changing Data from PL/SQL
12. Assume the worst!
13. Things only get more complicated over time.
14. Timing is everything in the world of cursors.
15. Best Practices for Dynamic SQL
16. Make it easy to untangle and debug your dynamic SQL statements.
17. Give the RDBMS a break.
18. So you think you know what users might do with your code?
19. It's rude to drop someone else's objects.
Chapter 8 Playing with Blocks (of Code)
1. Best Practices for Parameters
2. Once a program is in use, you can't change it willy-nilly.
3. What the heck do those parameter values mean?
4. Where'd that data come from?
5. Best Practices for Procedures and Functions
6. Write tiny chunks of code.
7. Every program should be an island (of purpose).
8. Gifts should always come tightly wrapped.
9. One way in, one way out: multiple exits confuse me.
10. Black or white programs don't know from NULL.
11. Best Practices for Packages
12. Where there is one program, there will soon be two.
13. "Easy to use code" is code that is used—and reused.
14. Best Practices for Triggers
15. Uncertainty in trigger execution is a most unsettling emotion.
16. "One-stop triggering" is so much easier to understand and maintain.
17. Your application should not be able to perform a "Houdini" with business rules.
Chapter 9 My Code Runs Faster Than Your Code
1. Best Practices for Finding Slow Code
2. Take the guesswork out of optimization.
3. There are so many ways to implement an algorithm; which is best?
4. Best Practices for High-Impact Tuning
5. Let Oracle do most of the tuning for you.
6. Who has time for querying (or inserting or deleting or updating) one row at a time?
7. If the SGA is so wonderful, why not emulate it?
8. When waiting is not an option...
9. Best Practices for Other Tuning
10. Sometimes "safe programming" is a little too slow.
11. PL/SQL loops should not resemble hamsters running in circles.
12. Tailor-made datatypes are the best fit for your programs.

Appendix Best Practices Quick Reference
Appendix Resources for PL/SQL Developers
1. Book Example Files
2. Books and Other Content
3. Free Software
Colophon

Title:

Oracle PL/SQL Best Practices, 2nd Edition

By:

Steven Feuerstein

Publisher:

O'Reilly Media

Formats:

Print
Ebook
Safari Books Online

Print:

October 2007

Ebook:

February 2009

Pages:

304

Print ISBN:

978-0-596-51410-5

| ISBN 10:

0-596-51410-7

Ebook ISBN:

978-0-596-10273-9

| ISBN 10:

0-596-10273-9

Colophon

The animal on the cover of Oracle PL/SQL Best Practices, Second Edition, isa red wood ant. Red wood ants (Formica aquilonia) are often the dominantants of forests throughout the northern hemisphere. F. aquilonia can buildnest mounds of dried spruce needles and twigs that are three feet or more indiameter and height. Each nest can contain thousands of ants as well asseveral queens. The insects have no sting but can defend themselves byfiring formic acid from their rear ends when disturbed.

The workers vary in size up to about half an inch in length with a red thorax,black abdomen, and red and black marked head. The ants are both scavengersand general predators of insects, carrying many soft-bodied caterpillars,flies, and sawflies along their several major trails back to the nest.

Red wood ants are a keystone species (i.e., without them the ecosystemchanges fundamentally). When red ants disappear from a system, herbivorousinsects can subsequently damage forest trees. In forests weakened bypollution and acid rain in central Europe, red wood ant populations areoften endangered, which in turn causes further imbalances in predator-preydynamics and the ecosystem. These rare ants are protected by law in someEuropean countries because of their great value in destroying forest pests.

For 28 years, Professor Seigo Higashi has been studying a supercolony ofJapanese red wood ants (Formica yessensis), which dwell along a strip ofshoreline on the Ishikari coast of northern Japan. When first discovered in1973, the colony consisted of approximately 45,000 nests with connectingtunnels extending nearly 12.4 miles along the shore of the Japan Sea. It wasestimated that the colony had about 306 million workers and 1.1 millionqueens, and is thought to be about 1,000 years old. Since 1973, the colonyhas been under siege, threatened by the development of infrastructure for anew port on Ishikari Bay, which has occurred on top of 30 percent of the antmegalopolis. This has reduced the number of red wood ants living there bymore than half.

The Ishikari ants are one of only two known ant supercolonies in the world.The other, smaller one is in the Swiss Jura mountains.

Description

Table of Contents

Product Details

About the Author

Colophon

Recommended for You

Recently Viewed

Introducing Silverlight 1.1

By Todd Anglin

October 2007

Ebook: $9.99

Regular Expression Pocket Reference, 2nd Edition

Regular Expressions for Perl, Ruby, PHP, Python, C, Java and .NET

By Tony Stubblebine

July 2007

Ebook: $11.99

Print & Ebook: $16.49

Print: $14.99

Network Printing

By Matthew Gast, Todd Radermacher

October 2000

Print: $34.95

Customer Reviews

REVIEW SNAPSHOT®

by PowerReviews

oreilly Oracle PL/SQL Best Practices, Second Edition

5.0

(based on 1 review)

Reviews

Reviewed by 1 customer

Displaying review 1

12/7/2007

(2 of 3 customers found this review helpful)

5.0

Excellent...

By jdruin

from Undisclosed

Comments about oreilly Oracle PL/SQL Best Practices, Second Edition:

Oracle PL/SQL Best Practices

Steven Feuerstein

O'Reilly _ 2nd Edition

http://www.oreilly.com/catalog/9780596514105/index.html

This is a great reference for PL/SQL Programmers. The book covers most aspects of PL/SQL code from start to finish. The style used is somewhat narrative and even entertaining. The author lists several pitfalls in each chapter. The "pitfall story" talks about why programmers might write sub-optimal code in the first place, how to recognize the problem, what is the root cause of the problem, and how to fix the problem. Each example lists code with the issue in place and then the "patched" code after the best-practice has been implemented. Most of the example center around a database at the fictional company used to tell the stories, but the examples are forms of common issue found in most any Oracle database.

The reference does not teach the SQL language itself outside the context of PL/SQL programming. It does cover how to write better SQL in some cases and gives advice on encapsulation of SQL, packaging SQL, and auto-generating SQL. Also, best practices for how to use SQL are covered.

By following the guidelines, PL/SQL code would be easier to maintain and debug, especially for someone who did not write the code or for the developer who has not looked at the code in a while. Also, the "best practice" code had better security feature although the author does not cover security extensively. Some of the examples of "in-application" SQL would be vulnerable to SQL injection attacks and the recommendation by the author to encapsulate the SQL into procedures or packages mitigates this risk. The best-practice code runs more efficiently in most cases as well.

Chapters generally cover:

Overall best programming practices

Standards

Testing

Variables

Loops and Branches

Error Handling

Encapsulation of SQL

Functions and Procedures

Performance

This is an excellent reference for those who program PL/SQL.

Displaying review 1

Save a Tree - Go Digital what is this?

Ebook: $23.99

Formats: DAISY, ePub, Mobi, PDF

Print & Ebook: $32.99

Print: $29.99

Safari Books Online - Read now >

Chapter 1 The Big Picture

Successful Applications Are Never an Accident

Best Practices for Successful Applications

Software is like ballet: choreograph the moves or end up with a mess.

Deferred satisfaction is a required emotion for best practices.

Contracts work for the real world; why not software, too?

Don't act like a bird: admit weakness and ignorance.

Five heads are better than one.

Don't write code that a machine could write for you instead.

We need more than brains to write software.

Chapter 2 Real Developers Follow Standards

Best Practices for Developing and Using Standards

It's a free country; I don't have to use carriage returns in my code.

Too much freedom is a very bad thing.

Good names lead to good code.

Put your checklists into your code.

Who needs comments? My code is self-documenting!

Chapter 3 Life After Compilation

Testing, Tracing, and Debugging

Best Practices for Testing, Tracing, and Debugging

Thanks, but no thanks, to DBMS_OUTPUT.PUT_LINE!

Assume the worst, and you will never be disappointed.

Users really don't want to be programmers.

Do you take road trips without a destination in mind?

For every test you can think of, there are 10 tests waiting to be performed.

Sherlock Holmes never had it so good.

Chapter 4 What's Code Without Variables?

Best Practices for Declaring Variables and Data Structures

That column's never going to change!

There's more to data than columns in a table.

I take exception to your declaration section.

Best Practices for Using Variables and Data Structures

This logic is driving me crazy!

Go ahead and splurge: declare distinct variables for different usages.

Didn't your parents teach you to clean up after yourself?

Programmers are (or should be) control freaks.

Best Practices for Declaring and Using Package Variables

Danger, Will Robinson! Globals in use!

Packages should have a strong sense of personal space.

Chapter 5 Developer As Traffic Cop

Best Practices for Conditional and Boolean Logic

Reading your code should not require mental gymnastics.

KISS (Keep it Simple, Steven).

Beware the hidden costs of NULL.

Best Practices for Loop Processing

There's a right way and a wrong way to say goodbye.

Don't take out "programmers' insurance" . . . and don't worry about SkyNet.

There is more than one way to scan a collection.

Best Practices for Branching Logic

Maze-like programs are never a good thing.

Chapter 6 Doing the Right Thing When Stuff Goes Wrong

Best Practices for Understanding Error Handling

Ignorance is bad exception management.

All exceptions are not created equal.

One error management approach for all.

Best Practices for Nitty-Gritty, Everyday Exception Programming

Your code makes me feel dumb.

Avoid programmer apathy.

Best Practices for Coding Defensively

You weren't supposed to do that with my program!

Chapter 7 Break Your Addiction to SQL

SQL Is Bad!

General SQL Best Practices

The best way to avoid problematic code is to not write it.

You may write PL/SQL code, but SQL always takes precedence.

When one transaction is not enough.

I don't always want to save my changes.

Best Practices for Querying Data from PL/SQL

It's always better to fetch items into a single basket.

Answer the question being asked; that is, be a good listener.

Your code makes my head spin.

Best Practices for Changing Data from PL/SQL

Assume the worst!

Things only get more complicated over time.

Timing is everything in the world of cursors.

Best Practices for Dynamic SQL

Make it easy to untangle and debug your dynamic SQL statements.

Give the RDBMS a break.

So you think you know what users might do with your code?

It's rude to drop someone else's objects.