Calling an Oracle Function with PL/SQL BOOLEAN Type from SQL


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?