1. Set echo on 2. SET SERVEROUT ON 3. Set up a spool file to receive your output
ID: 3890831 • Letter: 1
Question
1. Set echo on
2. SET SERVEROUT ON
3. Set up a spool file to receive your output for submission. I would suggest c:CSwa5spool.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
6. for all employees WHERE DEPARTMENT_ID = 30
7. Add a LOOP to print all the records
8. Add DBMS_OUTPUT lines to print EMPLOYEE_ID, FIRST_NAME, LAST_NAME, and SALARY for the selected record
9. Use TO_CHAR to format the salary as $999,999
10. Add a EXCEPTION block to report when no data is found
11. Compile and run the procedure.
12. Close the spool file
Explanation / Answer
Below is the step wise procedure written in PL/SQL,
set echo on
set serveroutput on
spool wa5spool.txt
DECLARE
E_id EMPLOYEE.EMPLOYEE_ID%type;
E_name EMPLOYEES.FIRST_NAME%type;
E_lname EMPLOYEES.LAST_NAME%type;
E_sal EMPLOYEES.SALARY%type;
CURSOR E_Employees is
SELECT EMPLOYEE_ID, FIRST_NAME, LAST_NAME, TOCHAR(SALARY,'$999,999') FROM Employees WHERE DEPARTMENT_ID = 30;
BEGIN
OPEN E_Employees;
LOOP
FETCH E_Employees into E_id, E_name, E_lname, E_sal;
EXIT WHEN E_Employees%notfound;
dbms_output.put_line(E_id || ' ' || E_name || ' ' || E_lname || ' ' || E_sal);
EXCEPTION
WHEN no_data_found THEN
dbms_output.put_line('No such employee!');
WHEN others THEN
dbms_output.put_line('Error!');
END LOOP;
CLOSE E_Employees;
END;
spool off
set serveroutput off
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.