One of the most wanted features in the Oracle database is the BOOLEAN type. The SQL standard specified it a while ago, and RDBMS like PostgreSQL show how powerful it can be, e.g. when using the EVERY() aggregate function.
Before you move on reading this article, note that starting with Oracle 23c, the standard
BOOLEANtype is finally supported!
The PL/SQL language already has support for boolean types. We can write:
CREATE OR REPLACE FUNCTION number_to_boolean (i NUMBER)
RETURN BOOLEAN
IS
BEGIN
RETURN NOT i = 0;
END number_to_boolean;
/
CREATE OR REPLACE FUNCTION boolean_to_number (b BOOLEAN)
RETURN NUMBER
IS
BEGIN
RETURN CASE WHEN b THEN 1 WHEN NOT b THEN 0 END;
END boolean_to_number;
/
From PL/SQL, we can now easily call the above functions:
SET SERVEROUTPUT ON
BEGIN
IF number_to_boolean(1) THEN
dbms_output.put_line('1 is true');
END IF;
IF NOT number_to_boolean(0) THEN
dbms_output.put_line('0 is false');
END IF;
IF number_to_boolean(NULL) IS NULL THEN
dbms_output.put_line('null is null');
END IF;
END;
/
The above prints
1 is true 0 is false null is null
But we cannot do the same from the SQL engine:
SELECT
number_to_boolean(1),
number_to_boolean(0),
number_to_boolean(null)
FROM dual;
This yields:
ORA-00902: invalid datatype
Eventually, Oracle will fix this by supporting boolean types in the SQL engine (show your love to Oracle here).
The WITH clause
Until then, we can make use of a nice workaround using new functionality from Oracle 12c. We can declare functions in the WITH clause! Run this:
WITH
FUNCTION f RETURN NUMBER IS
BEGIN
RETURN 1;
END f;
SELECT f
FROM dual;
You’ll get
F --- 1
That’s wonderful, and what’s even better, this part of the WITH clause is written in PL/SQL, where we can use the BOOLEAN type again. So we can define bridge functions for each function call. Instead of this:
SELECT
number_to_boolean(1),
number_to_boolean(0),
number_to_boolean(null)
FROM dual;
We can write this:
WITH
FUNCTION number_to_boolean_(i NUMBER)
RETURN NUMBER
IS
b BOOLEAN;
BEGIN
-- Actual function call
b := number_to_boolean(i);
-- Translation to numeric result
RETURN CASE b WHEN TRUE THEN 1 WHEN FALSE THEN 0 END;
END number_to_boolean_;
SELECT
number_to_boolean_(1) AS a,
number_to_boolean_(0) AS b,
number_to_boolean_(null) AS c
FROM dual;
This now yields:
A B C ------------- 1 0 null
Of course, we don’t get an actual boolean type back in the result set, as the SQL engine cannot process that. But if you’re calling this function from JDBC, 1/0/null can be translated transparently to true/false/null.
It also works for chaining. Instead of the following, which still yields ORA-00902:
SELECT
boolean_to_number(number_to_boolean(1)),
boolean_to_number(number_to_boolean(0)),
boolean_to_number(number_to_boolean(null))
FROM dual;
We can write this:
WITH
FUNCTION number_to_boolean_(i NUMBER)
RETURN NUMBER
IS
b BOOLEAN;
BEGIN
-- Actual function call
b := number_to_boolean(i);
-- Translation to numeric result
RETURN CASE b WHEN TRUE THEN 1 WHEN FALSE THEN 0 END;
END number_to_boolean_;
FUNCTION boolean_to_number_(b NUMBER)
RETURN NUMBER
IS
BEGIN
-- Actual function call
RETURN boolean_to_number(NOT b = 0);
END boolean_to_number_;
SELECT
boolean_to_number_(number_to_boolean_(1)) AS a,
boolean_to_number_(number_to_boolean_(0)) AS b,
boolean_to_number_(number_to_boolean_(null)) AS c
FROM dual;
… which again yields
A B C ------------- 1 0 null
And now, the 1/0/null integers are the actual desired result types.
This technique can be automated for any type of PL/SQL function that accepts and/or returns a PL/SQL BOOLEAN type, or even for functions that accept %ROWTYPE parameters, which we’ll work into jOOQ soon, in the near future.
A more real world example can be seen in this Stack Overflow question.
jOOQ 3.12 support
In jOOQ 3.12, we will add native support for using such functions in SQL through #8522. We have already supported PL/SQL boolean types in standalone procedure calls since jOOQ 3.8. With the next version, we can call a function like this one:
FUNCTION f_bool (i BOOLEAN) RETURN BOOLEAN;
From anywhere within a jOOQ statement, e.g.
Record1<Integer> r =
create()
.select(one())
.where(PlsObjects.fBool(false))
.fetchOne();
assertNull(r);
When the above is called, the following SQL statement is generated by jOOQ 3.12, behind the scenes:
with
function "F_BOOL_"(I integer)
return integer
is
"r" boolean;
begin
"r" := "TEST"."PLS_OBJECTS"."F_BOOL"(not I = 0);
return case when "r" then 1 when not "r" then 0 end;
end "F_BOOL_";
select 1
from dual
where (F_BOOL_(0) = 1)
Notice how the boolean expression codes like a true boolean / predicate?