Part 2: Writing a PL/SQL program to create the procedure.

Heres the code.

SET SERVEROUTPUT ON

CREATE OR REPLACE PROCEDURE DEPT_EMPLOYEE_DETAILS

AS

CURSOR C_DETAILS

AS

select d.department_id Department_id,

d.department_name department_name,

d.manager_id manager_id,

e.employee_id employee_id,

e.first_name || ' ' || e.last_name full_name

from departments d, employees e

where e.department_id = e.department_id;

R_DETAILS C_DETAILS%ROWTYPE;

BEGIN

OPEN C_DETAILS;

FETCH C_DETAILS;

DBMS_OUTPUT.PUT_LINE(----------------------);

LOOP

DBMS_OUTPUT.PUT_LINE(Department Id: || r_details.department_id);

DBMS_OUTPUT.PUT_LINE(Department Name: || r_details.department_name);

DBMS_OUTPUT.PUT_LINE(Department Manager: || r_details.manager_id);

DBMS_OUTPUT.PUT_LINE(Employee Id: || r_details.Employee_id);

. . .

Complete the remaining display of employees data based on the cursor variables. i.e. full_name

EXIT WHEN R_DETAILS%NOTFOUND;

END LOOP;

DBMS_OUTPUT.PUT_LINE(----------------------);

EXCEPTION

WHEN NO_DATA_FOUND THEN

DBMS_OUTPUT.PUT_LINE(No data found);

END;

Enter the above PL/SQL block into the Query Builder.

Compile and run the program by clicking on the green triangle. If you receive errors, correct them until the program runs correctly.

Create an anonymous block to call the procedure using the following template. Complete the remaining part as directed in the comment inside the BEGIN section of the anonymous block.

SET SERVEROUTPUT ON

BEGIN

-- Call the procedure DEPT_EMPLOYEE_DETAILS here

EXCEPTION

END;

/

Part 2 Questions

Write the above anonymous block and share the screenshot of the output. (75%)

Is an exception needed here? (25%)