In out class our databases are Scott, Hr.Employees.

Using SQLplus. Oracle 11g

1. Set echo on

2. SET SERVEROUT ON

3. Set up a spool file to receive your output for submission. I would suggest c:\CS4210\wa4spool.txt .

4. DECLARE a record variable (Emp_rec) using %ROWTYPE

5. In the BEGIN block add a select statement to read a record into the declared variable from HR.EMPLOYEES for an Employee_ID to be determined at run time

6. Add DBMS_OUTPUT lines to print DEPARTMENT, EMPLOYEE_ID, FIRST_NAME, LAST_NAME, and SALARY for the selected record

7. Use a CASE statement to print the associated Department Name for DEPARTMENT_ID

8. Use TO_CHAR to format the salary as $999,999

9. Add a EXCEPTION block to report when no data is found

10. Compile and run the procedure for the Employee, 114

11. Close the spool file To upload your completed work, click on the Submit Assignment button.