Is there any boolean type in Oracle databases?

291
votes

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

11
sqldatabaseoraclebooleansqldatatypes
Unfortunately Oracle does not fully support the ANSI SQL:1999 standard (en.wikipedia.org/wiki/SQL:1999) when this was introduced. - Jeffrey Kemp
Alternative viewpoint (why SQL shouldn't have a boolean type): vadimtropashko.wordpress.com/2010/09/16/… - Jeffrey Kemp
@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
@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. - jpmc26
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

11 Answers

322
votes

Not only is the boolean datatype missing in Oracle's SQL (not PL/SQL), but they also have 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.

70
votes

Nope.

Can use:

IS_COOL NUMBER(1,0)

1 - true
0 - false

--- enjoy Oracle

Or use char Y/N as described here

43
votes

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.

18
votes

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' ));
15
votes

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

9
votes

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

5
votes

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

3
votes

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

3
votes

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

3
votes

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.

-1
votes
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;