Oracle Database 23ai: Boolean Data Type
The Boolean Data Type has been introduced in Oracle Database 23ai. In this post, we will look at how we can use this new data type.
Let us create a table called INVOICES with two columns – Invoice Number and Paid Status. We use the Boolean Data Type for the Paid Status column.
SQL> CREATE TABLE invoices(invoice_num VARCHAR2(10), paid_status BOOLEAN);
Table INVOICES created.
We can use true or false to record whether the invoice has been paid.
SQL> INSERT INTO invoices VALUES('INV_001', true);
1 row inserted.
SQL> INSERT INTo invoices VALUES('INV_002', false);
1 row inserted.
We can use ‘Yes’ or ‘No’ as well.
SQL> INSERT INTO invoices VALUES('INV_003', 'Yes');
1 row inserted.
SQL> INSERT INTo invoices VALUES('INV_004', 'No');
1 row inserted.
Or ‘Y’ or ‘N’.
SQL> INSERT INTO invoices VALUES('INV_005', 'Y');
1 row inserted.
SQL> INSERT INTo invoices VALUES('INV_006', 'N');
1 row inserted.
We can even use ‘1’ or ‘0’ to represent the boolean.
SQL> INSERT INTO invoices VALUES('INV_007', 1);
1 row inserted.
SQL> INSERT INTo invoices VALUES('INV_008', 0);
1 row inserted.
We can also insert NULL values in a column of data type boolean, unless there is a NOT NULL constrained applied on the column.
SQL> INSERT INTo invoices VALUES('INV_009', NULL);
1 row inserted.
We query the table. In old versions of SQL*Plus and all versions of SQLcl, data will be represented either by 1/0,
SQL> SELECT * FROM invoices;
INVOICE_NUM PAID_STATUS
______________ ______________
INV_001 1
INV_002 0
INV_003 1
INV_004 0
INV_005 1
INV_006 0
INV_007 1
INV_008 0
INV_009
9 rows selected.
New versions will display true/false.
SQL> SELECT * FROM invoices;
INVOICE_NUM PAID_STATUS
______________ ______________
INV_001 TRUE
INV_002 FALSE
INV_003 TRUE
INV_004 FALSE
INV_005 TRUE
INV_006 FALSE
INV_007 TRUE
INV_008 FALSE
INV_009
9 rows selected.
Now let us look at the SQL statements. You can use either of the following statements.
SQL> SELECT * FROM invoices WHERE paid_status is true;
INVOICE_NUM PAID_STATUS
______________ ______________
INV_001 1
INV_003 1
INV_005 1
INV_007 1
SQL> SELECT * FROM invoices WHERE paid_status = 1;
INVOICE_NUM PAID_STATUS
______________ ______________
INV_001 1
INV_003 1
INV_005 1
INV_007 1
SQL> SELECT * FROM invoices WHERE paid_status = 'YES';
INVOICE_NUM PAID_STATUS
______________ ______________
INV_001 1
INV_003 1
INV_005 1
INV_007 1
SQL> SELECT * FROM invoices WHERE paid_status = 'Y';
INVOICE_NUM PAID_STATUS
______________ ______________
INV_001 1
INV_003 1
INV_005 1
INV_007 1
SQL> SELECT * FROM invoices WHERE paid_status = 'y';
INVOICE_NUM PAID_STATUS
______________ ______________
INV_001 1
INV_003 1
INV_005 1
INV_007 1
Or simply use the following.
SQL> SELECT * FROM invoices WHERE paid_status;
INVOICE_NUM PAID_STATUS
______________ ______________
INV_001 1
INV_003 1
INV_005 1
INV_007 1
Now, say, we already have a table – invoices_2, that is already in use. And you want to convert the paid_status column which is a VARCHAR2 column to BOOLEAN.
SQL> DESC invoices_2
Name Null? Type
______________ ________ _______________
INVOICE_NUM VARCHAR2(10)
PAID_STATUS VARCHAR2(1)
If the paid_status column is NULL for all the records, then you can easily modify the column.
SQL> ALTER TABLE invoices_2 MODIFY paid_status BOOLEAN;
Table INVOICES_2 altered.
SQL> DESC invoices_2
Name Null? Type
______________ ________ _______________
INVOICE_NUM VARCHAR2(10)
PAID_STATUS BOOLEAN
However, if say you already have a table with data populated.
SQL> SELECT * FROM invoices_3;
INVOICE_NUM PAID_STATUS
______________ ______________
INV_001 Y
INV_002
If you try to convert the column to BOOLEAN, Oracle will throw an error.
SQL> ALTER TABLE invoices_3 MODIFY paid_status BOOLEAN;
Error starting at line : 1 in command -
ALTER TABLE invoices_3 MODIFY paid_status BOOLEAN
Error report -
ORA-01439: column to be modified must be empty to change datatype
01439. 00000 - "column to be modified must be empty to change datatype"
*Cause: An ALTER TABLE MODIFY statement attempted to change the
datatype of a column containing data. A column whose datatype
was to be altered must contain only NULL values.
*Action: To alter the datatype, first set all values in the
column to NULL.
To convert the column to BOOLEAN, you can follow the steps below.
First add another column, say paid_status_new of type BOOLEAN, to the table
SQL> ALTER TABLE invoices_3 ADD paid_status_new BOOLEAN;
Table INVOICES_3 altered.
SQL> DESC invoices_3
Name Null? Type
__________________ ________ _______________
INVOICE_NUM VARCHAR2(10)
PAID_STATUS VARCHAR2(1)
PAID_STATUS_NEW BOOLEAN
Use the TO_BOOLEAN function to update the PAID_STATUS_NEW column.
SQL> UPDATE invoices_3 SET PAID_STATUS_NEW = TO_BOOLEAN(paid_status);
2 rows updated.
SQL> SELECT * FROM invoices_3 ;
INVOICE_NUM PAID_STATUS PAID_STATUS_NEW
______________ ______________ __________________
INV_001 Y 1
INV_002
Set the old column as Unused.
SQL> ALTER TABLE invoices_3 SET UNUSED COLUMN paid_status;
Table INVOICES_3 altered.
Now, rename the new column.
SQL> ALTER TABLE invoices_3 SET UNUSED COLUMN paid_status;
Table INVOICES_3 altered.
SQL> ALTER TABLE invoices_3 RENAME COLUMN paid_status_new to paid_status;
Table INVOICES_3 altered.
SQL> DESC invoices_3
Name Null? Type
______________ ________ _______________
INVOICE_NUM VARCHAR2(10)
PAID_STATUS BOOLEAN
SQL> SELECT * FROM invoices_3;
INVOICE_NUM PAID_STATUS
______________ ______________
INV_001 1
INV_002
References
RishOraDev's Oracle Blogs 