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:
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.
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?
|
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?
|
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?
|
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; / |
4-21. | When an exception is raised in a block of code, a number of actions occur. Organize the following actions in the correct order, and identify those that do not occur:
| |||||||||
4-22. | The built-in RAISE_APPLICATION_ERROR accepts three parameters: the error number to be used, the text of the error message, and whether or not to clear the error stack. List all the restrictions on each of these parameters. | |||||||||
4-23. | The built-in RAISE_APPLICATION_ERROR can programmatically signal an error condition. Identify which of the following calls to RAISE_APPLICATION_ERROR are valid, and which are not. For those that are not valid, explain the problem:
| |||||||||
4-24. | True or false? After you call RAISE_APPLICATION_ERROR to raise an application-specific error, you can call the APPLICATION_ERROR_CODE and APPLICATION_ERROR_MESSAGE functions to retrieve the error number and string passed to RAISE_APPLICATION_ERROR. | |||||||||
4-25. | While talking with your boss, you mention casually how you think you could improve upon the current calc_commission function that was written by your predecessor. “If I were to write it,” you say, “I would make it more flexible and more bulletproof.” “What a great idea,” says your boss, “by the end of today, trap the condition when an employee has no sales (or negative sales), and display a custom message.” FUNCTION calc_commission (sales_in IN NUMBER) RETURN NUMBER IS commission NUMBER(5,2) := 0; BEGIN RETURN (sales_in * 0.1); END; | |||||||||
4-26. | The following function accepts a first and last name and returns a formatted version of the name: FUNCTION format_name ( surname_in IN VARCHAR2, given_name_in IN VARCHAR2) RETURN VARCHAR2 IS no_surname EXCEPTION; no_given_name EXCEPTION; formatted_name VARCHAR2(100); BEGIN IF surname_in IS NULL THEN raise no_surname; ELSIF given_name_in IS NULL THEN raise no_given_name; ELSE formatted_name := surname_in || ', ' || SUBSTR (given_name_in, 1, 1); END IF; RETURN (formatted_name); EXCEPTION WHEN OTHERS THEN RAISE_APPLICATION_ERROR (-20001, 'Invalid name supplied.'); END;How does the output differ when the surname is not supplied, as opposed to when the given name is not supplied? | |||||||||
4-27. | How would you change the program in 4-26 to take advantage of the locally defined exceptions? | |||||||||
4-28. | Which of the following code segments trap the exception raised and provide the most information about the error that occurred? What is the difference between the two code blocks, and which provides greater control? DECLARE bad_data EXCEPTION; BEGIN RAISE bad_data; EXCEPTION WHEN bad_data THEN DBMS_OUTPUT.PUT_LINE ('data was bad: ' || SQLCODE); END; DECLARE bad_data EXCEPTION; BEGIN RAISE bad_data; EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE ('data was bad: ' || SQLCODE); END; | |||||||||
4-29. | Your manager has been so impressed with the previous improvements you made to the calc_commission function that he is just brimming with new ideas to improve it. In fact, he has already promised these improvements to his boss, and she expects them for the release going out tomorrow morning. One new requirement is that the function should optionally accept a second parameter, representing the amount of commission to be applied. Another is that the commission should be specified as a percentage (0 to 100), and that the condition of negative or zero sales should be handled as a custom exception. If the rate is not supplied, assume 10% commission. The same message should be displayed in the sales error condition. Given the current version of the following calc_commission function, make the appropriate changes to satisfy the new requirements: FUNCTION calc_commission (sales_in IN NUMBER) RETURN NUMBER IS commission NUMBER(5,2) := 0; BEGIN IF sales_in <= 0 THEN RAISE_APPLICATION_ERROR (-20001, 'Pull your own weight'); ELSE RETURN (sales_in * 0.1); END IF; END; | |||||||||
4-30. | Laxmi of Technical Support at your company calls; a user has reported that the following program is failing. It “just suddenly stopped working,” and it works for some clients but not others: FUNCTION is_overdue (due_date IN DATE, paid_date IN DATE) RETURN BOOLEAN IS days_between NUMBER(2) := due_date - paid_date; BEGIN RETURN days_between > 30; EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE ('Error in is_overdue; Check input data.'); END;You are sure this module is being executed, but the error message is never displayed. Instead, when an error occurs, it goes unhandled, and the user sees this incomprehensible “dump”: SQL> DECLARE 2 gotAproblem BOOLEAN; 3 BEGIN 4 gotAproblem := is_overdue (SYSDATE+400, SYSDATE); 5 END; 6 / DECLARE * ERROR at line 1: ORA-06502: PL/SQL: numeric or value error: number precision too largeWhy isn’t the exception section trapping the error? Why is the error intermittent? How can you fix this program? | |||||||||
4-31. | Your friend Jennifer comes to you at 4:45 on Friday (as usual) and asks you to look at the following code: BEGIN BEGIN ... RAISE NO_DATA_FOUND; ... EXCEPTION WHEN VALUE_ERROR THEN DBMS_OUTPUT.PUT_LINE ('Value Error Alert!'); WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE ('Other Alert!'); END; IF SQLCODE != 0 THEN DBMS_OUTPUT.PUT_LINE ('We ran into some problems'); END IF; END;Jennifer can’t figure out why her second debug statement (“We ran into some problems”) is never being displayed. No matter what exception is raised, all she sees is: Other Alert!Why is she not seeing her second debug statement? | |||||||||
4-32. | Joe arrives on your doorstep with a puzzled look. “I keep getting this ORA-12154 error and have no idea what it is and what causes it.” What is the easiest way of obtaining a description of the Oracle error message? | |||||||||
4-33. | Under what circumstances might a NO_DATA_FOUND exception be raised? | |||||||||
4-34. | Consider the following program: This program could raise the NO_DATA_FOUND exception in one of three ways, yet the exception section can’t tell us “who did that”—which part of the code caused the problem. Rewrite this procedure so that the exception section contains three different handlers for each potential cause of this error. | |||||||||
4-35. | What do you see on your screen (a message delivered via DBMS_OUTPUT, or an unhandled exception, or no error at all) when you execute the following code segments? DECLARE string_of_5_chars VARCHAR2(5); BEGIN BEGIN string_of_5_chars := 'Daniel'; EXCEPTION WHEN VALUE_ERROR THEN RAISE NO_DATA_FOUND; WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE ('Inner block'); END; EXCEPTION WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE ('Outer block'); END; | |||||||||
4-36. | Suppose that you execute the following code segments: DECLARE counter INTEGER; BEGIN DBMS_OUTPUT.PUT('Starting test, '); BEGIN SELECT * INTO counter FROM dual WHERE 1 = 2; DBMS_OUTPUT.PUT('found data, '); EXCEPTION WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT('No data in inner block, '); RAISE; END; DBMS_OUTPUT.PUT_LINE ('Finished test.'); EXCEPTION WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE ('No data in outer block'); WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE ('Caught OTHER exception'); END;What do you see on your screen?
|
Expert
4-37. | Rewrite the following PL/SQL block so that it allows each of the following SQL DML statements to execute, even if any of the others fail: BEGIN UPDATE emp SET empno = 100 WHERE empno > 5000; DELETE FROM dept WHERE deptno = 10; DELETE FROM emp WHERE deptno = 10; END; |
4-38. | Write a PL/SQL block that handles by name the following Oracle error: ORA-01014: ORACLE shutdown in progress.The exception handler should display an appropriate message and then reraise the exception. |
4-39. | Which of the following uses of the EXCEPTION_INIT pragma are valid, which are invalid, and why?
|
4-40. | What shows up on your screen when you run the following block of code in your execution environment as a top-level PL/SQL block? Explain the behavior. DECLARE d VARCHAR2(1); no_data_found EXCEPTION; BEGIN SELECT dummy INTO d FROM dual WHERE 1=2; IF d IS NULL THEN RAISE no_data_found; END IF; EXCEPTION WHEN no_data_found THEN DBMS_OUTPUT.PUT_LINE ('Trapped the error!?'); END; |
4-41. |
|
4-42. | First, compile the valerr package as shown here (can you imagine a more poorly named function?): Then call DBMS_OUTPUT.PUT_LINE to display the value returned by the valerr.get function as follows: SQL> EXEC DBMS_OUTPUT.PUT_LINE ('Value of v is ' || valerr.get);What information is displayed on the screen? What is displayed if you execute the same line of code a second time? |
4-43. | You are working on a program that requires exclusive access to data in the EMP table. You attempt to lock the rows using a SELECT FOR UPDATE. If you cannot immediately acquire the lock, you should simply print a message to try again later. If you do acquire the lock, print the name of each employee using a loop. |
4-44. | While helping a friend time his new program, you decide to calculate the elapsed time from executing the code as follows (note that you are better off using DBMS_UTILITY.GET_TIME instead of SYSDATE): DECLARE start_time DATE; end_time DATE; BEGIN start_time := SYSDATE; DBMS_OUTPUT.PUT_LINE ('Filler code...'); end_time := SYSDATE; DBMS_OUTPUT.PUT_LINE (end_time - start_time); END;When you execute this block, you receive the following output, but you can successfully execute the new procedure on its own: * ERROR at line 1: ORA-00604: error occurred at recursive SQL level 1 ORA-01422: exact fetch returns more than requested number of rows ORA-06512: at "SYS.STANDARD", line 1027What could be the problem? |
4-45. | Just about the most important information you can obtain about an error is the line number on which the error is raised. Which of the following statements accurately describes ways to obtain this information?
|
4-46. | You have been asked to record all errors that occur in your application so they can be examined later. Identify all the different forms your error log can take inside a PL/SQL environment. |
4-47. | You have decided to write your error information to a database table. You create a procedure to write to the log as follows: You then test the log mechanism as follows: SQL> DECLARE 2 myval NUMBER; 3 BEGIN 4 myval := 'abc'; 5 EXCEPTION 6 WHEN OTHERS 7 THEN 8 logpkg.putline (SQLCODE, SQLERRM); 9 RAISE; 10 END; 11 / DECLARE * ERROR at line 1: ORA-06502: PL/SQL: numeric or value error ORA-06512: at line 9But there is nothing in your log! SQL> SELECT count(*) FROM logtab; COUNT(*) --------- 0Why didn’t the log package insert a row into the log table? |
4-48. | Associate the type of error log in the first list with the characteristics in the second list: Type of error log:
Characteristics:
|
4-49. | Write a procedure that raises any error number passed to it, whether it be an Oracle error number (like ORA-01855), an application-specific error (in the –20,XXX range), or your own positive error numbers. (Perhaps you did not want to be constrained by the 1,000 error numbers Oracle offers. You noticed, instead, that the only positive numbers used for error handling are 1 and 100, leaving an awful lot for you.) |