```
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;
/
```

```
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;
/
```

1 is true 0 is false null is nullBut we cannot do the same from the SQL engine:

```
SELECT
number_to_boolean(1),
number_to_boolean(0),
number_to_boolean(null)
FROM dual;
```

ORA-00902: invalid datatypeEventually, 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;
```

F --- 1That’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;
```

```
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;
```

A B C ------------- 1 0 nullOf 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;
```

```
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;
```

A B C ------------- 1 0 nullAnd 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;
```

```
Record1<Integer> r =
create()
.select(one())
.where(PlsObjects.fBool(false))
.fetchOne();
assertNull(r);
```

```
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)
```