In preparation for this exercise run the following SQL statement to create an

In preparation for this exercise run the following

This preview shows page 11 - 15 out of 17 pages.

1. In preparation for this exercise, run the following SQL statement to create an error-logging table: CREATE TABLE error_log (who VARCHAR2(30), when DATE, error_code NUMBER(6), error_message VARCHAR2(255)); Modify your PL/SQL block from question 5 to remove the four explicit exception handlers, replacing them with a single WHEN OTHERS handler. The handler should INSERT a row into the error_log table each time an exception is raised and handled.The row should consist of the Oracle username (who), when the error was raised (when), and the SQLCODE and SQLERRM of the exception. Test your block severaltimes, with different data values to raise each of the four kinds of exceptions handledin the block. Finally, SELECT from the error-logging table to check that the rows have been inserted. Database Programming with PL/SQL 7-3: Trapping User-Defined Exceptions Practice Activities Vocabulary Identify the vocabulary word for each definition below: A procedure used to return user-defined error messages from stored subprograms. Use this statement to raise a named exception.
Image of page 11
These errors are not automatically raısed by the Oracle Server, but are defined by the programmer and are specific to the programmer's code. Try It / Solve It All the questions in this exercise use a copy of the employees table. Create this copy by running the following SQL statement: CREATE TABLE excep_emps AS SELECT * FROM employees; 1 Create a PL/SQL block that updates the salary of every employee to a new value of 10000 in a chosen department. Include a user-defined exception handler that handles the condition where no rows are updated and displays a custom message. Also include an exception handler that will trap any other possible error condition and display the corresponding SQLCODE and SQLERRM. Test your code three times, using department_ids 20, 30, and 40. 2 Modify your code from question 1 to handle the condition where no rows are updated using RAISE_APPLICATION_ERROR procedure in the exception section. Use an error number of –20202. Test your code again using department_id 40 and check that the – 20202 error is displayed.
Image of page 12
3 Modify your code from question 2 to use RAISE_APPLICATION_ERROR in the executable section instead of the exception section. Test your code again using department_id 40.
Image of page 13
Image of page 14
Image of page 15

  • Left Quote Icon

    Student Picture

  • Left Quote Icon

    Student Picture

  • Left Quote Icon

    Student Picture