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:
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.
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:
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.
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;
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?
How would you change the program in 4-26 to take advantage of the locally defined exceptions?
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;
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;
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?
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:
Why is she not seeing her second debug statement?
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?
Under what circumstances might a NO_DATA_FOUND exception be raised?
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.
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;
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?