OracleBlog

There is no BOOLEAN datatype in Oracle, as far as tables are concerned.

CREATE TABLE BooleanTable (MyBool BOOLEAN);

ORA-00902: invalid datatype

But there is a BOOLEAN datatype in PL/SQL.

CREATE OR REPLACE PROCEDURE BoolProc (in_bool IN BOOLEAN)
AS
my_bool BOOLEAN := TRUE;
BEGIN
IF (in_bool = my_bool) THEN
DBMS_OUTPUT.PUT_LINE('True');
ELSE
DBMS_OUTPUT.PUT_LINE('False or NULL');
END IF;

EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.PUT_LINE('SQLERRM: ' || SQLERRM);
DBMS_OUTPUT.PUT_LINE('SQLCODE: ' || SQLCODE);
END BoolProc;

Why is there no Boolean in Oracle for tables? What should we do instead? This:

CREATE TABLE BoolTable (MyBool CHAR(1) CHECK (MyBool IN ( 'Y', 'N' )));

As for the first question, as usual, let's Ask Tom:

http://asktom.oracle.com/pls/ask/f?p=4950:8:2109566621053828525::NO::F4950_P8_DISPLAYID,
F4950_P8_CRITERIA:6263249199595