Q

Expression is of wrong type

This is a function to check whether the given date is a holiday or not. When I execute this, I am getting an error: "ORA-06552: Statement ignored ORA-06553:PLS-382: Expression is of Wrong Type." What is the problem?

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.
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 last published in January 2007

Dig Deeper on Oracle and SQL

PRO+

Content

Find more PRO+ content and other member only offers, here.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

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

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close