Share
Create bookmark
Oracle PL/SQL Best Practices
Do you like this document?
Notes
Please login to add notes
- Table of Contents
-
+
Preface
-
+
The Big Picture
- + Successful Applications Are Never an Accident
-
+
Best Practices for Successful Applications
- 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
- Step 1. Validate program requirements
- Step 2. Implement just the header of the program
- Step 3. Define the tests that must be run
- Step 4. Build your test code
- Problem: What is never discussed is never agreed upon.
- Solution: Contracts capture agreements.
- Problem: Steven is a hypocritical programmer.
- Solution: Give your brain a break, and ask others for help.
- Problem: Sunita spent six months developing comprehensive coding standards for her group.
- Solution: Move beyond documents to a review process that directly engages the development team.
- Resources
- Problem: Jasper is starting to feel more like a robot than a human being.
- Solution: If you can recognize a pattern in what you are writing, generate code from that pattern.
-
+
Real Developers Follow Standards
-
+
Best Practices for Developing and Using Standards
- Problem: Delaware writes code that no one else can read.
- Solution: Use the built-in functionality of your IDE to automatically format your code.
- Problem: Jasper’s eagerness to help is overwhelmed by his hurry to get it done.
- Solution: Rely on naming conventions that are intuitive and easy to apply.
- Problem: Badly formed or inaccurate names can greatly reduce usability of programs.
- Solution: Construct subprogram names so they reflect both what they are and what they do.
- Problem: Checklists on paper rarely translate into changes in the way we write our code.
- Solution: Make your checklists active and directly involved in development.
- Resources
- Problem: One person’s clarity is another person’s bewilderment.
- Solution: Sometimes you really do need to add comments.
-
+
Best Practices for Developing and Using Standards
-
+
Life After Compilation
- + Testing, Tracing, and Debugging
-
+
Best Practices for Testing, Tracing, and Debugging
- Problem: DBMS_OUTPUT.PUT_LINE is inadequate for tracing.
- Solution: Build a layer of code over DBMS_OUTPUT.PUT_LINE.
- 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 o...
- Specific Solution 3: Build trace “windows” into your packages using standardized programs.
- Problem: Sunita’s team is dragged down off its pedestal of semi-godliness.
- General Solution: Don’t make testing an option, and don’t just “try” a few things.
- Specific Solution 1: A brief introduction to utPLSQL.
- Specific Solution 2: A brief introduction to Quest Code Tester for Oracle.
- Resources
- Problem: We cannot trust our own minds to fully and objectively test our code.
- Solution: Decide before you implement your program how you will know when it works correctly.
- Resources
- Problem: Lizbeth has entered a medical condition called the Test-By-Hand Coma State.
- Solution: When it comes to testing, be happy with getting started and with steady progress.
- Problem: Jasper writes some complicated code and has no idea what it is doing.
- Solution: Use the built-in, interactive source debuggers available in almost every PL/SQL IDE.
-
+
What’s Code Without Variables?
-
+
Best Practices for Declaring Variables and Data Structures
- Problem: Lizbeth writes a “quick-and-dirty” program.
- Solution: Assume that everything will change and that any program you write could be around for d...
- Problem: Lizbeth learns her lesson but then cannot apply it.
- Solution: Create a new datatype with SUBTYPE and anchor to that.
- Problem: The exception section of a block can only trap errors raised in the execution section.
- Solution: Don’t trust the declaration section to assign default values.
-
+
Best Practices for Using Variables and Data Structures
- Problem: Business rules can be complicated, and it’s hard to keep them straight.
- Solution: Simplify code to make the criteria for the business rules more obvious.
- Problem: World weariness infects Lizbeth’s code.
- Solution: Don’t let your weariness show in your code—and don’t recycle!
- Problem: Sometimes you really do need to clean up in a PL/SQL block.
- Resources
- Problem: PL/SQL performs implicit conversions—but they’re not always what you want.
- Solution: Perform explicit conversions rather than relying on implicit conversions.
- Resources
-
+
Best Practices for Declaring and Using Package Variables
- Problem: Jasper needs Lizbeth’s program data. Delaware needs Jasper’s program data.
- Solution: Don’t expose program data in package specifications, letting everyone see and change it.
- Problem: Data structures declared in a package specification may end up bypassing business rules.
- Solution: Declare data in the package body, and hide the data structures via functions in the pac...
-
+
Best Practices for Declaring Variables and Data Structures
-
+
Developer As Traffic Cop
-
+
Best Practices for Conditional and Boolean Logic
- Problem: IF-statement logic can become complex and confusing.
- Solution: Simplify by specifying clauses at multiple levels.
- Problem: Lizbeth needs to construct a string conditionally from a number of pieces.
- Solution: CASE will greatly simplify the code.
- Solution: Don’t forget the ELSE in the CASE!
- Problem: Why is Lizbeth’s IF statement doing that?
- Solution: Remember that a NULL is not equal to anything else, even another NULL, and code explici...
-
+
Best Practices for Loop Processing
- Problem: Jasper writes a loop that offers many exit paths.
- Solution: One way in, one way out.
- Problem: The previous developer wrote some code just in case.
- Solution: Cyberspace is a world of our making. Remove all uncertainty from your code.
- Problem: It’s so hard to write code without making assumptions!
- Solution: Write “full collection scans” so that there is no assumption about how the collection i...
- + Best Practices for Branching Logic
-
+
Best Practices for Conditional and Boolean Logic
-
+
Doing the Right Thing When Stuff Goes Wrong
-
+
Best Practices for Understanding Error Handling
- Problem: Know-it-alls don’t know the most important thing: they’re wrong!
- Solution: Take some time to familiarize yourself with how PL/SQL exception management works and w...
- Resources
- 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 th...
- 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...
- 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.
- Resources
-
+
Best Practices for Nitty-Gritty, Everyday Exception Programming
- Problem: A little bit of laziness and a slight dose of advanced features can ruin a good program.
- Solution: Avoid writing “clever” code that shows just how much you know and how smart you are.
- Problem: The “I don’t care” exception handler can cover up problems too indiscriminately.
- Solution: Add value in WHEN OTHERS: log information and re-raise some exception or other!
- + Best Practices for Coding Defensively
-
+
Best Practices for Understanding Error Handling
-
+
Break Your Addiction to SQL
- + SQL Is Bad!
-
+
General SQL Best Practices
- Problem: PL/SQL developers are addicted to SQL!
- Solution: Never repeat a SQL statement; instead, implement SQL behind procedures and functions.
- Problem: Global search-and-replace to the rescue!
- Solution: Always check after global search-and-replace, and qualify all variable references.
- Problem: Your error log entries have disappeared!
- Solution: Save your log information separately from your business transaction logic.
- Problem: Is everything Lizbeth does in her programs some kind of hardcoding?
- Solution: Call your own program to do the commit (or rollback), and make it more flexible.
- Resources
-
+
Best Practices for Querying Data from PL/SQL
- Problem: A change in one place affects many others—never a good idea.
- Solution: Skip all those declarations and replace them with a single record.
- Problem: Delaware is not a good listener.
- Solution: Use COUNT only when you need to know “How many rows?”
- Problem: Jasper chooses the wrong time to be lazy.
- Solution: Use the cursor FOR loop only when fetching multiple rows.
-
+
Best Practices for Changing Data from PL/SQL
- Problem: Jasper knows what he’s supposed to do. He just can’t bring himself to do it.
- Solution: Make sure that any errors that can be anticipated are logged and communicated to the user.
- Problem: It’s hard to think about what a table will be like in the future.
- Solution: Always explicitly list the columns that are part of the INSERT statement.
- Problem: I check the contents of the SQL%ROWCOUNT too late in the game.
- Solution: Remember that SQL% attributes always refer to the most recently executed implicit curso...
-
+
Best Practices for Dynamic SQL
- Problem: There’s something wrong with Lizbeth’s dynamic SQL, but she can’t figure it out.
- Solution: Make sure your exception section can display the string that failed to parse.
- Problem: You have lots of dynamic SQL updates, with different values each time.
- Solution: Bind, don’t concatenate, to optimize performance and simplify dynamic string construction.
- Problem: So many teenagers, so many ways to enter data at the web site.
- Solution: Avoid concatenation of SQL text, rely on bind variables, and secure your schemas.
- Problem: A seemingly handy utility goes badly wrong.
- Solution: Make sure your dynamic SQL programs run under the invoker’s authority.
- Resources
-
+
Playing with Blocks (of Code)
-
+
Best Practices for Parameters
- Problem: Lizbeth’s program needs to do more, and do it differently.
- Solution: Make sure all new IN arguments have defaults, or add an overloading.
- Problem: What seems obvious at the moment of writing is far less clear months or years later.
- Solution: Use named notation to make everything clear.
- Problem: Jasper returns data in a very confusing manner.
- Solution: Return multiple values through a single, composite structure or with a procedure.
-
+
Best Practices for Procedures and Functions
- Problem: Lengthy blobs of spaghetti code are unmanageable.
- Solution: Use step-wise refinement and local subprograms to make code transparent in purpose and ...
- There’s more to tiny chunks than just local subprograms.
- Resources
- Problem: Delaware packs it all in and no one wants to use it.
- Solution: Write programs with very specific purposes and avoid hidden (a.k.a. global) dependencies.
- Problem: Jasper actually thinks a rule will never change!
- Solution: Wrap or hide all business rules and formulas inside functions.
- Problem: Sunita tosses off “quick-and-dirty” code in a function lookup routine.
- Problem: Sure, the program works—but only if you assume a perfect world.
- Solution: Don’t allow multiple exit points from the function.
- Problem: Jasper gets really confused with a Boolean function returning a NULL.
- Solution: Ensure that a Boolean function returns only TRUE or FALSE.
-
+
Best Practices for Packages
- Problem: It seemed that there would be only one program—now there are many.
- Solution: Put in the dot from the start: package.subprogram.
- Problem: Some of Oracle’s supplied packages are harder to use than they should be.
- Solution: Create multiple programs with the same name (overloading) that anticipate user needs.
-
+
Best Practices for Triggers
- Problem: Seemingly random trigger behavior is driving Jasper nuts.
- Solution: Consolidate “same event” triggers or use the FOLLOWS clause.
- Resources
- Problem: Lizbeth has created a dozen triggers and a support package on a critical database table.
- Solution: Consolidate all logic into a compound trigger, and lose the package if it is present on...
- Problem: There’s more than one way to make an excuse.
- Solution 1: Apply the business rule at the lowest level possible, to ensure that it cannot be avo...
- Solution 2: Populate columns of derived values with triggers.
-
+
Best Practices for Parameters
-
+
My Code Runs Faster Than Your Code
-
+
Best Practices for Finding Slow Code
- PL/SQL Profiler (DBMS_PROFILER)
- PL/SQL Trace (DBMS_TRACE)
- PL/SQL Hierarchical Profiler (DBMS_HPROF)
- Application Data Profiler (DBMS_APPLICATION_INFO)
- Problem: There are hundreds of ways to implement a requirement. How can Lizbeth find the fastest ...
- Solution: Use DBMS_UTILITY.GET_TIME and GET_CPU_TIME for very granular analysis.
- Resources
-
+
Best Practices for High-Impact Tuning
- Problem: It worked so quickly in SQL*Plus. Why is it so slow in PL/SQL?
- Solution: Sometimes you have to help PL/SQL integrate with SQL.
- Problem: Jasper queries the same, unchanging data over and over and over again.
- Solution: If the normal SGA cache doesn’t the trick, look for other ways to cache.
- Problem: Users hate to wait.
- Solution: Design your function to return data while it is still running!
- And that’s not all!
-
+
Best Practices for Other Tuning
- Problem: Delaware wants to use collections as parameters, but they are causing a bottleneck.
- Solution: Turn off the default copying that comes with IN OUT arguments.
- Problem: Lizbeth lost her focus as she wrote her loop.
- Solution: Avoid executing anything inside a loop that doesn’t change in that loop.
- Problem: Oracle goes out of its way to make it easy for us to write PL/SQL programs.
- Solution: Avoid implicit conversions and choose your datatypes carefully.
-
+
Best Practices for Finding Slow Code
- Best Practices Quick Reference
- + Resources for PL/SQL Developers
- Index
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
Your free to read time expires in minutes. After that you have to pause for an hour.
Test the closed alpha on paperc.com
Book Details
Authors
Categories
Computers > Programming Languages > SQL
Publishers
Publication year : 2008
License: All rights reserved ©
Times read: 507
