Feuerstein Error
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
Oracle Pl Sql Error Handling Best Practices
for both an overall exception handling strategy and best practices for raising exceptions in pl sql exception handling examples your programs. In this article, I complete my treatment of error handling in PL/SQL, with a look at how best to handle exceptions quest error manager 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 given block of code. 2. Constructing reusable code elements
Pl Sql Logging Example
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 and informative failure. The form this failure takes does not, by the way,
Pl/sql Logging Best Practices
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; 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
in the declaration section are not handled in the exception section. This sometimes surprises a developer new to PL/SQL. The exception section of a PL/SQL block can oracle pl sql error handling package only possibly handle an exception raised in the executable section. An exception raised
How To Find Which Line Error Was Raised In Oracle
in the declaration section (in an attempt to assign a default value to a variable or constant) always propagates out dbms_errlog unhandled to the enclosing block. Verify onLiveSQL Exceptions Raised in Declaration Section Not Handled Locally 2. An exception raised does not automatically roll back uncommitted changes to tables. Any non-query DML statements that http://www.oracle.com/technetwork/issue-archive/o43plsql-089319.html complete successfully in your session are not rolled back when an exception occurs - either directly in PL/SQL or propagated out from the SQL engine. You still have the option of either committing or rolling back yourself. If, however, the exception goes unhandled out to the host environment, a rollback almost always occurs (this is performed by the host environment). Verify onLiveSQL Exceptions Do Not Rollback http://stevenfeuersteinonplsql.blogspot.com/2016/03/nine-good-to-knows-about-plsql-error.html Uncommitted Changes 3. You can (and should!) name those unnamed ORA errors (never hard-code an error number). Oracle Database pre-defines a number of exceptions for common ORA errors, such as NO_DATA_FOUND and VALUE_ERROR. But there a whole lot moreerrors for which there is no pre-defined name. And some of these can be encountered quite often in code. The key thing for developers is to avoid hard-coding these error numbers in your code. Instead, use the EXCEPTION_INIT pragma to assign a name for that error code, and then handle it by name. Verify onLiveSQL Generate Named Exceptions Use EXCEPTION_INIT to Give Names to Un-named Oracle Errors 4. If you do not re-raise an exception in your exception handler, the outer block doesn't know an error has occurred. Just sayin'. You have a subprogram that invokes another subprogram (or nested block). That "inner" subprogram fails with an exception. It contains an exception handler. It logs the error, but then neglects to re-raise that exception (or another). Control passes out to the invoking subprogram, and it continues executing statements, completely unaware that an error occurred in that inner block. Which means, by the way, that a call to SQLCODE will
Occured:'||SQLERRM);why??? I don't get it. Is it becausea) you don't want to know what line really caused the error?b) you get paid by the number of lines of code you write?c) http://tkyte.blogspot.com/2008/01/why-do-people-do-this.html you want to spend lots of time looking up the actual error code the you just lost?Why is it that everyone seems to feel "I must catch all exceptions". I cannot understand this, I do not see the point, I only see this doing HARM, never any good. Why take a perfectly good error code/message and totally destroy it?This goes to people that turn exceptions into "return codes", masking the error, why??? posted by pl sql Thomas Kyte at 7:22 AM POST A COMMENT 63 Comments: Adrian said.... I couldn't agree more... Tue Jan 29, 08:01:00 AM EST Anonymous said.... [SYSADM@PSDB]> create table hx_a (no integer);Table created.[SYSADM@PSDB]> create unique index hx_a_idx on hx_a(no);Index created.my way....[SYSADM@PSDB]> editWrote file afiedt.buf 1 begin 2 insert into hx_a values(1); 3 insert into hx_a values(1); 4 exception 5 when others then 6 raise_application_error(-20001,'I caught it!!!:'||sqlerrm); 7* end;[SYSADM@PSDB]> /begin*ERROR at line 1:ORA-20001: I caught it!!!:ORA-00001: oracle pl sql unique constraint (SYSADM.HX_A_IDX)violatedORA-06512: at line 6tom's way...[SYSADM@PSDB]> editWrote file afiedt.buf 1 begin 2 insert into hx_a values(1); 3 insert into hx_a values(1); 4* end;[SYSADM@PSDB]> /begin*ERROR at line 1:ORA-00001: unique constraint (SYSADM.HX_A_IDX) violatedORA-06512: at line 3My way cant find for a million year which line actually caused the error...Learning process its never ending...People learn from mistakes.... Tue Jan 29, 08:05:00 AM EST Bill S. said.... My experience has been people do this to "provide the user with a more easily understood message".Frankly, I personally would rather give the users some simple instructions, like "when you get an error message, please write the ENTIRE MESSAGE DOWN and call the help desk BEFORE YOU CLICK ON ANYTHING!!".Bill S. Tue Jan 29, 08:19:00 AM EST Wiktor Moskwa said.... ...My experience has been people do this to "provide the user with a more easily understood message"...Bill, I don't want to believe that someone presents "unique constraint violated" or any other message straight from DB to end users...Most of such errors have some transactional/business meaning and others are bugs that should go straight to error report or to be sent directly by the program to the bug database. Tue Jan 29, 08:31:00 AM EST Eric said.... A case of 'almost right'?I agree it can be useful to give extra information of what has happen
be down. Please try the request again. Your cache administrator is webmaster. Generated Sat, 15 Oct 2016 16:10:07 GMT by s_wx1094 (squid/3.5.20)