Boolean variables are used to indicate whether a condition is true or not. Evaluation of a condtion/state is something most programmers do on a daily basis.

Sooner or later we all come across code that converts a number to a boolean or reverse for some reason. To my experience, this is always “home” written code with sometimes interesting side effects.

I have never seen an implementation of Oracle’s own conversion package DIUTIL for the purpose of boolean conversion. The DIUTIL package is not documented by Oracle.

The Oracle SYS package DIUTIL provides 2 functions

  • sys.diutil.bool_to_int
  • sys.diutil.int_to_bool

As the names states one is from converting boolean to integer, and the other from integer to a boolean. Both functions will return NULL if the in- parameter is NULL.

Let’s make a simple test. First the function diutil.int_to_bool.
First the variable lv_state is set to 1 then we change it to 0. Testing both cases as an boolean.

set serveroutput on

rem True -> 1, False -> 0
declare
 lv_state integer := 1;
begin
dbms_output.put_line('Testing diutil.int_to_bool');
  if diutil.int_to_bool(lv_state) then
    dbms_output.put_line('state is true');
    lv_state := 0;
    if not diutil.int_to_bool(lv_state) then
      dbms_output.put_line('state is false');
    end if;
  else
    dbms_output.put_line('state is not true or false');
  end if;
end;
/
Testing diutil.int_to_bool
state is true
state is false

PL/SQL procedure successfully completed.

If the lv_state is not 1 or 0 we will get ORA-06512: vid “SYS.DIUTIL”, row 742.

Next diutil.bool_to_int.
First the variable lv_state is set to true then we change it to false
Testing both cases as an integer.

rem True -> 1, False -> 0
rem Testing diutil.bool_to_int
declare
 lv_state boolean := true;
begin
dbms_output.put_line('Testing diutil.bool_to_int');
  if diutil.bool_to_int(lv_state)=1 then
    dbms_output.put_line('state is number 1');
    lv_state := false;
    if diutil.bool_to_int(lv_state)=0 then
      dbms_output.put_line('state is number 0');
    end if;
  else
    dbms_output.put_line('state is not 1 or 0');
  end if;
end;
/
Testing diutil.bool_to_int
state is true
state is false

PL/SQL procedure successfully completed.

As always Boolean isn’t a SQL datatype so it can’t be used in sql statements.

Over & Out