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.
When i attempt to compile this as a whole it kicks out this error.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.
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?




Reply With Quote