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: | Control is passed to the exception section, if it exists. | | Oracle goes to the package STANDARD to determine how to handle the error. | | The executable section terminates immediately. | | If there is no match and no WHEN OTHERS clause, propagate the exception out to the enclosing block. | | If after executing handler code, an exception is raised (or reraised), propagate the exception out to the enclosing block. | | If there is no match for the error, check for a WHEN OTHERS section and execute that handler code. | | If after executing handler code, an exception is raised (or reraised), and there is no enclosing block, propagate the exception out to the calling environment. | | Oracle logs the exception to the alert log for the database. | | If there is an exception section, find a match for the error and execute that handler code. |
|
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: RAISE_APPLICATION_ERROR (20001, ‘Invalid employee number!’);
RAISE_APPLICATION_ERROR (‘Invalid Social Security Number.’);
RAISE_APPLICATION_ERROR (NO_DATA_FOUND);
RAISE_APPLICATION_ERROR (-20023, ‘Date must not be in the future’);
RAISE_APPLICATION_ERROR (-21001, ‘Invalid date.’);
RAISE_APPLICATION_ERROR (-20002, ‘Invalid product code.’, ‘TRUE’);
RAISE_APPLICATION_ERROR (-20002, ‘Invalid product code.’, FALSE);
|
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 large Why 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: 
/* Filename on web page: whodidthat.sql */
CREATE OR REPLACE PROCEDURE who_did_that (
emp_in IN emp.empno%TYPE)
IS
v_ename emp.ename%TYPE;
line VARCHAR2(1023);
fid UTL_FILE.FILE_TYPE;
list_of_numbers PLVtab.number_table;
BEGIN
SELECT ename INTO v_ename
FROM emp
WHERE empno = emp_in;
DBMS_OUTPUT.PUT_LINE (v_ename);
fid := UTL_FILE.FOPEN ('c:\temp', 'notme.sql', 'R');
UTL_FILE.GET_LINE (fid, line);
UTL_FILE.GET_LINE (fid, line);
IF list_of_numbers (100) > 0
THEN
DBMS_OUTPUT.PUT_LINE ('Positive value at row 100');
END IF;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
DBMS_OUTPUT.PUT_LINE ('Who did that?');
END who_did_that;
/
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? Starting test, found data, Finished test
Starting test, No data in inner block, Finished test
Starting test, No data in inner block, No data in outer block
Starting test, No data in inner block, Caught OTHER exception
Starting test, No data in inner block, No data in outer block, Finished test
|
