DEV Community

loading...

#100CodeExamples – PL/SQL BOOLEAN to INT

pesse profile image Samuel Nitsche Originally published at cleandatabase.wordpress.com on ・2 min read

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!

Discussion (0)

pic
Editor guide