Database Programming With PL/SQL

Database Programming withPL/SQL7-4Recognizing the Scope of ExceptionsCopyright 2016, Oracle and/or its affiliates. All rights reserved.

ObjectivesThis lesson covers the following objectives: Describe the scope of an exception Recognize an exception-scope issue when an exception iswithin nested blocks Describe the effect of exception propagation in nested blocksPLSQL S7L4Recognizing the Scope of ExceptionsCopyright 2016, Oracle and/or its affiliates. All rights reserved.3

Purpose You learned about nested blocks and scope of variables inan earlier lesson. An exception is a PL/SQL variable; therefore, it follows thesame scoping and visibility rules as any other kind ofvariable. To handle exceptions correctly, you must understand thescope and visibility of exception variables. This is particularly important when using nested blocks.PLSQL S7L4Recognizing the Scope of ExceptionsCopyright 2016, Oracle and/or its affiliates. All rights reserved.4

Exception Handling in Nested BlocksYou can deal with an exception by: Handling it (“trapping it”) in the block in which it occurs, or Propagating it to the calling environment (which can be ahigher-level block)ExceptionraisedIs theexceptiontrapped?noPropagateto callingenvironmentyesHandle withException handlerPLSQL S7L4Recognizing the Scope of ExceptionsCopyright 2016, Oracle and/or its affiliates. All rights reserved.5

Handling Exceptions in an Inner Block In this example, an error occurs during the execution of theinner block. The inner block’s EXCEPTION section deals with theexception successfully, and PL/SQL considers that thisexception is now finished. The outer block resumes execution as normal.BEGIN -- outer block.BEGIN -- inner block. -- exception name occurs here.EXCEPTIONWHEN exception name THEN -- handled here.END; -- inner block terminates successfully. -- outer block continues executionEND;PLSQL S7L4Recognizing the Scope of ExceptionsCopyright 2016, Oracle and/or its affiliates. All rights reserved.6

Propagating Exceptions to an Outer BlockIf the exception is raised in the executable section of theinner block and no corresponding exception handler exists,the PL/SQL block terminates with failure and the exception ispropagated to an enclosing block.ExceptionraisedIs theexceptiontrapped?noTerminateabruptlyPropagate theexceptionyesExecute statements in theEXCEPTION sectionPLSQL S7L4Recognizing the Scope of ExceptionsTerminategracefullyCopyright 2016, Oracle and/or its affiliates. All rights reserved.7

Propagating Exceptions to an Outer Block In this example, an exception occurs during the executionof the inner block. The inner block’s EXCEPTION section does not deal withthe exception.DECLARE-- outer blocke no rowsEXCEPTION;BEGINBEGIN-- inner blockIF . THEN RAISE e no rows; –- exception occurs here.END;-- Inner block terminates unsuccessfully.-- Remaining code in outer block’s executable.-- section is skippedEXCEPTIONWHEN e no rows THEN – outer block handles the exception.END;PLSQL S7L4Recognizing the Scope of ExceptionsCopyright 2016, Oracle and/or its affiliates. All rights reserved.8

Propagating Exceptions to an Outer Block The inner block terminates unsuccessfully and PL/SQLpasses (propagates) the exception to the outer block. The outer block’s EXCEPTION section successfullyhandles the exception.DECLARE-- outer blocke no rowsEXCEPTION;BEGINBEGIN-- inner blockIF . THEN RAISE e no rows; –- exception occurs here.END;-- Inner block terminates unsuccessfully.-- Remaining code in outer block’s executable.-- section is skippedEXCEPTIONWHEN e no rows THEN – outer block handles the exception.END;PLSQL S7L4Recognizing the Scope of ExceptionsCopyright 2016, Oracle and/or its affiliates. All rights reserved.9

Propagating Exceptions from a Sub-Block If a PL/SQL raises an exception and the current block does nothave a handler for that exception, the exception propagatesto successive enclosing blocks until it finds a handler. When the exception propagates to an enclosing block, theremaining executable actions in that block are bypassed. One advantage of this behavior is that you can enclosestatements that require their own exclusive error handling intheir own block, while leaving more general exceptionhandling (for example WHEN OTHERS) to the enclosingblock. The next slide shows an example of this.PLSQL S7L4Recognizing the Scope of ExceptionsCopyright 2016, Oracle and/or its affiliates. All rights reserved.10

Propagating Predefined Oracle ServerExceptions from a Sub-Block Employee id 999 does not exist. What is displayed when this code is executed?DECLAREv last nameemployees.last name%TYPE;BEGINBEGINSELECT last name INTO v last nameFROM employees WHERE employee id 999;DBMS OUTPUT.PUT LINE('Message 1');EXCEPTIONWHEN TOO MANY ROWS THENDBMS OUTPUT.PUT LINE('Message 2');END;DBMS OUTPUT.PUT LINE('Message 3');EXCEPTIONWHEN OTHERS THENDBMS OUTPUT.PUT LINE('Message 4');END;PLSQL S7L4Recognizing the Scope of ExceptionsCopyright 2016, Oracle and/or its affiliates. All rights reserved.11

Propagating User-named Exceptions: Ex. 1What happens when this code is executed?BEGINDECLAREe myexcepEXCEPTION;BEGINRAISE e myexcep;DBMS OUTPUT.PUT LINE('Message 1');EXCEPTIONWHEN TOO MANY ROWS THENDBMS OUTPUT.PUT LINE('Message 2');END;DBMS OUTPUT.PUT LINE('Message 3');EXCEPTIONWHEN e myexcep THENDBMS OUTPUT.PUT LINE('Message 4');END;PLSQL S7L4Recognizing the Scope of ExceptionsCopyright 2016, Oracle and/or its affiliates. All rights reserved.12

Scope of Exception Names Predefined Oracle server exceptions, such asNO DATA FOUND, TOO MANY ROWS, and OTHERS arenot declared by the programmer. They can be raised in any block and handled in any block. User-named exceptions (non-predefined Oracle serverexceptions and user-defined exceptions) are declared bythe programmer as variables of type EXCEPTION. They follow the same scoping rules as other variables.PLSQL S7L4Recognizing the Scope of ExceptionsCopyright 2016, Oracle and/or its affiliates. All rights reserved.13

Scope of Exception Names Therefore, a user-named exception declared within aninner block cannot be referenced in the exception sectionof an outer block. To avoid this, always declare user-named exceptions in theoutermost block.PLSQL S7L4Recognizing the Scope of ExceptionsCopyright 2016, Oracle and/or its affiliates. All rights reserved.14

Propagating User-named Exceptions: Ex. 2Now what happens when this code is executed?DECLAREe myexcepEXCEPTION;BEGINBEGINRAISE e myexcep;DBMS OUTPUT.PUT LINE('Message 1');EXCEPTIONWHEN TOO MANY ROWS THENDBMS OUTPUT.PUT LINE('Message 2');END;DBMS OUTPUT.PUT LINE('Message 3');EXCEPTIONWHEN e myexcep THENDBMS OUTPUT.PUT LINE('Message 4');END;PLSQL S7L4Recognizing the Scope of ExceptionsCopyright 2016, Oracle and/or its affiliates. All rights reserved.15

Propagating User-named Exceptions: Ex. 3What happens when this code is executed?DECLAREe myexcepEXCEPTION;BEGINBEGINRAISE e myexcep;DBMS OUTPUT.PUT LINE('Message 1');EXCEPTIONWHEN TOO MANY ROWS THENDBMS OUTPUT.PUT LINE('Message 2');END;DBMS OUTPUT.PUT LINE('Message 3');EXCEPTIONWHEN NO DATA FOUND THENDBMS OUTPUT.PUT LINE('Message 4');END;PLSQL S7L4Recognizing the Scope of ExceptionsCopyright 2016, Oracle and/or its affiliates. All rights reserved.16

Propagating Unhandled Exceptions to theCalling Environment If a raised exception is not handled in any block, theoutermost block is exited with the exception still raised. The calling environment, for example Application Express,must then try to handle the exception. Because Application Express is Oraclesoftware and therefore understandsPL/SQL exceptions, Application Expresswill display an error message.PLSQL S7L4Recognizing the Scope of ExceptionsCopyright 2016, Oracle and/or its affiliates. All rights reserved.17

Propagating Unhandled Exceptions to theCalling Environment But other applications cannot always do this, and may failwith unexpected errors. To avoid this, always handle exceptions within PL/SQL. One way to guarantee this is to alwaysinclude a WHEN OTHERS handler inthe outermost block.PLSQL S7L4Recognizing the Scope of ExceptionsCopyright 2016, Oracle and/or its affiliates. All rights reserved.18

TerminologyKey terms used in this lesson included: Exception scope Exception visibility Propagation of exceptionsPLSQL S7L4Recognizing the Scope of ExceptionsCopyright 2016, Oracle and/or its affiliates. All rights reserved.19

SummaryIn this lesson, you should have learned how to: Describe the scope of an exception Recognize an exception-scope issue when an exception iswithin nested blocks Describe the effect of exception propagation in nested blocksPLSQL S7L4Recognizing the Scope of ExceptionsCopyright 2016, Oracle and/or its affiliates. All rights reserved.20

User-named exceptions (non- predefined Oracle server exceptions and user -defined exceptions) are declared by the programmer as variables of type EXCEPTION.