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.
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?
Like this:
Like Loading...
