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 Exceptions
Practice Activities
Vocabulary
Identify the vocabulary word for each definition below:
Predefine oracle server
errors
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 Oracle
error number. That allows you to refer to any Oracle
Server exception by name and to write a specific handler for it.
SQLERRM
Returns character data containing the message associated with
the error number
NON PREDEFINED
ORACLE 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.
SQLCODE
Returns 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?
