[sql] Is there any boolean type in Oracle databases?

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

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

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.

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.

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

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.

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

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

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;

Nope.

Can use:

IS_COOL NUMBER(1,0)

1 - true
0 - false

--- enjoy Oracle

Or use char Y/N as described here

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

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