Boolean data type

The BOOLEAN data type stores the result of a logical operation.

The BOOLEAN data type can be TRUE, FALSE, or NULL. NULL indicates an unknown result.

The syntax for assigning a value to a BOOLEAN variable is as follows:

variable_name BOOLEAN

Because the BOOLEAN data type is not a valid SQL data type, it has the following limitations:

• You cannot assign a BOOLEAN value to a column in a table.

• You cannot assign data from a table to a BOOLEAN variable.

• You cannot use a BOOLEAN variable in any SQL function.

• You cannot use a BOOLEAN variable in an SQL statement unless it is passed in by a PL function.

Here is an example:

obclient> CREATE OR REPLACE PROCEDURE output_bool (b BOOLEAN)
AUTHID DEFINER IS
BEGIN
  DBMS_OUTPUT.PUT_LINE (
    CASE
      WHEN b IS NULL THEN 'NA'
      WHEN b THEN 'Y'
      WHEN NOT b THEN 'N'
    END
  );
END;
/
obclient> BEGIN
  output_bool(TRUE);
  output_bool(FALSE);
  output_bool(NULL);
END;
 /
Query OK, 0 rows affected

Y
N
NA