Oracle PL/SQL Best Practices

Table of Contents......Page 9
Preface......Page 11
Best Practices and the Real World......Page 12
They Call This “Work”?......Page 13
The Cast of Characters......Page 14
Structure of This Book......Page 16
How to Use This Book......Page 18
About the Code......Page 19
Comments and Questions......Page 20
Acknowledgments......Page 21
The Big Picture......Page 23
Successful Applications Are Never an Accident......Page 24
Successful Applications Meet User Requirements......Page 25
Successful Applications Run Fast Enough......Page 26
Best Practices for Successful Applications......Page 27
Problem: In software, the ends (production code) are inseparable from the means (the build process).......Page 28
Solution: Agree on a common development workflow built around standards, testing, and reviews.......Page 29
Application-level workflow......Page 30
Single-program construction workflow......Page 34
Step 2. Implement just the header of the program......Page 38
Step 3. Define the tests that must be run......Page 39
Step 4. Build your test code......Page 40
Problem: What is never discussed is never agreed upon.......Page 41
Solution: Contracts capture agreements.......Page 42
Problem: Steven is a hypocritical programmer.......Page 46
Solution: Give your brain a break, and ask others for help.......Page 48
Problem: Sunita spent six months developing comprehensive coding standards for her group.......Page 49
Solution: Move beyond documents to a review process that directly engages the development team.......Page 50
Resources......Page 51
Problem: Jasper is starting to feel more like a robot than a human being.......Page 52
Solution: If you can recognize a pattern in what you are writing, generate code from that pattern.......Page 53
Real Developers Follow Standards......Page 59
Problem: Delaware writes code that no one else can read.......Page 60
Solution: Use the built-in functionality of your IDE to automatically format your code.......Page 61
Problem: Jasper’s eagerness to help is overwhelmed by his hurry to get it done.......Page 62
Solution: Rely on naming conventions that are intuitive and easy to apply.......Page 63
Problem: Badly formed or inaccurate names can greatly reduce usability of programs.......Page 65
Solution: Construct subprogram names so they reflect both what they are and what they do.......Page 66
Problem: Checklists on paper rarely translate into changes in the way we write our code.......Page 67
Solution: Make your checklists active and directly involved in development.......Page 68
Resources......Page 69
Problem: One person’s clarity is another person’s bewilderment.......Page 70
Solution: Sometimes you really do need to add comments.......Page 71
Life After Compilation......Page 73
Tracing......Page 74
Best Practices for Testing, Tracing, and Debugging......Page 75
Solution: Build a layer of code over DBMS_OUTPUT.PUT_LINE.......Page 76
Problem: We live in the moment, and don’t think about what it will take to maintain our code.......Page 77
General Solution: Build instrumentation (tracing) into your application from the start.......Page 78
Specific Solution 1: Embed trace calls in Boolean expressions to minimize overhead.......Page 79
Specific Solution 2: Include standardized modules in packages to dump package state when errors o.........Page 80
Specific Solution 3: Build trace “windows” into your packages using standardized programs.......Page 82
General Solution: Don’t make testing an option, and don’t just “try” a few things.......Page 84
Specific Solution 1: A brief introduction to utPLSQL.......Page 86
Specific Solution 2: A brief introduction to Quest Code Tester for Oracle.......Page 88
Problem: We cannot trust our own minds to fully and objectively test our code.......Page 89
Solution: Decide before you implement your program how you will know when it works correctly.......Page 90
Problem: Lizbeth has entered a medical condition called the Test-By-Hand Coma State.......Page 91
Solution: When it comes to testing, be happy with getting started and with steady progress.......Page 92
Problem: Jasper writes some complicated code and has no idea what it is doing.......Page 93
Solution: Use the built-in, interactive source debuggers available in almost every PL/SQL IDE.......Page 94
Problem: Lizbeth writes a “quick-and-dirty” program.......Page 95
Solution: Assume that everything will change and that any program you write could be around for d.........Page 97
Problem: Lizbeth learns her lesson but then cannot apply it.......Page 98
Solution: Create a new datatype with SUBTYPE and anchor to that.......Page 99
Problem: The exception section of a block can only trap errors raised in the execution section.......Page 100
Solution: Don’t trust the declaration section to assign default values.......Page 102
Solution: Simplify code to make the criteria for the business rules more obvious.......Page 103
Problem: World weariness infects Lizbeth’s code.......Page 104
Solution: Don’t let your weariness show in your code—and don’t recycle!......Page 105
Problem: Sometimes you really do need to clean up in a PL/SQL block.......Page 106
Resources......Page 108
Solution: Perform explicit conversions rather than relying on implicit conversions.......Page 109
Best Practices for Declaring and Using Package Variables......Page 110
Problem: Jasper needs Lizbeth’s program data. Delaware needs Jasper’s program data.......Page 111
Solution: Don’t expose program data in package specifications, letting everyone see and change it.......Page 112
Solution: Declare data in the package body, and hide the data structures via functions in the pac.........Page 114
Best Practices for Conditional and Boolean Logic......Page 117
Problem: IF-statement logic can become complex and confusing.......Page 118
Problem: Lizbeth needs to construct a string conditionally from a number of pieces.......Page 119
Solution: CASE will greatly simplify the code.......Page 120
Solution: Don’t forget the ELSE in the CASE!......Page 121
Solution: Remember that a NULL is not equal to anything else, even another NULL, and code explici.........Page 122
Best Practices for Loop Processing......Page 123
Problem: Jasper writes a loop that offers many exit paths.......Page 124
Solution: One way in, one way out.......Page 126
Problem: The previous developer wrote some code just in case.......Page 127
Solution: Cyberspace is a world of our making. Remove all uncertainty from your code.......Page 128
Problem: It’s so hard to write code without making assumptions!......Page 129
Solution: Write “full collection scans” so that there is no assumption about how the collection i.........Page 132
Best Practices for Branching Logic......Page 133
Resources......Page 135
Doing the Right Thing When Stuff Goes Wrong......Page 136
Problem: Know-it-alls don’t know the most important thing: they’re wrong!......Page 137
Solution: Take some time to familiarize yourself with how PL/SQL exception management works and w.........Page 139
Resources......Page 141
Deliberate exceptions......Page 142
How to benefit from this categorization......Page 143
Solution: Avoid application logic in the exception section.......Page 144
Solution: Transform the exception to a status indicator that can be interpreted by the user of th.........Page 147
Solution: Handle those unexpected, “hard” errors and then re-raise the exception.......Page 148
Specific Problem 1: Get word back to the users.......Page 149
Specific Problem 2: How do I log my error? Let me count the ways.........Page 150
General Solution: One component, under source control, for all to use.......Page 151
Specific Solution 2: Use declarative error handler routines.......Page 152
Best Practices for Nitty-Gritty, Everyday Exception Programming......Page 153
Problem: A little bit of laziness and a slight dose of advanced features can ruin a good program.......Page 154
Solution: Avoid writing “clever” code that shows just how much you know and how smart you are.......Page 155
Problem: The “I don’t care” exception handler can cover up problems too indiscriminately.......Page 156
Best Practices for Coding Defensively......Page 157
Problem: Delaware expects everyone to be a know-it-all like him.......Page 158
Solution: Assume nothing! Make all assumptions explicit, and then validate them.......Page 160
Resources......Page 161
Break Your Addiction to SQL......Page 162
SQL Is Bad!......Page 163
Step 1. Hardcoding is bad.......Page 164
Step 2: Every SQL statement you write is a hardcoding.......Page 165
Problem: PL/SQL developers are addicted to SQL!......Page 166
Solution: Never repeat a SQL statement; instead, implement SQL behind procedures and functions.......Page 167
Problem: Global search-and-replace to the rescue!......Page 173
Solution: Always check after global search-and-replace, and qualify all variable references.......Page 174
Problem: Your error log entries have disappeared!......Page 175
Solution: Save your log information separately from your business transaction logic.......Page 176
Problem: Is everything Lizbeth does in her programs some kind of hardcoding?......Page 177
Solution: Call your own program to do the commit (or rollback), and make it more flexible.......Page 179
Problem: A change in one place affects many others—never a good idea.......Page 180
Solution: Skip all those declarations and replace them with a single record.......Page 181
Solution: Use COUNT only when you need to know “How many rows?”......Page 182
Solution: Use the cursor FOR loop only when fetching multiple rows.......Page 184
Problem: Jasper knows what he’s supposed to do. He just can’t bring himself to do it.......Page 185
Problem: It’s hard to think about what a table will be like in the future.......Page 186
Solution: Always explicitly list the columns that are part of the INSERT statement.......Page 187
Problem: I check the contents of the SQL%ROWCOUNT too late in the game.......Page 188
Solution: Remember that SQL% attributes always refer to the most recently executed implicit curso.........Page 189
Best Practices for Dynamic SQL......Page 190
Problem: There’s something wrong with Lizbeth’s dynamic SQL, but she can’t figure it out.......Page 192
Solution: Make sure your exception section can display the string that failed to parse.......Page 193
Problem: You have lots of dynamic SQL updates, with different values each time.......Page 194
Solution: Bind, don’t concatenate, to optimize performance and simplify dynamic string construction.......Page 195
Problem: So many teenagers, so many ways to enter data at the web site.......Page 196
Solution: Avoid concatenation of SQL text, rely on bind variables, and secure your schemas.......Page 197
Problem: A seemingly handy utility goes badly wrong.......Page 198
Solution: Make sure your dynamic SQL programs run under the invoker’s authority.......Page 199
Resources......Page 200
Playing with Blocks (of Code)......Page 201
Problem: Lizbeth’s program needs to do more, and do it differently.......Page 202
Solution: Make sure all new IN arguments have defaults, or add an overloading.......Page 203
Problem: What seems obvious at the moment of writing is far less clear months or years later.......Page 204
Solution: Use named notation to make everything clear.......Page 205
Problem: Jasper returns data in a very confusing manner.......Page 206
Solution: Return multiple values through a single, composite structure or with a procedure.......Page 207
Best Practices for Procedures and Functions......Page 208
Problem: Lengthy blobs of spaghetti code are unmanageable.......Page 209
Solution: Use step-wise refinement and local subprograms to make code transparent in purpose and .........Page 210
There’s more to tiny chunks than just local subprograms.......Page 214
Resources......Page 217
Problem: Delaware packs it all in and no one wants to use it.......Page 218
Solution: Write programs with very specific purposes and avoid hidden (a.k.a. global) dependencies.......Page 219
Problem: Jasper actually thinks a rule will never change!......Page 220
Solution: Wrap or hide all business rules and formulas inside functions.......Page 222
Problem: Sunita tosses off “quick-and-dirty” code in a function lookup routine.......Page 223
Solution: Don’t allow multiple exit points from the function.......Page 224
Solution: Ensure that a Boolean function returns only TRUE or FALSE.......Page 226
Problem: It seemed that there would be only one program—now there are many.......Page 227
Solution: Put in the dot from the start: package.subprogram.......Page 229
Problem: Some of Oracle’s supplied packages are harder to use than they should be.......Page 230
Solution: Create multiple programs with the same name (overloading) that anticipate user needs.......Page 232
Problem: Seemingly random trigger behavior is driving Jasper nuts.......Page 233
Solution: Consolidate “same event” triggers or use the FOLLOWS clause.......Page 234
Problem: Lizbeth has created a dozen triggers and a support package on a critical database table.......Page 235
Solution: Consolidate all logic into a compound trigger, and lose the package if it is present on.........Page 236
Solution 1: Apply the business rule at the lowest level possible, to ensure that it cannot be avo.........Page 238
Solution 2: Populate columns of derived values with triggers.......Page 239
My Code Runs Faster Than Your Code......Page 240
Best Practices for Finding Slow Code......Page 241
PL/SQL Trace (DBMS_TRACE)......Page 242
Problem: There are hundreds of ways to implement a requirement. How can Lizbeth find the fastest .........Page 243
Solution: Use DBMS_UTILITY.GET_TIME and GET_CPU_TIME for very granular analysis.......Page 245
Resources......Page 246
Best Practices for High-Impact Tuning......Page 247
Problem: It worked so quickly in SQL*Plus. Why is it so slow in PL/SQL?......Page 248
Solution: Sometimes you have to help PL/SQL integrate with SQL.......Page 249
Problem: Jasper queries the same, unchanging data over and over and over again.......Page 253
Solution: If the normal SGA cache doesn’t the trick, look for other ways to cache.......Page 255
Problem: Users hate to wait.......Page 259
Solution: Design your function to return data while it is still running!......Page 260
Problem: Delaware wants to use collections as parameters, but they are causing a bottleneck.......Page 262
Solution: Turn off the default copying that comes with IN OUT arguments.......Page 263
Problem: Lizbeth lost her focus as she wrote her loop.......Page 264
Solution: Avoid executing anything inside a loop that doesn’t change in that loop.......Page 265
Problem: Oracle goes out of its way to make it easy for us to write PL/SQL programs.......Page 266
Solution: Avoid implicit conversions and choose your datatypes carefully.......Page 267
Best Practices Quick Reference......Page 269
Book Example Files......Page 274
Other Helpful Books......Page 277
Free Software......Page 279
Index......Page 281