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?
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
Table of contents
- A Note Regarding Supplemental Files
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
Put into place a practical workflow that emphasizes iterative
development based on a shared foundation.
- Problem: In software, the ends (production code) are inseparable from the means (the build process).
- Solution: Agree on a common development workflow built around standards, testing, and reviews.
- Application-level workflow
- Single-program construction workflow
- Put into place a practical workflow that emphasizes iterative development based on a shared foundation.
- 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.
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!
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.
Instrument your code to trace execution.
- Problem: We live in the moment, and don’t think about what it will take to maintain our code.
- General Solution: Build instrumentation (tracing) into your application from the start.
- Specific Solution 1: Embed trace calls in Boolean expressions to minimize overhead.
- Specific Solution 2: Include standardized modules in packages to dump package state when errors occur.
- Specific Solution 3: Build trace “windows” into your packages using standardized programs.
- Instrument your code to trace execution.
Users really don’t want to be programmers.
- Test your programs thoroughly, and as automatically as possible.
- 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.
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.
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.
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.
Distinguish between deliberate, unfortunate, and unexpected
- Deliberate exceptions
- Unfortunate and unexpected exceptions
- How to benefit from this categorization
- Problem: Application logic in the exception is hard to find and maintain.
- Solution: Avoid application logic in the exception section.
- Problem: Unhandled unfortunate exceptions make your code less flexible and more difficult to reuse.
- Solution: Transform the exception to a status indicator that can be interpreted by the user of that code.
- Problem: Unhandled unexpected errors cause a loss of information about the cause of the error.
- Solution: Handle those unexpected, “hard” errors and then re-raise the exception.
- In conclusion . . .
- Distinguish between deliberate, unfortunate, and unexpected errors.
One error management approach for all.
Use error-management standards to avoid confusion and
- General Problem: Chaos reigns!
- Specific Problem 1: Get word back to the users.
- Specific Problem 2: How do I log my error? Let me count the ways . . .
- General Solution: One component, under source control, for all to use.
- Specific Solution 1: No more RAISE_APPLICATION_ERROR.
- Specific Solution 2: Use declarative error handler routines.
- Use error-management standards to avoid confusion and conflicts.
- 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!
7. Break Your Addiction to SQL
- SQL Is Bad!
- General SQL Best Practices
The best way to avoid problematic code is to not write
Hide your SQL statements behind a programmatic
- Problem: PL/SQL developers are addicted to SQL!
- Solution: Never repeat a SQL statement; instead, implement SQL behind procedures and functions.
- Hide your SQL statements behind a programmatic interface.
- 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.
8. Playing with Blocks (of Code)
- Best Practices for Parameters
- Once a program is in use, you can’t change it willy-nilly.
- What the heck do those parameter values mean?
- Where’d that data come from?
- Best Practices for Procedures and Functions
- Write tiny chunks of code.
- Every program should be an island (of purpose).
- Gifts should always come tightly wrapped.
- One way in, one way out: multiple exits confuse me.
- Black or white programs don’t know from NULL.
- Best Practices for Packages
- Where there is one program, there will soon be two.
- “Easy to use code” is code that is used—and reused.
- Best Practices for Triggers
- Uncertainty in trigger execution is a most unsettling emotion.
“One-stop triggering” is so much easier to understand and
- Use Oracle Database 11g compound triggers to consolidate all related trigger logic on a table.
- Your application should not be able to perform a “Houdini” with business rules.
9. My Code Runs Faster Than Your Code
- Best Practices for Finding Slow Code
- Take the guesswork out of optimization.
- There are so many ways to implement an algorithm; which is best?
- Best Practices for High-Impact Tuning
- Let Oracle do most of the tuning for you.
- Who has time for querying (or inserting or deleting or updating) one row at a time?
If the SGA is so wonderful, why not emulate it?
- Cache static data in the fastest memory location possible.
- When waiting is not an option...
- Best Practices for Other Tuning
- Sometimes “safe programming” is a little too slow.
- PL/SQL loops should not resemble hamsters running in circles.
- Tailor-made datatypes are the best fit for your programs.
- A. Best Practices Quick Reference
- B. Resources for PL/SQL Developers
- About the Author
- Title: Oracle PL/SQL Best Practices, 2nd Edition
- Release date: October 2007
- Publisher(s): O'Reilly Media, Inc.
- ISBN: 9780596514105
You might also like
Oracle PL/SQL Best Practices
In this book, Steven Feuerstein, widely recognized as one of the world's experts on the Oracle …
Beginning Oracle PL/SQL, Second Edition
Beginning Oracle PL/SQL gets you started in using the built-in language that every Oracle developer and …
Advanced Oracle PL/SQL Developer's Guide - Second Edition
Master the advanced concepts of PL/SQL for professional-level certification and learn the new capabilities of Oracle …
Oracle SQL*Plus: The Definitive Guide, 2nd Edition
Despite its wide availability and usage, few developers and DBAs have mastered the true power of …