CODEWITHSUNDEEP
oracle-databasefunction
Mr Robot
Mr Robot

Reputation: 57

Oracle - How to call a boolean function properly

I am having a problem executing my boolean function leapyear. I have modified it to return a boolean value but I seem to know how to properly call it.

create or replace function leapyear(year IN NUMBER)
RETURN BOOLEAN IS
BEGIN
    IF TO_CHAR(LAST_DAY(TO_DATE('01/02/'||year, 'dd/mm/yyyy')), 'DD') = 29 THEN
        RETURN TRUE;
    ELSE
        RETURN FALSE;
    END IF;
END;

The function compiles, but the problem is how to execute it : This shows " expression is of the wrong type" error

DECLARE
    leapy varchar2(10);
BEGIN
    SELECT leapyear(1600) into leapy from dual;
    dbms_output.put_line(leapy);
END;

Upvotes: 0

Views: 184

Answers (2)

MT0
MT0

Reputation: 167982

You cannot use it in an SQL statement as BOOLEAN is a PL/SQL data type and does not exist in SQL.

If you want to return a truthy value in SQL then return a literal (i.e. 0/1 or 'Y'/'N' etc.) and compare that if you need to.

create or replace function leapyear(year IN NUMBER)
RETURN NUMBER IS
BEGIN
    IF TO_CHAR(LAST_DAY(TO_DATE('01/02/'||year, 'dd/mm/yyyy')), 'DD') = 29 THEN
        RETURN 1;
    ELSE
        RETURN 0;
    END IF;
END;
/

then just use SQL:

SELECT leapyear(1600) from dual

If you want to use PL/SQL then just use your function and don't context switch to SQL and do it all in PL/SQL:

BEGIN
  IF leapyear(1600) THEN
    dbms_output.put_line('1600 is a leap year');
  ELSE
    dbms_output.put_line('1600 is not a leap year');
  END IF;
END;
/

Upvotes: 1

Littlefoot
Littlefoot

Reputation: 142713

For example:

SQL> set serveroutput on;
SQL> begin
  2    if leapyear(1600) then
  3       dbms_output.put_line('Leap year');
  4    else
  5       dbms_output.put_line('Not leap year');
  6    end if;
  7  end;
  8  /
Leap year

PL/SQL procedure successfully completed.

Or:

SQL> begin
  2    dbms_output.put_line(case when leapyear(1600) then 'Leap year'
  3                              else 'Not leap year'
  4                         end);
  5  end;
  6  /
Leap year

PL/SQL procedure successfully completed.

SQL>

Basically, if you want to use that function in pure SQL (i.e. not in PL/SQL), you'd rather return something else such as

  • NUMBER (0 being false, 1 being true)
  • VARCHAR2 (N being false, Y being true

or similar. Boolean - as datatype - doesn't exist in SQL layer which makes it kind of difficult to use.

Upvotes: 1

Related Questions