Pl Sql Error Handling Best Practices
Contents |
Magazine Online 2016 2015 2014 2013 2012 2011 2010 As Published In May/June 2003 TECHNOLOGY: PL/SQL Managing Exceptional Behavior, Part 1 By Steven Feuerstein Handle PL/SQL exceptions with pl/sql logging best practices best practices. Many programmers don't take the time to properly bulletproof their pl sql exception handling examples programs. Most of us find it hard enough—and more than enough work—to simply write the code that implements
Oracle Function Exception Return
the positive aspects of an application: maintaining customers, generating invoices, and so on. You write applications that often assume the best of all possible worlds, hoping that your programs are bug-free,
Oracle Function Exception Return Null
that users will enter only the correct data in only the correct fashion, and that all systems (hardware and software) will always be a "go." No matter how hard you try, there will always be one more bug in your application. And your users will always find just the right sequence of keystrokes it takes to make a screen implode. The exception handling in oracle 11g reality is clear: Either you spend the time up front to properly debug and bulletproof your programs, or you will fight an unending series of rear-guard battles, taking frantic calls from your users and putting out the fires. Fortunately, PL/SQL offers a powerful and flexible way to trap and handle errors in your programs. It is entirely feasible within the PL/SQL language to build an application that fully protects the user and the database from errors. And if you go about it correctly, you minimize the amount of code you need to write to get the job done. This article, the first of two parts, will explore a set of best practices you should follow to incorporate high-quality, comprehensive error handling in your PL/SQL-based applications. This article will start by considering some overall best practices for error handling and then focus on best practices relating to raising exceptions. The next article will move on to how best to handle exceptions. Set Guidelines before You Start Before you start writing any application code, you first need to decide on your overall str
Best Practices by Steven Feuerstein Published by O'Reilly Media, Inc. Oracle PL/SQL Best Practices SPECIAL
Pl/sql Exception When Others
OFFER: Upgrade this ebook with O’Reilly A Note Regarding Supplemental pl sql logging example Files Dedication Preface Structure of This Book How to Use This Book Not All Best Practices oracle pl sql best practices Are Created Equal About the Code Other Resources Conventions Used in This Book Comments and Questions Acknowledgments 1. The Development Process DEV-01: Set standards and http://www.oracle.com/technetwork/issue-archive/o33plsql-083386.html guidelines before writing any code. DEV-02: Ask for help after 30 minutes on a problem. DEV-03: Walk through each other's code. DEV-04: Validate standards against source code in the database. DEV-05: Generate code whenever possible and appropriate. DEV-06: Set up and use formal unit testing procedures. DEV-07: Get independent testers for functional https://www.safaribooksonline.com/library/view/oracle-plsql-best/0596001215/apas05.html sign-off. 2. Coding Style and Conventions STYL-01: Adopt a consistent, readable format that is easy to maintain. STYL-02: Adopt logical, consistent naming conventions for modules and data structures. STYL-03: Standardize module and program headers. STYL-04: Tag module END statements with module names. STYL-05: Name procedures with verb phrases and functions with noun phrases. STYL-06: Self-document using block and loop labels. STYL-07: Express complex expressions unambiguously using parentheses. STYL-08: Use vertical code alignment to emphasize vertical relationships. STYL-09: Comment tersely with value-added information. STYL-10: Adopt meaningful naming conventions for source files. 3. Variables and Data Structures 3.1. Declaring Variables and Data Structures 3.2. Using Variables and Data Structures 3.3. Declaring and Using Package Variables 4. Control Structures 4.1. Conditional and Boolean Logic 4.2. Loop Processing 4.3. Miscellaneous 5. Exception Handling EXC-00: Set guidelines for application-wide error handling before you start coding. 5.1. Raising Exceptions 5.2. Handling Exceptions 5.3. Declaring Exceptions 6. Writing SQ
here for a quick overview of the site Help Center Detailed answers to any questions you might have Meta Discuss the http://stackoverflow.com/questions/7360565/pl-sql-exception-handling-in-procedures workings and policies of this site About Us Learn more about Stack Overflow the company Business Learn more about hiring developers or posting ads with us Stack Overflow Questions Jobs Documentation Tags Users Badges Ask Question x Dismiss Join the Stack Overflow Community Stack Overflow is a community of 6.2 million programmers, just like you, helping each other. Join pl sql them; it only takes a minute: Sign up PL/SQL Exception handling in procedures up vote 1 down vote favorite 2 This is kind of a question about best practices. I have a PL/SQL block similar to this DECLARE --work variables PROCEDURE p1(in_parameter1, out_parameter1, out_parameter2...) IS BEGIN --do stuff --select ( ... ) into ( ... ) from t1 where oracle function exception ( ... ) END; PROCEDURE p2(in_parameter1, out_parameter1, out_parameter2...) IS BEGIN --do stuff --insert/update tables --do more stuff END; BEGIN -- MAIN PROCESS STARTS HERE open c1; fetch c1 into c1RowData; EXIT WHEN c1%NOTFOUND --call procedure1 --do stuff --call procedure2 --do stuff --do stuff --call procedure1 --call procedure2 END; / EXIT; The statements in procedures p1 and p2 could possibly raise exceptions ( NO_DATA_FOUND, DUP_VAL_ON_INDEX, ... ). What do you think is the best way to handle this exceptions? Should they be handled inside the procedures or do you think that I should surround every call the procedures in the main body with a TRY-CATCH block? stored-procedures exception-handling plsql share|improve this question edited Sep 10 '11 at 19:56 APC 87.3k1384184 asked Sep 9 '11 at 10:52 bruno 1,798816 add a comment| 3 Answers 3 active oldest votes up vote 6 down vote accepted Personally I'd catch them inside the procedures they're thrown from. This means that you have much more control with regards to how they're handled externally. For example, you can throw them again a
be down. Please try the request again. Your cache administrator is webmaster. Generated Mon, 24 Oct 2016 05:53:16 GMT by s_wx1196 (squid/3.5.20)