New type BOOLEAN

user1361687

Boolean:

Varchar(1) = '1' True, '0' False

Bool1 * Bool2 = IF Bool1 AND Bool2

Multilingual

Can readapt the concept of Boolean

Efficiency vs Number(1) 1|0

Can use a default value (1|0) as not explicit value

fridoo

As Chris Date pointed out: Boolean is a fundamental datatype. In fact, it's the only fundamental datatype. A database just supporting boolean as a datatype could function perfectly (though it wouldn't be very useful). It's just not explicable why SQL (or any other relational database language) is full of expressions yielding a boolean result (to be precise: a three-valued boolean) yet these cannot be stored in the database. It's a clear violation of the principle of closure which is one of the key features of relational databases and relational database languages.

ANSI-SQL99 finally included Boolean (though in a crippled way). Better late than never.

Boolean is absolutely the most desired but unsupported datatype in Oracle database. If it were present, it would probably rank 3rd or 4th in the list of most used datatypes (after VARCHAR2, NUMBER and possibly DATE, but before the many more obscure datatypes which are supported).

Yevon

Absolutely agree, this is a must have. There is no standard way for defining binary values in the database, and you are unable to call plsql procedures with boolean parameters from sql.

C Elgholm

This is a no-brainer. Just add it.

We don't care how it is stored internally, or what hoops one has to jump through to use it, "where COL=true|false|is null|is not null" is fine. Just fine. I don't need any dangling syntax, "where COL".

Just for the ability to call PL/SQL, instead of having to wrap and overload each and every possible combo, this needs to be added to the SQL-engine.

Yevon

Agree, this is that basic that is surprising that oracle prioritize much secondary features. Any developer has to deal with this daily and make workarrounds for avoid this limitations.

Yevon

If you don't find this useful, you don't really use plsql that much. It is very annoying having to avoid using booleans in plsql because sql just doesn't support it. Not even oracle has consistency for defining binary values in the database or in related products. Sometimes is Y/N, others Yes/No, others YES/NO, others 0/1. Most of modern databases do support this.

user12135243

Need this:

CREATE TABLE x (BOOLEAN_COL1 BOOLEAN DEFAULT FALSE)

SELECT ... WHERE BOOLEAN_COL1 IS TRUE

SELECT ... WHERE BOOLEAN_COL1 IS FALSE

INSERT INTO x (BOOLEAN_COL1) VALUES ( 1 < 0);

INSERT INTO x (BOOLEAN_COL1) VALUES (TRUE);

INSERT INTO x (BOOLEAN_COL1) VALUES (FALSE);

INSERT INTO x (BOOLEAN_COL1) VALUES (COL_X IS NULL);

INSERT INTO x (BOOLEAN_COL1) VALUES (0);

INSERT INTO x (BOOLEAN_COL1) VALUES (1);