Coder Perfect

Is a boolean type available in Oracle databases?


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


Can use:


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