CODEWITHSUNDEEP
oracle-databasefunctionboolean
ashish
ashish

Reputation: 239

Return boolean value from oracle function

Trying to return value from function 

create or replace function compairenumber(num1 in number,num2 in number)
return boolean is
begin
if num1 < num2 then
return true;
else 
return false;
end if;
end;

when i'm giving query select compairenumber(5,10) from dual its not returning true or false.

Upvotes: 8

Views: 67865

Answers (2)

user1205115
user1205115

Reputation:

use return varchar2

create or replace function compairenumber(num1 in number, num2 in number)
  return varchar2 is
begin
  if num1 < num2 then
    return 'TRUE';
  else
    return 'FALSE';
  end if;
end;


select CASE
         WHEN compairenumber(5, 10) = 'TRUE' THEN
          'OK'
         ELSE
          'NOT'
       END
  from dual

Upvotes: 5

user5683823
user5683823

Reputation:

Boolean values can only be used in other PL/SQL code, not in Oracle SQL. If you want a function whose return value is available in a select ... from dual then you will need to define the function to return varchar2 with the return values 'true' and 'false' respectively (or 'T' and 'F', or return number, with the values 1 and 0).

As sad as this is, Oracle SQL does not support the Boolean data type (although the programming language PL/SQL does).

Upvotes: 11

Related Questions