Set echo on SET SERVEROUT ON Set up a spool file to receive your output for subm

Question

Set echo on

SET SERVEROUT ON

Set up a spool file to receive your output for submission. I would suggest c:cs422awa5spool.txt .

DECLARE a record variable (Emp_rec) using %ROWTYPE

In the BEGIN block add a select statement to read a record into the declared variable from HR.EMPLOYEES
for all employees WHERE DEPARTMENT_ID = 30

Add a LOOP to print all the records

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

Use TO_CHAR to format the salary as $999,999

Add a EXCEPTION block to report when no data is found

Compile and run the procedure.

Close the spool file

Explanation / Answer

SET echo ON
SET SERVEROUTPUT ON
SET SPOOL c:cs422awa4spool.txt;

DECLARE

Emp_rec HR.EMPLOYEES%ROWTYPE;

PROCEDURE pro_name(emp_id IN INTEGER) IS

BEGIN

For Emp_rec in (SELECT *
                FROM HR.EMPLOYEES%ROWTYPE
                WHERE department_id = 30)

LOOP

dbms_output.put_line('Employee Department :'|| Emp_rec.department_id||
                   'Employee ID : '|| Emp_rec.employee_id||
                   'Employee first name : '|| Emp_rec.First_name||
                   'Employee last name : '||Emp_rec.Last_name
       'Salary : '||to_char(Emp_rec.salary,'$999,999'));
END LOOP;
    
EXCEPTION NO_DATA_FOUND THEN
dbms_output.put_line('No data Found');

END;

SET SPOOL OFF;   
      

Related Questions

Navigate

• Browse (All)
• Browse S
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.