October 20, 2010 at 2:30 pm
This is what i needed to do
SQL> CREATE OR REPLACE PACKAGE BODY emp_net_pay IS
2
3 --Subtract the before tax deduction from Monthly Salary
4
5 FUNCTION sal_before_Deduc
6 (emp_no IN NUMBER)
7 RETURN NUMBER
8 IS
9 emp_before_tax Number;
10 BEGIN
11 SELECT
12 e.Sal - coalesce(ed.Deduction_amount,0) result
13 INTO emp_before_tax
14 FROM
15 Employee e
16 LEFT JOIN Emp_Deductions ed ON ed.fk_empno = e.EmpNo
17 AND ed.Before_or_After_Flag = 'B';
18 EXCEPTION
19 WHEN NO_DATA_FOUND THEN
20 DBMS_OUTPUT.PUT_LINE('no salary for the employee has been declared');
21 END sal_before_deduc;
22
23 --calculate TAX deduction
24 FUNCTION tax_calc
25 (emp_no IN NUMBER)
26 RETURN NUMBER
27 IS
28 tax_deduction Number;
29 BEGIN
30 SELECT (tr.percent/100) * e.sal
31 Into tax_deduction
32 FROM Employee e,tax_rates tr
33 where e.Sal BETWEEN tr.Salary_MIN AND tr.Salary_Max;
34 EXCEPTION
35 WHEN NO_DATA_FOUND THEN
36 DBMS_OUTPUT.PUT_LINE('no salary for the emoployee has been declared');
37 END tax_calc;
38
39 --Calculate after tax deduction
40
41 FUNCTION after_tax_calc
42 (emp_no IN NUMBER)
43 RETURN NUMBER
44 IS
45 After_tax_deduction Number;
46 BEGIN
47 SELECT
48 COALESCE(ed.Deduction_amount,0) result
49 INTO After_tax_deduction
50 FROM
51 Employee e
52 LEFT JOIN Emp_Deductions ed ON ed.fk_EmpNo = e.EmpNo
53 AND ed.Before_or_After_Flag = 'A';
54 EXCEPTION
55 WHEN NO_DATA_FOUND THEN
56 DBMS_OUTPUT.PUT_LINE('no salary for the emoployee has been declared');
57 END after_tax_calc;
58 PROCEDURE net_pay
59 (p_empno OUT NUMBER,
60 P_comm OUT Number,
61 p_netpay OUT NUMBER
62 )
63 IS
64 BEGIN
65 Select empno,comm into p_empno, p_comm from employee;
66 P_netpay := sal_before_deduc(P_empno) - 2 * (tax_calc(p_empno)) + p_comm - after_tax_calc(p_emp
no);
67
68 END;
69 END;
70 /
Package body created.
October 20, 2010 at 8:26 pm
Where is the Package Header?
Where are the errors?
What's the question?
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.October 21, 2010 at 9:18 am
sorry about the late response
this is the package i created to calculate net pay for a user and get an error as ORA-01422: exact fetch returns more than requested number of rows
--Calculate the pay for an employee
CREATE OR REPLACE PACKAGE emp_net_pay_pkg
IS
PROCEDURE net_pay
(p_empno IN NUMBER,
P_comm OUT Number,
p_netpay OUT NUMBER);
END;
/
--Create the Package Body
CREATE OR REPLACE PACKAGE BODY emp_net_pay IS
--Subtract the before tax deduction from Monthly Salary
FUNCTION sal_before_Deduc
(emp_no IN NUMBER)
RETURN NUMBER
IS
emp_before_tax Number;
BEGIN
SELECT
e.Sal coalesce(ed.Deduction_amount,0) result
INTO emp_before_tax
FROM
Employee e
LEFT JOIN Emp_Deductions ed ON ed.fk_empno = e.EmpNo
AND ed.Before_or_After_Flag = 'B'
Where emp_no = e.empno;
RETURN emp_before_tax ;
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('no salary for the employee has been declared');
END sal_before_deduc;
--calculate TAX deduction
FUNCTION tax_calc
(emp_no IN NUMBER)
RETURN NUMBER
IS
tax_deduction Number;
BEGIN
SELECT (tr.percent/100) * e.sal
Into tax_deduction
FROM Employee e,tax_rates tr
where e.Sal BETWEEN tr.Salary_MIN AND tr.Salary_Max;
RETURN tax_deduction;
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('no salary for the emoployee has been declared');
END tax_calc;
--Calculate after tax deduction
FUNCTION after_tax_calc
(emp_no IN NUMBER)
RETURN NUMBER
IS
After_tax_deduction Number;
BEGIN
SELECT
COALESCE(ed.Deduction_amount,0) result
INTO After_tax_deduction
FROM
Employee e
LEFT JOIN Emp_Deductions ed ON ed.fk_EmpNo = e.EmpNo
AND ed.Before_or_After_Flag = 'A'
where emp_no = e.empno;
RETURN After_tax_deduction;
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('no salary for the emoployee has been declared');
END after_tax_calc;
PROCEDURE net_pay
(p_empno IN NUMBER,
P_comm OUT Number,
p_netpay OUT NUMBER
)
IS
BEGIN
Select comm into p_comm from employee e
Where e.empno = p_empno;
P_netpay := sal_before_deduc(P_empno) - 2 * (tax_calc(p_empno)) + p_comm - after_tax_calc(p_empno);
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('Invallid emp ');
END net_pay;
END;
SET SERVEROUTPUT ON
SET SERVEROUTPUT ON
DECLARE
lv_emp_netPay NUMBER;
BEGIN
lv_netpay := cal_net_pay_pkg.net_pay_proc(1);
DBMS_OUTPUT.PUT_LINE(lv_netpay);
END;
/
October 21, 2010 at 9:21 am
The tables are
(
empno NUMBER(5) NOT NULL,
ename VARCHAR2(15) NOT NULL,
job VARCHAR2(10),
mgr NUMBER(4),
hiredate DATE DEFAULT (sysdate),
sal NUMBER(7,2),
comm NUMBER(7,2),
deptno NUMBER(3) NOT NULL
);
ALTER TABLE employee add CONSTRAINT empno_pk PRIMARY KEY (empno);
Insert into employee Values (1, 'A', 'QA' , '123',TO_DATE( '3-DEC-1990', 'DD-MON-YYYY'), 10000, 2, 19);
/***************************
Create table deductions
**************************/
CREATE TABLE SalaryGrade
(
Fk_Grade varchar(2),
salary_grade_high NUMBER(7,2) ,
salary_grade_Low NUMBER(7,2)
);
Insert into SalaryGrade Values ( 'A',1000, 10000);
Insert into SalaryGrade Values ( 'B',10000, 20000);
Insert into SalaryGrade Values ( 'C',20000, 30000);
/***************************
Create table deductions
**************************/
CREATE TABLE deductions
(
name VARCHAR2(30) Not null,
salary_grade_min NUMBER(2) ,
salary_grade_max NUMBER(2),
Constraint deduction_name Primary Key (name)
);
Insert into deductions values ( '401K',1, 2);
Insert into deductions values ( 'eye',3, 5);
Insert into deductions values ( 'commute',4, 5);
CREATE TABLE emp_deductions
(
fk_deduction VARCHAR2(30) not null,
fk_empno NUMBER(4) not null,
before_or_after_flag CHAR(1),
deduction_amount NUMBER(6,2),
CONSTRAINT Fk_deduction FOREIGN KEY (fk_deduction) REFERENCES deductions(name)
);
October 21, 2010 at 12:24 pm
Welcome to the wonderfull world of troubleshooting 😀
Some of your "select into" queries is returning more than one row - either because of faulty data or faulty logic.
I would execute queries one by one manually until finding the root cause, as soon as you see a "select into" query returning anything but one row you got it - providing there are not more of them hidding there 😉
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply