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

Dig deeper on Oracle and SQL

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

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.

0 comments

Oldest 

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:

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close