This is a function to check whether the given date is a holiday or not. Holiday_master table contains a list of all holidays. The function is complied well. This is the code of function: 
CREATE OR REPLACE FUNCTION IS_HOLIDAY( CHECK_DATE IN DATE )
  RETURN BOOLEAN
IS
  RETURN_VAL BOOLEAN;
  CNT  NUMBER(3);
BEGIN
RETURN_VAL := FALSE;
 BEGIN
 SELECT COUNT(*) INTO CNT FROM HOLIDAY_MASTER
 WHERE FROM_DATE <= CHECK_DATE AND TO_DATE >= CHECK_DATE AND
RECORD_STATUS='ACTIVE';  RETURN_VAL := TRUE;  EXCEPTION  WHEN
NO_DATA_FOUND  THEN RETURN_VAL := FALSE;  END;

  RETURN RETURN_VAL;

EXCEPTION
WHEN NO_DATA_FOUND THEN
RAISE_APPLICATION_ERROR(-20005,'Error (NO DATA FOUND) while Calculating
Dates for Esclation or To Be Rectification Date for Date : ' ||
CHECK_DATE); WHEN OTHERS THEN RAISE_APPLICATION_ERROR(-20006,'Error
while Calculating Dates for Esclation or To Be Rectification Date for
Date : ' || CHECK_DATE);

-- Data_Replication_Error_Log(SQLCODE, SQLERRM, NULL,
--  'Date Error: ');
END;
/
When I execute this using the following statement: 
select is_holiday('25-DEC-2006') from dual
I am getting an error like this: "ORA-06552: Statement ignored ORA-06553:PLS-382: Expression is of Wrong Type." What is the problem here? Why am I getting the error? Thank you.

    Requires Free Membership to View

    Login

There is no boolean datatype in SQL; therefore, your SQL statement does not recognize the boolean return value. However, PL/SQL does have a boolean datatype.

This was first published in January 2007

Join the conversationComment

Share
Comments

    Results

    Contribute to the conversation

    All fields are required. Comments will appear at the bottom of the article.
    Back to top