Problem
Is there a Boolean type equivalent to the BIT datatype in Microsoft SQL Server in Oracle databases?
Asked by Peder
Solution #1
Not only does Oracle’s SQL (not PL/SQL) lack the boolean datatype, but they also don’t have a clear recommendation for what to use instead. Take a look at this thread on asktom. When someone points out that CHAR(1) ‘Y’/’N’ depends on the English language, whereas German programmers might use ‘J’/’N’ instead, they switch to NUMBER(1) 0/1.
Worse, they defend this foolish decision in the same way as they defend the “=NULL ignorance.
Answered by Erich Kitzmueller
Solution #2
Nope.
Can use:
IS_COOL NUMBER(1,0)
1 - true
0 - false
— enjoy Oracle
Or use char Y/N as described here
Answered by Bohdan
Solution #3
We use number(1) with a default of 0 and don’t allow nulls, according to Ammoq and kupa’s responses.
Here’s an example of an add column:
ALTER TABLE YourSchema.YourTable ADD (ColumnName NUMBER(1) DEFAULT 0 NOT NULL);
I hope this information is useful to someone.
Answered by Alex Stephens
Solution #4
No, Oracle Database doesn’t have a boolean type, but you can do it this way:
A check constraint can be applied to a column.
You can add a check column to your table if it doesn’t already exist:
ALTER TABLE table_name
ADD column_name_check char(1) DEFAULT '1';This column gets 1 by default when you add a register.
You can only write 1 or 0 in this box to limit the column value.
ALTER TABLE table_name ADD
CONSTRAINT name_constraint
column_name_check (ONOFF in ( '1', '0' ));Answered by Roberto Góes
Solution #5
Unfortunately, not at the SQL level. However, there is one in PLSQL.
Answered by vc 74
Post is based on https://stackoverflow.com/questions/3726758/is-there-any-boolean-type-in-oracle-databases