Oracle Pl Sql Error Handling Best Practices
Contents |
Magazine Online 2016 2015 2014 2013 2012 2011 2010 As Published In July/August 2003 TECHNOLOGY: PL/SQL Handling Exceptional Behavior, Part II By Steven Feuerstein Handle PL/SQL exceptions with best practices. In the May/June 2003 issue of Oracle Magazine, I offered suggestions for both an overall
Pl/sql Logging Best Practices
exception handling strategy and best practices for raising exceptions in your programs. In this article, I pl sql exception handling examples complete my treatment of error handling in PL/SQL, with a look at how best to handle exceptions once they have been raised. For
Oracle Function Exception Return
handling exceptions, there are two main considerations: 1. Deciding which errors should be handled and which can go unhandled in any given block of code. 2. Constructing reusable code elements that allow the handling (and logging) of errors in oracle function exception return null consistent, useful ways. I touch on both of these topics in the following best-practice recommendations. Handle Exceptions That Cannot Be Avoided If you are writing a program in which you can predict that a certain error will occur, you should include a handler in your code for that, allowing for a graceful and informative failure. The form this failure takes does not, by the way, necessarily need to be an exception. When writing functions, you may well decide that exception handling in oracle 11g in the case of certain exceptions, you will want to return a value such as NULL, rather than allow an exception to propagate out of the function. This recommendation is easy to demonstrate with the ubiquitous SELECT INTO lookup query. An error that often occurs is NO_DATA_FOUND , indicating that the query did not identify any rows. In the following function, book_title , I put my SELECT INTO inside a function, but I do not allow the NO_DATA_FOUND exception to propagate out of the function: CREATE OR REPLACE FUNCTION book_title ( isbn_in IN book.isbn%TYPE) RETURN book.title%TYPE IS l_title book.title%TYPE; BEGIN SELECT title INTO l_title FROM book WHERE isbn =isbn_in; RETURN l_rec.title; EXCEPTION WHEN NO_DATA_FOUND THEN RETURN NULL; END; In other words, if the ISBN passed to the function finds no book, return NULL for the title. This is an unambiguous indicator of failure; a book must have a title. I have decided in this case not to allow NO_DATA_FOUND to propagate (unhandled) out of the function. I use a SELECT INTO (an implicit query) to fetch the book title; Oracle's implementation of implicit queries means that NO_DATA_FOUND (as well as TOO_MANY_ROWS ) might be raised. That doesn't mean, however, that within my function, it really is an exception when no row is found. In fact, I might be expecting to not find a match. By returning NULL rather than propagating an exception, I
errors. The latter are called exceptions. Note: The language of warning and error messages depends on the NLS_LANGUAGE parameter. For information about
Pl/sql Exception When Others
this parameter, see Oracle Database Globalization Support Guide. Topics Compile-Time Warnings Overview pl sql logging example of Exception Handling Internally Defined Exceptions Predefined Exceptions User-Defined Exceptions Redeclared Predefined Exceptions Raising Exceptions Explicitly Exception Propagation
Oracle Pl Sql Best Practices
Unhandled Exceptions Error Code and Error Message Retrieval Continuing Execution After Handling Exceptions Retrying Transactions After Handling Exceptions See Also: "Exception Handling in Triggers" "Handling FORALL Exceptions After FORALL Statement Completes" http://www.oracle.com/technetwork/issue-archive/o43plsql-089319.html Tip: If you have problems creating or running PL/SQL code, check the Oracle Database trace files. The USER_DUMP_DEST initialization parameter specifies the current location of the trace files. You can find the value of this parameter by issuing SHOW PARAMETER USER_DUMP_DEST. For more information about trace files, see Oracle Database Performance Tuning Guide. Compile-Time Warnings While compiling stored PL/SQL units, the PL/SQL https://docs.oracle.com/cd/E11882_01/appdev.112/e25519/errors.htm compiler generates warnings for conditions that are not serious enough to cause errors and prevent compilation—for example, using a deprecated PL/SQL feature. To see warnings (and errors) generated during compilation, either query the static data dictionary view *_ERRORS (described in Oracle Database Reference) or, in the SQL*Plus environment, use the command SHOW ERRORS. The message code of a PL/SQL warning has the form PLW-nnnnn. For the message codes of all PL/SQL warnings, see Oracle Database Error Messages. Table 11-1 summarizes the categories of warnings. Table 11-1 Compile-Time Warning Categories Category Description Example SEVERE Condition might cause unexpected action or wrong results. Aliasing problems with parameters PERFORMANCE Condition might cause performance problems. Passing a VARCHAR2 value to a NUMBER column in an INSERT statement INFORMATIONAL Condition does not affect performance or correctness, but you might want to change it to make the code more maintainable. Code that can never run By setting the compilation parameter PLSQL_WARNINGS, you can: Enable and disable all warnings, one or more categories of warnings, or specific warnings Treat specific warnings as errors (so that those conditions must be c
Best Practices by Steven Feuerstein Published by O'Reilly Media, Inc. Oracle PL/SQL Best Practices SPECIAL OFFER: Upgrade this ebook with O’Reilly A Note Regarding https://www.safaribooksonline.com/library/view/oracle-plsql-best/0596001215/apas05.html Supplemental Files Dedication Preface Structure of This Book How to Use This https://www.tutorialspoint.com/plsql/plsql_exceptions.htm Book Not All 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 guidelines before writing any code. DEV-02: Ask for help after 30 minutes on a problem. DEV-03: Walk through pl sql 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 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 oracle pl sql 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 SQL in PL/SQL SQL-00: Establish and follow clear rules for how to write SQL in your application. 6.1. General SQL and Transaction Management 6.2. Querying Data from PL/SQL 6.3. Changing Data from PL/SQL 6.4. Dynamic SQL and Dynamic PL/SQL 7. Program Construction 7.1. Structure and Parameters 7.2. Functions 7.3. Triggers 8. Package Construction PKG
Basic Syntax PL/SQL - Data Types PL/SQL - Variables PL/SQL - Constants PL/SQL - Operators PL/SQL - Conditions PL/SQL - Loops PL/SQL - Strings PL/SQL - Arrays PL/SQL - Procedures PL/SQL - Functions PL/SQL - Cursors PL/SQL - Records PL/SQL - Exceptions PL/SQL - Triggers PL/SQL - Packages PL/SQL - Collections PL/SQL - Transactions PL/SQL - Date & Time PL/SQL - DBMS Output PL/SQL - Object Oriented PL/SQL Useful Resources PL/SQL - Questions and Answers PL/SQL - Quick Guide PL/SQL - Useful Resources PL/SQL - Discussion Selected Reading Developer's Best Practices Questions and Answers Effective Resume Writing HR Interview Questions Computer Glossary Who is Who PL/SQL - Exceptions Advertisements Previous Page Next Page An error condition during a program execution is called an exception in PL/SQL. PL/SQL supports programmers to catch such conditions using EXCEPTION block in the program and an appropriate action is taken against the error condition. There are two types of exceptions: System-defined exceptions User-defined exceptions Syntax for Exception Handling The General Syntax for exception handling is as follows. Here you can list down as many as exceptions you want to handle. The default exception will be handled using WHEN others THEN: DECLARE