-
Problems with a Package
Ok so I am creating a package to calculate a bonus for an instructor, I have created a function that calculates his bonus depending upon how many lessons he has done in the last 7 days.
The function runs fine on its own.
I want to then access the returned value from the function in a procedure that then checks if the bonus is over 400 and if it is it caps it at 400 (400 is the max bonus per week).
here is the package contaning the function and the procedure.
Code:
SQL> CREATE OR REPLACE PACKAGE wage_calc IS
2 full_wage NUMBER(8);
3
4 FUNCTION instructor_bonus
5 (ins_grade in NUMBER , ins_id in VARCHAR2)
6 RETURN NUMBER;
7
8 PROCEDURE bonus_checker
9 (ins_bonus IN NUMBER,
10 ins_grade IN NUMBER,
11 ins_id IN VARCHAR2,
12 bonus OUT VARCHAR2);
13 END;
14 /
Package created.
SQL>
SQL> CREATE OR REPLACE PACKAGE BODY wage_calc IS
2 FUNCTION instructor_bonus
3 (ins_grade in NUMBER , ins_id in VARCHAR2)
4 RETURN NUMBER
5
6 IS
7
8 less_taught NUMBER(8);
9 ins_bonus NUMBER(8);
10
11
12 BEGIN
13
14
15 SELECT count(fk_ins_id)
16 INTO less_taught
17 FROM class_schedule
18 WHERE fk_ins_id = ins_id
19 AND date_of BETWEEN SYSDATE-7 AND SYSDATE;
20
21 IF ins_grade = '1' THEN ins_bonus := less_taught * 15.00;
22 ELSIF ins_grade = '2' THEN ins_bonus := less_taught * 25.00;
23 ELSIF ins_grade = '3' THEN ins_bonus := less_taught * 35.00;
24
25
26 END IF;
27
28 IF less_taught >= 5 THEN ins_bonus := ins_bonus * 1.1;
29 END IF;
30
31 RETURN ins_bonus;
32 END instructor_bonus;
33 PROCEDURE bonus_checker
34 (ins_bonus IN NUMBER,
35 ins_grade IN NUMBER,
36 ins_id IN VARCHAR2,
37 bonus OUT VARCHAR2)
38
39 IS
40 BEGIN
41 SELECT instructor_bonus(ins_bonus)
42 INTO bonus
43 FROM instructor;
44
45
46 IF bonus < 400
47 THEN
48 DBMS_OUTPUT.PUT_LINE(ins_id|| 'Bonus has not been capped, Bonus = '||bonus);
49
50 ELSIF bonus > 400
51 THEN bonus := 400;
52
53 END IF;
54 DBMS_OUTPUT.PUT_LINE(ins_id|| 'Has reached full bonus it will be capped, bonus = '||bonus);
55
56
57 END bonus_checker;
58
59 END;
60 /
Warning: Package Body created with compilation errors.
When i attempt to compile this as a whole it kicks out this error.
Code:
SQL> show err
Errors for PACKAGE BODY WAGE_CALC:
LINE/COL ERROR
-------- -----------------------------------------------------------------
41/2 PL/SQL: SQL Statement ignored
41/9 PLS-00306: wrong number or types of arguments in call to
'INSTRUCTOR_BONUS'
41/9 PL/SQL: ORA-00904: "WAGE_CALC"."INSTRUCTOR_BONUS": invalid
identifier
The problem is getting the value from the function and passing it to the procedure.
Any idea how I can do this? I have looked everywhere and havent been able to find any helpful examples!!
Cant imagine there are many problems with my above code.
Other question:
Would you describe the above program units as constraint methods? Seen as the procedure is constraining the max bonus?
-
Your package function INSTRUCTOR_BONUS() requires two input parameters (INS_GRADE and INS_ID), but when you call it from your procedure BONUS_CHECKER() you only pass one parameter (INS_GRADE). The error is pointing directly to your offending line in the package body, that is line 41:
Code:
....
41 SELECT instructor_bonus(ins_bonus)
42 INTO bonus
43 FROM instructor;
....
Change that code to:
Code:
....
41 SELECT instructor_bonus(ins_bonus, ins_id)
42 INTO bonus
43 FROM instructor;
....
and your package body will compile just fine.
-
Yep! silly me. long hours lead to silly mistakes!
Thanks mate!
Now having trouble to test it!
