Oracle PL/SQL Programming: A Developer's Workbook

Chapter 4. Exception Handling

Unlike politicians, who can resort to tired phrases like “Mistakes were made,” PL/SQL developers must make sure their programs behave responsibly in the face of the unpredictable. For example, a database server can run out of memory; a user can attempt to insert a duplicate value for a primary key; a SELECT…INTO clause can return too many rows. You can use exception handlers to trap, or handle, these exceptions.

There are two steps to handling an exception:

1. Define the conditions that represent exceptions; you can supplement the extensive Oracle-supplied set (for example, DUP_VAL_ON_INDEX) by creating exceptions of your own (for example, PROFIT_TOO_LOW), by associating unnamed exceptions with your own names (via the EXCEPTION_INIT pragma), or even by defining your own unnamed exceptions.

2. Create an exception handling section in your code, where you associate a subset of named exceptions with corresponding blocks of code called handlers.

When an exception occurs (whether it’s generated by the system or you use the RAISE command to create it), program control immediately branches to the handler associated with that exception. If there is no handler for that particular exception (or no exception section at all), you have an unhandled exception, in which case the program terminates immediately and returns the error to the original caller. This chapter tests your ability to define your own named exceptions, create an exception section in your code, and understand how exceptions propagate from one block to the next.

Beginner

4-1.	Which of the following functions can you call to retrieve a text description of the most recent error that has occurred? SHOWERR SQLERRM SQL_ERROR_MESSAGE
4-2.	Change the following block so that it traps a NO_DATA_FOUND exception and displays a description of the current error: DECLARE my_flavor ice_cream.fav_flavor%TYPE; BEGIN SELECT fav_flavor INTO my_flavor FROM ice_cream WHERE name = USER; DBMS_OUTPUT.PUT_LINE ('I love ' || my_flavor || '!'); END;
4-3.	What are the two attributes of (pieces of information associated with) every exception? What third attribute is optional?
4-4.	Which of the following named exceptions are defined by PL/SQL? DATA_NOT_FOUND VALUE_ERROR NO_DATA_FOUND DIVIDE_BY_ZERO INVALID_NUMBER TOO_MANY_ROWS DUP_KEY_IN_INDEX CURSOR_OPEN VALUE_TOO_LARGE
4-5.	What is the only error that has two different error numbers?
4-6.	Where are the predefined, named exceptions defined?
4-7.	What is the error code and the error message of a user-defined exception?
4-8.	What are the only two positive error numbers used by Oracle?
4-9.	What does it mean for an exception to go “unhandled”?
4-10.	Do you have to supply an exception section in your programs and blocks of code?
4-11.	What special exception handler can you provide that will trap any error that is raised in a block of code?
4-12.	What do you see on your screen (a message delivered via DBMS_OUTPUT, or an unhandled exception, or perhaps nothing at all) when you execute this block? DECLARE my_dream VARCHAR2(5); BEGIN my_dream := 'JUSTICE'; END;
4-13.	What do you see on your screen (a message delivered via DBMS_OUTPUT, or an unhandled exception, or perhaps nothing at all) when you execute this block? DECLARE my_dream VARCHAR2(5); BEGIN BEGIN my_dream := 'JUSTICE'; EXCEPTION WHEN VALUE_ERROR THEN DBMS_OUTPUT.PUT_LINE ('Inner block'); END; DBMS_OUTPUT.PUT_LINE ('Dream deferred...'); END;
4-14.	What do you see on your screen (a message delivered via DBMS_OUTPUT, or an unhandled exception, or perhaps nothing at all) when you execute this block? DECLARE my_dream VARCHAR2(10) := 'JUSTICE'; BEGIN DECLARE reality VARCHAR2(3) := 'MILLIONS STARVE'; BEGIN my_dream := 'PEACE'; EXCEPTION WHEN VALUE_ERROR THEN DBMS_OUTPUT.PUT_LINE ('Inner block'); END; EXCEPTION WHEN VALUE_ERROR THEN DBMS_OUTPUT.PUT_LINE ('Outer block'); END;
4-15.	In each of the following examples, there is an error that prevents compilation. What’s wrong with each sample? EXCEPTION WHEN ANY THEN EXCEPTION WHEN VALUE_ERROR AND NO_DATA_FOUND THEN EXCEPTION WHEN OTHERS THEN NULL; WHEN VALUE_ERROR THEN NULL; BEGIN do_stuff; WHEN OTHERS THEN NULL; END; BEGIN ... EXCEPTION WHEN -1403 THEN NULL; END;
4-16.	How can you reraise the current exception from within an exception handler clause? Why would you want to do so?
4-17.	You write the following block of code, declaring your own local exception and handling it as well. Under what circumstances is the exception raised? FUNCTION big_name (name_in IN VARCHAR2) RETURN VARCHAR2 IS no_name EXCEPTION; name_in_caps VARCHAR2(100); BEGIN name_in_caps := UPPER (name_in); RETURN (name_in_caps); EXCEPTION WHEN no_name THEN DBMS_OUTPUT.PUT_LINE ('You must supply a name'); END;
4-18.	The following code was written by your coworker Kristopher, who is conveniently on vacation, when you are asked to enhance the code. The users want a specialized message displayed when an invalid value is supplied for the sex parameter: FUNCTION build_name (name_in IN VARCHAR2, sex_in IN VARCHAR2) RETURN VARCHAR2 IS name_out VARCHAR2(100); BEGIN IF first_char = 'M' THEN name_out := 'Mr. ' || name_in; ELSIF first_char = 'F' THEN name_out := 'Mrs. ' || name_in; END IF; RETURN (name_out); END; Add the code necessary to display the text “A valid sex must be provided” whenever the parameter sex_in is neither “M” nor “F”.
4-19.	Employee SMITH currently earns a salary of $800, and ALLEN earns a salary of $1,600. What will the salaries be for SMITH and ALLEN after the following anonymous block of code executes: BEGIN UPDATE EMP SET sal = sal * 2 where ename = 'SMITH'; RAISE VALUE_ERROR; UPDATE EMP SET sal = sal * 2 where ename = 'ALLEN'; EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE ('We had an error'); END;
4-20.	The following anonymous block of code is executed directly from SQL*Plus by the user SCOTT. What will the salaries be for SMITH and ALLEN be after its execution? BEGIN UPDATE EMP SET sal = sal * 2 where ename = 'SMITH'; RAISE VALUE_ERROR; UPDATE EMP SET sal = sal * 2 where ename = 'ALLEN'; END; /