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?

Source link https://blog.jooq.org/2019/04/16/calling-an-oracle-function-with-pl-sql-boolean-type-from-sql/