#100CodeExamples – PL/SQL BOOLEAN to INT
Tags: 100codeexamples, plsql, oracle, sql
Published: 2019-01-15 21:42:46

---
title: #100CodeExamples – PL/SQL BOOLEAN to INT
published: true
tags: #100CodeExamples,PL/SQL,Oracle,SQL
canonical_url: https://cleandatabase.wordpress.com/2019/01/11/100codeexamples-pl-sql-boolean-to-int/
---

One of the main annoyances of Oracle databases for me has been its lack of a BOOLEAN SQL-Type.

Yes, BOOLEAN exists in Oracle, but only inside of PL/SQL, not in the SQL context, which means that you can’t select a boolean type and also can’t retreive it via JDBC.

The simplest way to overcome this is to have an easy way of converting BOOLEAN to 1/0 INTEGER – and there are several possibilities to do this:

```sql
declare
  -- Use a straight if-else approach
  function bool_int_if ( i_bool boolean ) return int
  as
    begin
      if ( i_bool ) then
        return 1;
      else
        return 0;
      end if;
    end;
 
  -- Use case-when-else
  function bool_int_case( i_bool boolean ) return int
  as
    begin
      return case i_bool when true then 1 else 0 end;
    end;
 
  -- Use case-when-else with possible NULL return
  function bool_int_case_null( i_bool boolean ) return int
  as
    begin
      return case i_bool when true then 1 when false then 0
             else null end;
    end;
 
  -- Use the bool_to_int-function of sys.diutil-package
  function bool_int_diutil( i_bool boolean ) return int
  as
    begin
      return sys.diutil.bool_to_int(i_bool);
    end;
 
  -- Use sys.diutil and nvl to deal with NULL-values
  function bool_int_diutil_nvl( i_bool boolean ) return int
  as
    begin
      return nvl(sys.diutil.bool_to_int(i_bool),0);
    end;
begin
 
  dbms_output.put_line(
    'IF-approach: ' ||
    bool_int_if(true) || ', ' ||
    bool_int_if(false) || ', ' ||
    bool_int_if(null)
  );
 
  dbms_output.put_line(
    'CASE-approach: ' ||
    bool_int_case(true) || ', ' ||
    bool_int_case(false) || ', ' ||
    bool_int_case(null)
  );
 
  dbms_output.put_line(
    'CASE-approach with NULL: ' ||
    bool_int_case_null(true) || ', ' ||
    bool_int_case_null(false) || ', ' ||
    bool_int_case_null(null)
  );
 
  dbms_output.put_line(
    'DIUTIL-approach: ' ||
    bool_int_diutil(true) || ', ' ||
    bool_int_diutil(false) || ', ' ||
    bool_int_diutil(null)
  );
 
  dbms_output.put_line(
    'DIUTIL with NVL-approach: ' ||
    bool_int_diutil_nvl(true) || ', ' ||
    bool_int_diutil_nvl(false) || ', ' ||
    bool_int_diutil_nvl(null)
  );
 
end;
```

Output:

```
IF-approach: 1, 0, 0
CASE-approach: 1, 0, 0
CASE-approach with NULL: 1, 0,
DIUTIL-approach: 1, 0,
DIUTIL with NVL-approach: 1, 0, 0
```

You can run this on [LiveSQL](https://livesql.oracle.com/apex/livesql/s/htbibfu7s3t7wyqbkzt1lxrun), but only without the DIUTIL-Approach (the package is not available on LiveSQL).

For me, the winner is depending on the use-case.

If I want a non-nullable boolean conversion (different to PL/SQL BOOLEAN which \*does\* allow NULL), the **CASE-approach** is the easiest to understand, uses the most commonly known syntax and keywords and is the least verbose.

If I want a nullable conversion, **SYS.DIUTIL** might be exactly what I need.

_Update: Thanks to Jacek for reminding me, that a nullable Boolean might be a valid use-case. I updated the post_ and _example to make that clear.  
Jacek also pointed out that the CASE-approach is not exactly single-responsibility, because it does not solely convert PL/SQL BOOLEAN to INT but also interprets NULL as 0.  
I would argue, that the responsibility depends on the use-case. If my use case expects non-nullable boolean values, the responsibility of a conversion is to provide non-nullable boolean values.  
You’re welcome to reach out and discuss that with me!_