CODEWITHSUNDEEP
oracle-databasefunctiondateplsqlboolean
Dilshad Abduwali
Dilshad Abduwali

Reputation: 1458

why my function is returning false even with the correct parameter in PL/SQL?

I have written a boolean function in PL/SQL which takes a date as parameter and check the parameter against a table which contains the holiday dates and if the parameter is in the table the function returns a TRUE otherwise a FALSE. The code looks like this:

CREATE OR REPLACE
FUNCTION isholiday (p_holiday DATE)
RETURN BOOLEAN

IS

CURSOR c_holiday is SELECT holiday_date FROM dbp_holiday;

BEGIN

FOR r1 IN c_holiday
LOOP
    IF p_holiday = r1.holiday_date
    THEN
        RETURN TRUE;
    ELSE 
        RETURN FALSE;
    END IF;
END loop;

END;

even if I enter a valid holiday (e.g to_date('01-01-2013', 'dd-mm-yyy') it is in the table) as an argument the function is returning a false.

And the table is Oracle table its name is DBP_HOLIDAY and it has only one column with the type DATE and its name is Holiday_date.

Please give some advices. Thanks

Upvotes: 0

Views: 463

Answers (1)

Noel
Noel

Reputation: 10525

If the first record in your cursor doesn't match with the input date, it straightaway returns FALSE, without checking other records. You don't need a cursor here. You can simplify it like this.

CREATE OR REPLACE FUNCTION isholiday (p_holiday DATE)
   RETURN BOOLEAN
IS
   x   NUMBER(1);
BEGIN
   SELECT 1
     INTO x
     FROM DBP_HOLIDAY
    WHERE holiday_date = p_holiday;

   RETURN TRUE;
EXCEPTION
   WHEN NO_DATA_FOUND
   THEN
      RETURN FALSE;
END;

Upvotes: 3

Related Questions