DEV Community

Vahid Yousefzadeh
Vahid Yousefzadeh

Posted on

Boolean Data Type in Oracle ِDatabase 23ai

#oracle #linux #sql #data

Oracle introduced the Boolean data type in SQL with version 23ai (although this data type has existed in PL/SQL previously). With this feature, you can define table columns as Boolean during table creation.

SQL> CREATE TABLE jadval1 (id NUMBER, is_accept BOOLEAN);
Table created
Enter fullscreen mode Exit fullscreen mode

You can also use the keyword BOOL instead of BOOLEAN:

SQL> CREATE TABLE jadval1 (id NUMBER, is_accept BOOL);
Table created
Enter fullscreen mode Exit fullscreen mode

You can set default values for Boolean columns:

SQL> CREATE TABLE table1 (id NUMBER, is_accept BOOL DEFAULT FALSE);
Table created
Enter fullscreen mode Exit fullscreen mode

To populate the is_accept column, you can use the three keywords: TRUE, FALSE, and NULL:

SQL> insert into table1 values(1, true);
1 row inserted
SQL> insert into table1 values(2, null);
1 row inserted
SQL> insert into table1 values(3, false);
1 row inserted
SQL> select * from table1;
        ID    IS_ACCEPT
---------- ----------
         1          1
         2 
         3          0
Enter fullscreen mode Exit fullscreen mode

Additionally, you are not limited to using just the three keywords. Strings can also be used for assigning values to Boolean columns.

The following strings are equivalent to FALSE:

'false', 'no', 'off', '0', 'f', 'n'

The following strings are equivalent to TRUE:

'true', 'yes', 'on', '1', 't', 'y'

Example:

SQL> CREATE TABLE jadval1 (id NUMBER, c1 BOOL,c2 BOOL,c3 BOOL,c4 BOOL,c5 BOOL,c6 BOOL,c7 BOOL,c8 BOOL);
Table created
SQL> insert into jadval1 values(1,true,'true', 'yes', 'on', '1', 't', 'y',false);
1 row inserted
SQL> insert into jadval1 values(2,false,'false' , 'no' , 'off' , '0' , 'f' , 'n',true);
1 row inserted
Enter fullscreen mode Exit fullscreen mode

Image description
The number 0 is treated as FALSE, and any other number is considered TRUE:

SQL> insert into jadval1 values(1,0,66,9.10);
1 row created.
SQL> select * from jadval1;
 ID C1     C2     C3
--- ------ ------ -----
  1 FALSE  TRUE   TRUE
Enter fullscreen mode Exit fullscreen mode

When running queries, remember that AND and OR operators can be used with columns of the Boolean data type:

SQL> select * from jadval1 where c1 and c2;
no rows selected
SQL> select * from jadval1 where c3 and c2;
 ID C1     C2     C3
--- ------ ------ -----
  1 FALSE  TRUE   TRUE
Enter fullscreen mode Exit fullscreen mode

Telegram channel :https://t.me/oracledb

Top comments (0)