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
Leave a Reply