# Calling an Oracle Function With a PL/SQL BOOLEAN Type From SQL

### Let's see how to call an Oracle function with a PL/SQL BOOLEAN type from SQL.

Join the DZone community and get the full member experience.

Join For FreeOne 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 shows 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 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 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?

Let us know your thoughts in the comments!

If you enjoyed this article, you can read more of Lukas's thoughts jOOQ here:

Published at DZone with permission of Lukas Eder, DZone MVB. See the original article here.

Opinions expressed by DZone contributors are their own.

Comments