QUESTION POSED ON: 09 January 2007
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.
|
