1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Problem with package body

Discussion in 'SQL PL/SQL' started by mukulverma2408, Jul 29, 2015.

  1. mukulverma2408

    mukulverma2408 Active Member

    Messages:
    38
    Likes Received:
    1
    Trophy Points:
    90
    Location:
    New delhi
    Hi,

    Can you please help to me identify error in this package body as it's giving me compilation error :
    Code (Text):

    CREATE OR REPLACE PACKAGE BODY COMM_PKG IS
     FUNCTION VALIDATE(COMM NUMBER) RETURN BOOLEAN IS
      MAX_COMM EMP.COMMISSION_PCT%TYPE;
      BEGIN
      SELECT MAX(COMMISSION_PCT) INTO MAX_COMM FROM EMP;
      RETURN (COMM BETWEEN 0.0 AND MAX_COMMISSION);
      END VALIDATE;
     PROCEDURE RESET_COMM(NEW_COMM NUMBER) IS
      BEGIN
      IF VALIDATE(NEW_COMM) THEN
       STD_COMM := NEW_COMM;
       ELSE
       RAISE_APPLICATION_ERROR (-20210,'BAD COMMISSION');
      END IF;
     END RESET_COMM;
    END COMM_PKG;

     
     
  2. DTSIGuy

    DTSIGuy Forum Advisor

    Messages:
    402
    Likes Received:
    58
    Trophy Points:
    410
    Location:
    Texas
    And that error is? And the reported error is indicated on what line?
     
  3. ocprep

    ocprep Forum Advisor

    Messages:
    277
    Likes Received:
    76
    Trophy Points:
    410
    Location:
    Orlando, Florida
    Having the error message would make it a bit easier. My guess would be that at least one error is caused by the line: "STD_COMM := NEW_COMM;". This appears to be setting a value to a variable called STD_COMM... except there is no such variable declared in the package body. Mind you -- it is conceivable that a global variable is declared in the package spec, but since you did not supply that, I can't say. It would make some sense if you have, because otherwise nothing is being done by the RESET_COMM procedure since the STD_COMM value isn't being passed out of it explicitly.

    Likewise, the line "RETURN (COMM BETWEEN 0.0 AND MAX_COMMISSION)" is going to cause an error because it refers to MAX_COMMISSION, which is not declared anywhere. Presumably this should have referenced the MAX_COMM variable.

    PL/SQL code is a lot more readable when you give parameters, variables, and global variables prefixes to indicate what they are. I tend to use p_[name] for parameters, g_[name] for global variables and v_[name] for local variables (some people use l_[name] for local). The convention makes it much easier to figure out what the code is doing.

    Code (Text):

    CREATE OR REPLACE PACKAGE comm_pkg IS
      g_std_comm   NUMBER;

      FUNCTION validate(p_comm NUMBER) RETURN BOOLEAN;
      PROCEDURE reset_comm(p_new_comm NUMBER);
    END comm_pkg;


    CREATE OR REPLACE PACKAGE BODY comm_pkg IS
    FUNCTION validate(p_comm NUMBER) RETURN BOOLEAN IS
      v_max_comm EMP.COMMISSION_PCT%TYPE;
    BEGIN
      SELECT MAX(commission_pct)
      INTO   v_max_comm
      FROM   emp;

      RETURN (p_comm BETWEEN 0.0 AND v_max_comm);
    END validate;

    PROCEDURE reset_comm(p_new_comm NUMBER) IS
    BEGIN
      IF VALIDATE(p_new_comm) THEN
       g_std_comm := p_new_comm;
       ELSE
       RAISE_APPLICATION_ERROR (-20210,'BAD COMMISSION');
      END IF;
    END reset_comm;

    END comm_pkg;
     
  4. mukulverma2408

    mukulverma2408 Active Member

    Messages:
    38
    Likes Received:
    1
    Trophy Points:
    90
    Location:
    New delhi
    Thanks Morris, the problem is with this line only and it's fine after using the correct variable, i will also going to use the naming convention as suggested by you to identify the local and global variables.

    Thanks
     
  5. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,351
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    RETURN takes a variable, NOT a logical construct one would use in a WHERE clause. You're asking to return the supplied COMM value if it's between 0 and the max you just calculated; change your code to this:

    if comm between 0 and max_comm then
    return(comm);
    end if;

    and it should work as you want it to.
     
  6. ocprep

    ocprep Forum Advisor

    Messages:
    277
    Likes Received:
    76
    Trophy Points:
    410
    Location:
    Orlando, Florida
    It's actually legal for a function returning a BOOLEAN to use a logical comparison that generates a BOOLEAN result in the RETURN clause:

    Code (Text):

    CREATE FUNCTION bool_func
    RETURN BOOLEAN
    AS
    BEGIN
      RETURN (1 > 2);
    END bool_func;

    CREATE FUNCTION bool_char
    RETURN VARCHAR2
    AS
    BEGIN
      IF bool_func THEN
        RETURN 'TRUE';
      ELSE
        RETURN 'FALSE';
      END IF;
    END bool_char;

    SELECT bool_char FROM dual;

    BOOL_CHAR
    ---------
    FALSE