Ask the Expert

Expression is of wrong type

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

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

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to: