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!
Top comments (0)