Forum Stats

  • 3,854,709 Users
  • 2,264,411 Discussions
  • 7,905,763 Comments

Discussions

New type BOOLEAN

1234568»

Comments

  • user1361687
    user1361687 Member Posts: 3 Blue Ribbon
    edited Dec 30, 2020 4:20PM

    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
    fridoo Member Posts: 1 Blue Ribbon

    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).

    William Robertson
  • Yevon
    Yevon Member Posts: 121 Blue Ribbon

    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
    C Elgholm Member Posts: 21 Blue Ribbon

    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
  • Yevon
    Yevon Member Posts: 121 Blue Ribbon

    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
    Yevon Member Posts: 121 Blue Ribbon

    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
    user12135243 Member Posts: 1 Blue Ribbon

    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);