334

Is there any Boolean type in Oracle databases, similar to the BIT datatype in Ms SQL Server?

shA.t
  • 16,580
  • 5
  • 54
  • 111
Peder
  • 3,341
  • 2
  • 15
  • 3
  • 9
    Unfortunately Oracle does not fully support the ANSI SQL:1999 standard (http://en.wikipedia.org/wiki/SQL:1999) when this was introduced. – Jeffrey Kemp Sep 17 '10 at 04:36
  • 2
    Alternative viewpoint (why SQL **shouldn't** have a boolean type): http://vadimtropashko.wordpress.com/2010/09/16/on-boolean-datatype-in-sql-and-beyond/ – Jeffrey Kemp Sep 17 '10 at 04:48
  • 18
    @JeffreyKemp That blog is non-sensical. Just because some boolean vales may be calculated based on other fields in a table, doesn't mean all boolean fields may be calculated. For example "is_trusted_customer" where this is true if and only if a human decides, "I trust that person." – Jay Jan 22 '14 at 01:37
  • @Jacob (just playing devil's advocate here) "is_trusted_customer" could be redesigned as "customer_trust_score > 0" :) P.S. I don't necessarily agree with the article. – Jeffrey Kemp Jan 22 '14 at 04:28
  • possible duplicate of [Boolean Field in Oracle](http://stackoverflow.com/questions/30062/boolean-field-in-oracle) –  Jul 03 '14 at 14:42
  • 4
    @JeffreyKemp Congratulations, you've just reinvented C-style booleans (where you're using `int`s instead). We should definitely go back to those in code. Additionally, the argument completely falls apart if the data types between table columns and *result columns* (from a `SELECT`) are shared, since it is absolutely appropriate to return a boolean as a computed result sometimes even given the rest of the argument. – jpmc26 Apr 21 '15 at 19:26
  • No, I have invented nothing. In the expression "score > 0", score is a number, not a boolean (C style or otherwise). – Jeffrey Kemp Apr 26 '15 at 13:33
  • 1
    @JeffreyKemp My point is that if applications and users don't care about the magnitude of the score (which is the only time you would consider using a boolean instead of a number), then it effectively is a boolean, where everything >0 is true and 0 is false. Such a choice would actually *obscure* the true meaning of the column without improvement of the system. (I am aware that you don't necessarily agree with the article's point of view, so I apologize that my wording was rather personal. I meant to address the concept.) – jpmc26 Apr 29 '15 at 17:12
  • 4
    Yes. More data types like booleans would give more exact expressive power - you'll get no argument from me on that front. I'm just glad we at least have a `DATE` type - imagine having to deal with string representations of dates all the time :) – Jeffrey Kemp Apr 29 '15 at 22:59
  • 1
    More data types is not necessarily the better. Having a single number datatype in oracle is a blessing. Speaking of dates, if dates were just numbers of seconds (with some supported functions) perhaps then we didn't have to go through silly ritual of adjusting myriads of clocks twice a year? – Tegiri Nenashi Jul 07 '15 at 23:50
  • @TegiriNenashi Oracle actually supports NUMBER, BINARY_FLOAT, and BINARY_DOUBLE in SQL. Got no use for the latter two, though. – David Aldridge Sep 10 '15 at 07:48
  • 1
    No, boolean datatype doesn't add any expressive power. It is just syntactic sugar for 0 and 1 values. In fact, unlike logicians mathematicians refer to booleans as elements of Z/(2). From practical perspective, how about boolean conjunction? It is plain multiplication xy. How about disjunction? It is x+y-xy. Negation? 1-x. – Tegiri Nenashi Feb 25 '16 at 19:09
  • @JeffreyKemp Unless you're being sarcastic about the `DATE` datatype: How is using '01-JAN-1970' to create a date or `TO_DATE('2012-06-05', 'YYYY-MM-DD')` **not** using Strings to represent dates? Honestly, I'd rather just store UNIX timestamps as `NUMBER` values. (It's what I've been taught to do and have never been given reason not to.) – Agi Hammerthief Jul 17 '18 at 08:37
  • 1
    @AgiHammerthief no I wasn't being sarcastic. The `DATE` datatype is one of the best parts of SQL. (BTW, `'01-JAN-1970'` is a string, NOT a date; and `to_date` returns a `date`, not a string) A `DATE` value represents a date (date/time, actually) unambiguously (something strings and numbers cannot do) and the basic comparison and arithmetic operators work on it in useful ways that you can't do with strings or numeric values. – Jeffrey Kemp Jul 17 '18 at 12:43
  • @JeffreyKemp UNIX timestamps use numbers unambiguously (number of seconds or milliseconds before or since 01 Jan 1970, UTC/GMT, or 0). `'01-JAN-1970'` and `TO_DATE('2012-06-05', 'YYYY-MM-DD')` both use Strings to provide values to a data type that has some other (internal) representation that is not apparent by simple/visual inspection. Any decent programming language (including SQL) should have `String from_unix_timestamp(long inVal, String format)` and `long to_unix_timestamp(String inVal, String format)` functionality, part of which should allow specifying date format, TZ & DST offset. – Agi Hammerthief Jul 17 '18 at 13:46
  • @JeffreyKemp For a UNIX Timestamp `x` in milliseconds , `x + 1` will return 1 millisecond after `x`. `x + (24 * 60 * 60 * 1000)` will return 24 hours after `x`, assuming the number of milliseconds in an hour has not increased since the date represented by `x`. In the case of the year 1752, The UNIX timestamp for `01 January 1752 00:00:00 GMT+00:00` is `-6879427200000`. An oracle DATE would represent this as '01-JAN-52', which is ambiguous, given the lack of precision on the year portion and the failure to show the hours, minutes, seconds and GMT offset. – Agi Hammerthief Jul 18 '18 at 12:05
  • 1
    @AgiHammerthief, no - Oracle does NOT represent dates as strings. `'01-JAN-52'`, as you correctly point out, is just a string of characters and is not a date and is certainly ambiguous. Oracle does not use strings like this to represent dates. – Jeffrey Kemp Jul 19 '18 at 01:11

11 Answers11

358

Before version 23c (2023), not only was the boolean datatype missing in Oracle's SQL (not PL/SQL), but they also had no clear recommendation about what to use instead. See this thread on asktom. From recommending CHAR(1) 'Y'/'N' they switch to NUMBER(1) 0/1 when someone points out that 'Y'/'N' depends on the English language, while e.g. German programmers might use 'J'/'N' instead.

The worst thing is that they defend this stupid decision just like they defend the ''=NULL stupidity.

Oracle 23c finally supports Boolean data types in all contexts, along with several other features it has famously lacked compared to other databases.

SQL> select true;

TRUE
-----------
TRUE

SQL> create table test1(a boolean);

Table created.

SQL> insert into test1 values (true),(false),(to_boolean(0)),(to_boolean('y'));

4 rows created.

But the empty string issue will never change.

Philippe Cloutier
  • 156
  • 10
Erich Kitzmueller
  • 36,381
  • 5
  • 80
  • 102
  • 17
    1/0 is, if not ambiguous, at least less ambiguous. – Adam Musch Sep 16 '10 at 16:13
  • 1
    I also prefer 1/0, but of course you will also find people with a Visual Basic background who choose -1/0 instead. IMO there is only one sane solution to that problem: Oracle should create a boolean datatype. Can't be that hard when almost every other RDBMS has one. – Erich Kitzmueller Sep 16 '10 at 18:13
  • 29
    But ''=NULL is false! '' IS NULL is true. :) – Jim Davis Sep 16 '10 at 20:19
  • 4
    Michael-O: I have seen that several times. For me, it's 0/1 all the time, but other programmers prefer J/N. (I live in a German speaking country) – Erich Kitzmueller Feb 07 '12 at 08:48
  • 2
    I've seen French-speaking programmers use O/N as well... (O for oui) – Pierre Henry May 04 '15 at 12:51
  • 2
    Just as an amusement, allow me to mention that I once had to store a very great number of booleans (hundreds) in a rows of a table, and successfully combined them all into a small quantity of NUMBER types, accessing and in some cases indexing them individually by using BITAND functions embedded in a view. I'm not sure I'd care to again, but it pretty cool at the time. One particularly good feature was the ability to find rows that effectively matched 100+ predicates on these booleans with a single predicate of the form: "MY_NUMBER = 45439794". – David Aldridge Sep 10 '15 at 08:01
  • 1
    I'd love to see a (speaking) language pop up, where `yes` and `no` begin with the same letter... (quick googling hasn't help find one) – Irfy Sep 23 '15 at 10:36
  • 12
    @Irfy Recently, I saw `N` and `F` being used, because `ON` and `OFF` begin with the same letter... – JimmyB Dec 10 '15 at 16:15
  • 3
    Using something different from "Y/N" because your mother language is not english makes sense as much as refusing to use "SELECT", "FROM", "WHERE", etc. for the same reason. – ocramot Sep 30 '16 at 12:29
  • 2
    @ocramot: For many German speaking programmers (working in a German speaking countriy, on a software for a German speaking client ...), using "J" and "N" makes perfect sense. It's something you have to expect. BTW, in the world of SAP, the convention is to use "X" for true and " " for false. – Erich Kitzmueller Sep 30 '16 at 12:54
  • @ammoQ I know I should expect it, I live in Italy and I'm used to find "S/N" as options; but I still find it weird in a context where all the other words are in english, especially if it is a substitute of a boolean. – ocramot Oct 01 '16 at 06:52
  • 9
    one might argue that as a replacement for a boolean, 'T'/'F' makes even more sense – Erich Kitzmueller Oct 03 '16 at 08:32
  • 1
    this is extra stupid when using an ORM like Entity Framework. you have to jump through hoops to map above insanity. – Sonic Soul Apr 18 '17 at 14:49
  • 2
    There is one small glitch in the answer as `''=NULL` is not true but `'' IS NULL` is. ))) – GingerHead Jul 05 '17 at 09:52
  • @GingerHead Thanks for your comment. The `=` is not meant literally as the SQL-operator, but logically - as in *Oracle treats NULL and '' the same* – Erich Kitzmueller Jul 05 '17 at 09:55
  • 4
    Yes I know what you exactly meant, but I wanted to highlight sarcastically the curious notions of Oracle. – GingerHead Jul 05 '17 at 09:59
  • In multilingual application, I use '*' for True and '-' for False. On screen or on listing, '-' false value is sometimes replaced by ' ', so that only '*' True value are represented. – schlebe Aug 16 '20 at 08:29
  • The "best" thing I saw in this regard is `NUMBER(1)` used for boolean where `NULL` is true and `0` is false while `1` is randomly interpreted as true or false depending on who developed the code/sql in question... – GACy20 Mar 23 '22 at 12:03
  • 1
    @GACy20 It's obvious that the lack of a proper boolean type and also the ommision of a definitive recommendation how to substitute it eventually leads to all kinds of weird random implementations, like the one you are describing. Someone with a backgroup of shell scripting might be tempted to use 0 as `true` and every other numerical value as false, because that's how the `if` command in the Linux shells (e.g. bash) behaves. – Erich Kitzmueller Mar 23 '22 at 12:23
80

Nope.

Can use:

IS_COOL NUMBER(1,0)

1 - true
0 - false

--- enjoy Oracle

Or use char Y/N as described here

Bohdan
  • 16,531
  • 16
  • 74
  • 68
  • 7
    I prefer char(1) because it uses less space. You can check it this way: `create table testbool (boolc char(1), booln number(1)); insert into testbool values ('Y', 1 ); select dump(boolc), dump(booln) from testbool;` That CHAR is stored: `Typ=96 Len=1: 89` and that NUMBER: `Typ=2 Len=2: 193,2` At least in 12c, NUMBER(1) can use 2 bytes... – phil_w Apr 19 '16 at 17:29
  • 3
    Coming from a Java background, the JDBC specification of `ResultSet.getBoolean()` says: If the designated column has a datatype of CHAR or VARCHAR and contains a "0" or has a datatype of BIT, TINYINT, SMALLINT, INTEGER or BIGINT and contains a 0, a value of false is returned. If the designated column has a datatype of CHAR or VARCHAR and contains a "1" or has a datatype of BIT, TINYINT, SMALLINT, INTEGER or BIGINT and contains a 1, a value of true is returned. -- Based on this, I would recommend the `0/1` solution over `Y/N`. Even when using a CHAR column, it's better to use numbers. – Crusha K. Rool Apr 05 '22 at 15:24
  • @phil_w As an old C programmer, I prefer TRUE = 1 and FALSE = 0 and in logic statements, and anything non-zero is true. Number vs char is a speed vs size trade-off because numbers never have to go through codeset conversion and characters frequently do. – Chris Golledge Aug 18 '22 at 13:15
  • without check constraint this is unrealible decision. – Simon Logic Jun 22 '23 at 08:23
50

As per Ammoq and kupa's answers, We use number(1) with default of 0 and don't allow nulls.

here's an add column to demonstrate:

ALTER TABLE YourSchema.YourTable ADD (ColumnName NUMBER(1) DEFAULT 0 NOT NULL);

Hope this helps someone.

Alex Stephens
  • 3,017
  • 1
  • 36
  • 41
  • 19
    Note that you can store -1 in there also. You might add a check constraint on that to limit values to 0 and 1. – David Aldridge Sep 10 '15 at 07:54
  • 2
    @DavidAldridge In Boolean logic, any number that is not 0 (FALSE) is equivalent to 1 (TRUE), so it doesn't matter what number is stored, voiding the need for a check constraint. Adding a function that returns a Boolean from an int is trivial: `boolean intToBool(int in) { return (in != 0); }` – Agi Hammerthief Jul 17 '18 at 13:59
  • 6
    @AgiHammerthief True, but if you want to find rows using a predicate on the "boolean" column I would rather know that my options are `ColumnName = 0` or `ColumnName = 1`, rather than `ColumnName = 0` or `ColumnName <> 0`. The semantics of last one are not programmer friendly. I would also want to keep it more simple for the query optimiser by having two value. – David Aldridge Jul 17 '18 at 21:30
  • To not support normal true/false boolean value in database is such stupid decision :/ Whole IT is build upon booleans. We need to store some data from programs - oh wait, what about not supporting boolean in a normal way. It will be fun! xDD – JsonKody Nov 08 '22 at 20:00
20

No, there isn't a boolean type in Oracle Database, but you can do this way:

You can put a check constraint on a column.

If your table hasn't a check column, you can add it:

ALTER TABLE table_name
ADD column_name_check char(1) DEFAULT '1';

When you add a register, by default this column get 1.

Here you put a check that limit the column value, just only put 1 or 0

ALTER TABLE table_name ADD
CONSTRAINT name_constraint 
column_name_check (ONOFF in ( '1', '0' ));
Roberto Góes
  • 778
  • 1
  • 13
  • 20
15

Not at the SQL level and that's a pity There is one in PLSQL though

vc 74
  • 37,131
  • 7
  • 73
  • 89
9

No there doesn't exist type boolean,but instead of this you can you 1/0(type number),or 'Y'/'N'(type char),or 'true'/'false' (type varchar2).

kupa
  • 1,861
  • 5
  • 25
  • 41
6

There is a boolean type for use in pl/sql, but none that can be used as the data type of a column.

Klaus Byskov Pedersen
  • 117,245
  • 29
  • 183
  • 222
6

If you are using Java with Hibernate then using NUMBER(1,0) is the best approach. As you can see in here, this value is automatically translated to Boolean by Hibernate.

Saša
  • 4,416
  • 1
  • 27
  • 41
damndemon
  • 331
  • 3
  • 11
3

A common space-saving trick is storing boolean values as an Oracle CHAR, rather than NUMBER:

Pranay Rana
  • 175,020
  • 35
  • 237
  • 263
  • 4
    CHAR(1) and VARCHAR2(1) are identical in space usage. – Tony Andrews Sep 16 '10 at 13:13
  • 3
    As I learned here https://docs.oracle.com/cd/E17952_01/refman-5.5-en/char.html when we story one char there exists difference only between char and varchar - char uses 1 byte, but varchar uses 1 byte for empty space + 1 byte for one character -> varchar(varchar2) uses 2 bytes for 1 character< when char uses only 1 byte – Artem.Borysov Jul 23 '15 at 17:04
  • 2
    @Artem.Borysov: that manual is for MySQL, not for the Oracle database –  May 13 '20 at 20:24
3

Just because nobody mentioned it yet: using RAW(1) also seems common practice.

Filburt
  • 17,626
  • 12
  • 64
  • 115
  • 1
    raw(1) is great, in that the user cant assume what is in it, the person doing the query has to understand what is in the raw(1) column and translate it in to something meaningful. – Jay Jan 22 '14 at 01:39
  • 15
    Yes it's so great that you can't write portable jdbc code with it. – chubbsondubs Aug 31 '14 at 16:12
  • 1
    @jacob - That's an amazing idea! We should get rid of all other data types and store everything in RAW columns! Then NOBODY could arbitrarily misinterpret the data! – Bob Jarvis - Слава Україні Aug 30 '19 at 17:30
  • 1
    Imagine if there was some way in oracle to define data types so that we could create a bool type that wraps the ‘raw(1)’ type naming it bool or boolean. We could then define a function to print ‘true’ or ‘false ‘ depending on the contents. – Jay Aug 31 '19 at 22:37
-3
DECLARE
error_flag  BOOLEAN := false;
BEGIN

error_flag := true;
--error_flag := 13;--expression is of wrong type

  IF error_flag THEN 

UPDATE table_a SET id= 8 WHERE id = 1;

END IF;
END;
zloctb
  • 10,592
  • 8
  • 70
  • 89
  • This example works. I also noticed that I can only work with boolean types within PL/SQL. Boolean calls within SQL does not, and yields an invalid relational operator error. – Richard Pascual Jan 20 '16 at 18:30