How to use BOOLEAN type in SELECT statement
You can definitely get Boolean value from a SELECT query, you just can't use a Boolean data-type.
You can represent a Boolean with 1/0.
CASE WHEN (10 > 0) THEN 1 ELSE 0 END (It can be used in SELECT QUERY)SELECT CASE WHEN (10 > 0) THEN 1 ELSE 0 END AS MY_BOOLEAN_COLUMN FROM DUAL
Returns, 1 (in Hibernate/Mybatis/etc 1 is true). Otherwise, you can get printable Boolean values from a SELECT.
SELECT CASE WHEN (10 > 0) THEN 'true' ELSE 'false' END AS MY_BOOLEAN_COLUMN FROM DUAL
This returns the string 'true'
.
You can build a wrapper function like this:
function get_something(name in varchar2, ignore_notfound in varchar2) return varchar2isbegin return get_something (name, (upper(ignore_notfound) = 'TRUE') );end;
then call:
select get_something('NAME', 'TRUE') from dual;
It's up to you what the valid values of ignore_notfound are in your version, I have assumed 'TRUE' means TRUE and anything else means FALSE.
From documentation:
You cannot insert the values
TRUE
andFALSE
into a database column. You cannot select or fetch column values into aBOOLEAN
variable. Functions called from aSQL
query cannot take anyBOOLEAN
parameters. Neither can built-inSQL
functions such asTO_CHAR
; to representBOOLEAN
values in output, you must useIF-THEN
orCASE
constructs to translateBOOLEAN
values into some other type, such as0
or1
,'Y'
or'N'
,'true'
or'false'
, and so on.
You will need to make a wrapper function that takes an SQL
datatype and use it instead.