Problems with a Package
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3

Thread: Problems with a Package

  1. #1
    Join Date
    Dec 2003
    Posts
    4

    Exclamation 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?
    Last edited by decoy; 11-24-2005 at 04:11 PM.

  2. #2
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    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.
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  3. #3
    Join Date
    Dec 2003
    Posts
    4
    Yep! silly me. long hours lead to silly mistakes!

    Thanks mate!

    Now having trouble to test it!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width