What is the result in thisEXECUTE command?

Examine this code:
CREATE OR REPLACE PROCEDURE insert_dept
(p_location_id NUMBER)
IS
v_dept_id NUMBER(4);
BEGIN
INSERT INTO departments
VALUES (5, ‘Education’, 150, p_location_id);
SELECT department_id
INTO v_dept_id
FROM employees
WHERE employee_id=99999;
END insert_dept;
/
CREATE OR REPLACE PROCEDURE insert_location
( p_location_id NUMBER,
p_city VARCHAR2)
IS
BEGIN
INSERT INTO locations(location_id, city)
VALUES (p_location_id, p_city);
insert_dept(p_location_id);
END insert_location;
/
You just created the departments, the locations, and the employees table. You did not insert any rows. Next you created both procedures. You new invoke the insert_location procedure using the following command:
EXECUTE insert_location (19, ‘San Francisco’)
What is the result in thisEXECUTE command?

A.
The locations, departments, and employees tables are empty.

B.
The departments table has one row.
The locations and the employees tables are empty.

C.
The location table has one row.
The departments and the employees tables are empty.

D.
The locations table and the departments table both have one row.
The employees table is empty.

Explanation:
All of the tables are empty. When the following statement executed:
SELECT department_id
INTO v_dept_id
FROM employees
WHERE employee_id = 9999;
An error is thrown and there is no exception section in this procedure or the calling procedure, therefore all transactions are rolled back. Note: If the exception is not handled in the called procedure, the control is transferred to the exception-handling
section of the calling procedure. If the exception is handled, all the statements remain intact. If the
exception is not handled in the calling procedure’s exception-handling section, all the statements are
rolled back, and the exception propagates to the calling environment.
Incorrect Answers
B: This is not true because an error is occurs on the SELECT Statement and the INSERT Transaction is rolled back when the error is encountered. C: The insert into the location table is also rolled back. The insert_locaton calls the insert_dept Stored Procedure, this procedure fails and control is passed back to the calling procedure which does not have an exception section , therefore both the insert into the locations table and the insert into the departments table are rolled back. D: Due to the lack of exception handling the inserts into the locations & departments are rollback back.