Oracle PL/SQL Programming: A Developer's Workbook

Chapter 4. Exception Handling

Beginner

Q:
4-1.	The built-in SQLERRM (b) displays the error text associated with a particular error. If it is used without any arguments, it returns a description of the most recently raised error. If an error code is provided, it returns the description of that error, as illustrated here: SQL> EXEC DBMS_OUTPUT.PUT_LINE (SQLERRM (-1855)) ORA-01855: AM/A.M. or PM/P.M. required
Q:
4-2.	To solve this problem, you need to: Add an exception section to the end of the block. Include a handler for the NO_DATA_FOUND exception. Call the SQLERRM function to retrieve the error message. Here is the modified block: 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 || '!'); EXCEPTION WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE (SQLERRM); END;
Q:
4-3.	Each exception has associated with it an error number and an error description or message. An optional attribute is the name of an exception; not all exceptions have a name.
Q:
4-4.	These exceptions are predefined by Oracle in the STANDARD package: VALUE_ERROR, NO_DATA_FOUND, INVALID_NUMBER, TOO_MANY_ROWS. These identifiers do not name predefined exceptions: DATA_NOT_FOUND (should be NO_DATA_FOUND), DIVIDE_BY_ZERO (should be ZERO_DIVIDE), DUP_KEY_IN_INDEX (should be DUP_VAL_ON_INDEX), CURSOR_OPEN (should be CURSOR_ALREADY_OPEN), and VALUE_TOO_LARGE (should be VALUE_ERROR).
Q:
4-5.	The NO_DATA_FOUND exception is represented by ...