Forum Stats

  • 3,825,491 Users
  • 2,260,518 Discussions
  • 7,896,548 Comments

Discussions

New type BOOLEAN

123468

Comments

  • Asterix45
    Asterix45 Member Posts: 11

    I vote for, it will avoid the cast when retrieving objects

    ApexBine
  • Niels Hecker
    Niels Hecker Member Posts: 28 Bronze Badge

    I also would like to have a native boolean type in the database, at least for writing PL/SQL functions that accept/return boolean values which would make WHERE clauses much more readable and understandable at first sight (like e.g. RegExp_Like).

    In our web-application we use an object type to store some data and privileges of the user (stored in a variable "Who" of a package "ws" whereat the package also has a function "Who#()" which just returns the object and enables us to use the object in views). This object has some privilege dedicated functions like "Granted( privilege)" or "IsDeveloper()" which aren't useable in SQL so we always must implement a second function (for the given examples "Granted_()" and "IsDeveloper_()") which return 0 for false and 1 for true.

    So our WHERE clauses always look like "WHERE (ws.Who#().IsDeveloper_() = 1)". But with a real BOOLEAN datatype that would be more readable "WHERE ws.Who#().IsDeveloper()".

    Sven W.Peter Hraško
  • 3361660
    3361660 Member Posts: 1

    The WHERE clause in Oracle SQL uses booleans.  That is to say that BOOLEAN is an intrinsic part of the database's relational operations.  You don't just encounter boolean in PL/SQL or in some host language or embedded-SQL language.  Oracle's database-native type system is incomplete without a boolean column type that comports with SQL WHERE.

    TPD-OpitzSentinelThorsten Kettner
  • 3041703
    3041703 Member Posts: 1

    very good idea

  • [Deleted User]
    [Deleted User] Posts: 0 Silver Trophy

    I'm not so sure about this. If you want to add a proper boolean column to a table, avoiding the 3-value-logic issue, then that would imply a not null constraint for that column straightaway. How will that work: would that constraint be automatically added by Oracle? Would you have to add that constraint yourself (which means folks will forget it, and then have a bug on their hands they can't explain)?

    I'd like to see some more ideas about how this would be implemented before I say yes or no (pun intended ). Having to add the constraint myself would have my preference, because then I can decide depending on my datamodel whether I want to use 2-value-logic or 3-value-logic. Although since I almost always create and use API's to access the data, the whole thing is a bit of a non-issue for me because the appication doesn't even know about tables.

  • ApexBine
    ApexBine Member Posts: 153 Silver Badge

    I'm not so sure about this. If you want to add a proper boolean column to a table, avoiding the 3-value-logic issue, then that would imply a not null constraint for that column straightaway. How will that work: would that constraint be automatically added by Oracle? Would you have to add that constraint yourself (which means folks will forget it, and then have a bug on their hands they can't explain)?

    I'd like to see some more ideas about how this would be implemented before I say yes or no (pun intended ). Having to add the constraint myself would have my preference, because then I can decide depending on my datamodel whether I want to use 2-value-logic or 3-value-logic. Although since I almost always create and use API's to access the data, the whole thing is a bit of a non-issue for me because the appication doesn't even know about tables.

    Absolutely agree - why should other data types be nullable while boolean is not?

    We do need 3-value-logic, and anyone who wants to constrain it can do so.

    Thorsten KettnerPeter Hraško
  • Thorsten Kettner
    Thorsten Kettner Member Posts: 42 Red Ribbon

    We have been waiting for this for ages it seems. My packages are littered with functions like

    FUNCTION is_available01( <long parameter list here> )

    RETURN INTEGER AS

    BEGIN

      IF is_available( <long parameter list here> ) THEN

        RETURN 1;

      ELSE

        RETURN 0;

      END IF;

    END is_available01;

    so as to have proper boolean functions I can use within PL/SQL plus wrappers for the use in SQL.

    And it's annoying to have to write

    WHERE is_active = 0 -- 0 means FALSE

    all the time instead of a mere

    WHERE NOT is_active

    Lukas EderPeter Hraško
  • EricKR-Oracle
    EricKR-Oracle Member Posts: 5 Employee

    It's seems controversial to acheeve.

  • William Robertson
    William Robertson Member Posts: 9,567 Bronze Crown

    It's seems controversial to acheeve.

    Really? Everyone seems to like the idea.

    Lukas EderApexBineThorsten Kettner