Type BOOLEAN is extended in 23/26AI and supports more values for sql and PL/SQL

Lets create simple test table

DROP TABLE IF EXISTS test_tbl ;

CREATE TABLE test_tbl
(
  bool_id  NUMBER,
  bool_str VARCHAR2(20),
  bool_1   BOOLEAN,
  bool_2   BOOL
);

INSERT INTO test_tbl
(
  bool_id,
  bool_str,
  bool_1,
  bool_2
)
VALUES
(1, 'values: true false', TRUE, FALSE),
(2, 'values: TRUE FALSE', TRUE, FALSE),
(3, 'string: true false', 'true', 'false'),
(4, 'string: TRUE FALSE', 'TRUE', 'FALSE'),
(5, 'string: yes no', 'yes', 'no'),
(6, 'string: YES NO', 'YES', 'NO'),
(7, 'string: on off', 'on', 'off'),
(8, 'string: ON OFF', 'ON', 'OFF'),
(9, 'numeric: 1 0', 1, 0),
(10, 'string: 1 0', '1', '0'),
(11, 'string: t f', 't', 'f'),
(12, 'string: T F', 'T', 'F'),
(13, 'string: y n', 'y', 'n'),
(14, 'string: Y N', 'Y', 'N'),
(15, 'values: null null', NULL, NULL);


COMMIT;

SELECT * FROM test_tbl;

BOOL_ID  BOOL_STR               BOOL_1  BOOL_2
-------  ---------------------  ------  ------
1        values: true false     true    false
2        values: TRUE FALSE     true    false
3        string: true false     true    false
4        string: TRUE FALSE     true    false
5        string: yes no         true    false
6        string: YES NO         true    false
7        string: on off         true    false
8        string: ON OFF         true    false
9        numeric: 1 0           true    false
10       string: 1 0            true    false
11       string: t f            true    false
12       string: T F            true    false
13       string: y n            true    false
14       string: Y N            true    false
15       values: null null      

You can reference BOOLEAN columns using more values not only TRUE/FALSE

SELECT 'true' val, COUNT(*) cnt FROM test_tbl WHERE bool_1 = TRUE
UNION ALL
SELECT 'TRUE', COUNT(*) FROM test_tbl WHERE bool_1 = TRUE
UNION ALL
SELECT '''true''', COUNT(*) FROM test_tbl WHERE bool_1 = 'true'
UNION ALL
SELECT '''TRUE''', COUNT(*) FROM test_tbl WHERE bool_1 = 'TRUE'
UNION ALL
SELECT '''yes''', COUNT(*) FROM test_tbl WHERE bool_1 = 'yes'
UNION ALL
SELECT '''YES''', COUNT(*) FROM test_tbl WHERE bool_1 = 'YES'
UNION ALL
SELECT '''on''', COUNT(*) FROM test_tbl WHERE bool_1 = 'on'
UNION ALL
SELECT '''ON''', COUNT(*) FROM test_tbl WHERE bool_1 = 'ON'
UNION ALL
SELECT '1', COUNT(*) FROM test_tbl WHERE bool_1 = 1
UNION ALL
SELECT '''1''', COUNT(*) FROM test_tbl WHERE bool_1 = '1'
UNION ALL
SELECT '''t''', COUNT(*) FROM test_tbl WHERE bool_1 = 't'
UNION ALL
SELECT '''T''', COUNT(*) FROM test_tbl WHERE bool_1 = 'T'
UNION ALL
SELECT '''y''', COUNT(*) FROM test_tbl WHERE bool_1 = 'y'
UNION ALL
SELECT '''Y''', COUNT(*) FROM test_tbl WHERE bool_1 = 'Y'
UNION ALL
SELECT 'NULL', COUNT(*) FROM test_tbl WHERE bool_1 IS NULL;

CONDITION  CNT
---------  ---
true       14
TRUE       14
'true'     14
'TRUE'     14
'yes'      14
'YES'      14
'on'       14
'ON'       14
1          14
'1'        14
't'        14
'T'        14
'y'        14
'Y'        14
NULL       1

New function TO_BOOLEAN is introduced also BOOLEAN type can be converted to NUMBER, CHAR

--Simple example

SELECT
  TO_BOOLEAN('true') literal,
  TO_NUMBER(bool_1)  num_col,
  TO_CHAR(bool_1)    char_col,
  TO_NCHAR(bool_1)   nchar_col
FROM test_tbl
WHERE bool_id = 1;

LITERAL  NUM_COL  CHAR_COL  NCHAR_COL
-------  -------  --------  ---------
true     1        TRUE      TRUE

In PL/SQL it works as well however parameter plsql_implicit_conversion_bool must be set to TRUE

ALTER SESSION SET plsql_implicit_conversion_bool=TRUE;


DECLARE
  l_boolean BOOLEAN;
BEGIN
  ------------------------------------------------------------------
  -- BOOLEAN literals
  ------------------------------------------------------------------
  l_boolean := TRUE;
  l_boolean := FALSE;

  ------------------------------------------------------------------
  -- string literals (lowercase)
  ------------------------------------------------------------------
  l_boolean := 'true';
  l_boolean := 'false';
  l_boolean := 'yes';
  l_boolean := 'no';
  l_boolean := 'on';
  l_boolean := 'off';
  l_boolean := 't';
  l_boolean := 'f';
  l_boolean := 'y';
  l_boolean := 'n';

  ------------------------------------------------------------------
  -- string literals (uppercase)
  ------------------------------------------------------------------
  l_boolean := 'TRUE';
  l_boolean := 'FALSE';
  l_boolean := 'YES';
  l_boolean := 'NO';
  l_boolean := 'ON';
  l_boolean := 'OFF';
  l_boolean := 'T';
  l_boolean := 'F';
  l_boolean := 'Y';
  l_boolean := 'N';

  ------------------------------------------------------------------
  -- numeric literals
  ------------------------------------------------------------------
  l_boolean := 1;
  l_boolean := 0;

  ------------------------------------------------------------------
  -- NULL
  ------------------------------------------------------------------
  l_boolean := NULL;
END;
/

In other case you will get error

ALTER SESSION SET plsql_implicit_conversion_bool=FALSE;

DECLARE
  l_boolean BOOLEAN;
BEGIN
  l_boolean := TRUE;
  l_boolean := 'n';
END;
/

  l_boolean := 'n';
               *
ERROR at line 5:
ORA-06550: line 5, column 16:
PLS-00382: expression is of wrong type

Have a fun 🙂

Tomasz