Modify your code from question 6 to add two more exception handlers to trap the

Modify your code from question 6 to add two more

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
Image of page 5
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
Image of page 6
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?
Image of page 7
Image of page 8

  • Left Quote Icon

    Student Picture

  • Left Quote Icon

    Student Picture

  • Left Quote Icon

    Student Picture