#100CodeExamples – PL/SQL BOOLEAN to INT

#100codeexamples #plsql #oracle #sql

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:

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, 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!