Q$5.(30$ points$)$ This question will use table Locations.

It is useful to insert a record of the error into a table for future review when an exception happens. The code below will create a table to store the error information,

DROP TABLE log$\_$error; $--$ in case you have that table created, otherwise ignore this.

CREATE TABLE log$\_$error $($

Occur$\_$date DATE DEFAULT SYSDATE,

Username VARCHAR$2(15)$ DEFAULT USER,

Err$\_$code NUMBER,

Err$\_$msg VARCHAR$2(255))$;

$**$ Here, we use "SYSDATE" for Occur$\_$date, "USER" for username.

According to the scripts $($for creating the tables$)$ provided on D$2$L$,$

ALTER TABLE departments

ADD $(...$ CONSTRAINT dept$\_$loc$\_$fk FOREIGN KEY $($location$\_$id$)$

REFERENCES locations $($location$\_$id$))$;

the location$\_$id in the Departments table is a foreign key referencing to location$\_$id in the Locations table, thus the program cannot simply delete a location if there is$/$are department$($s$)$ in that location $($ID$).$

The following Select statements will display which location ID are used or not.

select distinct location$\_$id from departments; $--$ list all locations that are used now

select location$\_$id from locations $--$ list all locations that are defined,

select location$\_$id from locations minus

select distinct location$\_$id from departments; $--$ list all locations that are not currently used.

Write a PL$/$SQL block that will run two SQL delete commands. First time, delete a location with ID $1900$; second time, delete the location with ID $1800.$

In the executable section, you will define an exception section with OTHERS handler, that will catch up any unexpected error. In that OTHERS handler, your program will use SQLCODE and SQLERRM to get the error number and error message. Your program will execute an insert command to add a record into the log$\_$error table when an error occurs.

After you run the PL$/$SQL block, you need to run a SQL statement separately to display the contents of the log$\_$error table, that should include the record just inserted when an error was caught in the last program.

To keep the data as that were created, it is recommended to $$rollback$$ these deletions after you have run the select from log$\_$error table and before you leave the program.