DBA Hub

📋Steps in this guide1/3

Boolean Data Type in Oracle Database 23ai/26ai

Oracle database 23ai/26ai introduced the boolean data type in SQL.

oracle 23configurationintermediate
by OracleDba
55 views
1

Boolean Table Columns

We can define a boolean data type using the or keywords. We can assign a value to a Boolean type in a number of ways. The following all set the active flag to true and the archived flag to false. We can see the result of this below. Old versions of SQL*Plus and all versions of SQLcl up to and including SQLcl 23.1 will display true and false as 1 and 0. This is because of the drivers being used, which don't currently understand true/false. Later releases will display Booleans as true/false. A Boolean column can also accept NULL values, unless a constraint has been applied. Boolean columns can have and constraints applied to them. They can be part of , or constraints.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
drop table if exists boolean_test purge;

create table boolean_test (
  id        number generated always as identity,
  active    boolean,
  archived  bool
);

insert into boolean_test (active, archived) values
  (true, false),
  (TRUE, FALSE),
  ('true', 'false'),
  ('TRUE', 'FALSE'),
  ('yes', 'no'),
  ('YES', 'NO'),
  ('on', 'off'),
  ('ON', 'OFF'),
  (1, 0),
  ('1', '0'),
  ('t', 'f'),
  ('T', 'F'),
  ('y', 'n'),
  ('Y', 'N');

commit;

select * from boolean_test;

        ID ACTIVE      ARCHIVED
---------- ----------- -----------
         1 TRUE        FALSE
         2 TRUE        FALSE
         3 TRUE        FALSE
         4 TRUE        FALSE
         5 TRUE        FALSE
         6 TRUE        FALSE
         7 TRUE        FALSE
         8 TRUE        FALSE
         9 TRUE        FALSE
        10 TRUE        FALSE
        11 TRUE        FALSE
        12 TRUE        FALSE
        13 TRUE        FALSE
        14 TRUE        FALSE

14 rows selected.

SQL>

select * from boolean_test;

        ID     ACTIVE   ARCHIVED
---------- ---------- ----------
         1          1          0
         2          1          0
         3          1          0
         4          1          0
         5          1          0
         6          1          0
         7          1          0
         8          1          0
         9          1          0
        10          1          0
        11          1          0

        ID     ACTIVE   ARCHIVED
---------- ---------- ----------
        12          1          0
        13          1          0
        14          1          0

14 rows selected.

SQL>

insert into boolean_test (active, archived) values (null, null);
commit;


select *
from   boolean_test
where  active is null;

        ID ACTIVE      ARCHIVED
---------- ----------- -----------
        15

SQL>


delete from boolean_test
where  active is null;
commit;
2

SQL Support

Boolean expressions can be used in SQL anywhere expressions are allowed. Here are some examples, but you can find more details about comparisons, assignments, conditions and operators in the documentation. The function has been added, along with overloads of the , and functions to support Booleans.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
select count(*)
from   boolean_test
where  active;

  COUNT(*)
----------
        14

SQL>


select count(*)
from   boolean_test
where  active is true;

  COUNT(*)
----------
        14

SQL>


select count(*)
from   boolean_test
where  active and not archived;

  COUNT(*)
----------
        14

SQL>


select count(*)
from   boolean_test
where  active or archived;

  COUNT(*)
----------
        14

SQL>


select count(*)
from   boolean_test
where  active is null;

  COUNT(*)
----------
         0

SQL>


select count(*)
from   boolean_test
where  active > archived;

  COUNT(*)
----------
        14

SQL>

select to_boolean('true'),
       to_boolean('false'),
       to_number(active),
       to_number(archived),
       to_char(active),
       to_char(archived),
       to_nchar(active),
       to_nchar(archived)
from   boolean_test
where  id = 1;

TO_BOOLEAN( TO_BOOLEAN( TO_NUMBER(ACTIVE) TO_NUMBER(ARCHIVED) TO_CH TO_CH TO_NC TO_NC
----------- ----------- ----------------- ------------------- ----- ----- ----- -----
TRUE        FALSE                       1                   0 TRUE  FALSE TRUE  FALSE

SQL>
3

PL/SQL Support

PL/SQL has had support for Boolean types for many years. Now they can be persisted in the database like many other data types. The function mentioned previously is available from PL/SQL by default. In Oracle 23.2 the overloads of the other conversion functions do not work by default in PL/SQL. For these to work the initialisation parameter has to be set to true at system or session level. In Oracle 23.3 you no longer need to set the initialisation parameter for these overloads to work. For more information see: - Boolean Data Type Hope this helps. Regards Tim...

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
declare
  l_active    boolean := true;
  l_archived  boolean := false;
begin
  insert into boolean_test (active, archived)
  values (l_active, l_archived);

  commit;
end;
/

PL/SQL procedure successfully completed.

SQL>

declare
  l_true    boolean;
  l_false   boolean;
begin
  -- Work
  l_true  := to_boolean('true');
  l_false := to_boolean('false');
end;
/

-- Oracle 23.2 only
alter session set plsql_implicit_conversion_bool=true;

declare
  l_true    boolean := true;
  l_false   boolean := false;
  l_number  number;
begin
  l_number := to_number(l_true);
  l_number := to_number(l_false);
end;
/

declare
  l_true    boolean;
  l_false   boolean;
  l_string  varchar2(5);
begin
  l_string := to_char(l_true);
  l_string := to_char(l_false);
end;
/

declare
  l_true    boolean;
  l_false   boolean;
  l_string  nvarchar2(5);
begin
  l_string := to_nchar(l_true);
  l_string := to_nchar(l_false);
end;
/

Comments (0)

Please to add comments

No comments yet. Be the first to comment!