Boolean Data Type in Oracle ِDatabase 23ai

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 You can also use the keyword BOOL instead of BOOLEAN: SQL> CREATE TABLE jadval1 (id NUMBER, is_accept BOOL); Table created You can set default values for Boolean columns: SQL> CREATE TABLE table1 (id NUMBER, is_accept BOOL DEFAULT FALSE); Table created 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 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 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 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 Telegram channel :https://t.me/oracledb

Apr 14, 2025 - 17:43

Boolean Data Type in Oracle ِDatabase 23ai

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

You can also use the keyword BOOL instead of BOOLEAN:

SQL> CREATE TABLE jadval1 (id NUMBER, is_accept BOOL);
Table created

You can set default values for Boolean columns:

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

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

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

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

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

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