67%(9)6 out of 9 people found this document helpful
This preview shows page 5 - 8 out of 17 pages.
7.Modify your code from question 6 to add two more exception handlers to trap the possible exceptions individually. Use NO_DATA_FOUND and TOO_MANY_ROWS. Re-run the block three times, using 10, 20, and 30 as before. Observe the message displayed in each case. 8.List three guidelines for trapping exceptions. 9.Enter and run the following PL/SQL block. Explain the output. Note: the WHEN OTHERS handler successfully handles any type of exception which occurs. DECLARE
v_number NUMBER(2); BEGIN v_number := 9999; EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('An exception has occurred'); END; 10.Modify the block in question 9 to omit the exception handler, then re-run the block. Explain the output. 11.Enter and run the following code and explain the output. DECLARE v_number NUMBER(4); BEGIN v_number := 1234; DECLARE v_number NUMBER(4); BEGIN v_number := 5678; v_number := 'A character string'; END; EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('An exception has occurred'); DBMS_OUTPUT.PUT_LINE('The number is: ' || v_number); END; Database Programming with PL/SQL
7-2: Trapping Oracle Server ExceptionsPractice Activities Vocabulary Identify the vocabulary word for each definition below: Predefine oracle servererrors Each of these has a predefined name. For example, if the error ORA-01403 occurs when no rows are retrieved from the database in a SELECT statement, then PL/SQL raises the predefined exception-name NO_DATA_FOUND. Pragma exception_init Tells the compiler to associate an exception name with an Oracleerror number. That allows you to refer to any Oracle Server exception by name and to write a specific handler for it. SQLERRMReturns character data containing the message associated with the error number NON PREDEFINEDORACLE EXCEPTINS Each of these has a standard Oracle error number (ORA-nnnnn) and error message, but not a predefined name. We declare our own names for these so that we can reference these names in the exception section. SQLCODEReturns the numeric value for the error code (You can assign it to a NUMBER variable.) Try It / Solve It 1What are the three types of exceptions that can be handled in a PL/SQL block?