Oracle PL/SQL Best Practices, 2nd Edition

Table of contents

1. Dedication
2. A Note Regarding Supplemental Files
3. Preface
   1. Why Best Practices?
      1. Best Practices and the Real World
      2. They Call This “Work”?
   2. The Cast of Characters
   3. Structure of This Book
   4. How to Use This Book
   5. Conventions Used in This Book
   6. About the Code
   7. Comments and Questions
   8. Safari Books Online
   9. Acknowledgments
4. 1. The Big Picture
   1. Successful Applications Are Never an Accident
      1. Successful Applications Meet User Requirements
      2. Successful Applications Are Maintainable
      3. Successful Applications Run Fast Enough
   2. Best Practices for Successful Applications
   3. Software is like ballet: choreograph the moves or end up with a mess.
      1. Put into place a practical workflow that emphasizes iterative development based on a shared foundation.
         1. Problem: In software, the ends (production code) are inseparable from the means (the build process).
         2. Solution: Agree on a common development workflow built around standards, testing, and reviews.
         3. Application-level workflow
            1. Step 1: Define requirements and standards.
            2. Step 2: Build the application foundation.
            3. Step 3: Build the next application iteration.
            4. Step 4: Validate the application iteration
         4. Single-program construction workflow
            1. Step 1: Prepare for program construction.
            2. Step 2: Build an iteration of the program.
            3. Step 3: Test the program iteration.
            4. Step 4: Trace execution of the program.
            5. Step 5: Debug the program.
            6. Step 6: Validate the program: optimize and review.
            7. Which steps do you perform?
   4. Deferred satisfaction is a required emotion for best practices.
      1. Hold off on implementing the body of your program until your header is stable and your tests are defined.
         1. Step 1. Validate program requirements
         2. Step 2. Implement just the header of the program
         3. Step 3. Define the tests that must be run
         4. Step 4. Build your test code
   5. Contracts work for the real world; why not software, too?
      1. Match strict input expectations with guaranteed output results.
         1. Problem: What is never discussed is never agreed upon.
         2. Solution: Contracts capture agreements.
            1. About Design by Contract.
            2. Enforcing contracts in code.
   6. Don’t act like a bird: admit weakness and ignorance.
      1. Ask for help (or at least take a break) after 30 minutes on a problem.
         1. Problem: Steven is a hypocritical programmer.
         2. Solution: Give your brain a break, and ask others for help.
   7. Five heads are better than one.
      1. Review and walk through one another’s code; then do automated code reviews.
         1. Problem: Sunita spent six months developing comprehensive coding standards for her group.
         2. Solution: Move beyond documents to a review process that directly engages the development team.
         3. Resources
   8. Don’t write code that a machine could write for you instead.
      1. Generate code whenever possible.
         1. Problem: Jasper is starting to feel more like a robot than a human being.
         2. Solution: If you can recognize a pattern in what you are writing, generate code from that pattern.
   9. We need more than brains to write software.
      1. Take care of your “host body”: fingers, wrists, back, etc.
5. 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.
      1. Adopt a consistent format that is easy to read and maintain.
         1. Problem: Delaware writes code that no one else can read.
         2. Solution: Use the built-in functionality of your IDE to automatically format your code.
   3. Too much freedom is a very bad thing.
      1. Adopt consistent naming conventions for subprograms and data structures.
         1. Problem: Jasper’s eagerness to help is overwhelmed by his hurry to get it done.
         2. Solution: Rely on naming conventions that are intuitive and easy to apply.
   4. Good names lead to good code.
      1. Name procedures with verb phrases, and functions with noun phrases.
         1. Problem: Badly formed or inaccurate names can greatly reduce usability of programs.
         2. Solution: Construct subprogram names so they reflect both what they are and what they do.
   5. Put your checklists into your code.
      1. Define templates to foster standardization in package and program structure.
         1. Problem: Checklists on paper rarely translate into changes in the way we write our code.
         2. Solution: Make your checklists active and directly involved in development.
         3. Resources
   6. Who needs comments? My code is self-documenting!
      1. Comment tersely with value-added information.
         1. Problem: One person’s clarity is another person’s bewilderment.
         2. Solution: Sometimes you really do need to add comments.
6. 3. Life After Compilation
   1. Testing, Tracing, and Debugging
      1. Testing
      2. Tracing
      3. Debugging
   2. Best Practices for Testing, Tracing, and Debugging
   3. Thanks, but no thanks, to DBMS_OUTPUT.PUT_LINE!
      1. Avoid using the DBMS_OUTPUT.PUT_LINE procedure directly.
         1. Problem: DBMS_OUTPUT.PUT_LINE is inadequate for tracing.
         2. Solution: Build a layer of code over DBMS_OUTPUT.PUT_LINE.
   4. Assume the worst, and you will never be disappointed.
      1. Instrument your code to trace execution.
         1. Problem: We live in the moment, and don’t think about what it will take to maintain our code.
         2. General Solution: Build instrumentation (tracing) into your application from the start.
         3. Specific Solution 1: Embed trace calls in Boolean expressions to minimize overhead.
         4. Specific Solution 2: Include standardized modules in packages to dump package state when errors occur.
         5. Specific Solution 3: Build trace “windows” into your packages using standardized programs.
   5. Users really don’t want to be programmers.
      1. Test your programs thoroughly, and as automatically as possible.
         1. Problem: Sunita’s team is dragged down off its pedestal of semi-godliness.
         2. General Solution: Don’t make testing an option, and don’t just “try” a few things.
         3. Specific Solution 1: A brief introduction to utPLSQL.
         4. Specific Solution 2: A brief introduction to Quest Code Tester for Oracle.
         5. Resources
   6. Do you take road trips without a destination in mind?
      1. Follow the test-driven development methodology.
         1. Problem: We cannot trust our own minds to fully and objectively test our code.
         2. Solution: Decide before you implement your program how you will know when it works correctly.
         3. Resources
   7. For every test you can think of, there are 10 tests waiting to be performed.
      1. Don’t worry about getting to 100 percent test coverage.
         1. Problem: Lizbeth has entered a medical condition called the Test-By-Hand Coma State.
         2. Solution: When it comes to testing, be happy with getting started and with steady progress.
   8. Sherlock Holmes never had it so good.
      1. Use source code debuggers to hunt down the cause of bugs.
         1. Problem: Jasper writes some complicated code and has no idea what it is doing.
         2. Solution: Use the built-in, interactive source debuggers available in almost every PL/SQL IDE.
7. 4. What’s Code Without Variables?
   1. Best Practices for Declaring Variables and Data Structures
   2. That column’s never going to change!
      1. Always anchor variables to database datatypes using %TYPE and %ROWTYPE.
         1. Problem: Lizbeth writes a “quick-and-dirty” program.
         2. Solution: Assume that everything will change and that any program you write could be around for decades.
   3. There’s more to data than columns in a table.
      1. Use SUBTYPEs to declare program-specific and derived datatypes.
         1. Problem: Lizbeth learns her lesson but then cannot apply it.
         2. Solution: Create a new datatype with SUBTYPE and anchor to that.
   4. I take exception to your declaration section.
      1. Perform complex variable initialization in the execution section.
         1. Problem: The exception section of a block can only trap errors raised in the execution section.
         2. Solution: Don’t trust the declaration section to assign default values.
   5. Best Practices for Using Variables and Data Structures
   6. This logic is driving me crazy!
      1. Replace complex expressions with well-named constants, variables, or functions.
         1. Problem: Business rules can be complicated, and it’s hard to keep them straight.
         2. Solution: Simplify code to make the criteria for the business rules more obvious.
   7. Go ahead and splurge: declare distinct variables for different usages.
      1. Don’t overload data structure usage.
         1. Problem: World weariness infects Lizbeth’s code.
         2. Solution: Don’t let your weariness show in your code—and don’t recycle!
   8. Didn’t your parents teach you to clean up after yourself?
      1. Clean up data structures when your program terminates (successfully or with an error).
         1. Problem: Sometimes you really do need to clean up in a PL/SQL block.
         2. Resources
   9. Programmers are (or should be) control freaks.
      1. Beware of and avoid implicit datatype conversions.
         1. Problem: PL/SQL performs implicit conversions—but they’re not always what you want.
         2. Solution: Perform explicit conversions rather than relying on implicit conversions.
         3. Resources
   10. Best Practices for Declaring and Using Package Variables
   11. Danger, Will Robinson! Globals in use!
       1. Use package globals sparingly and only in package bodies.
          1. Problem: Jasper needs Lizbeth’s program data. Delaware needs Jasper’s program data.
          2. Solution: Don’t expose program data in package specifications, letting everyone see and change it.
   12. Packages should have a strong sense of personal space.
       1. Control access to package data with “get and set” modules.
          1. Problem: Data structures declared in a package specification may end up bypassing business rules.
          2. Solution: Declare data in the package body, and hide the data structures via functions in the package specification.
8. 5. Developer As Traffic Cop
   1. Best Practices for Conditional and Boolean Logic
   2. Reading your code should not require mental gymnastics.
      1. Use IF . . . ELSIF only to test a single, simple condition.
         1. Problem: IF-statement logic can become complex and confusing.
         2. Solution: Simplify by specifying clauses at multiple levels.
   3. KISS (Keep it Simple, Steven).
      1. Use CASE to avoid lengthy sequences of IF statements.
         1. Problem: Lizbeth needs to construct a string conditionally from a number of pieces.
         2. Solution: CASE will greatly simplify the code.
         3. Solution: Don’t forget the ELSE in the CASE!
   4. Beware the hidden costs of NULL.
      1. Treat NULL conditions explicitly in conditional statements.
         1. Problem: Why is Lizbeth’s IF statement doing that?
         2. Solution: Remember that a NULL is not equal to anything else, even another NULL, and code explicitly for that case.
   5. Best Practices for Loop Processing
   6. There’s a right way and a wrong way to say goodbye.
      1. Never EXIT or RETURN from WHILE and FOR loops.
         1. Problem: Jasper writes a loop that offers many exit paths.
         2. Solution: One way in, one way out.
   7. Don’t take out “programmers’ insurance” . . . and don’t worry about SkyNet.
      1. Never declare the FOR loop index or any other implicitly declared structure.
         1. Problem: The previous developer wrote some code just in case.
         2. Solution: Cyberspace is a world of our making. Remove all uncertainty from your code.
   8. There is more than one way to scan a collection.
      1. Use FOR loops for dense collections, WHILE loops for sparse collections.
         1. Problem: It’s so hard to write code without making assumptions!
         2. Solution: Write “full collection scans” so that there is no assumption about how the collection is filled.
   9. Best Practices for Branching Logic
   10. Maze-like programs are never a good thing.
       1. Use GOTO and CONTINUE only when structured code is not an option.
          1. Resources
9. 6. Doing the Right Thing When Stuff Goes Wrong
   1. Best Practices for Understanding Error Handling
   2. Ignorance is bad exception management.
      1. Study how error raising, handling, and logging work in PL/SQL.
         1. Problem: Know-it-alls don’t know the most important thing: they’re wrong!
         2. Solution: Take some time to familiarize yourself with how PL/SQL exception management works and what PL/SQL offers to help you get the job done.
         3. Resources
   3. All exceptions are not created equal.
      1. Distinguish between deliberate, unfortunate, and unexpected errors.
         1. Deliberate exceptions
         2. Unfortunate and unexpected exceptions
         3. How to benefit from this categorization
         4. Problem: Application logic in the exception is hard to find and maintain.
         5. Solution: Avoid application logic in the exception section.
         6. Problem: Unhandled unfortunate exceptions make your code less flexible and more difficult to reuse.
         7. Solution: Transform the exception to a status indicator that can be interpreted by the user of that code.
         8. Problem: Unhandled unexpected errors cause a loss of information about the cause of the error.
         9. Solution: Handle those unexpected, “hard” errors and then re-raise the exception.
         10. In conclusion . . .
   4. One error management approach for all.
      1. Use error-management standards to avoid confusion and conflicts.
         1. General Problem: Chaos reigns!
         2. Specific Problem 1: Get word back to the users.
         3. Specific Problem 2: How do I log my error? Let me count the ways . . .
         4. General Solution: One component, under source control, for all to use.
         5. Specific Solution 1: No more RAISE_APPLICATION_ERROR.
         6. Specific Solution 2: Use declarative error handler routines.
         7. Resources
   5. Best Practices for Nitty-Gritty, Everyday Exception Programming
   6. Your code makes me feel dumb.
      1. Use the EXCEPTION_INIT pragma to name exceptions and make your code more accessible.
         1. Problem: A little bit of laziness and a slight dose of advanced features can ruin a good program.
         2. Solution: Avoid writing “clever” code that shows just how much you know and how smart you are.
   7. Avoid programmer apathy.
      1. Never use WHEN OTHERS THEN NULL.
         1. Problem: The “I don’t care” exception handler can cover up problems too indiscriminately.
         2. Solution: Add value in WHEN OTHERS: log information and re-raise some exception or other!
   8. Best Practices for Coding Defensively
   9. You weren’t supposed to do that with my program!
      1. Use assertion routines to verify all assumptions made in your program.
         1. Problem: Delaware expects everyone to be a know-it-all like him.
         2. Solution: Assume nothing! Make all assumptions explicit, and then validate them.
         3. Resources
10. 7. Break Your Addiction to SQL
    1. SQL Is Bad!
       1. Step 1. Hardcoding is bad.
       2. Step 2: Every SQL statement you write is a hardcoding.
       3. Step 3. Draw the logical conclusion.
    2. General SQL Best Practices
    3. The best way to avoid problematic code is to not write it.
       1. Hide your SQL statements behind a programmatic interface.
          1. Problem: PL/SQL developers are addicted to SQL!
          2. Solution: Never repeat a SQL statement; instead, implement SQL behind procedures and functions.
             1. Never hardcode or expose a query in your application-level code.
             2. Encapsulate INSERT, UPDATE, and DELETE statements behind procedure calls.
             3. The path to an effective programmatic interface for your SQL.
    4. You may write PL/SQL code, but SQL always takes precedence.
       1. Qualify PL/SQL variables with their scope names when referenced inside SQL statements.
          1. Problem: Global search-and-replace to the rescue!
          2. Solution: Always check after global search-and-replace, and qualify all variable references.
    5. When one transaction is not enough.
       1. Use autonomous transactions to isolate the effect of COMMITs and ROLLBACKs.
          1. Problem: Your error log entries have disappeared!
          2. Solution: Save your log information separately from your business transaction logic.
    6. I don’t always want to save my changes.
       1. Don’t hardcode COMMITs and ROLLBACKs in your code.
          1. Problem: Is everything Lizbeth does in her programs some kind of hardcoding?
          2. Solution: Call your own program to do the commit (or rollback), and make it more flexible.
          3. Resources
    7. Best Practices for Querying Data from PL/SQL
    8. It’s always better to fetch items into a single basket.
       1. Fetch into cursor records, never into a hardcoded list of variables.
          1. Problem: A change in one place affects many others—never a good idea.
          2. Solution: Skip all those declarations and replace them with a single record.
    9. Answer the question being asked; that is, be a good listener.
       1. Use COUNT only when the actual number of occurrences is needed.
          1. Problem: Delaware is not a good listener.
          2. Solution: Use COUNT only when you need to know “How many rows?”
    10. Your code makes my head spin.
        1. Don’t use a cursor FOR loop to fetch just one row.
           1. Problem: Jasper chooses the wrong time to be lazy.
           2. Solution: Use the cursor FOR loop only when fetching multiple rows.
    11. Best Practices for Changing Data from PL/SQL
    12. Assume the worst!
        1. Don’t forget exception handlers for your DML statements.
           1. Problem: Jasper knows what he’s supposed to do. He just can’t bring himself to do it.
           2. Solution: Make sure that any errors that can be anticipated are logged and communicated to the user.
    13. Things only get more complicated over time.
        1. List columns explicitly in your INSERT statements.
           1. Problem: It’s hard to think about what a table will be like in the future.
           2. Solution: Always explicitly list the columns that are part of the INSERT statement.
    14. Timing is everything in the world of cursors.
        1. Reference cursor attributes immediately after executing the SQL operation.
           1. Problem: I check the contents of the SQL%ROWCOUNT too late in the game.
           2. Solution: Remember that SQL% attributes always refer to the most recently executed implicit cursor in your session.
    15. Best Practices for Dynamic SQL
    16. Make it easy to untangle and debug your dynamic SQL statements.
        1. Always parse a string variable; do not EXECUTE IMMEDIATE a literal.
           1. Problem: There’s something wrong with Lizbeth’s dynamic SQL, but she can’t figure it out.
           2. Solution: Make sure your exception section can display the string that failed to parse.
    17. Give the RDBMS a break.
        1. Avoid concatenation of variable values into dynamic SQL strings.
           1. Problem: You have lots of dynamic SQL updates, with different values each time.
           2. Solution: Bind, don’t concatenate, to optimize performance and simplify dynamic string construction.
    18. So you think you know what users might do with your code?
        1. Do not allow malicious injection of code into your dynamic statements.
           1. Problem: So many teenagers, so many ways to enter data at the web site.
           2. Solution: Avoid concatenation of SQL text, rely on bind variables, and secure your schemas.
    19. It’s rude to drop someone else’s objects.
        1. Apply the invoker rights method to stored code that executes dynamic SQL.
           1. Problem: A seemingly handy utility goes badly wrong.
           2. Solution: Make sure your dynamic SQL programs run under the invoker’s authority.
           3. Resources
11. 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.
       1. Ensure backward compatibility as you add parameters.
          1. Problem: Lizbeth’s program needs to do more, and do it differently.
          2. Solution: Make sure all new IN arguments have defaults, or add an overloading.
    3. What the heck do those parameter values mean?
       1. Use named notation to self-document subprogram calls and pass values more flexibly.
          1. Problem: What seems obvious at the moment of writing is far less clear months or years later.
          2. Solution: Use named notation to make everything clear.
    4. Where’d that data come from?
       1. Functions should return data only through the RETURN clause.
          1. Problem: Jasper returns data in a very confusing manner.
          2. Solution: Return multiple values through a single, composite structure or with a procedure.
    5. Best Practices for Procedures and Functions
    6. Write tiny chunks of code.
       1. Limit execution section length to no more than 50 lines.
          1. Problem: Lengthy blobs of spaghetti code are unmanageable.
          2. Solution: Use step-wise refinement and local subprograms to make code transparent in purpose and design.
          3. There’s more to tiny chunks than just local subprograms.
          4. Resources
    7. Every program should be an island (of purpose).
       1. Minimize side effects and maximize reuse by creating programs with narrowly defined purposes.
          1. Problem: Delaware packs it all in and no one wants to use it.
          2. Solution: Write programs with very specific purposes and avoid hidden (a.k.a. global) dependencies.
    8. Gifts should always come tightly wrapped.
       1. Hide business rules and formulas inside functions.
          1. Problem: Jasper actually thinks a rule will never change!
          2. Solution: Wrap or hide all business rules and formulas inside functions.
    9. One way in, one way out: multiple exits confuse me.
       1. Limit functions to a single RETURN statement in the execution section.
          1. Problem: Sunita tosses off “quick-and-dirty” code in a function lookup routine.
          2. Problem: Sure, the program works—but only if you assume a perfect world.
          3. Solution: Don’t allow multiple exit points from the function.
    10. Black or white programs don’t know from NULL.
        1. Never return NULL from Boolean functions.
           1. Problem: Jasper gets really confused with a Boolean function returning a NULL.
           2. Solution: Ensure that a Boolean function returns only TRUE or FALSE.
    11. Best Practices for Packages
    12. Where there is one program, there will soon be two.
        1. Avoid schema-level programs; instead, group related code into packages.
           1. Problem: It seemed that there would be only one program—now there are many.
           2. Solution: Put in the dot from the start: package.subprogram.
    13. “Easy to use code” is code that is used—and reused.
        1. Anticipate programmer needs and simplify call interfaces with overloading.
           1. Problem: Some of Oracle’s supplied packages are harder to use than they should be.
           2. Solution: Create multiple programs with the same name (overloading) that anticipate user needs.
    14. Best Practices for Triggers
    15. Uncertainty in trigger execution is a most unsettling emotion.
        1. Consolidate “overlapping” DML triggers to control execution order, or use the FOLLOW syntax of Oracle Database 11g.
           1. Problem: Seemingly random trigger behavior is driving Jasper nuts.
           2. Solution: Consolidate “same event” triggers or use the FOLLOWS clause.
           3. Resources
    16. “One-stop triggering” is so much easier to understand and maintain.
        1. Use Oracle Database 11g compound triggers to consolidate all related trigger logic on a table.
           1. Problem: Lizbeth has created a dozen triggers and a support package on a critical database table.
           2. Solution: Consolidate all logic into a compound trigger, and lose the package if it is present only for trigger implementation.
    17. Your application should not be able to perform a “Houdini” with business rules.
        1. Validate complex business rules with DML triggers.
           1. Problem: There’s more than one way to make an excuse.
           2. Solution 1: Apply the business rule at the lowest level possible, to ensure that it cannot be avoided.
           3. Solution 2: Populate columns of derived values with triggers.
12. 9. My Code Runs Faster Than Your Code
    1. Best Practices for Finding Slow Code
    2. Take the guesswork out of optimization.
       1. Use trace facilities to gather raw data about program performance.
          1. PL/SQL Profiler (DBMS_PROFILER)
          2. PL/SQL Trace (DBMS_TRACE)
          3. PL/SQL Hierarchical Profiler (DBMS_HPROF)
          4. Application Data Profiler (DBMS_APPLICATION_INFO)
    3. There are so many ways to implement an algorithm; which is best?
       1. Build or find tools to calculate elapsed time.
          1. Problem: There are hundreds of ways to implement a requirement. How can Lizbeth find the fastest version?
          2. Solution: Use DBMS_UTILITY.GET_TIME and GET_CPU_TIME for very granular analysis.
          3. Resources
    4. Best Practices for High-Impact Tuning
    5. Let Oracle do most of the tuning for you.
       1. Make sure your code is being optimized when compiled.
    6. Who has time for querying (or inserting or deleting or updating) one row at a time?
       1. Use BULK COLLECT and FORALL to improve performance of multirow SQL operations in PL/SQL.
          1. Problem: It worked so quickly in SQL*Plus. Why is it so slow in PL/SQL?
          2. Solution: Sometimes you have to help PL/SQL integrate with SQL.
             1. Recommendations for BULK COLLECT
             2. Recommendations for FORALL.
    7. If the SGA is so wonderful, why not emulate it?
       1. Cache static data in the fastest memory location possible.
          1. Problem: Jasper queries the same, unchanging data over and over and over again.
          2. Solution: If the normal SGA cache doesn’t the trick, look for other ways to cache.
             1. Per-session cache in a PL/SQL package.
             2. Oracle Database 11g PL/SQL function result cache.
    8. When waiting is not an option...
       1. Use pipelined table functions to return data faster and to parallelize function execution.
          1. Problem: Users hate to wait.
          2. Solution: Design your function to return data while it is still running!
          3. And that’s not all!
    9. Best Practices for Other Tuning
    10. Sometimes “safe programming” is a little too slow.
        1. Use NOCOPY with care to minimize overhead when collections and records are OUT or IN OUT parameters.
           1. Problem: Delaware wants to use collections as parameters, but they are causing a bottleneck.
           2. Solution: Turn off the default copying that comes with IN OUT arguments.
    11. PL/SQL loops should not resemble hamsters running in circles.
        1. Move static expressions outside of loops and SQL statements.
           1. Problem: Lizbeth lost her focus as she wrote her loop.
           2. Solution: Avoid executing anything inside a loop that doesn’t change in that loop.
    12. Tailor-made datatypes are the best fit for your programs.
        1. Choose datatypes carefully to minimize implicit conversions of data.
           1. Problem: Oracle goes out of its way to make it easy for us to write PL/SQL programs.
           2. Solution: Avoid implicit conversions and choose your datatypes carefully.
13. A. Best Practices Quick Reference
14. B. Resources for PL/SQL Developers
    1. Book Example Files
    2. Books and Other Content
       1. Oracle PL/SQL Books
       2. Other Helpful Books
       3. Online Content
    3. Free Software
15. About the Author
16. Colophon
17. Copyright