Discussions
Categories
- 17.9K All Categories
- 3.4K Industry Applications
- 3.4K Intelligent Advisor
- 75 Insurance
- 537.7K On-Premises Infrastructure
- 138.7K Analytics Software
- 38.6K Application Development Software
- 6.1K Cloud Platform
- 109.6K Database Software
- 17.6K Enterprise Manager
- 8.8K Hardware
- 71.3K Infrastructure Software
- 105.4K Integration
- 41.6K Security Software
New type BOOLEAN

BOOLEAN is available in PL/SQL but not in SQL.
This request is now referenced as: Enh 28146286 - SUPPORT FOR ISO SQL BOOLEAN DATA TYPE
Comments
-
-
-
It would be good if there was some auto conversion so that the new type integrates into existing applications.
E.g. lots of applications use a NUMBER(1) field constrained to {0;1} to represent the boolean state. I think this easy to aoutoconvert to the hypothetical boolean type.
But there are others using a VARCHAR[2] column constraint to some meaningfull words like {'TRUE';'FALSE'}, {'YES';'NO'}, {'ON';'OFF'} or their localized translations.
Auto conversion should work with those too.
On the other hand a converson method would be handy like we have for Dates:
TO_BOOLEAN(the_legacy_value_to_convert, THE_LEGACY_TRUE_VALUES_AS_CSV_LIST)
It should look up the (non null) first Parameter in the second parameter and return TRUE when found, FLASE otherwise.
bye
TPD
-
Would it be a NUMBER(1) or a VARCHAR2(1)?
-
Would it be a NUMBER(1) or a VARCHAR2(1)?
Top, would it be Boolean not NUMBER or Varchar.
Regards
-
I vote for this, it would be nice to have boolean as column data type
-
People have been asking for it for ages.
What are the arguments against it, actually?
-
Boolean datatypes are typically used for decision making and that is most often done in PL/SQL which already has a boolean datatype.
So other than in a CASE statement how would a boolean datatype be of any benefit in SQL?
-
Boolean datatypes are typically used for decision making and that is most often done in PL/SQL which already has a boolean datatype.
So other than in a CASE statement how would a boolean datatype be of any benefit in SQL?
Hi rp0428,
booleans are not only used in programming, very often they are just part of the data and properties of the objects the data represents. (Typically 'is' or 'has' properties)
Just look at the real world - haven't you come across all the different efforts to express boolean values inside a table like TPD-Opitz described above?
Sometimes people even use different representations within the same database
-
Boolean datatypes are typically used for decision making and that is most often done in PL/SQL which already has a boolean datatype.
So other than in a CASE statement how would a boolean datatype be of any benefit in SQL?
All those Y/N flag columns (or 0/1 etc) would become Boolean and automatically become language-independent, self-documenting and constrained to two values (plus null). It might even encourage people to define things as Boolean rather than resorting to arbitrary letter conventions. ('A' and 'I' for Active/Inactive is the most recent one I've come across, but I've seen 'R'/'F' for Running/Failed etc etc).