Yes you can throw some shade Oracle’s way for taking so long, but we now finally have a Boolean datatype arriving in the latest version of the database.
SQL> create table person (
2 pid int,
3 married boolean);
Table created.
SQL> insert into person values (1,true);
1 row created.
SQL> insert into person values (2,false);
1 row created.
Perhaps an acknowledgement to the us being a little late to the game, we know that you probably already have implemented your Boolean via other means, say, as a string or number. One nice thing with our new datatype is that we’ll allow a nice broad selection of these typical values to be used, and we’ll automatically convert them to the equivalent Boolean representation.
SQL> insert into person values (3,'on');
1 row created.
SQL> insert into person values (4,'OFF');
1 row created.
SQL> insert into person values (5,1);
1 row created.
SQL> insert into person values (6,0);
1 row created.
SQL> insert into person values (8,'Y');
1 row created.
SQL> insert into person values (9,'Yes');
1 row created.
We can see that these all become Booleans upon subsequent query.
SQL> select * from person;
PID MARRIED
---------- -----------
1 TRUE
2 FALSE
3 TRUE
4 FALSE
5 TRUE
6 FALSE
8 TRUE
9 TRUE
8 rows selected.
This is nice because you might change an existing column to Boolean but perhaps one little piece of code in one of your existing applications slipped through the gap and is still trying to insert a string or number into your table.
Similarly, the intention for queries for using the new Boolean datatype is that you simply reference the column, for it is already the value TRUE or FALSE, so it does not have to be the result of an expression.
SQL> select *
2 from person
3 where married;
PID MARRIED
---------- -----------
1 TRUE
3 TRUE
5 TRUE
8 TRUE
9 TRUE
But that raises another interesting issue when it comes to application migration. What if you had an existing application that was initially built on 19c and uses the 19c client. The current version of the application would look something like this:
SQL> create table person (
2 pid int,
3 married varchar2(1));
Table created.
SQL>
SQL> insert into person values (1,'Y');
1 row created.
SQL> insert into person values (1,'N');
1 row created.
SQL>
SQL> select * from person
2 where married = 'Y';
PID M
---------- -
1 Y
Lets say you migrate that MARRIED column to Boolean using one of the ways covered by this video.
What is going to happen to this application if it is left untouched and yet the underlying database is moved to 26ai and the column changed to a Boolean?
With the 19c client, even though it does not understand a Boolean, the client driver will by default map the output to numeric on adhoc query. The DESCRIBE function in 19c shows a Boolean
SQL> desc person
Name Null? Type
----------------------------- -------- --------------------
PID NUMBER(38)
MARRIED BOOLEAN
but the output is cast to a datatype understood by 19c
SQL> select * from person;
PID MARRIED
---------- ----------
1 1
2 0
3 1
4 0
5 1
6 0
8 1
9 1
8 rows selected.
DML performed by the 19c client will also keep working, taking advantage of the automatic conversions shown earlier.
SQL> insert into person values (10,1);
1 row created.
SQL> insert into person values (11,'Y');
1 row created.
But your existing applications will also have code that uses the original datatype as a predicate. What is going to happen with that? In the main, they will continue to work as well
SQL> select * from person where married = 0;
PID MARRIED
---------- ----------
2 0
4 0
6 0
SQL>
SQL> select * from person where married = 'Y';
PID MARRIED
---------- ----------
1 1
3 1
5 1
8 1
9 1
10 1
11 1
7 rows selected.
However, we cannot guarantee that everything will work unchanged, because we can’t really see all of your code base and how your code retrieves data from the database into local variables.
Here’s some examples of where your code might need adjustment:
1. Rowtype variable doesn’t crash
SQL>
SQL> declare
2 l_row person%rowtype;
3 begin
4 select * into l_row
5 from person
6 where pid = 10;
7 end;
8 /
PL/SQL procedure successfully completed.
2. Original code assumed MARRIED was Y or N.
SQL> declare
2 l_pid number;
3 l_married varchar2(1);
4 begin
5 select pid, married
6 into l_pid, l_married
7 from person
8 where married = 'Y'
9 and rownum = 1;
10 end;
11 /
declare
*
ERROR at line 1:
ORA-06502: PL/SQL: value or conversion error: character
string buffer too small
ORA-06512: at line 5
In the example, we saw that you bound a VARCHAR2 to the fetched result, so we returned a string. However, that string was not Y or N as your code was expecting.
SQL> set serverout on
SQL> declare
2 l_pid number;
3 l_married varchar2(10);
4 begin
5 select pid, married
6 into l_pid, l_married
7 from person
8 where married = 'Y'
9 and rownum = 1;
10
11 dbms_output.put_line('l_married='||l_married);
12 end;
13 /
l_married=TRUE
PL/SQL procedure successfully completed.
So in this case, you need to increase the size of the target variable
SQL> declare
2 l_pid number;
3 l_married varchar2(10);
4 begin
5 select pid, married
6 into l_pid, l_married
7 from person
8 where married = 'Y'
9 and rownum = 1;
10 end;
11 /
PL/SQL procedure successfully completed.
But be aware, downstream code may not be aware of the change in value. In the below example, we would end up with a logic error because the IF-test would no longer work as expected.
SQL> set serverout on
SQL> declare
2 l_pid number;
3 l_married varchar2(10);
4 begin
5 select pid, married
6 into l_pid, l_married
7 from person
8 where married = 'Y'
9 and rownum = 1;
10
11 if l_married = 'Y' then
12 dbms_output.put_line('YES MARRIED');
13 end if;
14 end;
15 /
PL/SQL procedure successfully completed.
I stress – I’m not saying you can just change all of your datatypes to Boolean where appropriate and just leave all of your applications unchanged and they’ll all work out of the box. The nice integration with existing datatypes is designed to minimize the changes of unexpected crashes for any code coverage that you missed, not to endorse an approach of “Hey we don’t need to touch anything“
Learning is not a spectator sport 
Got some thoughts? Leave a comment