04-04-2024
14 december 2023
Oracle Database has known the Boolean datatype in PL/SQL since its introduction in Oracle Database version 7. However, until Oracle Database 23c, we had to use convoluted column definitions, such as char( 1 ), varchar2( 1 ), or number( 1 ), and add check constraints to tables to ensure that only valid data was entered. With the introduction of the new Boolean datatype in Oracle Database 23c, most of these workarounds are no longer necessary.
Oracle Database 23c introduces a Boolean datatype which can be stored in the database, just likenumbers, strings, and dates. For example:
create table if not exists booleandemo( id number generated by default on null as identit, text varchar2( 32 ), value boolean)/
The "IF NOT EXISTS" construct is a new feature of Oracle Database 23c, as seen in the "If [Not] Exists" documentation.
Now we can easily add Boolean values to the table, including NULL, as Oracle has implemented the NULL ‘value’ throughout the database.
insert into booleandemo ( text, value )
values ( q'[true]', true ) -- equals to true, ( q'[false]', false ) -- equals to false, ( q'[null]', null ) -- equals to null/
The Multi-Row-Insert construct is another new feature of Oracle Database 23c, as seen in the Table Values Constructor documentation.
In addition to using true and false, you can also use numbers to represent Boolean value. In this case, 0 equals false, and any other value equals true, except for NULL of course, which will remain, well, NULL.
insert into booleandemo ( text, value )
values ( q'[-1]', -1 ) -- equals to true, ( q'[0]', 0 ) -- equals to false, ( q'[1]', 1 ) -- equals to true, ( q'[2]', 2 ) -- equals to true/
And there are also a couple of string-pairs that automagically get converted to Boolean values, such as ( ‘y’, ’n’ ),( ‘yes’, ‘no’ ), ( ‘true’, ‘false’ ), ( ‘on’, ‘off’ ) and ( ‘yes’, ‘no’ ). The strings are case insensitive, so, for example, ‘TRUE’ and ‘FALSE’ are also valid.
insert into booleandemo ( text, value )
values ( q'['t']', 't' ) -- equals to true, ( q'['f']', 'f' ) -- equals to false, ( q'['true']', 'true' ) -- equals to true, ( q'['false']', 'false' ) -- equals to false, ( q'['on']', 'on' ) -- equals to true, ( q'['off']', 'off' ) -- equals to false, ( q'['yes']', 'yes' ) -- equals to true, ( q'['no']', 'no' ) -- equals to false, ( q'['y']', 'y' ) -- equals to true, ( q'['n']', 'n' ) -- equals to false/
Any other value will result in an error (ORA-61800: invalid boolean literal).
If you query the data from the table with an Oracle Client older than 23c, you will see numbers, instead of the Boolean values, with, 0 representing false and 1 representing true. As you can see by the alignment of the column (right aligned), they are returned as a number.
select *
from booleandemo
/
ID TEXT VALUE
---------- -------------------------------- ----------
1 true 1
2 false 0
3 null
...
If you query the data from the table with an Oracle Client 23c or higher, you will see the Boolean values:
select *from booleandemo/
ID TEXT VALUE---------- -------------------------------- ----------- 1 true TRUE 2 false FALSE 3 null...
PL/SQL
You can now also use the Boolean value in PL/SQL, for instance when logging information or writing the value to output. But be aware, this requires the parameter PLSQL_IMPLICIT_CONVERSION_BOOL to be set, which is not enabled by default.
This script will fail if the parameter is not enabled:
declare v_b boolean := true;begin dbms_output.put_line( 'my bool is: ' || v_b );end;/ORA-06550: line 4, column 24:PLS-00306: wrong number or types of arguments in call to '||'ORA-06550: line 4, column 3:PL/SQL: Statement ignored
If your DBA enables this parameter for the entire database, or if you enable it for your current session, the same script will complete successfully:
alter session set PLSQL_IMPLICIT_CONVERSION_BOOL=true/
Session altered
declare v_b boolean := true;begin dbms_output.put_line( 'my bool is: ' || v_b );end;/my bool is: TRUE
Aggregates
Suprisingly, it is even possible to perform aggregation functions on a Boolean column. You can use a min( <column> ) and a max( <column> ) function on the column, where the min( <column> ) function will return false if there are one or more false values in the column and the max( <column> ) function will return true if there are one or more true values in the column.
You can also run sum( <column> ) on the column, which will give you the number of true values in the column. If you would like to see the number of false values, you can do so by querying sum( not <column> ). You can query the avg( <column> ) to get a feeling of how many true values there are compared to the number of false values. I have not tested all available aggregates, but it seems that Oracle Database internally represents false values as 0 and true values as 1, no matter what you used to populate the column.
This might not be the most prominent new feature of Oracle Database 23c, but it is certainly one that a lot of people have been eagerly waiting for.
The source code used in this article can be found at:
https://github.com/Qualogy-
Also read my previous blogs about Oracle Database 23:
- Introduction blog series Oracle Database 23
- Developer Role
- Group by Alias
- If [Not] Exists
- Table values constructor
And my next blog:
Auteur: Patrick Barel
