In PL/SQL, an error condition is called an exception. Exceptions can be internally defined (by the runtime system) or user-defined. Examples of internally defined exceptions include division by zero and out of memory. Show
Guidelines for Avoiding and Handling PL/SQL Errors and Exceptions
Carefully consider whether each exception handler should commit the transaction, roll it back, or let it continue. Go for this in-depth job-oriented PL/SQL Training Course now!Defining Your Own PL/SQL ExceptionsPL/SQL lets you define exceptions of your own. Unlike predefined exceptions, user-defined exceptions must be declared and must be raised explicitly by RAISE statements. Declaring PL/SQL ExceptionsExceptions can be declared only in the declarative part of a PL/SQL block, subprogram, or package. You declare an exception by introducing its name, followed by the keyword EXCEPTION. In the following example, you declare an exception named past_due: DECLARE past_due EXCEPTION;Scope Rules for PL/SQL ExceptionsExceptions declared in a block are considered local to that block and global to all its sub-blocks. Because a block can reference only local or global exceptions, enclosing blocks cannot reference exceptions declared in a sub-block. Defining Your Own Error Messages:Procedure RAISE_APPLICATION_ERRORThe procedure RAISE_APPLICATION_ERROR lets you issue user-defined ORA- error messages from stored subprograms. That way, you can report errors to your application and avoid returning unhandled exceptions. Come to Intellipaat’s PL/SQL Community if you have more queries on PL/SQL! Raising Exceptions with the RAISE StatementPL/SQL blocks and subprograms should raise an exception only when an error makes it undesirable or impossible to finish processing. You can place RAISE statements for a given exception anywhere within the scope of that exception. How PL/SQL Exceptions PropagateWhen an exception is raised, if PL/SQL cannot find a handler for it in the current block or subprogram, the exception propagates. That is, the exception reproduces itself in successive enclosing blocks until a handler is found or there are no more blocks to search. If no handler is found, PL/SQL returns an unhandled exception error to the host environment. Handling Raised PL/SQL ExceptionsWhen an exception is raised, normal execution of your PL/SQL block or subprogram stops and control transfers to its exception-handling part, which is formatted as follows: EXCEPTION WHEN exception1 THEN -- handler for exception1 sequence_of_statements1 WHEN exception2 THEN -- another handler for exception2 sequence_of_statements2 ... WHEN OTHERS THEN -- optional handler for all other errors sequence_of_statements3 END;Overview of PL/SQL Compile-Time WarningsTo make your programs more robust and avoid problems at run time, you can turn on checking for certain warning conditions. These conditions are not serious enough to produce an error and keep you from compiling a subprogram. They might point out something in the subprogram that produces an undefined result or might create a performance
problem. Check out the top PL/SQL Interview Questions to learn what is expected from PL/SQL professionals! PL/SQL Warning CategoriesPL/SQL warning messages are divided into categories so that you can suppress or display groups of similar warnings during compilation. The categories are:
What happens if an exception is raised and there are no handler for it in the current block?Handlers in the current block cannot catch the raised exception because an exception raised in a declaration propagates immediately to the enclosing block.
What happens if exceptions are not handled?When an exception occurred, if you don't handle it, the program terminates abruptly and the code past the line that caused the exception will not get executed.
What is exception handling what will happen if an exception is thrown for which no matching catch () block is defined?If there is no catch block at the current scope matching the thrown exception, the current scope is exited, and all automatic (local nonstatic) objects defined in that scope are destroyed. The surrounding scope (which might be function scope) is checked for a matching handler.
When Value_error exception is raised?In procedural statements, VALUE_ERROR is raised if the conversion of a character string into a number fails. (In SQL statements, INVALID_NUMBER is raised.) A program attempts to divide a number by zero.
|