Database Programming (Oracle) ***I would need the spool file from executing the

Question

Database Programming (Oracle)

***I would need the spool file from executing the following commands:

Following the example in the Lecture, print a PIVOT report of salaries for HR.EMPLOYEES.

Set echo on

SET SERVEROUT ON

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

Add the following procedure to the DECLARE section:
PROCEDURE Var2Currency(v_col IN NUMBER)
IS
BEGIN
DBMS_OUTPUT.PUT(TO_CHAR(NVL(v_col,0), '999,999,999'));
END;
The null values in this dataset will kill you if you don't change them into zeros

Print the column headings for Dept, UK, USA, Germany, and Canada

Create a SELECT statement that will display salaries for each Department by Country. Notice that Country_ID has the values 'UK', 'US', 'DE', and 'CA'. You will need those single quotes in your IN statement

Compile and run your report

Close the spool file

Explanation / Answer

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

DECLARE

Emp_rec HR.EMPLOYEES%ROWTYPE;

PROCEDURE pro_name(emp_id IN INTEGER) IS

BEGIN

SELECT * into Emp_rec
FROM HR.EMPLOYEES%ROWTYPE
WHERE employee_id = emp_id;

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'));

CASE Emp_rec.department_id
    WHEN 10 THEN dbms_output.put_line('Employee Department :'|| Emp_rec.department_name);
    WHEN 20 THEN dbms_output.put_line('Employee Department :'|| Emp_rec.department_name);
    WHEN 30 THEN dbms_output.put_line('Employee Department :'|| Emp_rec.department_name);
   
    ELSE dbms_output.put_line('No Match Found ');
END CASE       
      
EXCEPTION NO_DATA_FOUND THEN
    dbms_output.put_line('No data Found');
END;

SPOOL OFF;   

Related Questions

Navigate

• Browse (All)
• Browse D
• Subjects

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