354

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

Improve this question
18
  • 9
    Unfortunately Oracle does not fully support the ANSI SQL:1999 standard (en.wikipedia.org/wiki/SQL:1999) when this was introduced. Commented Sep 17, 2010 at 4:36
  • 2
    Alternative viewpoint (why SQL shouldn't have a boolean type): vadimtropashko.wordpress.com/2010/09/16/… Commented Sep 17, 2010 at 4:48
  • 21
    @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." Commented Jan 22, 2014 at 1:37
  • 4
    @JeffreyKemp Congratulations, you've just reinvented C-style booleans (where you're using ints 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. Commented Apr 21, 2015 at 19:26
  • 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 :) Commented Apr 29, 2015 at 22:59

12 Answers 12

Reset to default
378

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.

Improve this answer
Sign up to request clarification or add additional context in comments.

21 Comments

1/0 is, if not ambiguous, at least less ambiguous.
But ''=NULL is false! '' IS NULL is true. :)
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)
@Irfy Recently, I saw N and F being used, because ON and OFF begin with the same letter...
one might argue that as a replacement for a boolean, 'T'/'F' makes even more sense
|
83

Nope.

Can use:

IS_COOL NUMBER(1,0) 1 - true 0 - false

--- enjoy Oracle

Or use char Y/N as described here

Improve this answer

5 Comments

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...
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.
@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.
without check constraint this is unrealible decision.
I just saw this. I think there is another thread on the same question, where I answered it providing same answer, just with wider context with all alternatives: stackoverflow.com/a/79555398/17329826
53

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.

Improve this answer

4 Comments

Note that you can store -1 in there also. You might add a check constraint on that to limit values to 0 and 1.
@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); }
@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.
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
22

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' ));
Improve this answer

Comments

15

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

Improve this answer

Comments

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

Improve this answer

Comments

6

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

Improve this answer

Comments

5

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

Improve this answer

3 Comments

CHAR(1) and VARCHAR2(1) are identical in space usage.
As I learned here 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: that manual is for MySQL, not for the Oracle database
5

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.

Improve this answer

Comments

3

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

Improve this answer

4 Comments

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.
<sarcasm>Yes it's so great that you can't write portable jdbc code with it.</sarcasm>
@jacob - <sarcasm> 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!</sarcasm>
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.
0

Short answer: to represent Boolean in Oracle, use data type NUMBER(1,0) where 0 represents False and 1 represents True

Long answer: when there are many possible representations or implementations of something, one should choose the one that is (1) most widely adopted, (2) simple, and (3) clean. This is why NUMBER, in my view, is better even than Boolean, introduced in Oracle 23. I have provided detailed explanation here, because there exists another, duplicate thread on the same topic:
https://stackoverflow.com/a/79555398/17329826

Improve this answer

Comments

-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;
Improve this answer

1 Comment

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.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.