Pl Sql Best Practices Error Handling
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 pl/sql logging best practices Oracle Magazine, I offered suggestions for both an overall exception handling strategy and best pl sql exception handling examples practices for raising exceptions in your programs. In this article, I complete my treatment of error handling in PL/SQL, with a
Oracle Function Exception Return
look at how best to handle exceptions once they have been raised. For handling exceptions, there are two main considerations: 1. Deciding which errors should be handled and which can go unhandled in any
Oracle Function Exception Return Null
given block of code. 2. Constructing reusable code elements that allow the handling (and logging) of errors in 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 exception handling in oracle 11g 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 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
July 2008 Oracle Magazine Online 2016 2015 2014 2013 2012 2011 2010 As Published In July/August 2008 DEVELOPER: PL/SQL Practices On Exceptions and Rules By Steven Feuerstein Best practices for where, when, and how to handle exceptions I
Pl/sql Exception When Others
recently learned that if an exception is raised in the declaration section of my pl sql logging example block, that block's exception section cannot handle the exception. That doesn't seem right. Why does PL/SQL work this way, and what oracle pl sql best practices does it mean for my coding practices? If an exception is raised in the declaration section of your block, that exception will propagate out of the block unhandled. PL/SQL behaves like this (or, to be http://www.oracle.com/technetwork/issue-archive/o43plsql-089319.html more accurate, the Oracle PL/SQL development team decided to implement exception handling like this) because until local variables and constants are fully elaborated, you don't have a viable subprogram with which to work. Suppose the declaration-raised exception were handled inside that subprogram. To what could you refer inside the exception handler? You couldn't be sure that any of your local variables were initialized. The key question is: How does this behavior http://www.oracle.com/technetwork/issue-archive/2008/08-jul/o48plsql-101224.html affect the way we should write our code? Before answering this question, let's explore when we are likely to encounter this issue. Exceptions in the declaration section occur when you try to initialize a variable declared in that section in a way that raises an exception. The most common exception raised surely must be ORA-06502 or VALUE_ERROR, which occurs (to name just two scenarios) when you try to assign a string value that is too large for the variable and when you try to assign a non-numeric value to a number. For example DECLARE l_name VARCHAR2(5) := 'STEVEN'; l_age NUMBER := '49 Years Old'; BEGIN This same rule for exceptions applies to initializing variables declared in a package (outside of any subprogram). If an exception occurs when you try to initialize a package-level variable, that exception will propagate unhandled out of the package, even if the initialization section contains an exception section. In such a situation, the PL/SQL runtime engine still registers the package as initialized and allows you to continue to reference subprograms and variables in the package. To understand this, consider this sequence of steps and PL/SQL statements: 1. I've compiled a package, valerr, that assigns a too-large value to a package-level string. The package body inc
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 - https://www.tutorialspoint.com/plsql/plsql_exceptions.htm 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 pl sql 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 pl sql best 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